3 Assignment: 4 Develop a Bond Pricing Model for three different options: Base Case, Scenario 1 and Scenario 2 5 The base case has the key input areas highlighted: A yellow highlight indicates a numeric input, a light blue is a formula input 6 The green cells contain formulas, no need to do anything with these cells 7 8 9 Base Case 10 $5,000 Bond (Par value), 6% Coupon Interest Rate, 10 yrs 11 Year 1 12 Term (years) 13 Current Bond Market Yield 14 Bond Coupon Rate 15 Bond Face Value 16 Bond Payment (coupon) 17 Final Payment 18 PV of Coupons 19 Sum of Coupon PV 20 PV of Final Payment 21 PV (Price) of Bond 22 PV Year 2 Year Year Year 3 4 5 Year 6 Year Year 7 8 PV of Final Payment Year 9 Year 10 25 Scenario 1 The Bond Market yield is 8% 26 The bond market yield is +2% over the bond 27 Term (years) 28 Current Bond Market Yield 29 Bond Coupon Rate 30 Bond Face Value 31 Bond Payment (coupon) 32 Final Payment 33 PV of Coupons 34 PV of Final Payment 35 PV (Price) of Bond 36 37 Scenario 2 The Bond Market yield is 4% 38 The bond market yield is -2% over the bond c 39 Term (years) 40 Current Bond Market Yield 41 Bond Coupon Rate 42 Bond Face Value 43 Bond Payment (coupon) 44 Final Payment 45 PV of Coupons 46 PV of Final Payment 47 PV (Price) of Bond Year 1 Year 1 Year 2 Year 2 Year Year Year Year Year Year 3 4 5 6 7 8 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 9 Year 10 Year 10 Base Case Term (years) current bond market yield Bond Face Value Bond Payment (coupon) Final Payment PV of Coupons Sum of Coupon PV PV of Final Payment PV (Price) of Bond 3 2.00% $ 10,000 $ 500 PV $ $ LA LA LA LA $ $ $ Year 1 Year 2 500 $ 500 $ 500 $ 10,000 490.20 $480.58 $ 471.16 1,441.94 9,423.22 10,865.16 Year 3 $9,423.22