Saturday, September 9, 2017

Discounted Cash Flows and the Value of a Bond

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