Forward Rates Part 3: Spot Rates

A newer version with four bootstrap methods can be found at:

A Forth Way To Bootstrap Spot Rates

This method of calculating spot rates is referred to as the bootstrapping method. Each spot rate (or zero coupon) along the Treasury yield curve needs the previous spot rates, in order to discount the current securities coupon payments. For our example, the current coupon yields are used. I have arbitrarily priced the par bonds at yields from 1% to 8%:

Spot_Rate_coupons

The table calculates 60 spot rates, so I interpolated the yields between these yields in 6 month increments. The table is very large, but if you enter the formula below in cell K5, you can drag it across the rest of the table to calculate the spot rates. Conditional formatting was used to highlight the spot rates.

This image is a small piece of the table:

 

I am going to show how the 1.5 year spot rate is calculated. After that, the calculations are basically the same:

 

Download workbook “Spot_Rates” from:

http://www.pistulka.com/Excel_Shared/ 

Downloads Written in Excel 2013

 

 

 

 

 

 

 

 

 

2 thoughts on “Forward Rates Part 3: Spot Rates

  1. Pingback: 3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve | Excel@CFO

  2. Pingback: U.S. Treasury Forward Rate Curves | Excel@CFO

Leave a Reply

Your email address will not be published. Required fields are marked *

WordPress spam blocked by CleanTalk.