Bond Ladder Spreadsheet Yield Question

[Fotodog]

Greetings All,
I’ve been building a ladder of corporate bonds/CD’s in my tIRA and municipal bonds in my taxable account. I made a spreadsheet to keep track of these investments, which includes the YTM. It’s easy to find the average YTM of the ladder with a simple formula. But that average does not account for the fact that bonds were purchased in different quantities.

For example, if I have 10 bonds with a YTM of 4%, 20 bonds earning 5%, and 30 bonds earning 6%, the average YTM would be 5%. But the fact that the quantities are different skews that number. If all of the bonds were purchased at par, I could do the math and make a formula, but that’s not the case. Some of my corporate bonds were purchased below par, and some of the municipal bonds were purchased above par.

Knowing my actual YTM won’t change my investment strategy, it’s more to satisfy my curiosity and learn more about using spreadsheets to track my investments. If anyone knows how to accomplish this I would love to know. Thanks!

 

[COcheesehead]

I use Fidelity’s fixed income analysis tool. It will calculate your YTM and much, much more. You can import third party bonds into it. They do not all have to be at Fidelity. Link below.

https://www.fidelity.com/fixed-income-bonds/fixed-income-tools-services/fixed-income-analysis-tool

 

[pb4uski]

Greetings All,
I’ve been building a ladder of corporate bonds/CD’s in my tIRA and municipal bonds in my taxable account. I made a spreadsheet to keep track of these investments, which includes the YTM. It’s easy to find the average YTM of the ladder with a simple formula. But that average does not account for the fact that bonds were purchased in different quantities.

For example, if I have 10 bonds with a YTM of 4%, 20 bonds earning 5%, and 30 bonds earning 6%, the average YTM would be 5%. But the fact that the quantities are different skews that number. If all of the bonds were purchased at par, I could do the math and make a formula, but that’s not the case. Some of my corporate bonds were purchased below par, and some of the municipal bonds were purchased above par.

Knowing my actual YTM won’t change my investment strategy, it’s more to satisfy my curiosity and learn more about using spreadsheets to track my investments. If anyone knows how to accomplish this I would love to know. Thanks!

You want to use a weighted average. I would calculate it as =SUMPRODUCT(range of cost, range of YTM)/SUM(range of cost). The numerator is the annual interest including amortization of discount or accrual of premium which are all factored into the YTM and the denominator is the amount invested.

I would use cost because the YTM is calculated based on a combination of cost, par, amortization of discount, coupon, etc.

 

[Fotodog]

I use Fidelity’s fixed income analysis tool. It will calculate your YTM and much, much more. You can import third party bonds into it. They do not all have to be at Fidelity. Link below.

https://www.fidelity.com/fixed-income-bonds/fixed-income-tools-services/fixed-income-analysis-tool

Thanks COcheeshead, you are one of the people I have learned from for my bond investments. I’m not a Fidelity customer, and although I created a guest account last year I can’t seem to be able to log in.

 

[Fotodog]

You want to use a weighted average. I would calculate it as =SUMPRODUCT(range of cost, range of YTM)/SUM(range of cost). The numerator is the annual interest including amortization of discount or accrual of premium which are all factored into the YTM and the denominator is the amount invested.

I would use cost because the YTM is calculated based on a combination of cost, par, amortization of discount, coupon, etc.

Thank you sir, you are another person that has helped me. It’s too bad that Freedom moved elsewhere. I’m not a spreadsheet guru by any means, so I will try to decipher your formula and see if I can make it work.

 

[pb4uski]

Another way to do it is to add a column for the annual interest income that is cost * YTM for each bond and then have a sum for that column which would be the annual interest income and then divide the annual interest income by the total cost.

SUMPRODUCT just takes the product of YTM and cost for each row in the range and then takes the sum of the products for each row... hence SUMPRODUCT.

 

[Gotadimple]

You can do a weighted value of anything using a spreadsheet. The basic formula looks at the current market value of an investment and divides it by the total of all the holdings of that investment type. That number tells you what percentage one bond has to the total amount of your investment in bonds/cds.

You want to know what the weighted value of your YTM is for your investment in Bonds/CDs. Your spreadsheet probably has the current value of each bond and the YTM value.

Add two columns: % of Total and Weighted YTM

You need to know the value of the total investments (do a sum on the current value column)
In the % of Total column: Multiply the current value of each investment by the total of all investments - that tells you the percentage each bond has to the total.
In the Weighted YTM column: Multiply the YTM% by the % of Total for each investment - that tells you the weighted YTM for each investment.
Do a sum on the Weighted YTM column - that tells you your weighted YTM for all bonds.

- Rita

 

[Fotodog]

Rita, thanks so much for your detailed response! With my limited spreadsheet experience that’s exactly what I need. I’ll get to work on updating my spreadsheet this weekend.