Note: I have added a fourth method of calculating spot rates for the Treasury yield curve. The workbook is the same. For a brief explanation of the forth method:

http://pistulka.com/Other/?p=2812

I have used what I am calling the “spreadsheet method” of bootstrapping spot rate from the U.S. Treasury yield curve in two pervious posts. The posts are U.S. Treasury Forward Rate Curves & Forward Rates Part 3: Spot Rates. I used the “spreadsheet method” because it seemed easier to understand the process of creating a Treasury spot curve. In this post I will offer three ways to get the same results:

- Spreadsheet method
- User defined function
- Excel formulas

All three methods will use the same Treasury Yields, from the six month bill out to the 30-year bond. In order to create a spot rate for each six month period (60 spot rates), I used linear interpolation between each given yield. I used the following Treasury yields:

It is assumed, for the purpose of these calculations, that all bonds are par bonds. For example, the 3-year note at a yield of 1.19% is assumed to have a coupon of 1.19%, and pays interest of $5.95 every six months on $1,000. Also, although Treasury notes pay on an actual/actual basis, I am not using dates, so I am assuming each 6-month period has an equal number of days.

.

Spreadsheet Method:

Each calculation assumes $100 in principal. Basically, starting at six months, each coupon payment is present valued using the corresponding spot rates that were previously calculated. In other words, we move to the right calculating new spot rates, and use those spot rates to discount the next bond’s coupon payments.

The present valued coupons are summed, and subtracted from the original $100 in principal. Take for example the 3-year note in the above image. There were five coupon payments that were present valued. That means that in present value terms, all five payments are as if they were received at the initial investment. They are therefore subtracted from the $100 initial principal investment. The future payment is the last coupon, including the $100 in principal:

.

**Present value = $100 – $2.938273 = $97.06173**

**Future Value = $100 + $.595 = $100.595**

**Solve for Annual Return (FV/PV) = ($100.595/97.06173)^(1/3)-1 = 1.19898%**

**Convert to Semiannual Basis =2*((1+1.19898%)^(1/2))-2 = 1.19541%**

For more detailed information you can read

__Forward Rates Part 3: Spot Rates__..

**User Defined Function:**

The UDF is VBA and does the same calculations as the “Spreadsheet” method. The functions name is “spotrate”

**=spotrate ((E$5:F6)**

The range the function is asking for is two columns, the years and yield columns down to the 30-year bond (red box). Note that the row 5 remains an absolute. The function can be entered at the 1-years sport rate and copied down. There is a big advantage to using the UDF, in that it does not need the shorter term spot rates to be calculated first, in order to calculate a specific spot rate. The UDF calculates those sport rates each time it is run.

**Function spotrate(yearsandrates As Range)**

**Dim myrange(100)**

**last = yearsandrates.Count / 2**

**myrange(1) = yearsandrates(1, 2)**

**If last = 1 Then Spot = myrange(1): spotrate = Spot: Exit Function**

**For i = 2 To last**

**c = 0**

**For j = 1 To i – 1**

**c = c + (yearsandrates(i, 2) / 2 * (1 / (1 + (myrange(j) / 200)) ^ (yearsandrates(j, 1) * 2)))**

**Next j**

**myrange(i) = (((100 + yearsandrates(i, 2) / 2) / (100 – c)) ^ (1 / (yearsandrates(j, 1) * 2)) – 1) * 200**

**Spot = myrange(i)**

**Next i**

**spotrate = Spot**

**End Function**

.

Excel Formula

The formula does exactly what the spreadsheet method does, but without the additional columns used in the spreadsheet method. The three year spot rate formula looks like this:

_

=IF($E10=0.5,F10,(((100+$F10/2)/(100-SUMPRODUCT((1/(1+$G$5:$G9/200))^($E$5:$E9*2)*($F10/2))))^(1/($E10*2))-1)*200)

_

The formula should be pasted in the first cell in the “Spot Rates” column and copied down. Note that the purple part of the formula needs a 6-month bill to start. The 6-month (.5 year) will not need to be calculated, because it is already a spot rate, in that it does not require any reinvestment of interest payments. The green portion of the formula solves for the present value of all the preceding coupon payments. The brown portion of the formula solves for the rate that equates the difference between the original investment less the present value of each coupon, and the last principle and coupon payment.

_

**Summary**

All three methods give the same results. The formula method takes 60 columns to calculate what the formula does in one column. The UDF requires VBA, but can calculate the spot rate for one term, without the need for the previous spot rates to be calculated first.

.

Download “3_Spot_Rates”

FloraHello, thank you very much! Is there any way to do it in C#? Thanks.

Don PistulkaPost authorFlora,

I don’t know C#.

Don

Pingback: A Forth Way To Bootstrap Spot Rates. | Excel@CFO

LiHi Don,

Thanks very much for the sharing. I am wondering what is the difference between linearly interpolated discount factors after bootstrapping and discount factors from linearly interpolated and bootstrapped interest rate? Thanks for sharing your opinion.

Best,

Li

Don PistulkaPost authorLi,

The reason I need to interpolate the Treasury curve when bootstrapping spot rates is that I don’t have access to live Treasury yields for each 6-month period out to the 30-year bond. I only have access to the current coupons (less than a dozen yields for the 60 spot rates I am calculating).The more live Treasury yields you have out the curve that mature either on or close to the six month increment that I used, the better your accuracy will be. My examples for bootstrapping are more about explaining the process, than being the most accurate. If I had access to a provider like Bloomberg for live Treasury security yields, I could build a more accurate spot curve.

I hope that helps.

Don

Donghyun Lee“Download “3_Spot_Rates”” link is not valid.

Don PistulkaPost authorThanks. It should work now.

Don PistulkaPost authorIt is a little disconcerting that the post has been out there for three days and Donghyun was the first one to tell me the link to the spreadsheet was broken.