How to Bootstrap Spot Rates

Updated: Mar 28, 2021

The only way to learn how to bootstrap spot rates is by doing it. In this article, we will calculate spot rates given a yield curve.

Two spot rates are known without calculation. The spot rate is equal to the yield of a zero-coupon bond, In this case, we are given information on two zero-coupon bonds.

Z1 spot rate for N=.5 spot rate = Yield = 2%

Z2 spot rate for N=1 spot rate = Yield = 2.5%

Using this information we can find the third spot rate Z3, which occurs at N=1.5.

The general process to find a spot rate is as follows. Assuming we discount each coupon at the spot rate in that period what discount rate for the newest cash flow makes the PV of our cash flows equal to the price?

The math we use to solve for Z3 is fairly simple but there a multiple steps. For the sake of oversimplifying rather than under-simplifying, I have shown every step below.

We can also solve using "Goal Seek" in Excel. We find the PV of each payment as we did above but instead of solving for Z3 well will merely add a random plug value. We will then set another cell equal to the sum of said present values.

In order to find the spot rate Z3 (the yellow cell), we will use the goal seek function located under the data tab and what-if analysis dropdown.

We set cell "Current Sum PV" to 100 by changing the Z3 spot rate (in yellow). The tool solves for Z3.

We could increase the accuracy of the result by playing with the settings but the answer is very close to spot on. This process can be repeated multiple times to find the spot rate at each maturity.