Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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.


2 Diverse Examples to Calculate Bootstrapping Spot Rates in Excel

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.

bootstrapping spot rates excel

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 3rd year so that you can easily understand how to use it for previous periods as well. So, let’s apprehend the following equation.

100 = ((100 × 4%)/(1 + 2%)) + ((100 × 4%)/(1 + 3%)2 )+ ((100 + 100 × 4%)/(1 + SR3)3)

Here, SR3 is the spot rate for the 3rd 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 2nd year, how should it be? Let’s see it together.

100 = ((100 × 3%)/(1 + 2%)) + ((100 + 100 × 3%)/(1 + SR2)2)

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,

SR2 = √((100 + 100 × 3%)/(100 – ((100 × 3%)/(1 + 2%))) – 1

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.

Bootstrapping Spot Rates for Annual Bond

As we said before, the Spot Rate for the 1st 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.
=D7
  • As usual, press the ENTER key.

Bootstrapping Spot Rates for Annual Bond using Excel Formula

  • Secondly, go to cell E8 and enter the following formula into the cell.
=((D4+(D4*D8))/(D4-((D4*D8)/(1+D7))))^(1/2)-1

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.

calculating spot rates for 2 year

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

  • Firstly, select cell E9 and insert the formula below. Then, hit ENTER.
=((D4+(D4*D9))/((D4-((D4*D9)/(1+D7))-((D4*D9)/(1+E8)^2))))^(1/3)-1

using formula to do bootstrapping spot rates in Excel

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


1.2 Utilizing Solver Add-in

Another way for bootstrapping 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.
=D4
  • As always, tap ENTER.

Utilizing Solver in Excel

To insert the right-hand side of the equation,

  • Initially, navigate to cell D14 and write down the formula below.
=((D4*D8)/(1+D7))+((D4+D4*D8)/(1+D16)^2)

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

  • After that, press ENTER.

We know that in an equation, always,

⇒ LHS = RHS

⇒ LHS – RHS = 0

Here, we applied this logic in cell D15.

=D13-D14

shifting RHS of the equation

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.

Navigating to data tab

Note: If Solver isn’t available on your ribbon, then follow the Where Is Solver in Excel? article on our website.

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.

Solver parameters dialog box

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

  • Just click OK.

solver results dialog box in Excel

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 2nd year is 3.015%.

spot rates for two year

  • Also, see the formula to construct the equation for 3rd year in cell D19.
=((D4*D9)/(1+D7))+((D4*D9)/(1+D8)^2)+((D4+D4*D9)/(1+D21)^3)

bootstrapping spot rates for 3 year in Excel

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

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.

Bootstrapping Spot Rates for Bi-Annual Bond

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.

100 = ((100 × 3% × 0.5)/(1 + 2% × 0.5)) + ((100 × 3% × 0.5)/(1 + 2.5% × 0.5)2) + ((100 + 100 × 3% × 0.5)/(1 + SR3× 0.5)3)

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.
=((D4*D9*0.5)/(1+D7*0.5))+((D4*D9*0.5)/(1+D8*0.5)^2)+((D4+D4*D9*0.5)/(1+D18*0.5)^3)
  • Then, tap the ENTER key.

calculating spot rate for bi annual bond in excel

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.


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.


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. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo