New Example: The maturity is reduced to 5 years, the coupon rate is 11%, and the required return is 8%. Solution: Definition Par Value Maturity Coupon Rate Required rate Interest Present Value of the bond. Notation: Inputs: par 1,000 t 5 CR 11% 8.00% INT ? B. ? r ond Valuation Table: Today Year! Year 2 Year 3 Year 4 Year 5 Coupon rate Required retum Time INT 0 2 5 par Total Cash Flows PV of Cash Flows Solving with the Excel Wizard or the Net Present Value (NPV) Formula: 1 Enter the coupon rate (CR) in cell E61 and the required retum (1) in cell E62. Or you can enter -G52 in cell E61, and G53 in cell E62. 2 Enter the par value in Year 5. In cell J65 enter -G50 3 In the INT row, in cell F64 enter the formula to calculate the interest: =$J$65 SE$61 This is equal to Par coupon rate. Copy from F64 to 164. 4 Find the Total Cash Flows for years 1-5 by adding the interest row to the par row. In F66 enter - F64+F65 and copy across. 5 Click on cell E67, open the Excel Wizard function dialog box and click on financial, then NPV. We must use NPV because the cash flows are unequal (year 5 Cash flow-INT + par) Enter the appropriate inputs in the dialog box and click OK. Rate - required retum. Or, you can use the NPV fomula directly in cell E67. Enter: -NPV(required retum, CF, CF, CF, CF, CF) NPV(E62,F66, G66, 166, 166, J66) or -NPV(use the actual values). 6 Your answer in cell E67 should be: $1,120 is the value of the bond today Tot Your Skills: Given the following inputs, create a spreadsheet table from the outline below.
PV of Cash Flows B Hint: Follow the steps in problem 1 Problem 2: We want to calculate the Internal Rate of Retum (IRR), or the Yield to Maturity (YTM), of a bond. Its par value is $1,000, it matures in four years, and it pays a coupon rate of 8%. Payments are made semi-annually (total of 8 payments). Given: Definition Notation: Inputs: Par Value par 1,000 Maturity in Years Coupon Rate CR 8% Number of Payments per year 2 Cash Flows INT ? Yield to Maturity YTM ? t 4 Finance Concept: IRR - YTM - The rate of retum camed on a bond if it is held to maturity The YTM is the interest rate for which the PV of the bond cash flows (coupons and face value) equals the bond price Numerical Solution: With unequal cash flows, the IRR or YTM can be found by trial-and-error, or by using a financial calculator or Excel Spreadsheet Solution: Notation: par Definition Par Value Maturity in Years Coupon Rate Interest Number of Payments per year Internal Rate of Retur CR INT Inputs 1,000 4 8% ? 2 ? IRR Note: There are 8 payments over a four year penod. Each payment is 1/2 the annual coupon Time Today Purchase Payment Periods 0 1 2 3 Cash Flow -1000 40 40 40 4 $$$$$$$$ 5 40 6 7 40 40 1040 End of Year 2 > 8 (M + INT) IRR It is very easy to find the IRR of the cash flows by using the Excel Wizard function: Step 1: Place your cursor in cell G154 and open the function wizard Go to financial, and click on IRR Step 2: Place the cursor in the first empty box (values) and then ding down the cash flow column including the purchase price (a negative number) and the last payment interest + return of principal). Click OK Test Your Skills: EJ Corp bond carries a 9 percent coupon, paid semi-annually. The par value is $1,000, and the bond matures in 12 years. If the bond currently sells for $905.50 (PV), what is its yield to maturity? Enter PV as a negative number. Calculate equal 24 payments ($1,000.09/2) and enter. Remember the last payment is the return of principal plus PMT. Use IRR (cash flows) Time Today Payment Periods 0 Cash Flow PV> 1 2 3 2 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Add FV to PMT - IRR-> The YTM is 2"IRR
