A Fourth Way To Bootstrap Spot Rates

In May of 2015 I published a post called “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve”. There was also an Excel workbook linked to that post. The workbook link is:

http://pistulka.com/Excel_Shared/3_Spot_Rates.xlsm

Rather than posting a new workbook, I am leaving the old workbook, but I added a new sheet called “Fourth Method”. As I was looking through some of my old Excel workbooks, I came across this method for calculating spot rates, that I should have used in the original post.

For an explanation of the first three bootstrap methods, see my post “3 -Ways to Bootstrap Spot Rates for the Treasury Yield Curve” at

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

After viewing the first three methods, come back to this post for an explanation of the forth method.

Method four on sheet “Fourth Method”:

This method may or may not be easier to follow than the first three. The difference between the formulas on sheets “Formula” and “Fourth Method” is that the calculations on the forth method are broken out into two extra columns. As was true with the other methods, the 6-month and 1-year Treasury yields are considered to already be spot rates, in that they are Treasury bills with no coupons, only a final payment of principal.

 

The first new column are the discount factors. This is a running present value of $1, calculated using the spot rate and term for each row. The second column is the present value of each coupon payment using the sum of all the previous discount rates multiplied by the semiannual coupon payment.

 

 

 

 

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

8 Comments

  1. Hi Don,
    Your models are awesome! Thank you for all the great insights. Would you be able to give me some guidance on how to use your excel formula to calculate the spot rate for a loan that pays interest monthly instead of semi annually? Any help would be greatly appreciated đŸ™‚
    GP

    1. Hi GP,
      Spot rates are usually calculated on Treasury securities because they have maturities from 4-weeks out to 30-years. Typically, you need rates all along the curve to calculate spot rates. If you are asking to convert a semiannual rate to a monthly rate, you can use the calculator attached to this post:
      http://pistulka.com/Other/?p=803
      Don

      1. Hi Don,
        I checked out your calculator for converting yields based on semi annual interest payments to yields based on monthly interest payments and found it to be very helpful. Thank you for all you do to help people like me to utilize Excel to solve problems. You are a true master at Financial Modelling.
        GP

Leave a Reply

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