r 46 New Example: The maturity is reduced to 5 years, the coupas rate is 11%, and the required return is 8% 47 45 Solution 49 Noon Inpuis SO Par Value 1.000 51 5 52 Coupon Rue 11% 53 Required mile 8.00% 54 ht INT 190 55 Prescil Value of the bond. . 1000 56 57 58 59 Band Valuation Tails: 60 Today Year! Ye3 Yes 61 Coupon me 62 Required cum 63 64 INT 65 pu 66 To Cash Flow 67 PV or Cu Flow 68 Solving with the Excel Win or the Net Pest Value (NPV Foto 70 71 Enter the coupon me (CR) in cell El and the ruined cum in cell ES2. 72 O you can clic 52 in 161.253 in 12 73 2 Finster the pas value in Year 5. In cell J65 cl CSO 74 3 Is the NTOW, in cell F64 cale the Soul to calculate the interest: $J$65 SESI 75 This is equal to Pucoupon le Copy from F64 D 564 75 4 Find the Total Cash Flows for years 1-5 by adding the interesow Do the pow. 77 InF66 cler-F64 +265 d copy KTOS 78 5 Click on cell E57, upon the Excel Wind Sanction dialog box and click on financial 79 the NPV 80 We must use NPV because the cash Bows se unequally 5 Cash Bow - INT+ pa 81 Enter the appropriate inputs in the dialog box and click OK. Rate quired retum. 82 Or, you can use the NPV Som directly in cell E67. 83 Ente NPVixequired ctum, CF CF, CF, CFCF) 84 -NPV (E62.66.66.166.166.366) 85 DENPVuse the actual values 6 Your answer in cell E67 should be: $1,120 is the value of the bond today Caves the following inpub, create a spreadshed table from the one budow. 88 89 90 91 92 93 Definition Pe Value HLO3 Notion lagu par 1.000 3 CR SS r 8.00% INT ? B Coupon Rate Requiredite ht Prescil Value of the bond. 95 Today Yel Year 2 Yew 3 Coupon de |Rauired retum 97 98 99 100 101 102 103 104 105 105 107 108 109 110 INT M ToulCal Flows PV of Cash Flows B wie NPP Hint: Follow the steps in pooblen I AutoSave OFF HEUS = Chapter 5 HW 5 Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments fx A .. v e Define Name Vix - H V Calculate Now ? AutoSum Recently Financial Logical Used ETrace Precedents a Trace Dependents Show Error FRemove Arrows Formulas Checking Text Insert Function Date & Time Lookup & Math & Reference Trig More Functions Create from Selection Calculation 10 calculate Sheet Options N28 A N28 X fox B D E F G H L M N P a R s T U V w Y z AA AB AC AD AE AF AG AH A AJ AK 46 New Example: The maturity is reduced to 5 years, the coupon rate is 11%, and the required return is 8%. 47 Solution 49 Definition Notation SO Par Value par 1,000 51 Maturity| t 5 52 Coupon Rate CR 53 Required rate 54 Interest INT 55 Present Value of the bed B ? 2 56 57 58 59 Bond Valuation Table: 60 Today Year 1 Year 2 Year 3 Yeard Years 61 Coupon rate 52 Required return Time 64 INT 65 par 66 Total Cash Flows 67 PV of Cash Flows Solving with the Excel Wizard or the Net Present Value (NPV) Formula: 70 71 72 73 74 75 75 77 78 79 80 81 82 83 84 85 86 87 Enter the coupon rate (CR) in cell E61 and the required return (r) in cell E62 Or you can enter -G52 in cell E61, and G53 in cell E62 2 Enter the par value in Year 3. In cell 365 enter -50 3 In the INT row, in cell F64 enter the formula to calculate the interest: SJS6S "SES61 This is equal to Parcoupon rate. Copy from PS4 to 64 4 Find the Total Cash Flows for years 1.5 by adding the interest row to the par row. In Penter -F64+F65 and copy across 3 Click on cell EST, open the Excel Wizard function dialog box and click on financial then NPVA 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 return. Or, you can use the NPV formala directly in cell E6T. Enter: NPV (required return, CF, CF, CF, CF, CF) -NPV/E62.46, G, H, 144,64) or NPV(use the actual values) 6 Your answer in cell EST should be: $1,120 is the value of the bond today. Given the following inputs, create a spreadsheet table from the outline below CH10 88 Test Your Si 89 90 91 92 93 94 95 Definition Par Vala Maturity Coupon Rate Required rate Interest Present Value of the bond Notice: g: par 1.000 t 3 3 CR 5% r INT ? ? ? Today Year 1 Year Year 3 97 98 99 100 101 102 103 104 0 Coupon rate Required return Time INT M Total Cash Flows Me NPP PV of Cash Flows 105 106 107 Hint Fatherinem 1 Bond + Loan Ready + 75% r 46 New Example: The maturity is reduced to 5 years, the coupas rate is 11%, and the required return is 8% 47 45 Solution 49 Noon Inpuis SO Par Value 1.000 51 5 52 Coupon Rue 11% 53 Required mile 8.00% 54 ht INT 190 55 Prescil Value of the bond. . 1000 56 57 58 59 Band Valuation Tails: 60 Today Year! Ye3 Yes 61 Coupon me 62 Required cum 63 64 INT 65 pu 66 To Cash Flow 67 PV or Cu Flow 68 Solving with the Excel Win or the Net Pest Value (NPV Foto 70 71 Enter the coupon me (CR) in cell El and the ruined cum in cell ES2. 72 O you can clic 52 in 161.253 in 12 73 2 Finster the pas value in Year 5. In cell J65 cl CSO 74 3 Is the NTOW, in cell F64 cale the Soul to calculate the interest: $J$65 SESI 75 This is equal to Pucoupon le Copy from F64 D 564 75 4 Find the Total Cash Flows for years 1-5 by adding the interesow Do the pow. 77 InF66 cler-F64 +265 d copy KTOS 78 5 Click on cell E57, upon the Excel Wind Sanction dialog box and click on financial 79 the NPV 80 We must use NPV because the cash Bows se unequally 5 Cash Bow - INT+ pa 81 Enter the appropriate inputs in the dialog box and click OK. Rate quired retum. 82 Or, you can use the NPV Som directly in cell E67. 83 Ente NPVixequired ctum, CF CF, CF, CFCF) 84 -NPV (E62.66.66.166.166.366) 85 DENPVuse the actual values 6 Your answer in cell E67 should be: $1,120 is the value of the bond today Caves the following inpub, create a spreadshed table from the one budow. 88 89 90 91 92 93 Definition Pe Value HLO3 Notion lagu par 1.000 3 CR SS r 8.00% INT ? B Coupon Rate Requiredite ht Prescil Value of the bond. 95 Today Yel Year 2 Yew 3 Coupon de |Rauired retum 97 98 99 100 101 102 103 104 105 105 107 108 109 110 INT M ToulCal Flows PV of Cash Flows B wie NPP Hint: Follow the steps in pooblen I AutoSave OFF HEUS = Chapter 5 HW 5 Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments fx A .. v e Define Name Vix - H V Calculate Now ? AutoSum Recently Financial Logical Used ETrace Precedents a Trace Dependents Show Error FRemove Arrows Formulas Checking Text Insert Function Date & Time Lookup & Math & Reference Trig More Functions Create from Selection Calculation 10 calculate Sheet Options N28 A N28 X fox B D E F G H L M N P a R s T U V w Y z AA AB AC AD AE AF AG AH A AJ AK 46 New Example: The maturity is reduced to 5 years, the coupon rate is 11%, and the required return is 8%. 47 Solution 49 Definition Notation SO Par Value par 1,000 51 Maturity| t 5 52 Coupon Rate CR 53 Required rate 54 Interest INT 55 Present Value of the bed B ? 2 56 57 58 59 Bond Valuation Table: 60 Today Year 1 Year 2 Year 3 Yeard Years 61 Coupon rate 52 Required return Time 64 INT 65 par 66 Total Cash Flows 67 PV of Cash Flows Solving with the Excel Wizard or the Net Present Value (NPV) Formula: 70 71 72 73 74 75 75 77 78 79 80 81 82 83 84 85 86 87 Enter the coupon rate (CR) in cell E61 and the required return (r) in cell E62 Or you can enter -G52 in cell E61, and G53 in cell E62 2 Enter the par value in Year 3. In cell 365 enter -50 3 In the INT row, in cell F64 enter the formula to calculate the interest: SJS6S "SES61 This is equal to Parcoupon rate. Copy from PS4 to 64 4 Find the Total Cash Flows for years 1.5 by adding the interest row to the par row. In Penter -F64+F65 and copy across 3 Click on cell EST, open the Excel Wizard function dialog box and click on financial then NPVA 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 return. Or, you can use the NPV formala directly in cell E6T. Enter: NPV (required return, CF, CF, CF, CF, CF) -NPV/E62.46, G, H, 144,64) or NPV(use the actual values) 6 Your answer in cell EST should be: $1,120 is the value of the bond today. Given the following inputs, create a spreadsheet table from the outline below CH10 88 Test Your Si 89 90 91 92 93 94 95 Definition Par Vala Maturity Coupon Rate Required rate Interest Present Value of the bond Notice: g: par 1.000 t 3 3 CR 5% r INT ? ? ? Today Year 1 Year Year 3 97 98 99 100 101 102 103 104 0 Coupon rate Required return Time INT M Total Cash Flows Me NPP PV of Cash Flows 105 106 107 Hint Fatherinem 1 Bond + Loan Ready + 75%
