How Can I Create a Yield Curve in Excel?

The yield curve depicts the term structures of interest rates for bonds. With term structures that could be normal, inverted, or flat, the shape of a yield curve indicates where future interest rates are headed.

You can create a yield curve in Microsoft Excel if you are given the time to maturities of bonds and their respective yields to maturity. The x-axis of the graph of a yield curve is reserved for the time to maturity, while the yield to maturities are located on the y-axis.

Assume you want to plot the yield curve for the two-, five-, 10-, 20- and 30-year U.S. Treasury bonds (T-bonds). The respective yield to maturities of the U.S.T-bonds are 2.5%, 2.9%, 3.3%, 3.60%, and 3.9%.

5 Steps to Calculating Yield Curve

  1. Using Microsoft Excel, enter “U.S. Treasury Bonds’ Times to Maturity” in cell A1 and “U.S. Treasury Bond’s Yields to Maturity” in cell B1.
  2. Next, enter “2” into cell A2, “5” into cell A3, “10” into cell A4, “20” into cell A5, and “30” into cell A6.
  3. Then, enter “2.5%” into cell B2, “2.9%” into cell B3, “3.3%” into cell B4, “3.6%” into cell B5, and “3.9%” into cell B6.
  4. Select cells A2 through A6 and B2 through B6 together and click on Insert. Under the Charts tab, select Scatter and click on Scatter with Smooth Lines and Markers.
  5. Next, click on the chart, select Chart Elements, and click on Axis Titles. For the horizontal axis, enter “Time to Maturity (In Years)” and “Yields” into the vertical axis title. Enter “U.S. Treasury Bonds Yield Curve” into the Chart Title.

The resulting yield curve for these U.S. T-bonds is considered normal because it is concave down (increasing), and the rates are increasing as the times to maturity are further out.

You may also like...