US Debt Analysis & Forecasting

OBJECTIVE: Analyze the historical trend and projected growth of U.S. public debt using daily records from 1993 to 2023. This project focuses on debt held by the public and intragovernmental holdings, examining growth rates, monthly patterns, and long-term projections through Excel-based calculations and visualizations.

BACKGROUND: U.S. national debt is one of the most closely watched indicators of fiscal health. As borrowing increases, understanding how debt levels evolve over time helps contextualize policy debates and economic conditions. This analysis addresses the following:

1. What is the annual growth rate of U.S. public debt?

2. Which months historically see the largest or smallest increases?

3. What are the long-term trends in debt composition?

4. How is publicly held debt projected to grow in the coming years?

TECH STACK:

1. Microsoft Excel: End-to-end data analysis and visualization

2. Pivot Tables: Aggregated historical trends by year and month

3. Built-in Excel Functions: FORECAST, YEAR, AVERAGE, IFERROR, and percentage change formulas

4. Line & Bar Charts: Visualized debt growth, monthly trends, and projections

5. Cell Formulas: Embedded directly into sheets for automated calculations in:

   • Year-over-year growth

   • Monthly averaging

   • Forecasting public debt growth

PROCESS:

1. Data Cleaning:
   ◇ Transformed wide-format data with dates as columns into long-form structured records
   ◇ Standardized number formatting to trillions for readability
   ◇ Removed missing or zero-value rows to ensure accurate results

2. Trend Analysis & Visualization:
   ◇ Created line charts showing debt trends from 1993 to 2023
   ◇ Built bar charts to highlight average monthly increases in total debt
   ◇ Calculated annual percentage changes in:

   • Debt held by the public

   • Intragovernmental Holdings

   • Total Public Debt Outstanding

3. Forecasting:
   ◇ Focused projections on "Debt Held by the Public"
   ◇ Used Excel’s FORECAST function to extend the trend through 2028
   ◇ Applied historical high values to contextualize projections

KEY INSIGHTS:

• Annual Growth: From 2016 to 2019, the Total Public Debt Outstanding increased at an average rate of approximately 5% per year

• Monthly Patterns: Historically, the largest increases in debt occur in January, February, November, and December

• Trendline: An upward trajectory is evident in projected publicly held debt

• Projection: Public debt is expected to continue growing, driven by long-term borrowing trends

CHALLENGES & SOLUTIONS:

⚠️ Challenge 1: The raw data had dates as column headers (wide format)
✅ Solution: Restructured the dataset using formulas to convert it into long format

⚠️ Challenge 2: Debt values were inconsistently formatted and included placeholders
✅ Solution: Cleaned and standardized values using formatting rules and filters

⚠️ Challenge 3: Forecasting within Excel had limitations in flexibility
✅ Solution: Used historical values alongside the FORECAST function to estimate reasonable future trends

⚠️ Challenge 4: The dataset begins in April 1993, but early years are incomplete, preventing consistent year-over-year calculations
✅ Solution: Focused analysis and charting on years with full records (e.g., starting from 1997) to ensure reliable comparisons

DATA SOURCES:

• Dataset: US_Debt_Analysis_&_Forecasting.xlsx

• Source: The dataset was sourced from fiscaldata.treasury.gov, specifically the U.S. Treasury’s “Debt to the Penny” historical records

• Timeframe: April 1993 – February 2023

DATA DICTIONARY:

• Debt Held by the Public: U.S. debt owned by external parties, including individuals, corporations, and foreign governments

• Intragovernmental Holdings: Internal debt owed by the federal government to its own agencies (e.g., Social Security)

• Total Public Debt Outstanding: Combined total of both categories above

VISUAL WALKTHROUGH: Each visualization below corresponds to the key questions above, now brought to life through data storytelling.

Click on any image to enlarge it to full screen.

Summary:

From 2016 to 2019, the Total Public Debt Outstanding increased at an average rate of approximately 5% per year. In 2020, there was a significant spike, likely driven by the U.S. government's pandemic response measures.

Summary:

Historically, the largest increases in debt occur in January, February, November, and December. In contrast, the lowest increases typically happen in April, May, June, and July.

Hypothesis:

During months with high debt increases, major U.S. holidays such as Thanksgiving, Christmas, and New Year’s lead to increased consumer spending, particularly on gifts. As a result, people may take on more debt during these months.

Conversely, during months with lower debt increases, the absence of major holidays reduces consumer spending, leading to less borrowing.

Summary:

From 1997 to 2007, publicly held debt increased by approximately $1 trillion.

From 2008 to 2019, debt rose from $6 trillion to $17 trillion.

From 2020 to 2022, debt increased from $21.5 trillion to $25 trillion.

From 2023 to 2027, publicly held debt is projected to reach $33 trillion.

Conclusion:

Publicly held debt is projected to continue rising significantly over the next five years.

Potential factors influencing debt growth include trends in the stock market, housing market, credit card debt levels, and unemployment rates.