Hello, for the project below if I am calculating all of the spot and forward rates correctly the sum of the present values columns (pv.spot and pv.forward) should total $100. However, for some reason the sum of the present values of the forward rates is not totaling $100. Can someone please take a look at the screenshots and formulas I'm using and let me know what I'm doing wrong? Perhaps the formula I'm using for the forward rate is incorrect? I am using Goalseek to find the spot and forward rates.

Thanks

=(3.5/(1.02^1)+(3.5/(1.030151^2))+(103.5/((1+C6)^3)) A D G Par interest rate curve Year Double check your rates using 4 year par bond Rate Spot rates 1 year forward rates Goalseek 0.020000 1 2.00% 0.02000 100.0000 t cf pv.fwd 1+ZbAb pv.spot 0.030151 100.0000 1.061212 2 3.00% 0.04040 1.0612 0 0.04566 100.0003 3.92157 3.92156863 3 3.50% 0.035297 1.1097 1 4 1.109672 4.00% 4 0.040570 0.05655 100.0005 1.1724 2 4 3.76928 3.69535385 1.172425 4 3.60467 3.49850327 4 104 88.70501 83.4586669 0 Interest rate volatility 1 15.00% price: 100.0005 94.5740927 price should be $100 for both methods, if you calculated all rates correctly! 4 5 fx (1+C6)A3 L6 A B D E F G K M 1 Par interest rate curve 2 Double check your rates using 4 year par bond 3 Year Rate Spot rates 1 year forward rates Goalseek 0.020000 0.02000 1+ZbAb 4 1 2.00% 100.0000 cf pv.spot pv.fwd 3.00% 100.0000 1.061212 5 2 0.030151 0.04040 1.0612 0 0.035297 100.0003 1.1097 1.109672 6 3 3.50% 0.04566 4 3.92157 3.92156863 1 4.00% 0.040570 100,0005 1.1724 3.76928 3.69535385 7 4 0.05655 2 4 1.172425 4 3.60467 3.49850327 104 83.4586669 9 4 88.70501 10 Interest rate volatility 11 15.00% price 100.0005 94.5740927 12 13 price should be $100 for both methods, if you calculated all rates correctly! 14 15 16 Bond 1 information F6 =(1+C5)A2)*((1+D6)^1) B D E G J Par interest rate curve 1 2 3 Year Rate Spot rates 1 year forward rates Goalseek Double check your rates using 4 year par bond pv.fwd 0.020000 0.02000 1+ZbAb 4 1 2.00% 100.0000 cf pv.spot 5 2 3.00% 0.030151 0.04040 100.0000 1.0612 0 1.061212 100.0003 1.1097 3.92157 6 3 3.50% 0.035297 0.04566 1 4 3.92156863 1.109672 4.00% 0.040570 0.05655 1.1724 7 4 100.0005 2 4 3.76928 3.69535385 1.172425 3 4 3.60467 3.49850327 4 104 88.70501 83.4586669 10 Interest rate volatility 11 15.00% price: 100.0005 94.5740927 12 13 14 15 price should be $100 for both methods, if you calculated all rates correctly! =104/(1+C7)A4) A C D E E H J K L 1 Par interest rate curve 2 3 Year 1 year forward rates Goalseek 0.02000 Rate Spot rates Double check your rates using 4 year par bond pv.fwd 4 1 2.00% 0.020000 100.0000 t cf 1+ZbAb pv.spot 3.00% 1.0612 5 2 0.030151 0.04040 100.0000 0 1.061212 100.0003 6 3 3.50% 0.035297 0.04566 1.1097 1 4 3.92157 3.92156863 1.109672 0.05655 1.1724 3.76928 3.69535385 1.172425 7 4 4.00% 0.040570 100.0005 2 8 3 4 3.60467 3.49850327 88.70501 83.4586669 104 4 10 Interest rate volatility 11 15.00% price: 100.0005 94.5740927 12 13 price should be $100 for both methods, if you calculated all rates correctly! 14 15 19 -104/((1+D7)A4) A D F F H K L 1 Par interest rate curve 3 Year Double check your rates using 4 year par bond Rate Spot rates 1 year forward rates Goalseek 0.02000 1 2.00% 0.020000 100.0000 cf pv.spot 1+ZbAb pv.fwd 1.0612 1.061212 2 3.00% 0.030151 0.04040 100.0000 0 1.109672 3.50% 0.035297 0.04566 100.0003 1.1097 1 4 3.92157 3.92156863 4.00% 100.0005 1.1724 1.172425 4 0.040570 0.05655 2 4 3.76928 3.69535385 3.49850327 3 4 3.60467 88.70501 83.4586669| 4 104 0 Interest rate volatility |1 15.00% price: 100.0005 94.5740927 2 price should be $100 for both methods, if you calculated all rates correctly! 3 14 5 16 Bond 1 information =(3.5/(1.02^1)+(3.5/(1.030151^2))+(103.5/((1+C6)^3)) A D G Par interest rate curve Year Double check your rates using 4 year par bond Rate Spot rates 1 year forward rates Goalseek 0.020000 1 2.00% 0.02000 100.0000 t cf pv.fwd 1+ZbAb pv.spot 0.030151 100.0000 1.061212 2 3.00% 0.04040 1.0612 0 0.04566 100.0003 3.92157 3.92156863 3 3.50% 0.035297 1.1097 1 4 1.109672 4.00% 4 0.040570 0.05655 100.0005 1.1724 2 4 3.76928 3.69535385 1.172425 4 3.60467 3.49850327 4 104 88.70501 83.4586669 0 Interest rate volatility 1 15.00% price: 100.0005 94.5740927 price should be $100 for both methods, if you calculated all rates correctly! 4 5 fx (1+C6)A3 L6 A B D E F G K M 1 Par interest rate curve 2 Double check your rates using 4 year par bond 3 Year Rate Spot rates 1 year forward rates Goalseek 0.020000 0.02000 1+ZbAb 4 1 2.00% 100.0000 cf pv.spot pv.fwd 3.00% 100.0000 1.061212 5 2 0.030151 0.04040 1.0612 0 0.035297 100.0003 1.1097 1.109672 6 3 3.50% 0.04566 4 3.92157 3.92156863 1 4.00% 0.040570 100,0005 1.1724 3.76928 3.69535385 7 4 0.05655 2 4 1.172425 4 3.60467 3.49850327 104 83.4586669 9 4 88.70501 10 Interest rate volatility 11 15.00% price 100.0005 94.5740927 12 13 price should be $100 for both methods, if you calculated all rates correctly! 14 15 16 Bond 1 information F6 =(1+C5)A2)*((1+D6)^1) B D E G J Par interest rate curve 1 2 3 Year Rate Spot rates 1 year forward rates Goalseek Double check your rates using 4 year par bond pv.fwd 0.020000 0.02000 1+ZbAb 4 1 2.00% 100.0000 cf pv.spot 5 2 3.00% 0.030151 0.04040 100.0000 1.0612 0 1.061212 100.0003 1.1097 3.92157 6 3 3.50% 0.035297 0.04566 1 4 3.92156863 1.109672 4.00% 0.040570 0.05655 1.1724 7 4 100.0005 2 4 3.76928 3.69535385 1.172425 3 4 3.60467 3.49850327 4 104 88.70501 83.4586669 10 Interest rate volatility 11 15.00% price: 100.0005 94.5740927 12 13 14 15 price should be $100 for both methods, if you calculated all rates correctly! =104/(1+C7)A4) A C D E E H J K L 1 Par interest rate curve 2 3 Year 1 year forward rates Goalseek 0.02000 Rate Spot rates Double check your rates using 4 year par bond pv.fwd 4 1 2.00% 0.020000 100.0000 t cf 1+ZbAb pv.spot 3.00% 1.0612 5 2 0.030151 0.04040 100.0000 0 1.061212 100.0003 6 3 3.50% 0.035297 0.04566 1.1097 1 4 3.92157 3.92156863 1.109672 0.05655 1.1724 3.76928 3.69535385 1.172425 7 4 4.00% 0.040570 100.0005 2 8 3 4 3.60467 3.49850327 88.70501 83.4586669 104 4 10 Interest rate volatility 11 15.00% price: 100.0005 94.5740927 12 13 price should be $100 for both methods, if you calculated all rates correctly! 14 15 19 -104/((1+D7)A4) A D F F H K L 1 Par interest rate curve 3 Year Double check your rates using 4 year par bond Rate Spot rates 1 year forward rates Goalseek 0.02000 1 2.00% 0.020000 100.0000 cf pv.spot 1+ZbAb pv.fwd 1.0612 1.061212 2 3.00% 0.030151 0.04040 100.0000 0 1.109672 3.50% 0.035297 0.04566 100.0003 1.1097 1 4 3.92157 3.92156863 4.00% 100.0005 1.1724 1.172425 4 0.040570 0.05655 2 4 3.76928 3.69535385 3.49850327 3 4 3.60467 88.70501 83.4586669| 4 104 0 Interest rate volatility |1 15.00% price: 100.0005 94.5740927 2 price should be $100 for both methods, if you calculated all rates correctly! 3 14 5 16 Bond 1 information