This Microsoft Excel spreadsheet explores interest
rates and bond prices. The first two worksheets relate the zero coupon bond
yield curve and shortterm 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 1year spot rates. The spot
rates can be adjusted with the spinner controls. The graph will reflect
the changes in both the 1year 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
xaxis (horizontal axis) of the graph. There are two yaxis. The left side's
yaxis measures the bond's price, from 0 to 2,000. The right side's yaxis
measures the bond's duration, from 0 to 30. The three graphs are as follows:
 The green chart (bottom,
lefthand 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,
righthand 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,
righthand 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,
righthand 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,
righthand 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
durationgiven 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.
