
I would like to know how you interpreted the statement below and found
a correct solution.
Dennis
"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 10year 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 10year 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
'zerocouponbonds: = RATE(20,0,1500000, 2000000) to get the 6month
YTM. Result=4.33%
 Multiply the result by 2 to get the annualized YTM.
Result = 8.66%
 Another method to calculate the 6monthly 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 6monthly YTM as a percentage. To
get the annuaized 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
MSExcel:
