Help123 ?
Dividend paying bonds and yield-to-maturity are one of the
more-difficult financial calculations to model, in part because of the
problem of dividend reinvestment which is brought up in (d) of your
question. Even if we?re earning 12% today ? who says dividends will
be reinvested at that rate?
Also, from a mathematical standpoint it can involve a number of
?attempts? or iterations to get close to the right interest rate.
Luckily we have spreadsheets to do the heavy lifting and this solution
is aided by using the PRICE or YIELD functions in Microsoft Excel.
I?ve provided a linked spreadsheet, which is viewable in your browser,
and if you have Excel you can even change the entries for different
assumptions.
If you?re uncertain about the definition of this bond yield, see the
section on ?Bonds? for a precise definition of Yield-to-Maturity
(YTM):
QuickMBA.com
?Investment Management?
http://www.quickmba.com/finance/invest/
SECTION A
==========
I?m going to calculate the bond price two ways ? one using NPV and
discount factors. In the second, we?ll check the result using Excel?s
PRICE function. You?ll find models for Bond A and Bond B at the top
and we?ve taken the cash flows and applied NPV discount factors to
them to arrive at today?s selling price. (If there are any questions
about how Net Present Value works to discount tomorrow?s dollars to an
equivalent today, please use ask for a Clarification before rating
this answer):
http://www.mooneyevents.com/YTM2005.xls
You?ll see that I?ve checked the math, using Excel?s PRICE function,
where the entries are defined in the following way. It?s important to
note that while bonds are typically figured per $1,000 of value, for
some reason Excel treats everything adjusted to a ?per $100? value:
PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)
So, multiplying the Microsoft Excel numbers by 10, you?ll see that
they check. Bond A = $783.71
Bond B = $1,072.10
And it makes sense ? the coupon rate of bond B is higher than the YTM
? so the present value of the flow of dividends is greater than the
cash value of the dividends themselves.
SECTION B
==========
How many can Mark buy with $20K?
Bond A = $20,000/$783.71 = 25.52
Bond B = $20,000/$1,072.10 = 18.66
SECTION C
===========
Bond A = 25.52 * $60 = $1,531.20
Bond B = 18.66 * $140 = $2,612.40
Note that this assumes nothing about the re-investment of interest
payments. Section D will get to that issue.
SECTION D
==========
Note that each year your dividends are accumulating in an account and
being reinvested, so we?ll go BACK to the spreadsheet to calculate the
totals:
Bond A:
Annual dividend: $1,531.20
Principal: $25,520
Dividends reinvested: $9,348.13
TOTAL = $34,868.13
Bond B:
Annual dividend: $2,612.40
Principal: $18,660
Dividends reinvested: $15,948.96
TOTAL = $34,608.96
SECTION E
==========
WHY ARE THE 2 DIFFERENT? First let?s talk about when they?d be the
same ? it would be when interest rates (the yield-to-maturity is 12%).
There?s a discount of $6,660 built into the purchase price Bond A
because of its lower interest rate ? and it gets paid back at 12% over
the life of the bond.
Bond B has a higher current yield at 14% -- if that money doesn?t get
reinvested at a minimum of 12% Bond A will win out in the long-term
due to the initial purchase discount.
This is an excellent exercise in time value. If you have any
questions, please let me know via a clarification request.
Best regards,
Omnivorous-GA |