Question Twelve: A person buys a bond on June 1, 2018. Under the terms of the bond, the owner
receives two payments – one of $5,000 on 6-1-24. The other of $7,000 on 6-1-26. The market yield on the bond on the day it
is purchased is 12 %.
How much did the buyer pay
for the bond?
Verify that your answer is
correct with the XIRR function in Excel?
Analysis: The method used to find the value of the bond
is to sum the discounted cash flows of all payments. The discount factor is 1/(1.12)t where t is the number of years from the bond
purchase date to the date of the cash flow payments.
The difference in years from
the bond purchase to cash payments is 6 years for the first payment and 8 years
for the second payment.
The discounted cash flows are
$2,533.16 and $2,827.18 for the first and second cash flows
respectively. Total payments to the
bond holder sum to $5360.34.
This is the amount that a person should pay for
the bond if the market interest rate is 12 percent.
The internal rate of return on this bond payment
and this cash flow receipts should equal to 12 percent. See the tabulation below.
Market Interest Rate
|
0.12
|
6/1/18
|
($5,360.34)
|
6/1/24
|
$5,000
|
6/1/26
|
$7,000
|
XIRR
|
0.12
|
The syntax for the IRR function is XIRRR(Value
Range, XIRR Date Range).
The XIRR function gives us the market rate on
the bond, which is a check on our discounted cash flow calculations.
Authors Note: Go here for another interesting application
of the XIRR function.
Go here for a tutorial on
Excel Finance Functions
No comments:
Post a Comment