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


Example 1 – Bootstrapping Spot Rates for Annual Bond

The below dataset, Yield to Maturity of Annual Treasury Bond, includes the Par Value, Period, Maturity, and Par Rate in different columns.

bootstrapping spot rates excel


1.1 Using Formula in Excel


  • Create a new column with the heading, Spot Rate under Column E.

Bootstrapping Spot Rates for Annual Bond

The Spot Rate for the 1st year should be the same as the Par Rate for this period.

  • Select cell E7 and enter the cell reference of cell D7 with an equal sign.
  • Press the ENTER key.

Bootstrapping Spot Rates for Annual Bond using Excel Formula

  • Go to cell E8 and enter the following formula into the cell:
  • Press ENTER.

calculating spot rates for 2 year

To get the Spot Rate for the 3rd year, solve the preceding equation. In Excel,

  • Select cell E9 and insert the formula below:

using formula to do bootstrapping spot rates in Excel

  • Press ENTER.

1.2 Utilizing Solver Add-in


The Spot Rate for year 1 in cell D11 is 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.

  • Go to cell D13 and enter the following cell reference:
  • Press ENTER.

Utilizing Solver in Excel

To insert the right-hand side of the equation,

  • Go to cell D14 and write down the formula below:
  • Press ENTER.

We know that in an equation, always,


⇒ LHS – RHS = 0

Here, we applied this logic in cell D15.


shifting RHS of the equation

To get the value of the Spot Rate for 2nd Year in cell D16,

  • Go to the Data tab.
  • Click on Solver on the Analyze group of commands.

Navigating to data tab

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

The Solver Parameters dialog box appears.

  • In the Set Objective box, give the cell reference of cell D15.
  • Select the Value of option.
  • Place the cell reference of D16 in the By Changing Variable Cells box.
  • Click on the Solve button.

Solver parameters dialog box

We can see the Solver Results wizard. The Keep Solver Solution option will be automatically selected.

  • Click OK.

solver results dialog box in Excel

Here is the spot rate in cell D16. The values in cells in the D14:D15 range are automatically changed to make the two sides of the equation equal. The spot rate for the second year is 3.015%.

spot rates for two year

  • See the formula to construct the equation for 3rd year in cell D19.

bootstrapping spot rates for 3 year in Excel

  • Repeat the earlier steps to use the solver to solve the rate for this year.

Read More: How to Perform Bootstrapping in Excel

Example 2 – Bootstrapping Spot Rates for Bi-Annual Bond

In the below dataset we have a Yield to Maturity of Bi-Annual Treasury Bond. The maturity periods are 0.5 years, 1 year, 1.5 years, etc.

Bootstrapping Spot Rates for Bi-Annual Bond


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

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)

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

To build the right-hand side in Excel,

  • Select cell D16 and enter the following formula:
  • Press ENTER.

calculating spot rate for bi annual bond in excel

Read More: How to Resample Time Series in Excel

Practice Section

We have provided a Practice section like the one below in each sheet on the right side for you to practice.

Download the Practice Workbook

You may download the following Excel workbook and practice.

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF