U.S. Debt Analysis

📊 Analyzing U.S. Public Debt Growth: Patterns, Trends, and Projections

In this project, I conducted a data analysis on the U.S. Public Debt Outstanding over several years to better understand the trends, growth patterns, and potential future trajectory of the nation's debt. Here’s an overview of how I approached the project and the key findings:

🗂️ Data Preparation & Cleaning

The source data was already clean and accurate, but the way it was structured made it difficult to read and analyze. Specifically:

• The raw dataset listed dates as column headers and debt categories as row labels.

• To make the data easier to analyze and visualize, I transposed the dataset so that each date became a row and each debt category became a column.

This restructuring made it easier to work with in Excel, particularly when calculating trends over time.

Original Data

Transposed & Formatted Data

📈 Yearly Percentage Increase Calculation

To measure the yearly percentage increase in U.S. Public Debt, I used the following methodology:

• I selected the final day of each year as the reference point.

• Then, I calculated the percentage increase of each year compared to the previous year.

This calculation was applied to:

1. Debt Held by the Public

2. Intragovernmental Holdings

3. Total Public Debt Outstanding

The results were then visualized in a line chart showing the annual percentage increase from 2016 to 2022.

Calculation

Yearly Debt Percentage Increase

🔄 Monthly Trends Analysis

To analyze historical monthly patterns in total public debt, I utilized PivotTables. This allowed me to:

• Aggregate debt data by month.

• Identify months with historically higher or lower increases in debt.

• Observe seasonal trends in borrowing and spending behavior.

Additionally, I used PivotTables to assess the debt's trajectory over time and analyze how growth fluctuated month-to-month historically.

Monthly Average Total Debts

🔮 Projected Debt Forecasting

To estimate future debt levels, I used Excel's built-in FORECAST.ETS function. This function applies Exponential Triple Smoothing to generate a rough projection based on historical data patterns.

I projected publicly held debt from 2023 to 2027, extending the trend to provide a view of the expected growth trajectory.

Max debt Held by Public per Year

Excel function used to project publicly held debt (2023-2027):

=FORECAST.ETS(Measured year, '1997-2022', corresponding max debt)

This gave me the following projections

Forecast Function Projections

Maximum Debt by Year + Future Projections

📄 Final Report & Key Takeaways

The final output of my project includes:

• Three visualizations answering key debt-related questions:

  1. Yearly Debt Percentage Increase

  2. Monthly Average Total Debt

  3. Projected Growth of Publicly Held Debt

• Clean, concise Key Takeaways beneath each chart.

  
  

  

  Final Report

⭐️ Key Learnings & Insights:

1. Between 2016–2019, debt grew at an average rate of around 5%.

2. In 2020, there was a significant spike due to pandemic-related fiscal measures.

3. Monthly trends showed seasonal increases during holiday months (Thanksgiving, Christmas, New Years).

4. Long-term projections indicate continued debt growth through 2027.

🔍 Next Steps & Further Research

To build upon this analysis, future studies could include:

• Comparison of U.S. debt growth with other economic indicators (GDP, stock market, housing market, consumer credit behavior).

• Breakdown of federal spending categories contributing to debt increases.

• Analysis of the relationship between interest rate changes and debt accumulation.