Valuation of Bonds in MS Excel

I would like to know how you interpreted the statement below and found a correct solution.
“Steamliner, Inc. has a project that it expects will produce a cash flow of $4.5 million in 10 years. To finance the project, the company needs to borrow $1.5 million today. The project will produce intermediate cash flows of $125,000 per year that the company can use to service annual coupon payments. The firm’s underwriter suggests that the market would be receptive to a 10-year bond with a face value of $2 million with a $125,000 annual coupon (paid at the rate of $62,500 every six months). Alternatively, Steamliner has the option to raise the $1.5 million by issuing 10-year zero coupon bonds with a face value of $3.5 million. What is the annualized yield to maturity (YTM) on the preferred option? (Recall that the compounding interval is 6 months and the YTM, like all interest rates, is reported on an annualized basis.)”
Thanks for your help.

First create a timeline with 0 period and 20 more periods since the coupons will be paid per six months
Enter the data appropriately into the Excel worksheet cells as shown
Now use the RATE() function to calculate the Yield to maturity: ‘=RATE(2, 62500,-1500000, 2000000) where 20 = number of periods, 62500=coupon payments every period, -1.5M=money to be raised, 2M=face value of bonds. Result=5.16%
Multiply this value by 2 to get the annualized Yield to Maturity or YTM. Result=10.32%
Use the RATE() function again with the ‘zero-coupon-bonds: = RATE(20,0,-1500000, 2000000) to get the 6-month YTM. Result=4.33%
Multiply the result by 2 to get the annualized YTM. Result = 8.66%
Another method to calculate the 6-monthly YTM is to use the IRR function as shown
Yet another way to achieve the calculations is to divide the face value $3500000 by the initial investment of $1500000, raise the fraction result to the power 1/20, subtract 1 from the result and multiply the result by 100 to get 6-monthly YTM as a percentage. To get the annualized result multiply 4.33% by 2.

The RATE method in Excel appears to be the most sensible.
The Yield to Maturity rate is the cost of borrowing money from the investor. Therefore Streamliner would prefer the lower rate of 8.66%, isn’t it?
Watch the training video below to learn about the valuation of bonds in MS-Excel:

Watch the video on YouTube

Leave a Reply

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

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