11|EXCEL – Graphing Yield Curves

[Jonathan_Weston]

BYU Student Author: @Jonathan_Weston
Reviewers: @Erick_Sizilio, @Donovon
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

• Excel

Need a program? Click here.

Overview
The U.S. Department of the Treasury provides information regarding the interest rates that are offered on each maturity date for the U.S. Treasury Bond. These interest rates are an accurate predictor of economic recessions in the near future. Normally, we see that long term interest rates are higher than short term rates because risk and uncertainty increase with the duration of time. However, when short term rates are higher, it signals a lack of confidence in the strength of the economy. When short term rates remain higher for the duration of at least one fiscal quarter, we can expect an economic recession within 12-18 months. Economists demonstrate this difference in rates using the Yield Curve. The Yield Curve graphs the interest rates for each bond maturity for a given date. Normally the graph curves upwards, but when the curve inverts, this demonstrates that short term rates are higher as shown in the images below.

To graph the Yield curve over a period of time, economists typically use the difference of the following rates:

1. 10 Year maturity interest rate - 1 Month maturity interest rate
2. 10 Year maturity interest rate - 1 Year maturity interest rate
3. 10 Year maturity interest rate - 2 Year maturity interest rate

Included below is a picture of a recreation of the 10 Year – 1 Month chart you will be creating:

Instructions
Using the Yield_Curve_Rates_2017-2022.xlsx datafile, recreate each of the 3 yield curve graphs using scatterplots with the following requirements:

1. Click on the “Insert Tab” on the Excel Ribbon.
2. Insert a “Scatter Plot with Lines”.
3. For each chart, insert the date column as the X values and the yield curve as the Y values.
4. Each chart should have only one yield curve. Chart 1 will compare the 10Y-1Mo yield curve in Column M. Chart 2 will compare the 10Y-1Y yield curve in Column N. Chart 3 will compare the 10Y-2Y yield curve in Column O.
5. The rest of the steps focus on formatting the graph to make it readable and to call attention to important data.
6. First, lower the X axis so that it does not cut into the datapoints.
7. Next, set the X axis bounds to the beginning and ending dates in the dataset.
8. Next, format the X axis tick marks to show each quarter.
9. However, this makes the X axis a little cluttered and messy. Let’s format the X axis tick marks to be a little shorter. Format each tick mart to show the month and year so that your first tick mark looks like “1/17” representing January 2017.
10. Lastly, we want to add a reference line to the chart that highlights when the yield curve is negative. To do this in excel, you will have to add a new column to the dataset with a value of “0” in each row. Then, add this as an additional series to the chart.
11. Viewing the graphs, what time periods stand out to you?

Data Files

• Challenge11_Graphing_Yield_Cruves.xlsx
• Challenge11_Data_Dictionary.docx

Suggestions and Hints

1. To set the X axis bounds, view the first and last date as a general number to find the minimum and maximum bound values.
2. One quarter is equal to 3 months or 90 days.
3. The Format code for the tick marks is “m/yy”

Yield Curve Example

Solution

Solution Steps

1. Click on the “Insert Tab” on the Excel Ribbon.
2. Go to the Charts section and click on the “Insert Scatter” button and then click on the “Scatter with Straight Lines” option from the drop-down menu.
3. Right click on the newly created graph and click on “Select Data”. This will pull up a box where you can add new series to your scatter chart.
4. Click on the “Add” button under Legend Entries. Here is where we will compare the Yield curve (e.g., 10 Year – 1 Month) and the date the rates were recorded. The Y values will be the entire Yield curve column (excluding the table header) and the X values will be the dates (excluding the table header). Once you have made your selections, click “OK” and your chart should show the Yield curve you selected.
5. The rest of the steps focus on formatting the graph to make it readable and to call attention to important data.
6. First, we want to lower the X axis ticks so that they aren’t in the way of the datapoints. Right click on the X axis and click on “Format Axis”. You should see four tabs: a paint bucket, a pentagon, a square, and a column chart. Click on the column chart and scroll down to labels. Set the label position to “Low”.
7. Next, we want to set the bounds of the X axis so that the data points fill up the span of the chart. In the same tab from step 6, scroll to the Axis Options. We want to set the Bounds minimum and maximum to match the dates in our dataset. To do this, we need to enter the dates as a number that excel can read. To see what those numbers are, click on the first and last dates of the dataset, and format the date as a “General” Number. This can be done in the Number section of the “Home tab” on the Excel Ribbon. The minimum bound can be set to 42738 and the maximum bound can be set to 44925.
8. Next, we want to format the tick marks on the X axis to be every three months (one fiscal quarter). Slight inversions of the Yield curve are normal, it’s only when they stay negative for longer than three months that they become indicators for a recession. To make this change, we will stay in the same formatting tab from the two previous steps, but we will need to change the “Units Major” in “Axis Options”. For excel, one day is “1”, so three months will equal “90”.
9. However, this makes the X axis a little cluttered and messy. Let’s format the X axis tick marks to be a little shorter. In the same formatting menu, scroll down to Number and go the “Format Code” input box. Let’s format it to just show the month number and the last two digits of the year. Enter “m/yy” into the “Format Code” input box.
10. Lastly, we want to add a reference line to the chart that highlights when the yield curve is negative. To do this in excel, you will have to add a new column to the dataset with a value of “0” in each row. Then, add this as an additional series to the chart using the same process in Steps 3 & 4.

Challenge11_Solution.xlsx
Solution Video: Challenge 11|EXCEL – Graphing Yield Curves

[Parker_Sherwood]

Great challenge! I think of myself as a raw data kind of guy but this was a good refresher about the power and importance of visualizations, even simple charts. I thought the volatility between the 10Y and 1Mo bond was interesting, especially around Sep. 2019. I found that the latter half of 2022 was a time period that stood out.
One thing I did to save some time on this challenge was to: make the first chart and complete the formatting, then select the chart area and use Ctrl + C to copy it. I then pasted two copies of the chart and simply changed the y range of the data to be the appropriate bond comparison.

[klayton]

I thought that it was interesting that all three curves responded well to the COVID pandemic and were positive come March 2020. However, now that we are moving forward out of the pandemic the curves are all starting to go negative. It really shows the long-term effects of all the lockdowns had on the economy.

Also, good tips on some of the finer details that go into chart making in excel. I didn’t know about the number formatting, but I’m sure glad I do now!

Here’s what I got for the 10Y-2Y graph. It’s looks like the most optimistic one historically albeit it doesn’t look great right now.

[Mark]

Good challenge for refreshing Excel graphing skills. Here are a few tips/unique things I did:

The first is that you can actually type the dates (eg, “12/30/2022”) into the Minimum and Maximum bounds of the Axis Options and it will spit out the number for the date once you click ENTER. You can also enter “3/1/1900” into the Major box under Units and it will spit out 90. Pretty cool functionality!
The second is that you can click on the x-axis, go to the Fill & Line options (the bucket symbol), and add a solid line under the Line options. This will avoid the need to add another column to the data and another series to the graphs (Step 10 of instructions).

[Alex_Garrett]

This was a neat challenge because it dealt with real data. I had a little fun with the formatting and included my 10 Y to 1 Y yield curve. These graphs helped me see that for the past year or so, the short-term interest rate has been consistently higher than the long-term rate. Neat challenge!

[Hyrum]

Great Challenge. I have typically used tableau for my charts recently, and it’s always good to switch things up and try different platforms. There isn’t as much formatting with excel (at least, not at my skill level), but it definitely gets the job done!

[Carter_Ringger]

Couldn’t figure out the titles. Definitely interesting.

[McKenna_Evans]

There are a lot of time periods that are interesting, especially when they fall below the line. In the last curve, it looks like a lot of 2022 is below the line. I couldn’t figure out the right spacing so all of the dates are crowded.