How to Calculate Bootstrapping Spot Rates in Excel (2 Examples)

Are you an investor? Or are you willing to buy bonds or government securities? Then, you must want to know the future rate of these financial products. Here comes the necessity of bootstrapping spot rates. In this article, we’ll demonstrate 2 relatable examples using 2 different and easy-solving methods to do bootstrapping spot rates in Excel. So, go through the entire article to learn it properly.

What Is Bootstrapping Spot Rates?

Bootstrapping spot rates is a progressive replacement technique that enables traders to calculate zero-coupon interest rates. It uses the par yield curve to determine this. The yields to maturity on government bonds with financing amounts are displayed on the par curve for a variety of periods. At first, we have to obtain the spot rate for the first year. After obtaining the first-year spot rate, we may then use it to determine the spot rate for the next years, and so forth.

How to Calculate Bootstrapping Spot Rates in Excel: 2 Examples

At this time, we’ll show 2 different examples of bootstrapping spot rates in Excel. The first one we’ll do is for an annual bond. And the second one will be for a bi-annual bond. Also, we’ll exhibit two different methods to solve the problem. So, let’s follow them one by one.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.

Example 01: Bootstrapping Spot Rates for Annual Bond

In our first example, we’ll start with the annual bond. Here, we have the Yield to Maturity of Annual Treasury Bond in our hands. This dataset includes the Par Value, Period, Maturity and Par Rate in different columns.

Now, our task is to find the spot rate for these different periods.
Before doing it in Excel, we have to understand the basic calculation of this process.
Treasury securities with maturities of six months or one year are considered discount securities. Often, we called them T-bills. Practically, they are zero-coupon bonds. So, their spot rate is the same as the par rate. But, for further periods, we need to calculate them manually. Here, we are giving the equation for the 3^rd year so that you can easily understand how to use it for previous periods as well. So, let’s apprehend the following equation.

Here, SR₃ is the spot rate for the 3^rd year. Simply put, we have to solve these equations to get the value of this spot rate. And, if we opt to solve the value of the spot rate for the next periods too, then we just have to add other terms one by one in this way.

1.1 Using Formula in Excel

Currently, we will see how we can solve this problem in Excel. To do this, we’ll get help from an Excel formula; just a few simple algebraic operations. If we want to write the previous equation for 2^nd year, how should it be? Let’s see it together.

To solve this, we have to transfer the symbol of the spot rate to the left side and all other elements to the right side. Then, the equation becomes,

Forthwith, we just need to place the right cell reference in the formula in Excel. So, let’s start.

📌 Steps:

• At the very beginning, create a new column with the heading namely Spot Rate under Column E.

As we said before, the Spot Rate for the 1^st year should be the same as the Par Rate for this period. So,

• At first, select cell E7 and enter the cell reference of cell D7 with an equal sign.

• As usual, press the ENTER key.

• Secondly, go to cell E8 and enter the following formula into the cell.

Sounds pretty complicated, right? Are you feeling dizzy after seeing so many terms? Don’t be afraid. Just match them with the previous equation. Then, everything will become as clear as water.

• Then, press ENTER.

To get the Spot Rate for the 3^rd year, just solve the preceding equation. And, in Excel,

• Firstly, select cell E9 and insert the formula below. Then, hit ENTER.

We hope you find this approach to resolving the spot rate useful.

1.2 Utilizing Solver Add-in

Another way to bootstrap spot rates is to use Excel’s Solver Add-in. We recommend using this approach instead of the previous one. Because you don’t have to rearrange the equation to get the spot rate symbol on the left side, you just have to make the equation, and the Solver will do the rest. So, let’s see it in action.

📌 Steps:

First of all, we can see the Spot Rate for year 1 in cell D11. It’s equal to the Par Rate of cell D7.

Now, we’ll form the equation in our Excel sheet. On the left-hand side, we placed 100. So,

• Here, go to cell D13 and enter the following cell reference.

• As always, tap ENTER.

To insert the right-hand side of the equation,

• Initially, navigate to cell D14 and write down the formula below.

Just match with the previous equation to make sense. You simply need to enter the correct cell reference here.

• After that, press ENTER.

Here, we applied this logic in cell D15.

Now, we want to get the value of the Spot Rate for 2nd Year in cell D16. To do this, follow the next steps.

• Afterward, proceed to the Data tab.
• Then, click on Solver on the Analyze group of commands.

Note: If Solver isn’t available on your ribbon, then you have to install the Solver add-in in Excel.

Immediately, the Solver Parameters dialog box appears before us.

• In the Set Objective box, give the cell reference of cell D15.
• Then, select the Value of option.
• After that, place the cell reference of D16 in the By Changing Variable Cells box.
• Lastly, click on the Solve button.

As a result, we can see the Solver Results wizard. Here, the Keep Solver Solution option will be automatically selected.

• Just click OK.

In the worksheet, we can see the spot rate in cell D16. Also, the values in cells in the D14:D15 range get changed automatically to make the two sides of the equation equal. So, the spot rate for 2^nd year is 3.015%.

• Also, see the formula to construct the equation for 3^rd year in cell D19.

• Then, repeat the earlier steps to use the solver to solve the rate for this year.

Read More: How to Perform Bootstrapping in Excel

Example 02: Bootstrapping Spot Rates for Bi-Annual Bond

In our second example, we’ll know how to do bootstrapping for bi-annual bonds in Excel. Here, we have a Yield to Maturity of Bi-Annual Treasury Bond in our hands. We can see that the maturity periods are 0.5 years, 1 year, 1.5 years, etc.

So, let’s see the process with detailed steps.

📌 Steps:

The half-yearly and yearly Spot Rates would be the same as those periods’ Par Rate.

Here, we are giving the equation for the spot rate for 1.5 years. See it below.

You can notice that the difference from the previous formula is that there is an additional 0.5 in the numerators and denominators of the fractions.

So, to build the right-hand side in Excel,

• At first, select cell D16 and enter the following formula.

• Then, tap the ENTER key.

Other calculations would be the same as Example 01. Here, we solved it with the help of the Solver. You can also use the first method of Example 01.

Read More: How to Resample Time Series in Excel

Practice Section

For doing practice by yourself we have provided a Practice section like the one below in each sheet on the right side. Please do it by yourself.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

This article explains how to do bootstrapping spot rates in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.