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:
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.
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%
User Defined Function:
The UDF is VBA and does the same calculations as the “Spreadsheet” method. The functions name is “spotrate”
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)
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)))
myrange(i) = (((100 + yearsandrates(i, 2) / 2) / (100 – c)) ^ (1 / (yearsandrates(j, 1) * 2)) – 1) * 200
Spot = myrange(i)
spotrate = Spot
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:
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.
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.