Tuesday, November 13, 2012

How to Construct Yield Curves in Excel 2007


Preparation
1. 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.
2. Open Excel 2007.
3. 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
4. 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.
5. Type in the URL of the Yahoo! Finance web page that features the yield data you wish to import. Press the 'Go' button.
6. 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.
7. 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
8. 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.
9. 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.
10. Delete the first 'Yield' column. Right-click on the column letter and choose the 'Delete' function.
11. 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
12. 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.
13. 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.
14. Complete the Chart Wizard. It is acceptable to choose the 'Next' button for each step and use all the default settings.
15. View the resulting yield curve.

Blogger news