the instructions are recreate the illustrative spread sheet in excel using apprepriate formulas

i wish i had more information but this is all i have. i am also very lost anything would help. thank you for your effort.

AB D 1 lV. BOND VALUATION A VALUATION MODEL Value of a security - present value of expected cash flows 3 4 5 B. Value of the Bond, VIPVIFA. M'PVIF. Maturity 10 years 6 7 8 9 10 11 12 13 14 115 16 18 19 20 21 22 23 24 25 26 27 28 Ex #1: 10 percent, 10 year bond, interest rate (k.) -12% Coupor 10% or $100 Discount Rate 12% $1,000 $100 $100 $100 $100 $100 $100 $100 $100 $100 $100 1 1 1 t-0 1 2 5 6 8 9 10 Value PV of Coupons $100 $565.02 --PV(D9,68,615) PV of Facevalue $1.000 $321,97 E-PVCD9.68, 616 TOTAL $887.00 C. Bond Value Behavior 1. Inverse relationship between value and Interest rate Rate Value What if Table 1,500 Bond Value 12% and Interest Rate 4% 1,500 1,467 1,400 6% 1.294 1,300 8% 1.134 1,200 10% 1,000 1.100 12% 887 1.000 14% 791 900 16% 710 800 700 6% 8% 10% 12% 14% 16% 30 33 34 2. Interest rate and promium discount on bond ak>%, V Par bond sols at premium 35 36 87 E38 39 Bond Value with Maturity 40 Vields 42 43 3. Bond value over time: approaches par af maturity Time to Bond Value for Different Maturity 5567.00 8% 12% 10 1,134,20 887.00 9 1,124.94 293.44 8 1,114.99 900.66 7 1.104.13 908.72 6 1.092.46 91777 5 1,079.85 927.00 4 1.000 24 230 25 31.051.54 96130 2 1,035.67 96620 1 1,018.52 10 D 1.000.00 1,000.00 1,200 1.150 1,100 1,050 1,000 45 950 47 49 50 900 850 800 0 B 7 6 5 4 3 2 1 52 1.53 54 4. Interest Rate Risk on bonds: consider two bonds Bond A coupon Bond 3 years 10 years 5100 55 M 18 BA 58 60 51 62 63 84 65 C D F G Interest rate increases from 10% to 12% Comida hood WALL -PV(F60,061,85,1000) Maturity 10% 12% Change 10 $1,000 38871 5113) - 11.30% $1,000 $952 4.80 57 68 09 70 71 72 73 74 775 70 77 78 79 80 81 2) Shorter the maturity, less sensitive is the band value to changes in interest rates Long-term bonds are more price sensitive than short-term bonds OR short-term bonds are less risky than long-term bonds. Semi-Annual Coupons: Ex #3: Bond from Ex #1. Assume semi-annual coupons Value ? 1. I annual coupon/2 $50 2. n= years to maturity 2 20 3. Kannual interest rate/2 6.00% -PVF74,F73.F72.1000) Value of the Bond $885.30 EYield.io Maturity The rate of retur expected on a bond if it is held to matunity 1. Ex #2: Bond in Ex 41, sells for $108000what is the YTM ? V=1*PVIFA+MPVIF. 10 PMT $100 ik --PVD86,064,784,1000) 9% Value $1,054.18 Ik 8% Value $1.134 20 B3 85 36 87 80 00 2. Approximate YTM 1+(M.Vn (M+V2 92 93 YTM 8.85 3. Fiscalfunction Rate YTM 5.77% RATED64,784,-F1, 1000) 18 BA