U.S. Debt Analysis Project

Project Introduction

This project looks at U.S. federal debt trends from 1993-2023 in Excel, using pivot tables, XLOOKUP, and ETS forecasting. I dug into three questions: how debt changed year over year around major economic events, which months tend to see the biggest increases, and where publicly held debt is headed through 2027.

The data needed a fair amount of cleaning first, especially before 2005, where reporting was inconsistent and some values came in as “null.”

Initial Data Cleaning

First, I kept the raw data intact and created a new sheet transposing the data to have the debt data in rows rather than columns. This is much more usable in pivot tables where each column would be identified (thousands of columns).

Taking an initial glance at the data, I can see data wasn’t consistently reported until approximately 2005 for both Debt Held By Public and Intragovernmental Holdings. No ‘blanks’ or other outliers were discovered during this process. However, null values or string “null” values won’t help me work with numerical data. So I’ll remove “null” values and leave cells empty where no data is found. This is also where I could change from scientific notation to numerical depending on preference. With the data cleaned and properly formatted for analysis, I’ll dive into the first question.

Demonstrating pivot table fields for yearly debt percentage.

Yearly Debt Percentage Increase

Question for analysis: What was the Yearly Debt Percentage Increase for each year compared to the previous year?

For this first question, I’ll create a pivot table in a new sheet to filter the data to the final reported debt per each year. I don’t want to assume that this would always be reported on a specific date like 12/31/YY. So with Record Date filtered down to year used as the row values, I can add columns to show the maximum date reported per year (or latest report), as well as the maximum debt value for the given year. I’ll also filter out 2023 data since it’s partial and only has data through February 2023.

Formula for pulling in the Total Public Debt Outstanding value by date lookup in the Record Date column in the cleaned data sheet:

=XLOOKUP(C9,'Cleaned Data'!A:A, 'Cleaned Data'!D:D)

While debt is likely to continually increase, there’s also a chance the highest debt value each year happens sometime earlier in the year. I’ll go with the final reported date’s debt value for consistency. Depending on the needs of a report, perhaps it would be more interesting to look at the highest overall value per year instead of using the final reported data (often in late December). I’ve added some conditional highlighting to showcase the few data points with discrepancies.

Conditional highlighting to showcase max value inconsistencies (some occur before the year end).

Conditional formatting to highlight values that do not match:

=$D6<>$E6

With the accurate total debt per year calculated, I can now calculate the percentage increase year over year using a formula: =((E6-E7) / E7) (sorted descending) and formatting the column or table values to a percentage. I can see a surprising debt decrease (1.97%) in 2001 and a big debt spike (19.6%) in 2020, most likely due to COVID.

Demonstrating X-lookup across 3 columns to derive yearly debt percentage change.

Having calculated the annual Total Public Debt Outstanding and percent change year over year, I applied this same formula to the two remaining columns Debt Held by the Public and Intragovernmental Holdings.

=XLOOKUP(C9,'Cleaned Data'!A:A, 'Cleaned Data'!B:B)
=XLOOKUP(C9,'Cleaned Data'!A:A, 'Cleaned Data'!C:C)

With the data and percentages now in place, I can create a visualization to demonstrate the yearly debt percentage change:

Yearly debt percentage change visualization for Total Public Debt Outstanding, Debt Held by
the Public, and Intragovernmental Holdings.

Summary/Conclusions: The chart shows three major spikes in debt growth that align with economic crises: the 2008 financial crisis, and the 2020-2021 pandemic period which saw the largest increases with debt held by the public jumping over 25%. Outside of these crisis periods, annual debt growth typically stayed in the 2-8% range across all categories. The data reveals that debt held by the public is the most volatile component, while intragovernmental holdings remain relatively stable throughout the timeframe.

Historical Monthly Total Debt Averages: Highs & Lows

Question for analysis: Which months historically have seen the highest/lowest increases in Total debt?

Looking at the cleaned data, I need to aggregate based on month to look at the historical averages. I’ll set up a pivot table with the Record Month for the row and the average Total Debt Outstanding for the values. For simplicity here, I kept it in scientific notation but I can easily format the value in the pivot table if needed.

Initial pivot table aggregating monthly averages for total U.S. debt.

This question requires less data manipulation, so I can rely instead on aggregating data in the pivot table. When copying over the cleaned data, I removed the Debt Held by the Public and Intragovernmental Holdings to focus on total debt averages. Here’s the final data visualization:

Visualization for monthly average total debts showcasing an increase in November-January
and a decrease in April-June.

Summary/Conclusions: Highest monthly debt increases historically occur during November, December, and January. While lowest monthly debt increases occur during April, May, and June. For U.S. debt data, I’d hypothesize that higher debt averages in November-January are caused in part by holidays and increased spending. While April-June appears to put less economic pressures on spending.

Projected Growth of Publicly Held Debt

Question for analysis: What is the projected growth of the publicly held debt in the next few years?

For the sake of demonstration and exploring the forecasting abilities within Excel, I’ll start by creating a pivot table to look at the maximum recorded debt per year within the existing data. I’ll filter out 1993-1996 where there’s no existing data for the Debt Held by the Public column.

Pivot table for forecasting publicly held debt

Then I can introduce the forecast using the exponential triple smoothing (ETS) forecast function for years 2023-2027:

=FORECAST.ETS(E30,$F$3:$F$29,$E$3:$E$29)

I kept the data anchored to real reported values so I don’t lose the accuracy of the forecast by including new “forecasted” results in the formula. I’ll also exclude the 2023 result since it’s considered partial for this dataset (last recorded value in Q1 of 2023). For those curious, the actual total publicly held debt for 2023 was 26.3 trillion (source) and for 2024 (source) was approximately 29 trillion by end of fiscal year.

Summary/Conclusions: From 1997-2007, there was an increase of about 1 trillion in publicly held debt. From 2008-2019, debt increased from 6 trillion to 17 trillion. From 2020-2022, debt increased 21.5 trillion to 25 trillion. From 2023-2027, the publicly held debt is forecasted to reach 33 trillion. Publicly held debt is projected to increase at a steady rate over the next 5 years.

Future Exploration

That covers the three questions, though there’s more I could do to round out the picture. I could pull in concurrent data for credit card reporting, the housing market, or unemployment rates, or look at the volatility during major economic events.

You can find the final output of my analysis in the “Output” tab of my .xlsx file in the repo.