*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:

- 10 Year maturity interest rate - 1 Month maturity interest rate
- 10 Year maturity interest rate - 1 Year maturity interest rate
- 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:

- Click on the “Insert Tab” on the Excel Ribbon.
- Insert a “Scatter Plot with Lines”.
- For each chart, insert the date column as the X values and the yield curve as the Y values.
- 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.
- The rest of the steps focus on formatting the graph to make it readable and to call attention to important data.
- First, lower the X axis so that it does not cut into the datapoints.
- Next, set the X axis bounds to the beginning and ending dates in the dataset.
- Next, format the X axis tick marks to show each quarter.
- 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.
- 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.
- Viewing the graphs, what time periods stand out to you?

**Data Files**

## Suggestions and Hints

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

## Yield Curve Example

**Solution**

## Solution Steps

- Click on the “Insert Tab” on the Excel Ribbon.
- 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.
- 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.
- 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.
- The rest of the steps focus on formatting the graph to make it readable and to call attention to important data.
- 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”.
- 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.
- 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”.
- 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.
- 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