please help with this bootstrapping model in excel with formulas included

Bootstrapping Model Bootstrap the spot curve by calculating the sero rate for each year, Use the previous year's sports to catate each subsequent year's spot rate. Once you to all the watates, Calolote the price and yield of this 10 verbond with annual coupon by using the spot curve value each con sun and then add them up and Whave the moted bond nice. I've done vers 1.2 m3 Mot Coupon Bond Pries Term be . 10 ap 10 1010 ap AD 105 970 2 12 10 1045 99 4 5 MON Vu 10 W,4 mul pon bond Month $ 405 hodie voor Make the Wole wortels bond Stum Yesterdale UMI Press ww 1 Canto 100 Two IN 2 Chow 1001 Iron $50 The more of this archiv preferis Fanta Tiny IN 2N 950 900 1100 1100 1004 1100 4N 10 10 20 21 22 100 BAN Herende teef 0.51,00 and the area of the sweetheamarket 5.0 and those from 95 and what is te te make the free how the $1.00 COMISSINYO the year tesette MATE Nico www wwwinst you are we can TO the year and to the earrinio 1.2 potwear 1 IN ca - Old Value 40 40 3. S 38.10 37.24 26 27 21 1040 42 918 96 1 42 4 Cake Zero E15 fx prices above M w UGGEWS THURSTICI 3 10 11 8 Year's 5 40 $ 4 40$ 6 40 $ 7 40$ 9 40 $ 10 1,040 Market Data: Coupon Bond Prices Term Coupon Price 1 0% 40$ 970 12 2 6N e mkt price is just the present value of this year's cashflow.PV == 4 5 1045 9951 10101 9501 900l 17 time in years 7 14 15 te spot rate for this year 16 payment amount 18 19 20 21 22 23 5% 3% 2x 5 6X 4 5% 11001 1100 1004 1100 10 of the bond price (ie the price of $40.540 1,040) is $995 and the price of the first two $40 payments have a market value of $38.80 and $37.24. just subtract those from $995 and what is left is the market price of the final rero. Since you know the $1,040 in 3 years costs $918. you can now calculate the 3 year ratel Use the RATE Function to calculate the year rate. Now that you have the year rate "bootstrapped you can go to the year bond to find the 4 year rate, since you now know 1.2 and year rates Spot (Zero Curvel Term rate 1 2 1040 4.2N 275918.26 3.IN 3.ON 42N 4 7 10 E15 f prices above Com 1981 40 11 BEN SWS 10 3040 43 DIA LATE We how you to year burde find that you w2 ny 1 2 3 EN 42 Cher Jerome Valve Duro There Bero ed et Tuote www. Zwolle Bore | pot Curve E 5 10 Market Data: Coupon Bond Prices Term Coupon Price 1 0% 1,040 970 2 3 4 5 6 7 6% 4% 5% 3% 1045 995 1010 950 900 2% 5% 1100 1100 8 9 6% 49 5% 1004 1100 10 Spot (Zero Curve) Term rate 1 3.1% 3.6% 2 3 4.2% 4 5 6 7 8 9 10 B C D E F H Bootstrapping Model Bootstrap the spot curve by calculating the zero rate for each year. Use the previous year's spot rates to calculate each sulte spot rates, calculate the price and yield of this 10 year bond with a 4% annual coupon by using the spot curve to value eac you have the implied bond price. I've done years 1, 2 and 3. 1 today! $ 2 40 $ 3 40 $ 40 $ 40 $ Value a 10 year, 4% annual coupon bond Bond's Cash Flows Zero coupon discount rate (from Spot Curve Table) Market Price of each payment Implied Market Price of this Bond yield of this bond @market price