US Debt Tracker

By Nathan Adlam

Scenario

A small debt agency in Washington DC that specializes in analyzing and forecasting public and private debt has been tasked by the US government for a profile of public and governmental debt. A few questions are to be answered below: 

1. What was the yearly debt percentage increase for each year compared to the previous year?

2. Which months historically have seen the highest/lowest increases in total debt?

3. What is the projected growth of the publicly held debt in the next few years?

The ingested data looked like this, with data for each day (from February 15, 2023 back to April 1, 1993), including a row for debt held by the public, inter-governmental holdings, and total public debt outstanding. 

The data was first transposed into columns to make it more useable for analysis. So, let's look at the first question: What was the yearly debt percentage increase for each year compared to the previous year?

To calculate this, a data point was taken at the last day of each year and put into a table. A formula then calculated the percentage change from each year to the next. 

Because the data wasn't a full year, the 2023 rate was omitted. The chart of the yearly debt percentage increase looks like this. 

A large spike occurs from 2019 to 2020 for debt held by the public, and therefore the total public debt as well. There is no doubt that the COVID pandemic played a large part in this massive increase. Otherwise, for the total public debt outstanding, in the years 2016-2019, the average increase was around 5%. 

Let's look at the next question: Which months historically have seen the highest/lowest increases in total debt?

This question is pretty straightforward, and using a pivot table to aggregate the public debt outstanding into months, we can see the graph of that here. 

We can see that the highest debt increases historically occur from around October to December. The highest overall months of debt are the months from November to February. Higher spending in those months could be a result of increased consumer spending due to holidays. The lowest debt increases historically occur from around April to June. The lowest overall months of debt are also in that same range. 

And now, for the last question: What is the projected growth of the publicly held debt in the next few years?

To answer this question, the publicly held debt was aggregated into years and the Forecast function was used to project the debt until the year 2027. 

From 1997 to 2007, we saw an increase of about 1 trillion in publicly-held debt. From 2008 to 2019, debt increased from 6 trillion to 17 trillion. From 2020 to 2022, debt increased from 21.5 trillion to 25 trillion. From 2023 to 2027, it is projected that the publicly-held debt will increase to 33 million. It appears that the debt will increase at a steady rate over the next 5 years. 

And this concludes this brief US debt tracking analysis. This was a great practice using Excel to analyze a large dataset, using pivot tables and forecasting to make conclusions about US debt data.