Help with formula please

[lem1955]

I made a spreadsheet with all the CD's and Bonds in my portfolio. Columns include Quantity, Net amount I paid (including commission fees, and accrued interest), coupon, Settlement Date and Maturity Date. What is the formula to calculate my average rate of return if I hold each to maturity?

 

[Jeffman52]

I made a spreadsheet with all the CD's and Bonds in my portfolio. Columns include Quantity, Net amount I paid (including commission fees, and accrued interest), coupon, Settlement Date and Maturity Date. What is the formula to calculate my average rate of return if I hold each to maturity?

Maybe I am oversimplifying this. Wouldn't it be



((TOTAL Net paid*qty) * Interest rate) for each line item.


Set a cell as a percentage, in that cell (Sum of all Interest earned/(Sum all Net amount paid + sum interest paid)would be your average interest percent earned across all.

 

[pb4uski]

I would use the RATE function. An example for a bond with a coupon rate of 5%, purchased with 4-1/2 year remaining for 100 paying interest semi-annually would be:

=RATE(4.5*2,100*5%/2,-100,100)*2 = 5.00%

Same formula and situation except the bond is bought for 98 rather than 100:

=RATE(4.5*2,100*5%/2,-98,100)*2 = 5.51%

You can define the term as (maturity date - settlement date)/365

 

[pb4uski]

Here is a "live" example. CUSIP 3133EHSM6, a FFCB bond with a 2.68% coupon maturing 7/28/2028 that pays interest semi-annually. According to Schwab at a price of 91.775 the YTM is 4.563%

The term is (7/28/2028 - 8/21/2023)/365 or 4.92876712328767 years... * 2 is 9.85753424657534 periods since interest is paid semi-annually. The PMT is the coupon and is 100*2.68%/2 or 1.34 per period. The PV is the 91.775 price paid. The FV is the maturity value of 100.

RATE (9.85753424657534,1.34,-91.775,100)*2 = 4.562%... really close to the 4.563% yield according to Schwab.

 

[lem1955]

pb4uski - Thanks. I can always count on you for help with financial math!