A newer version with four bootstrap methods can be found at:
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%:
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:
Downloads Written in Excel 2013