PLEASE SHOW ME HOW TO FILL IT WRITE IT DOWN IN EXCEL
t A B C D E F G H I J 5 Bond Valuation 6 7 Estimating the present value of a bond. 8 9 Problem 1: .... We want to estimate the present value of a bond that has a par value of $1,000, and a coupon rate of 10%. The bond pays 10 interest annually and similar bonds are currently priced to yield 10%. It has fifteen years left to maturity. 11 12 13 Given: 14 15 Definition Notation: Inputs: 16 Par Value par 1,000 17 Maturity 15 18 Coupon Rate CR 10% 19 Required rate of return for the bond. r 10% 20 Interest INT ? 21 Present Value of the bond. BO ? 22 23 Par value = Face Value (usually in $1,000 increments) = amount stated on the bond.. 24 25 Maturity is the number of years until the bond is redeemed. ZU 27 The coupon interest rate = the stated annual interest rate on a bond. 28 The coupon payment = the specified number of dollars of interest paid each period. 29 30 31 Finance Concept: The price of a bond is equal to the PV of the coupons + PV of the face value. 32 PV = cpn/(1+r)' + cpn/(1+r)? +...+ (cpn+par)/(1+r)' 33 34 Numerical Solution: B. = (coupon * annuity factor) + (face value * discount factor) 35 coupon = cpn = face value * coupon rate = 1,000*.10= 100 36 Bo = (100 * [1/r - (1/(r(1+r)))) + (1,000 * 1/(1+r)) 37 Bo = 100 (1/.10) - 1/(.1*(1.1^15)) + 1,000 (1/(1.1^15)) 38 Bo = 100 (7.6061) + 1,000 (0.2394) 39 B = 760.61 +239.40 40 B = $1,000 41 42 In Words: The Present Value of a fifteen year bond with a 10% coupon rate and a required return of 10% 43 is priced at $1,000. It sells at its par value of $1,000.. 44 When the required return and the coupon rate are not equal, the bond will sell at a discount or premium. 45 46 New Example: The maturity is reduced to 5 years, the coupon rate is 11%, and the required return is 8%. Loan Bond + Select destination and press ENTER or choose Paste B C D E F. G I I J L Solution: Notation: Definition Par Value Maturity Coupon Rate Required rate Interest Present Value of the bond. par t CR Inputs: 1,000 5 11% 8.00% r INT ? ? Bond Valuation Table: Today Year 1 Year 2 Year 3 Year 4 Year 5 Coupon rate Required return Time INT 0 2 3 4 5 par Total Cash Flows PV of Cash Flows 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 Solving with the Excel Wizard or the Net Present Value (NPV) Formula: 1 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 5. In cell J65 enter =G50 3 In the INT row, in cell F64 enter the formula to calculate the interest: =$J$65*$E$61 This is equal to Par * coupon rate. Copy from F64 to J64. 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 return. Or, you can use the NPV formula directly in cell E67. Enter: =NPV(required return, CF1, CF2, CF3, CF4,CF5) =NPV(E62, F66, G66, H66, 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. Given the following inputs, create a spreadsheet table from the outline below. 88 Test Your Skills: 89 90 Definition Notation: Inputs
