show the functions in excel

E H A D E F Finding the "Fair Value of a Bond 2 Problem: A bond has a 20-year maturity, a 8% semiannual coupon, and a $1,000 par value. The 3 required rate of return (or the yield to maturity) on the bond is 8%, given its risk, maturity, 4 liquidity, and other rates in the economy. What is a fair value for the bond, i.e., its market price? 5 6 First, we list the key features of the bond as "model inputs": 7 Years to Mat: 20 8 Coupon rate: 8% 9 Annual Pmt: S80 10 Par value = FV: $1,000 11 Market rate, r: 8% 12 Frequency 2 (periods per year) 13 14 Value of bond = . A D E F 7 Problem: Suppose the going interest rate changed from 8%, falling to 2% or rising to 14%. How 8 would those changes affect the value of the bond? 19 20 21 First, we list the key features of the bond as "model inputs": 22 23 Bond Value Bond Value 24 Years to Maturity 20 1 25 Going rate, r: Positive Values Change Positive Values Change 26 2% 27 4% 28 6% 29 8% 30 10% 31 12% 32 14% Student Info Changes in market rate Changes in terms to maturity Coupon rates and yields > E G 15% B D $0.40 $0.20 e $0.00 3 0% 5% 10% 1 2 List the 3 main bond theorems you derive from the above graph -3 14 Frist Theorem: 15 46 47 48 49 Second Theorem: 50 51 52 53 54 Third Theorem: 55 56 57 58