Back

Overview

Project Link: View Project on OneDrive
US Debt Tracker is an Excel-based data analysis project created to explore and visualize trends in the United States’ public and governmental debt.
The project simulates a real-world scenario where a debt analytics agency in Washington D.C. was tasked by the U.S. Government to provide insights on historical and projected debt patterns.

The analysis focused on answering key questions about the yearly debt percentage increase, monthly historical trends, and future projections of publicly held debt.

Scenario

You were hired by a small debt agency in Washington, D.C. that specializes in analyzing and forecasting public and private debt.
The U.S. Government has requested an in-depth analysis of public and intergovernmental debt to answer the following questions:

  1. What was the yearly debt percentage increase for each year compared to the previous year?
  2. Which months historically have seen the highest and lowest increases in total debt?
  3. What is the projected growth of the publicly held debt in the next few years?

Tools & Techniques

  • Microsoft Excel for data cleaning, formula-based calculations, and visualization.
  • Pivot Tables and Charts for aggregating and analyzing patterns.
  • Scenario-based analysis for data interpretation and storytelling.
  • Data Visualization Techniques including:
    • Line charts for year-over-year growth.
    • Bar charts for monthly averages.
    • Area charts for future projections.

Data Preparation

The project utilized historical U.S. debt data containing:

  • Debt Held by the Public
  • Intragovernmental Holdings
  • Total Public Debt Outstanding

Steps taken:

  1. Cleaned and structured raw data to ensure consistency across years.
  2. Calculated year-over-year percentage changes for each debt category.
  3. Summarized monthly debt averages to identify historical seasonal patterns.
  4. Modeled and visualized projected debt growth from 1997–2027.

Key Insights & Visuals

📈 Yearly Debt Percentage Increase

  • Between 2016–2019, the average increase in Total Public Debt Outstanding was around 5%.
  • A significant spike in 2020 was observed, primarily due to pandemic-related economic responses.

📊 Monthly Historical Debt Trends

  • Highest increases occurred during January, February, November, and December, aligning with holiday and spending seasons.
  • Lowest increases were seen in April, May, June, and July, corresponding to periods with fewer major economic activities.
  • Hypothesis: Consumer spending and government fiscal behavior around holidays contribute to these fluctuations.

🌎 Projected Publicly Held Debt

  • From 1997–2007, debt increased by approximately $4 trillion.
  • From 2008–2019, debt rose from $10 trillion to $23 trillion.
  • From 2020–2022, debt grew from $27 trillion to $31 trillion.
  • Projections show that by 2027, publicly held debt will approach $40 trillion.
  • The overall trend suggests steady and accelerated growth, influenced by policy, inflation, and economic cycles.

Final Output

The final Excel dashboard presents:

  • Line Chart: Yearly Debt Percentage Increase
  • Bar Chart: Monthly Average Total Debts
  • Area Chart: Projected Publicly Held Debt

Each chart is accompanied by written summaries and conclusions for interpretability.

Conclusions

  • The United States has experienced a consistent upward trend in both public and governmental debt.
  • The pandemic year (2020) marked a sharp deviation due to emergency spending.
  • Debt accumulation patterns correlate strongly with economic cycles and consumer behavior.
  • Projections indicate continued growth, highlighting the need for fiscal management and policy intervention.

Future Work

Potential areas to expand this analysis:

  • Integrate macroeconomic indicators such as unemployment, inflation, and GDP.
  • Automate projections using Excel forecasting models or Python integration.
  • Compare debt trends across countries for a global economic perspective.