A Bond/CD Ladder Toolkit

A while ago I created a little toolkit to design my own bond and/or CD ladder. With a bond/CD ladder, by the way, I mean holding a portfolio of bonds and/or CDs so the cash flows comprised of maturing CDs/bonds plus interest income exactly matches a specified time series of target cash flows over time. Of course, if you’re regular readers of this blog you know that I’m not too thrilled about investing in bonds or CDs. Yields are just too low, compliments of my former colleagues at the Federal Reserve. But there are still a few interesting applications for bond/CD ladders. Some of them purely for “academic curiosity” and not because I actually want to implement them. So, here would be a few questions I’d try to answer with this toolkit:

  • If I wanted to build my own little “quasi-annuity” to guarantee a certain cash flow for 30 years or so, how much money would I have to set aside today to guarantee that cash flow? This is obviously not because I actually want to do this. As we will see later, the cash outlay today would be so prohibitively high that I’ll prefer to take my chances with the stock market and the Sequence Risk that comes with it!
  • How much of a difference would it make if I required regular cost-of-living adjustments (COLA) along the way rather than getting one fixed nominal “income” along the way?
  • Related to the issue above, if someone already has a corporate pension without COLA, how much would he or she have to set aside to supplement this pension and guarantee a certain COLA, say, 2% a year?
  • When interest rates were higher only a few decades ago, how feasible would it have been to create a bond ladder for retirement? Could we have gotten rid of Sequence Risk completely?
  • If I wanted to hedge my first 5-10 years of early retirement against Sequence Risk and at least partially fund my retirement expenses through a CD ladder, how much money would I have to set aside?

So, today I’ll share that spreadsheet and some of my sample calculations and results. I hope you’ll find value in playing around with it, too…

First, the link to the toolkit:

—> Click here to go to the Google Sheet <—

As always and for obvious reasons, you cannot edit this (clean) Google Sheet. So, please create your own copy first!

BondCDLadderToolkit Pic11
Please save your own copy. You can’t edit my “clean” copy posted on the web, for obvious reasons!

How to use this sheet


  • Your cash flow needs: The easiest way to get started is to pin down your cash flow needs through just three parameters: the number of years, an initial flow, and a cost of living adjustment (COLA) parameter, i.e., by what percentage we grow the cash flow needs each year. Keep that COLA parameter at 0% if you want to keep the nominal cash flows constant. Notice that the initial cash flow is paid upfront, so if you plan for 30 cash flows this exercise will go through years 0, 1, 2, … and all the way to 29.
  • You can also input your interest rates. As a default setting you can enter the rates for bonds/CDs with maturities of 1, 2, 3, 5, 7, 10, 20 and 30 years (conveniently, these are bond maturity dates for which the Federal Reserve reports daily estimates for Treasury yields in its H.15 data table (Selected Interest Rates). As a default setting, the interest rates in between are interpolated linearly.
BondCDLadderToolkit Pic01
Main parameters: Cash flow needs and selected bond interest rates

If this is too simplistic then you can also just override the cash flow needs generated by the three parameters and/or the interest rates and hard-code whatever you deem necessary:

  • Cells B28 to B58 for the cash flows.
  • Cells I25 to AL25 for the interest rates at maturities 1-30.
BondCDLadderToolkit Pic02
Cash flow needs are calculated in column B. But feel free to override the formulas and use your own numbers.
BondCDLadderToolkit Pic03
And likewise, with the interest rates: Feel free to override the numbers and enter your own estimates.

Also, before getting too far, here are a few limitations of this approach:

  1. There is no consideration for taxes. I do report the composition of how much capital is flowing back vs. how much is interest income, though. See columns D and E, rows 28 through 58 to see how much of the cash flow is a return of capital (tax-free) and how much is interest (ordinary income and thus taxable if exceeding your federal/state deductions).
  2. I assume that all interest is paid at an annual frequency, just to keep the spreadsheet manageable. Most CDs and bonds obviously pay interest at a monthly/quarterly frequency.
  3. I assume that bonds are traded “at par.” This is the correct assumption for CDs but may not be 100% accurate for bonds if the coupon differs from the annualized yield.
  4. Your COLA estimate might differ wildly from the actual inflation rate. That could be because the actual realized CPI is very different from our inflation estimate today (most commonly 2%). You could also have a consumption basket very different from the average CPI basket with much higher or lower (felt) inflation.
  5. Check the results and make sure they are actually implementable. I will show you a “pathological” case where some CD/bond purchases could be negative! Unless you can borrow at that CD/bond rate there may not even be any CD/bond ladder that exactly matches your cash flows!

But with all of those limitations, let’s look at some actual examples.

Example 1: 30-year payout, no COLA, invest in U.S. Treasury Bonds

Here, I set the parameters to $40,000 initial cash flow, COLA parameter to 0% and the time horizon to 30 years. I also use the Treasury Yield values from the Federal Reserve website (Table H.15) as of 4/17/2019:

BondCDLadderToolkit Pic04
Example 1: $40k p.a. cash flow for 30 years, using the Treasury yields as of 4/17/2018. Source: Federal Reserve.

To sustain these 30 years worth of cash flows ($1.2m total), we’d need to invest a pretty large sum today: almost $827k! Also, notice the pattern of how much we’d have to set aside for each of the different bond maturities (see bar chart on the right side):

  • $40,000 for the initial payout. I assume that the year 0 we just set aside the money at the beginning of the year without earning any interest.
  • A little bit less than $40,000 for the final year 29. That’s because a $38,845 notional bond purchase plus the interest for the final year will exactly match the $40,000 cash flow requirement.
  • Successively lower notional investment because in year 28 you receive not just the notional back for that year but also the interest from the bonds maturing in years 29 and 28. And so on, all the way to year 1 when you receive not just your year 1 principal and interest but also the bond interest for all the other bonds maturing in years 2-29!
  • If you’re one of my fellow Excel-Super-Geeks, you can check out how I calculated this in columns H through AL, the notional investments in column 26 and the year 0-30 cash flows in rows 28-58 for each of the bonds/CDs. I make heavy use of the “OFFSET” function in Excel, one of my favorite Excel hacks! 🙂
BondCDLadderToolkit Pic05
You’d need over 20x the annual cash flow to guarantee the $40k p.a. cash flow!

So, just to sum up, I was shocked about how much money you’d have to set aside to guarantee those $40k for 30 years! But it gets even worse! Recall that we didn’t even account for any COLA. So, let’s change that and see how much money we’d have to set aside if also like to make 2% p.a. increases to the initial $40k withdrawal, which brings us to Example 2…

Example 2: same as example 1 but with 2% COLA

The only change I have to make is to set the COLA parameter to 2% in cell B6. Let’s see how this changes the results: You’ll now need about $1,070,000 to guarantee this cash flow. And just to be sure, the money will be completely exhausted after year 29! So, you don’t even get to a 4% initial withdrawal rate over 30 years with this method despite the total depletion of your capital! This is clearly not a workable solution for early retirees. And it may not be very palatable for a traditional retiree, either. You might just go for an annuity in that case. At least you hedge the “risk” of living longer than 30 years…

BondCDLadderToolkit Pic06
What a difference a 2% COLA makes! You’ll now need more than $1m to guarantee $40k plus COLA over 30 years investing purely with Treasury bonds!

Example 3: same as example 1, but raise the bond yields to December 1999 levels (the good ol’ days!)

What if we ever were to go back to more “normal” bond/CD interest rates? Just about 20 years ago when we had short-term interest rates above 5%, and longer-maturity bonds with 6%+ rates! If I feed in the December 1999 interest rates from the Federal Reserve Data Table H.15 we can now push the initial investment all the way down to about $557k. What a difference those 350bps higher interest rates make!

BondCDLadderToolkit Pic12
Higher interest rates (as 20 years ago) imply you’ll need a lot less investment to generate your $40k a year cash flow! Note

Also, just out of curiosity, if you were to also require a 2% COLA, you’d still only about $687k initial capital, see below!

BondCDLadderToolkit Pic13
In 1999, with the prevailing Treasury Bond yields back then, you could have guaranteed a 5.82% Safe Withdrawal Rate (initial withdrawal plus 2% COLA every year after that) for 30 years!

This is really astonishing! Let this sink in:

Back in the late 1990s, you could have fully guaranteed a 5.82% Safe Withdrawal Rate (!) over 30 years with 2% COLA with a simple zero risk government bond ladder.

This, by the way, is why I’m quite skeptical of both Bill Bengen’s work and the Trinity Study: Back when this research was initially done, the whole discussion about the 4% Rule was completely moot! You could have easily guaranteed a Safe Withdrawal Rate of almost 6%. Or, alternatively, use a 4% initial withdrawal rate plus 2% COLA for 30 years with a bond portfolio and have tons of money left over ($313k per $1m of initial net worth) to invest in an equity portfolio, which you would not have to touch for 30 years. That’s a lot of dough to have as an additional hedge against longevity past the 30 years and/or money to leave to your heirs and charity causes! That would probably have been enough even for a 60-year retirement if you use the bond portfolio for the first 30 years and then the equity portfolio plus growth for the next 30 years!

Today, of course, the bond ladder as a retirement strategy is no longer feasible. Interest rates are way too low. But that doesn’t make the Trinity Study any more useful. As I have outlined here on the blog, especially in the SWR Series, looking at the unconditional success probabilities of the 4% Rule, while not taking into account today’s lofty equity valuations and rock-bottom bond yields seems highly inappropriate.

But I digress! Let’s move on to another example…

Example 4: An 8-year payout, linearly phased out, with CDs

Suppose we have an early retiree concerned about Sequence Risk. To alleviate the risk of retiring right at the peak of the bull market, he/she wants to have $40,000 guaranteed cash flow from a CD ladder right now, $35,000 next year, then reducing the guaranteed income by $5,000 every year down to $5,000 in year 7 and nothing more after that. I’d now have to override the cash flow values in column B, rows 28-58 with the actual target values. The parameters in cells B5-B7 are now irrelevant!

BondCDLadderToolkit Pic10
A hedge against Sequence Risk: Cover $40k of expenses in the first year, but then slowly phase out the bond ladder, while funding more and more from the stock portfolio over time.

I also input my estimates for interest rates, CD rates of between 2.7% to 3.3% for the first 7 years. I’m sure if you shop around you might even get slightly better rates. In any case, according to our spreadsheet, we’d need just about $168,000. The total flows, of course, are exactly $180k comprised of $168k in principal and only just under $12k in interest income. Is it worth it do this as a hedge against Sequence Risk? You be the judge! If you simply sell $168k of your fixed-income portfolio over the first few years then this looks a lot like a glidepath (see part 19 and part 20 of the SWR Series). That certainly helps somewhat with Sequence Risk but it’s not really a panacea either as I showed in those posts a while ago!

BondCDLadderToolkit Pic10
Hedge a few years against Sequence Risk? That’s still going to cost a big chunk of money!

Example 5: Set aside enough money to turn a non-COLA pension into a 2% COLA pension

Another interesting example. Imagine you already have a corporate pension but it has zero COLA, in other words, it will stay constant in nominal dollars and will be slowly eroded away by inflation. Bummer! How much money would I have to set aside to turn this into a pension with 2% COLA? Let’s assume this is a $20,000/year pension and I like to protect it from inflation over a 30-year horizon. I’m using the Treasury bond interest rates but I’ll have to hack the cash flows to match exactly the difference between 20,000×1.02^t (=$20k plus 2% COLA) and the 20,000, see below:

BondCDLadderToolkit Pic09
Override the cash flow inputs. We need $0 in the initial year, but then a rapidly growing amount to hedge the impact of 2% p.a. inflation.

Again, for the interest rates, I use the Treasury constant maturity yields from the Federal Reserve. Now we get the following output, see below:

BondCDLadderToolkit Pic08
Uh-oh! We can’t perfectly hedge this cash flow need with a Bond ladder! This would call for “negative” bond holdings for years 1 through 8!

OK, what’s going on here? We’d need negative bond holdings (!!!) for years 1-8! This is the “pathological” case I mentioned above where you’ll essentially have to borrow money at the short horizon because you’ll have excess cash flow in the early years; it’s because you get a ton of interest income from bonds maturing later but you need very little money early on. So the figure of net $121,398.05 outlays is really just an incomplete estimate. Of course, there are (at least) three ways to deal with this:

  1. If you already have some other short-term savings (e.g., an emergency fund) you could certainly “borrow” from there.
  2. Reduce the purchases of bonds with maturity 10+ years and instead reinvest some of the excess cash flow received in years 1-8.
  3. Reduce the excess cash flow early on by shifting more money into zero-coupon or at least low-coupon bonds (if available) for the very long maturities.

In any case, if you have a pension with COLA (as is the case with many government pensions), consider yourself really, really lucky! Over the years, the typical non-COLA corporate pension falls behind substantially and it would take a big chunk of money to “hedge” against this depletion of purchasing power. Over 30 years, you’ll need to come up with a total of more than 10x the initial pension ($211k vs. $20k) to create a 2% COLA, and in today’s dollars, that’s still more than 6x ($121k vs. $20k)! Bummer!

OK, enough Excel-geeky stuff for today! I hope you enjoyed this post! Please share your thoughts and suggestions below. And please let me know if you find other applications for this toolkit!


20 thoughts on “A Bond/CD Ladder Toolkit

  1. Nice post for us Excel nerds. I fully agree that looking at SWR success probabilities while not taking into account today’s high equity valuations and low bond yields is dangerous.

  2. Very nice and precise AS USUAL. I guess I better learn to knit some hats to sell to keep up with these numbers.

  3. Great post, makes me want to take an advanced Excel class 😉 Maybe a dumb question, but for early retirees, could a 5%+ rate environment come back within the next 10 years and if so, I’d imagine some people would love to pivot to a higher fixed income allocation. In theory if that happened though, would equities draw down to the point where your nest egg was too small to support prolonged retirement income? Maybe some future generation will be able to retire on a low risk allocation. That 1990’s rate environment was maybe a sweet spot or maybe it cycles around every so often…..?

    1. Great question! The consensus in economics seems to be that we’ll go through a longer period of low interest rates, see this:
      and the more academic version:

      But past the 10 years, I definitely think that we might again go back to the Good Ol’ Days of higher nominal and real interest rates!

  4. Great, conservative analysis as always. Since we are discussing non-sexy investments, consider that EE Bonds when held for 20 years returns are automatically put to around 4% such that their value has doubled. For everything over 20 years out, this could positively and significantly impact the amount needed.

  5. Thank you ERN! I was working on calculating the cash/cash equiv balance needed for 60k a year for 5 years in a SEPP and I will definitely use your work to check my own numbers.

  6. Big Ern! This post really hit home for me, because I have been busy building myself an early retirement Glidepath that involves bond and CD ladders. Based largely on your SWR series and my own emotional need for a sense of safety with the CAPE ratio above 30, the plan is to move from 60% equities (currently) to 80% over the next decade. However, I would like to do this actively, based on whenever the next bear market arrives and the CAPE ratio comes down to more historically normal levels. Is there historical data to suggest an appropriate “CAPE-target” for when to make this shift assuming the CAPE does come down at some point in the next decade?
    Thanks for all your insight and inspiration!

    1. I wouldn’t bet on the CAPE going down that much. Maybe low-20s is the new normal for the CAPE. But I agree that the CD/bond ladder looks a lot like the glidepath if you consume the CD flows and let the equity % increase over time. And you might phase out the CD ladder right around the time when the CAPE is back to a (new) normal of 22 or so again.
      So, that looks like a great plan! 🙂

      1. Thanks man — I’ll feel confident with anything in the low 20s, and 22 seems like a perfect provisional target. I truly appreciate your insight — it has helped me a great deal.

      2. This is a really nice tool to setup a CD ladder. One question relating to glide paths. Most of the optimum strategies look like that take you to 100% equities, which makes sense as they produce the highest returns. However, what happens if the market pulls back sharply shortly after you get to 100% equities. The nice option of having a bond or CD position is you still have money on the side to buy-in after a drop. It’s a similar discussion point with the dollar cost averaging vs. maximizing your time in the market debate.

        1. That is certainly a risk. But that means you would have escaped the big bear market early in your retirement and you escaped the worst part of Sequence Risk. So, if the bear market strikes so far into your retirement (10 years or so) then you’re OK anyways.
          But again: you’re right, the glidepath will not work better in every scenario. It only works better when the plain old 4% would have failed due to SoRR early on!!!
          Think of the GP like insurance. A bad investment if nothing happens! 🙂

  7. When it comes to glidepaths, bond laders and SWR, I think that this work here (although US focused) gives a very easy to understand way to produce a DIY low risk FIRE path – which probably has much lower costs than paying someone else for the privilege of looking after your money for you.
    Thanks for sharing!

    1. All right! Thanks for the compliment. Glad you found this useful!
      Also, please feel free to enter bond yields in other countries. Much lower rates in Europe (the German 10y Bund just went below 0% again!!!). And mich higher rates in Australia! The math is the same. The numerical results will be different! 🙂

  8. The CD/bond ladder tool would work well with the retirement plan outlined by the Oblivious Investor here: https://obliviousinvestor.com/an-ideal-retirement-spending-strategy/

    I like this plan because one can be sure of what funds will come available off the ladder each year regardless of what equities are doing (and those funds can be certain in real terms if the ladder rungs are TIPS or I bonds). But the portfolio outside the ladder to SS claiming age can and probably should be equity heavy, given the conservative nature of the ladder plus SS claiming at 70. I think the equity-heavy RMD portfolio aligns with what big ERN prefers for longer retirements.

    This plan results in a rising equity glide path. The potential return drag of a bond-heavy portfolio early in retirement can be considered an insurance premium to dodge some sequence risk. Could be worthwhile for some with wavering risk-tolerance. I know my own risk tolerance declined substantially when I cut off my human capital.

  9. I am going to ask a really dumb question: What if you have four grown kids, in their late 20’s to early 30’s, and you have $400,000 you want to put into four separate trusts ($100,000 a piece) and you want this to start paying them $15,000 a year in thirty years ( in the year 2049). Lets say you are extremely risk adverse. What kind of bonds/CD’s or (gulp) perhaps funds such as Vanguard would you suggest?

  10. I should also say, I would be, if my health holds up and I am able to work for ten more years, until I am almost 70, adding $400 a month ($100 to each trust fund by way of cd/treasury bond, or (gulp Vanguard).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.