Spreadsheet for building bond ladders

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
guppyguy
Posts: 328
Joined: Tue Jan 30, 2018 4:24 pm

Spreadsheet for building bond ladders

Post by guppyguy »

Before I try to roll my own, is there a good spreadsheet anywhere here for building/tracking laddered fixed income products?
Ideally it would be a rolling ladder with the ability to track US Treasuries and CDs. I just KNOW somebody here must have already made what I'm looking for ;)

Thank you in advance...

(I have already found a good spreadsheet for I-Bonds and TIPS Laddering)
User avatar
Kevin M
Posts: 13920
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for building bond ladders

Post by Kevin M »

guppyguy wrote: Sat Nov 12, 2022 1:06 pm Before I try to roll my own, is there a good spreadsheet anywhere here for building/tracking laddered fixed income products?
Ideally it would be a rolling ladder with the ability to track US Treasuries and CDs. I just KNOW somebody here must have already made what I'm looking for ;)

Thank you in advance...

(I have already found a good spreadsheet for I-Bonds and TIPS Laddering)
I have a spreadsheet I use for tracking Treasury trades (purchase, sale, maturity). I end up with ladders of sorts, but I don't specifically aim to build a ladder.

What kind of functionality are you looking for?
If I make a calculation error, #Cruncher probably will let me know.
Topic Author
guppyguy
Posts: 328
Joined: Tue Jan 30, 2018 4:24 pm

Re: Spreadsheet for building bond ladders

Post by guppyguy »

Kevin M wrote: Tue Nov 22, 2022 8:46 pm
guppyguy wrote: Sat Nov 12, 2022 1:06 pm Before I try to roll my own, is there a good spreadsheet anywhere here for building/tracking laddered fixed income products?
Ideally it would be a rolling ladder with the ability to track US Treasuries and CDs. I just KNOW somebody here must have already made what I'm looking for ;)

Thank you in advance...

(I have already found a good spreadsheet for I-Bonds and TIPS Laddering)
I have a spreadsheet I use for tracking Treasury trades (purchase, sale, maturity). I end up with ladders of sorts, but I don't specifically aim to build a ladder.

What kind of functionality are you looking for?
To be honest I'm not really sure what is most important to track. I'd like for it to calculate YTM/maturity/duration for each individual bond and as a portfolio. It would be neat if it included TIPS. Maybe CUSIP lookup?? Hope that makes sense.

Thanks for the assist!
User avatar
Peculiar_Investor
Posts: 2118
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB 🇨🇦
Contact:

Re: Spreadsheet for building bond ladders

Post by Peculiar_Investor »

You might check out Additional Tools | The Flexible Retirement Planner, particularly their Bond Portfolio Spreadsheet. I've used it for years.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
Kevin M
Posts: 13920
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for building bond ladders

Post by Kevin M »

guppyguy wrote: Wed Nov 23, 2022 3:55 pm
Kevin M wrote: Tue Nov 22, 2022 8:46 pm
guppyguy wrote: Sat Nov 12, 2022 1:06 pm Before I try to roll my own, is there a good spreadsheet anywhere here for building/tracking laddered fixed income products?
Ideally it would be a rolling ladder with the ability to track US Treasuries and CDs. I just KNOW somebody here must have already made what I'm looking for ;)

Thank you in advance...

(I have already found a good spreadsheet for I-Bonds and TIPS Laddering)
I have a spreadsheet I use for tracking Treasury trades (purchase, sale, maturity). I end up with ladders of sorts, but I don't specifically aim to build a ladder.

What kind of functionality are you looking for?
To be honest I'm not really sure what is most important to track. I'd like for it to calculate YTM/maturity/duration for each individual bond and as a portfolio. It would be neat if it included TIPS. Maybe CUSIP lookup?? Hope that makes sense.
Thanks for the assist!
I download my transactions, import them, then use formulas to get whatever I want. I extract maturity date and coupon from the description. For example, Fidelity's description of TIPS follows a standard format, so string functions can be used to get anything that's in the description. Vanguard uses a different format, so I use different formulas for Vanguard transactions. I import transactions for all Treasuries, including TIPS.

I have a Trades sheet, the consolidates transactions from Vanguard and Fidelity into a single view, with only the columns of interest to me. I filter out TIPS into a TIPS trades sheet, where I can compare current YTMs to YTMs I last bought at.

I have a Holdings sheet, where I filter out my current holdings from the transactions, aggregating the values for each CUSIP.

I try to have CUSIP in each sheet, and use it for lookups in other sheets. Sometimes I have to use coupon and maturity date as the lookup keys.

In another spreadsheet I download quotes from Fidelity-- currently I do this daily. I import this into a sheet in my Treasury Trades spreadsheet using IMPORTRANGE, and use it to look up current prices and yields by CUSIP. I also import reference CPI data that I use to get things like dated date, and dated date ref CPI, which I can then use this and the settlement date ref CPI to calculate index ratios.

Etc.

It would not be easy to share the entire solution, since there are a number of spreadsheets involved.

Kevin
If I make a calculation error, #Cruncher probably will let me know.
Post Reply