Dr. Jason Greene
Associate Professor of Finance
1243 Robinson College of Business
Phone: 404-651-2655
Fax: 404-651-2630
E-mail: jgreene7@gsu.edu
Fi8000
 
 
Interest Rates Spreadsheet


This Microsoft Excel spreadsheet explores interest rates and bond prices. The first two worksheets relate the zero coupon bond yield curve and short-term interest rates. The remaining worksheets graph the relationship between a bond's price and key variables: maturity, coupon rate, and yield to maturity. These worksheets also offer a graphical representation of duration and how it is related to the key variables.

This file is intended for use only by students enrolled in Finance courses at the Robinson College of Business at Georgia State University. However, if you would like to use this file in another setting, please contact the author of the spreadsheet (Jason Greene) for permission.

Download Instructions

  • Click here to download the Microsoft Excel spreadsheet. If a box appears asking for a username and password, click "Cancel" and the file should open. You should save the spreadsheet on your local disk in order to access it in the future.

Spreadsheet Instructions

When you first open the spreadsheet, it may not fit well on your screen. You should first choose to view the spreadsheet in Full Screen mode. Also, to get the entire graph on the screen, Zoom in or out. For most screens, this will require you to Zoom by 80% or so.

There are 6 worksheets in this spreadsheet file. Below are descriptions of each worksheet.

  • Yield Worksheet
    • This worksheet graphs the zero coupon bond yield curve for bonds with 1 to 8 years to maturity. This shows a world of certainty in which bonds are valued using the (known) future 1-year spot rates. The spot rates can be adjusted with the spinner controls. The graph will reflect the changes in both the 1-year spot rates and the yield to maturity on the zero coupon bonds.
  • Forward Worksheet
    • Like the Yield worksheet, this worksheet graphs the zero coupon bond yield curve for bonds with 1 to 8 years to maturity. However, the implied forward rates are calculated from the zero coupon bond yields. The yields for the each maturity can be adjusted with the spinner controls. The graph will reflect the changes in both the yields and the implied forward rates.
  • Price and Duration Worksheet
    • This worksheet contains three graphs and three spinner controls. The graphs and the spinners are color coded. The box containing the spinner control indicates the variable on the x-axis (horizontal axis) of the graph. There are two y-axis. The left side's y-axis measures the bond's price, from 0 to 2,000. The right side's y-axis measures the bond's duration, from 0 to 30. The three graphs are as follows:
    • The green chart (bottom, left-hand corner) graphs the bond's price (black line) and the bond's duration (white line) as a function of the bond's yield to maturity (horizontal axis). Adjusting the spinner, under 'Yield' will change the bond's yield to maturity. This will be reflected in the green chart as movements along the price and duration curve. The red and blue charts reflect a change in the 'Yield' as changes in the shape of the curves.
    • The blue chart (top, right-hand corner) graphs the bond's price (black line) and the bond's duration (white line) as a function of the bond's coupon rate (horizontal axis). Adjusting the spinner, under 'Coupon' will change the bond's coupon rate. This will be reflected in the blue chart as movements along the price and duration curve. The red and green charts reflect a change in the 'Coupon' as changes in the shape of the curves.
    • The red chart (bottom, right-hand corner) graphs the bond's price (black line) and the bond's duration (white line) as a function of the bond's maturity (horizontal axis). Adjusting the spinner, under 'Maturity' will change the bond's time to maturity. This will be reflected in the red chart as movements along the price and duration curve. The blue and green charts reflect a change in the 'Maturity' as changes in the shape of the curves.
  • Convexity
    • Like the Price and Duration worksheet, this worksheet allows you to change the 'Yield', 'Coupon', and 'Maturity' of a bond. There are three graphs.
    • The Price chart (top, right-hand corner) graphs the price of the bond as function of the yield to maturity (horizontal axis). The tangent line (straight red line) indicates the price movement associated with or predicted by the bond's duration. The curvature of the price graph away from the straight line reveals the error from duration pricing due to convexity.
    • The Duration chart (bottom, right-hand corner) graphs the duration of the bond as a function of the bond's yield to maturity (horizontal axis).
    • The Price % chart graphs the price of the bond as a percentage of its current price (at the set 'Yield') as a function of the yield to maturity (horizontal axis). The tangent line (straight red line) indicates the percentage price movement associated with or predicted by the bond's duration. As with the price chart, The curvature of the price graph away from the straight line reveals the error from duration-given percentage price changes due to convexity.
    • Changes in the 'Coupon' and 'Maturity' will result in changes to the shape of all three graphs. Changes in the 'Yield' will be reflected as movements along the curves in all graphs. This will also alter the shape of the 'Price %' graph.
  • Price Worksheet
    • This worksheet graphs the price of three bonds as a function of the yield to maturity (horizontal axis). The coupon rate for each bond can be changed with the spinner under 'Coupon'. The maturity for each bond can also be changed. These changes will cause the shape of the affected bond's price curve to change. Changing the 'Yield' will indicate movement along each curve. The price at the specified yield is reported under the 'Coupon' for each bond.
  • Duration Worksheet
    • This worksheet graphs the duration of three bonds as a function of the yield to maturity (horizontal axis). The coupon rate for each bond can be changed with the spinner under 'Coupon'. The maturity for each bond can also be changed. These changes will cause the shape of the affected bond's duration curve to change. Changing the 'Yield' will indicate movement along each curve. The duration at the specified yield is reported under the 'Coupon' for each bond.