Searchable Spreadsheet Now Available for Weekend Playing Around

Below you will find a link to a Google Sheet.

We have added cumulative/non cumulative as well as qualified/non-qualified to the sheet since we last gave you a peek at this sheet.

We are calling this sheet Version 12132019–obviously because that is todays date. There are 672 issues on the sheet and should include most all $25/share preferreds and baby bonds as well as some miscellaneous. There are some convertibles not here, nor illiquids as we can’t get reliable quotes on some of these issues.

TO USE THIS SHEET YOU NEED TO HAVE A GOOGLE ACCOUNT SO THAT WHEN YOU OPEN THE LINK BELOW IT WILL TRANSFER TO YOUR ACCOUNT.

YOU MUST THEN RENAME THE SHEET.

IF YOU DO NOT COPY AND RENAME YOU WILL BE USING THE MAIN MASTER SHEET. UNFORTUNATELY IF A BUNCH OF FOLKS DO THIS YOU WILL FIGHTING EACH OTHER (I.E. YOU DO A SORT–THEN SOMEONE ELSE DOES A DIFFERENT SORT–THEN A 3RD PERSON DOES ANOTHER–ALL AT THE SAME TIME–YOU WILL BE SEEING RESULTS OF OTHER FOLKS).

I am already working on the next VERSION, which I should have in a month. This means that a few new issues will be missing as I add a number of them every week (and I delete called issues).

Be aware that this spreadsheet has 3 separate sheets. I have ‘hidden’ 2 of them. The one showing is where you do the filtering parameters. If you want to make your own changes, after you copy the sheet, you can do so by going to View, Hidden Sheets, Alpha. The Alpha page is where you can make changes. I would strongly suggest not changing the sheet titled ‘list’.

If you try to make changes on the filter sheet it will break–just ‘undo’ to fix it.

Unless you are familiar with scripting etc and you fool around and break it bad–no problem, just come back and get a new copy.

Here is the sheet. Have fun.

67 thoughts on “Searchable Spreadsheet Now Available for Weekend Playing Around”

  1. Can someone please explain to me why rilyp YTW is -2.36% when it’s yield is 6.875% and it’s not callable to 2024 and its current price is $25.59. i have read the various descriptions of ytw but i can’t figure out how -2.36%. is achieved. Clearly i don’t understand ytw as well as i thought.

    1. Libero,
      I’m showing ~6.3% YTW so I think that whatever you’re looking at might be suspect. A negative YTW doesn’t make much sense to me in this case.

      1. A4I – He’s looking at Tim’s searchable spreadsheet which for whatever reason, I’ve not been able to fully load on any browser…. I suspect it might have to do with my Hughesnet and it’s lousy speeds .

        1. Yeah, I suspected from where he posted the comment but I know a lot of folks here use different calculators. I’ll see if I can email you a copy of the spreadsheet.

          1. Yes, there are other questionable YTWs that I’ve come across. Must start sending them to Errors and Ommissions because trying to figure them out makes my brain melt and run out my ears.

    2. That RILYP YTW of -2.36% looks like an error in the spreadsheet. The cell formula look wrong to me. Also wrong for RILYN.

      1. Thanks guys, i thought i was missing something but there are a few ytw in Tim’s spreadsheet that don’t make sense.

  2. Tim, I only see preferreds on lines 5 through 72 on the list–what am I missing? Thanks.

    edit: never mind, made a copy and there they were.

  3. That’s really useful. It’s the first public/free listing of such that I’ve ever seen!!

    OK So I don’t know much about spread sheets. Can I manipulate the a column…for instance click on YTC then stack the column from highest to low/or lowest to high?

    If so how?

    1. I’m having difficulty sorting by column after I saved the file to a local Excel format. Obiously, I’m not doing it properly.

      1. Mike, I ‘think’ that you have to keep it in Google-docs format in order to keep all of the sorting features intact. I also had issues when trying to move it into Excel. I’m good with Excel, but not an expert so maybe I too am goofing it up.

      2. mikeo–as others have mentioned I don’t think it will work in excel. I have excel on my computer but have never tried to use it for the site, because embedding excel sheets with live updating data doesn’t work last I knew.

        1. Tim – I’ve been using live update in excel for a year or so. Not perfect, but it get’s an overall “A”. Will be happy to provide more info if you want it.

    2. If you Prefer—your particular example isn’t possible at this moment. You can do multi sorts on columns c to g and then also N.

      I have the next version partially done so may add more sort columns.

      The best way to learn the sheet is to do a bunch of playing around–it is very nice.

  4. Thanks for sharing this powerful tool. Much gratitude to you Tim and Chad.

    I hope this doesn’t sound too lame but it would be great if you could share your take on the various pref metrics, like YTW, YTC, YTM, etc. I look up their definitions/formula but how to put them in context when it comes to evaluating a given security. e.g. do you pass on a security if its YTW is negative? TIA.

    1. JR This is the raw data. It’s not a list of must owns, much less a list of what to buy at what price. In other words it’s a starting point.

      What it is good for is comparative analysis and finding some you’ve never considered or forgot about.

    2. For negative or low YTC you have to estimate the odds of a call. That’s hard to do so many people just avoid negative YTW. For low YTC I just assume call is likely and if it’s not called that’s a bonus. Selling before call may give you a slightly higher APY yield.

    3. JayR. You’re welcome. I will share some thoughts on the YTC, YTW etc, but in the end those topics have no right or wrong answer as to which is best to use etc. as each of us is different in our approaches.

  5. In looking at the sheet, I see some that show IG rating as “N” and yet, there is a Moody’s and S&P rating. For example – Zions Bancorporation (BB- / Ba2), Webster Financial, Northern Trust – just to name a few. I thought that by having a rating, that, by definition, meant it was IG. In my newbie status in preferreds, I’m sure I’m missing something.

    BTW Tim, what an excellent tool. I can’t imagine the hours this must’ve taken to get to this point. Thank you for all your diligence in the moderation and upkeep of this site. You are truly a gift to all who frequent here. Also, very appreciative of all the folks on here adding their wisdom and points of view as well. I do not always come full circle when I ask a question, but rest assured, I do read the replies and appreciate the insight.

    Thanks again Tim. Hoping everyone has a safe and joyous holiday season.

    Mark

    1. Mark,

      You might find this link helpful: https://en.wikipedia.org/wiki/Bond_credit_rating

      It includes a chart which lists and equates Moody’s, S&P and Fitch ratings and delineates Investment Grade v Non-Investment Grade. You should be able to screen shot this for future reference.

      Also – though technically investment grade, one needs to be careful around the BBB- space. As many funds are required to hold only IG-rated issues, the downgrade of an issue from BBB- to BB+ could be costly due to forced selling if a large constituency of holders are funds. Counter-intuitively, at least for the short-term in an issue rated BBB- with a negative watch, one might actually be safer price-wise in a BB+ than a BBB-, though I am definitely not recommending BB+ as an alternative to IG.

      There was an article some months ago though cannot recall where, that detailed the oversized confluence of issues sitting on the BBB- precipice. A downturn in the economy therefore might result in a sudden downgrading of a great number of securities in a relatively short amount of time. That could get ugly faster than most investors would react.

      1. Alpha8, Thank you. I always assumed that since it had a rating, at least the A’s and B’s, that it was IG. The chart that shows the historic default rates is quite telling. Between BBB and BB, there is a 3 – 4+ times occurrence of default, depending on rating agency. It would seem that S&P is a little more loose in their ratings as opposed to Moody’s as they show a significantly higher rate of default across most all ratings categories.

        Regarding unrated (NR) securities, should it be assumed that they are even below the C rating, or is it possible that some would be very unlikely to default? For example, I have seen a number of comments on VER-F where people are very interested in their shares even though it is NR. I know everyone has different investing styles / goals…. I suppose this could turn into quite a long discussion. Maybe I could summarize…

        In the event of a downturn, higher rated issues would hold their value better, and lower rated / non rated will be much more volatile, but not necessarily default – although much higher risk of default?

        1. Mark, Yes the historic default rates are attention-getters. Now keep in mind those are “averages”. The last bunch of years has been kind to us and we’ve seen lower default rates than the historical norm. If we believe in reversion to mean – there’s some catching up to do here in that space. That underscores the importance of proper allocations.

          NR does not indicate an issue is not an investment grade equivalent. There are many reasons an issue may be unrated. Fees is one. Companies may have a better use for those funds than to pay for ratings. For these, we’ll need to visit the 10Ks and 10Qs or some other source to check out key items such as PE, forward PE, ROA, ROE, Debt/Equity. Some of my favorite holdings are NR and your referenced VER-F is among them. A 10-minute read on their financials and history would help you understand why they are popular in this crowd.

          You’re asking good questions. It’s important to have a balance in time spent evaluating return of equity as opposed to return on equity – meaning the risk factors need to be respected. Call risk is a recent prime example. Near every week Tim highlights a called issue, for the which the holders are taking a big-time haircut because they’re asleep at the wheel. IR risk on perpetuals as well as credit risk are other obvious examples.

          I like to arb between issues – trade one against the other based on short term fluctuations due to inefficiencies in the market – but just as frequently I’m trading on mis-priced risk, meaning trading for a higher rated issue offering the same yield. So yes, keeping return and risk both on the scales is critically important to long-term success.

          1. Alpha, 2WR, Thanks again for the excellent and informative replies. I’m still lacking a few pieces of the puzzle…. I’m sure this is elementary to most on here, but why do rising interest rates have a negative impact on BB and preferred issues? I would think that’s a good thing because new issues will have a higher coupon (interest rate) as well? Or are we getting into inflationary or other considerations now? Does it increase default risk? I’m still trying to wrap my head around all the discussion around REPO’s, other actions by the Fed, inflation, debt monetization, interest rates, negative interest rates, global implications of all of this, …., …..,

            What background do do many on here have? I’ve seen some talk about their careers in finance, illiguid muni bonds (2WR), and related fields. I’m definitely in the camp of “the more I know, the more I realize I don’t know” Is there some recommended reading that can help me gain a better understanding of all this and how to become better at investing my money? It seems like all the old rules don’t apply as they used to and we are in a re-writing phase, mostly by trial and error. Lots of talking heads out there, but most seem to be talking out the other end and no one really seems to know what is going on or what to expect. It’s like we are Columbus setting sail into the unknown. Only this time, the entire globe is setting sail for the edge of the universe. IT just keeps getting bigger and the consequences are much further reaching. I feel like we are on a snow cornice. The cornice is growing exponentially faster than we can shore up the foundation underneath and sooner or later it has to let go – and the longer it is falsely propped up, the bigger the avalanche…. Not trying to be a doomsdayer, but I guess I am to some degree. I’m just trying to gain a better understanding so that I can make informed decisions and not get caught with my pants down; and lose my shirt as well. (It’s cold here in the mountains.) (And I know, I’ve digressed sharply and this should be over on the sandbox page; or maybe should have just left it in my head….)

            One final question. Alpha, the haircuts you refer to when an issue is being called. If purchased at or near redemption value, I just don’t see the haircut. You’ve collected interest or dividends during the holding period so should be ahead overall. Is the concern when an issue is purchased at a significant premium, issue gets called, and then quickly moves back to redemption value? Or, is there a lot more to it than that?

            1. Mark,
              Rising rates cause prices to fall. They are said to work in the opposite direction of each other. You’re on the right track though as you have inflation and other factors coming into play, most importantly to me: higher rates mean that businesses pull back on spending, begin to signal a correction/recession, which causes lending to crater, and then supply and demand take over.

              Here is a primer you might find VERY helpful…It covers a lot of information and terms you hear written about here on the site:

              https://www.merrilledge.com/publish/content/application/pdf/gwmol/FixedIncomeDigest-PrimeronBondInvesting.pdf

              The haircut question is interesting but a little complicated to answer. Some here, play a ‘dividend capture’ strategy – meaning, they buy as the divvy ex-date approaches to capture the divvy and then dump soon thereafter. Get your timing wrong and you’re screwed. Others buy after a call has been announced – trying to play a little arbitrage off the spread and make a few pennies on each share in large volumes. Again, get your timing wrong and you’re screwed. Others just simply stare the call in the face and play a game of chicken. If the call comes, they take a face plant. If not, they collect the divvy and live to fight until the next expected divvy and call threat square off.

              Don’t feel like this is overwhelming because it is – even to the seasoned ones amongst the group. Tim just made a comment the other day about having played this game for 48yrs and he’s still learning new tricks. We all are. That’s why we are all so thankful to have an open forum where we can maintain civility, eliminate politics, and focus on the business at hand.

              1. Mark,
                And don’t forget the Education section here on the site. You might find some useful info there, too.

                https://innovativeincomeinvestor.com/income-securities-education-items-and-articles/

                But no matter what you research, please check out the esteemed Rida Moron that so many here admire and adore…He also has an extensive primer to compliment the amazing (sigh) picks he writes about

                https://seekingalpha.com/article/4263526-understanding-preferred-stocks-primer

            2. Mark – I hope you don’t feel alone is asking your questions because they’re really basic but not well understood by many. I remember trying to explain your question when my doctor asked the same question… This is what I told him: When you own a bond or a fixed rate instrument two things are fixed, the interest rate and the maturity (or lack of one if you’re talking about perpetuals), What’s not fixed is the price. So let’s assume you have purchased a 5% $25 baby bond at its original $25 price…. Some time later, prevailing interest rates have gone up to 5 1/4% but you now want to sell your bond… How are you going to entice someone to buy yours when they can now buy new issues at 5 1/4%? You can’t change your coupon, you can’t change your maturity but you can offer to sell yours at a cheaper price than $25 to make it competitive with the new issue… That’s all you can do… Hence, down goes the value of your $25 5% issue when interest rates go up.. Hang in there, Mark…. It’s great you’re willing to ask these questions, Don’t feel intimated if you discover some things that might make you feel there’s more to this game that you thought… It’ll come..

              1. Wow! Excellent everybody! Thanks for all the help in understanding all of this. I feel like a light clicked on momentarily. A4I, I’ll definitely check out the links. Everyone on here is so generous. I feel fortunate to have found this site. Thanks Tim for all your awesome work, and for creating this space.

            3. Mark, In response to your question about haircuts – your assumption of purchasing near redemption value and simply holding to maturity is perfectly valid. That plan is best executed with a term-dated security; issues like EP-C, HCXY, AEFC or INKBZ (all of which are in my hold-onto bucket).

              However, the haircuts occur when issues are called – on or after the 1st potential call date – and the price is considerably over redemption value. If you were fortunate enough to buy at redemption value, collect all the divvies, and the price has risen well-above redemption value and then the issue is past-call – then that cap gain over redemption value is at risk. Many here are experts at managing that risk, but ultimately, holding past call issues at a price far from par can lead to unexpected consequences.

              To manage that risk, pay attention to YTC (yield to call). YTC provides you with a single metric which incorporates the coupon, current price, redemption price and term to call all wrapped up in one number. Many newer investors (Rida readers come to mind) are lured in by the coupon rate or the current yield. The YTC provides a much clearer overall picture of the expectations of return for the purchase. Be sure to know and understand the YTC before any purchase. In a pinch, here’s a back of envelope way to calculate an approximation of the YTC. 1) Current price minus the redemption price 2) Divide that number by the number of years to call 3) Subtract that number from the annual dividend. 4) Divide the remaining dividend by the redemption price.

              Here’s an example: NEE-N. Current price: 27.73, redemption price: 25, annual divvy: 1.41, call date 06.15.2024. (27.73-25)/4.5 yrs=$0.61, $1.41-$0.61=$0.8, $0.80/25=3.2%. YTC. Remember that’s a back of envelope calc. The actual YTC – to June 2024 – is 2.90% as the issue just had a dividend last week. Hope this helps.

        2. Mark – I concur with Alpha, you’re asking great questions… One thing about NR, as you learn more about what parameters are important to ratings quality, you’ll see there are possibilities in NR rated issues because on the surface, a AAA quality company could just as well be NR as a C quality one could though obviously a company that believes it’s AAA quality would be more likely to pay to get the rating if that’s the only reason why it’s NR. As an example, back in the 80’s when bond insurance was just coming into vogue, I was an institutional muni bond trader and I made a career out of uncovering older unknown, infrequently traded non-rated muni issues in the secondary market and knowing which ones I could take to the insurers to pay to have them insured and, therefore, become AAA rated….All you had to do was have a decent idea of what the insurers were looking for and you could greatly increase your abilities to choose issues where you’d not get turned down… Ah, another one of “those were the days” moments… The point is, learning in general what makes a difference to the rating agencies can help you gain a willingness to take on NR issues once you’ve looked them over a bit… NR does not necessarily mean a company is afraid to have their pwn debt rated..

    2. Hi Mark–thanks for the note. As happens many times questions are accidently proofreading.

      1st–no many issues are rated but are NOT investment grade. So happens that Northern Trust and Webster ARE investment grade and I corrected those–I always say these things are 97% correct, but I can guarantee some errors since almost all this data is entered manually by me and I miss a few things.

      2nd–the companies rated are mostly investment grade, but many are not. Those companys that are rate are angling for a better interest rate at issuance since technically those not rated are labeled ‘junk’ or ‘speculative’ by many. I will add a page with the credit ratings definitions on them. You can set up a FREE account with S&P, Moodys and Fitch which will let you access all the ratings.

      Glad to have you here–I learn so much from other people–and I am intrigued by the Canadian issue discussion, but haven’t had time to delve into it so far–but I will.

  6. Great job, Tim. I had been copying and pasting your Master List into Excel and then doing my own sorting on various parameters. This looks like it will be a lot easier; really appreciate your efforts. I’ve learned so much from you and the other contributors on this site. I thank you all!
    Ed

    1. EDINFL–glad to be of help when I can. I learn from everyone–even after 15 years of all preferreds and 48 years of investing.

  7. Great job Tim…is it correct to assume that if I make a copy of the master spreadsheet that my copy will not receive any updates you make to the master sheet, or does that some how trickle down to the other shared copies?

    1. Hi Citadel–no it will not receive them after copying–BUT my intention is to release a new version at least MONTHLY–but maybe more often.

      If this was just a typical spreadsheet–not sortable or editable by the user it would work to just have the spreadsheet in your google account and it would update automatically, BUT with everyone putting in their own search parameters etc they need to have their own copy because otherwise everyone will be working off the same sheet–i.e. if they did a search you would see it and vice versa.

  8. Tim,
    One thing I like to do is freeze the pains so I can scroll down and see titles. This site and spreadsheets are a miracle, one more and I will nominate you for saint hood. Thank you and all your followers.

    JDC

    I see someone else had the same observation on freeze pain – disregard that portion of comment.

    1. JDC–thanks for you kind thoughts–not sure the Pope will go along with sainthood though.

      I had frozen the top 4 rows earlier–but I see someone removed the freeze–I just put it back in.

      Seems like we have some not reading instructions and are using the posted version–instead of taking a copy in their google account.

      If you take a copy then you may have to refreeze those top 4 rows.

  9. Thanks for the great tool. See if you can set the header to stay viewable when you scroll down through the issues.

  10. Very cool! I was curious for the illiquids, if you could replace market price with latest ask price. When I evaluate these types of issues, that’s typically what I’ll use to size them up. I have a very manual way of doing it though so I was curious if it could be built in to a Google sheet.

  11. Tim, thank you for this invaluable tool. It’s a huge help for researching this part of the fixed income universe. One question: how are you calculating YTW? I have been using a ytc (which should be the same as ytw on most?)calculator at this link:
    https://www.preferred-stock.com/calculator_ytc.php
    and i notice it differs slightly on the result.
    Any help or insight you can offer would be greatly appreciated.
    Thanks!

    1. Franklin–yes this formula doesn’t pick up the accrued portion of the dividend so YTW is a little better than what I state. I hope to add something like what you linked to on preferred-stock website (owned by Eagle Financial which I worked for previously and which bought my old Yield Hunter website).

      I will work on the YTW for future versions of spreadsheet.

      1. Tim, Beautiful sheet. I appreciate the time and effort that goes into this as I run my own which incorporates the same data. If you’re interested, I’ve built in YTC calcs and would be happy to share the format and cell by cell formulas to you and Chad. Just PM me via SA. Price data/changes are auto-populated to the sheet so the YTCs are current to the minute. At times it’s been darn valuable intraday/trade triggering intel.

        Thank you to Tim for your truly amazing site. Like others have said, so grateful for it and for our cast of characters and the combination of insightful, self-deprecating and hilarious comments.

      2. Tim, you can try this one (it’s for string #2)
        =IF(U2>0;(Q2*U2*4-J2+25)/J2/U2;(Q2-J2+25)/J2)
        but all cells in column U must be filled with formoula
        =(R2-TODAY())/365

        1. Hi Yuriy, This is a good general formula though plotted on a graph of YTC v time would be a straight line from today to call. This is what Tim is trying to get around. True YTC is irregular and event-sensitive, such as to the impact to YTC at and around ex-dates, which should be perfect but isn’t. For example, see recent graph of NEE-N and EP-C; not that one needed a tool to see those glaring opportunities.

          If you’re into it, run IRR tables and use some thing like this in the periodic $distribution. =IF(FH$18>=FH29, 0, FH$12/4) where 18 is current date, 29 is ex-date, and 12 is the annual dividend for each distribution and work the YTC off the cash to term. There are other variables such as the occasional mid-cycle call distributions to factor.

          Because this is not general discussion suggest we PM if we want to take it further.

          1. Hi, alfa8,
            I absolutely agree with you, but then we need at least to change the structure of the table to be able to extract from somewhere the payment date for each string.
            My formula sure is as simplified as possible, but I just wanted to do something close to real YTC without the need to make changes to the table’s structure and add columns.
            P.S. Writing at public chat because it seems that there is no PM system )))

  12. It is very useful for me to search the target. Can you add one column to separate traditional preferred stock/ETD….etc.?
    Any way, thanks a lot

  13. Great tool, Tim.
    I’m new at Prefs, but for sure feeling more and more confident using your pages and paying attention at what others have to say.

    1. j-mad–thanks. I have been exclusively preferreds for 15 years and learn a lot from others–learning nevere ceases.

      1. Tim, the learning part, I have no problem with. But the ability to remember what I learned is what I struggle with! So that is why I largely stay on the same old investing dirt road.

        1. Grid–I can relate. The spreadsheet works really well for those still developing their investment style. For you – you are hopelessly lost to the world of illiquids and arcane issues which in and of itself is great for many of us to learn from.

          1. Tim, I try, but I spent too many years having my secretary do spreadsheets for me. I wasted my learning years on avoiding technology and focusing on getting people to do that stuff for me. I made that bed, and now I am laying in it, ha.

  14. This is great Tim. The QDI column is a wonderful timesaver.

    Thanks and have a great weekend.

Leave a Reply

Your email address will not be published. Required fields are marked *