Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 4 Scenario 1 Increase the interest rate by 2% over the bond coupon rate of 6% 5 +2% Market Rate Year Year Year 6 Term (years) 1 2 3 7 Interest Rate 8 Bond Face Value 29 Bond Payment (coupon) 30 Final Payment 31 pv of Coupons 32 PV of Final Payment 33 PV (Price) of Bond 34 35 Scenario 2 Decrease the interest rate by 2% below the bond coupon rate of 6% 36 -2% Market Rate Year Year Year 37 Term (years) 1 2 1 38 Interest Rate (annually) 39 Bond Face Value 40 Bond Payment (coupon) 41 Final Payment 42 pv of Coupons 43 PV of Final Payment 44 PV (Price) of Bond 45 Year Year Year 4 Year Year 5 Year 9 Year 10 6 7 8 Develop a Bond Pricing Model for three different options: Base Case, Scenario 1 and Scenario 2 The base case has the key input areas highlighted: A yellow highlight indicates a numeric input, a light blue is a formula input The green cells contain formulas, no need to do anything with these cells Year Year 2 Year Year 3 Year 4 5 Year 7 Year 8 Year 9 Year 10 Base Case $5,000 Bond (Par value), 6% Coupon Interest Rate, 10 yrs Year Term (years) 1 Interest Rate Bond Face Value Bond Payment (coupon) Final Payment PV of Coupons PV Sum of Coupon PV py of Final Payment PV (Price) of Bond $ PV of Final Payment Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 4 Scenario 1 Increase the interest rate by 2% over the bond coupon rate of 6% 5 +2% Market Rate Year Year Year 6 Term (years) 1 2 3 7 Interest Rate 8 Bond Face Value 29 Bond Payment (coupon) 30 Final Payment 31 pv of Coupons 32 PV of Final Payment 33 PV (Price) of Bond 34 35 Scenario 2 Decrease the interest rate by 2% below the bond coupon rate of 6% 36 -2% Market Rate Year Year Year 37 Term (years) 1 2 1 38 Interest Rate (annually) 39 Bond Face Value 40 Bond Payment (coupon) 41 Final Payment 42 pv of Coupons 43 PV of Final Payment 44 PV (Price) of Bond 45 Year Year Year 4 Year Year 5 Year 9 Year 10 6 7 8 Develop a Bond Pricing Model for three different options: Base Case, Scenario 1 and Scenario 2 The base case has the key input areas highlighted: A yellow highlight indicates a numeric input, a light blue is a formula input The green cells contain formulas, no need to do anything with these cells Year Year 2 Year Year 3 Year 4 5 Year 7 Year 8 Year 9 Year 10 Base Case $5,000 Bond (Par value), 6% Coupon Interest Rate, 10 yrs Year Term (years) 1 Interest Rate Bond Face Value Bond Payment (coupon) Final Payment PV of Coupons PV Sum of Coupon PV py of Final Payment PV (Price) of Bond $ PV of Final Payment