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.
The RATE method in Excel appears to be the most sensible.
- 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
- 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 annuaized result multiply 4.33% by 2.
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