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

**different and easy-solving methods to do bootstrapping spot rates in Excel. So, go through the entire article to learn it properly.**

*2***Table of Contents**hide

## 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***and**

*Maturity***in different columns.**

*Par Rate*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

**. But, for further periods, we need to calculate them manually. Here, we are giving the equation for the**

*par rate*

*3***year so that you can easily understand how to use it for previous periods as well. So, let’s apprehend the following equation.**

^{rd}**100 = ((100 × 4%)/(1 + 2%)) + ((100 × 4%)/(1 + 3%)**

^{2})+ ((100 + 100 × 4%)/(1 + SR3)^{3})Here, **SR**** _{3}** is the spot rate for the

*3***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.**

^{rd}#### 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.

**100 = ((100 × 3%)/(1 + 2%)) + ((100 + 100 × 3%)/(1 + SR**

_{2})^{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,

**SR**

_{2}= √((100 + 100 × 3%)/(100 – ((100 × 3%)/(1 + 2%))) – 1Forthwith, 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
under*Spot Rate***Column E**.

As we said before, the ** Spot Rate** for the

*1***year should be the same as the**

^{st}**for this period. So,**

*Par Rate*- At first, select cell
**E7**and enter the cell reference of cell**D7**with an equal sign.

`=D7`

- As usual, press the
**ENTER**key.

- 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**.

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**.

`=((D4+(D4*D9))/((D4-((D4*D9)/(1+D7))-((D4*D9)/(1+E8)^2))))^(1/3)-1`

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

**of cell**

*Par Rate***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**.

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**.

**⇒ LHS = RHS**

**⇒ LHS – RHS = 0**

Here, we applied this logic in cell **D15**.

`=D13-D14`

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 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.

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***year is**

^{nd}**3.015%**.

- Also, see the formula to construct the equation for
*3*year in cell^{rd}**D19**.

`=((D4*D9)/(1+D7))+((D4*D9)/(1+D8)^2)+((D4+D4*D9)/(1+D21)^3)`

- 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

**years,**

*0.5***year,**

*1***years, etc.**

*1.5*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 + SR_{3}× 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.

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.