x

How to Construct Yield Curves in Excel 2007

by James HighlandUpdated September 28, 2017

Items you will need

  • Microsoft Excel

  • Yield Curve Data

Microsoft Excel 2007 is an excellent program for constructing charts of yield curves. The process is not particularly straightforward, but once learned it may be repeated whenever data requires it. A yield curve requires appropriate data to be properly constructed. Usually the yield data is derived from financial instruments, such as the United States Treasury Bonds. In fact, the U.S. Treasury yield curve is a good example of how these charts are built in Excel 2007. The U.S. Treasury yield curve is a significant variable in the investing industry and is watched around the world.

Preparation

Identify yield data for the construction of the yield curve. This data requires clear yield information associated with time factors. Yahoo! Finance provides yield data as part of its free service for sharing bond information.

Open Excel 2007.

Create a new blank worksheet. Press the "New" button at the top of the program window, or choose the "File" menu and the "New" option.

Import Yield Data

Type or import the yield information into a new spreadsheet in Excel 2007. If using Yahoo! Finance, Excel 2007 can pull this information directly from the website. Click the "Data" tab followed by the "From Web" command. A pop-up window will appear.

Type in the URL of the Yahoo! Finance web page that features the yield data you wish to import. Press the "Go" button.

Locate the table on the web page which contains the yield data. A yellow button with an arrow will appear in the upper left corner of the data table.

Click the yellow button and then press the "Import" button in the lower right of the window. The yield data is imported.

Format the Data

Copy the "Yield" column in your data set. Click the column letter at the top and press the "Copy" button at the top of the program window, or the "Control-C" keyboard combination.

Click the first cell in the first empty column to the right of the data set. Press the "Paste" button or the "Control-V" keyboard combination. This places the "Yield" data as the last column, which Excel requires for creating the proper yield curve chart.

Delete the first "Yield" column. Right-click on the column letter and choose the "Delete" function.

Change the "Maturity" data in the first column to numeric data only. For example, delete the word "Month" from the cell which contains the content "3 Month" so the result is simple "3."

Create Yield Curve

Hold down the mouse and drag from cell A2 to cell B9. Press the "Control" key and do the same from cell E2 to cell E9.

Click the "Insert" tab at the top of the Excel 2007 window. Locate the "Charts" group and click the "Scatter" option. Choose any of the "Scatter" design formats. The Excel Chart Wizard will open.

Complete the Chart Wizard. It is acceptable to choose the "Next" button for each step and use all the default settings.

View the resulting yield curve.

Video of the Day

Brought to you by Techwalla
Brought to you by Techwalla

More Articles