Creating a Financial Projection Table and Saving to Excel

Mar 27, 2024, 9:14 PM

I plan to save 8,000inaretirementaccountnextyear.Iexpectmyannualsavingstogrowat38,000 in a retirement account next year. I expect my annual savings to grow at 3% for the next 30 years. Afterwards, I hope to withdraw 100,000 per year for 30 years. I currently have no savings. I hope to earn 8% on my investments each year. Please prepare a table showing the beginning balance each year, the savings each year, the investment gain each year, and the ending balance each year. Save the table as an Excel file.

You can download the prepared Excel file with the financial projection table from the link below:

Show the full table in the chat.

The full table displays the financial projection over 60 years, including:
  • Year: Ranging from 1 to 60.
  • Beginning Balance: Starting balance of the account each year.
  • Savings This Year: Amount saved or withdrawn each year. For the first 30 years, this amount increases by 3% annually from an initial 8,000.Forthenext30years,it′sawithdrawalof8,000. For the next 30 years, it's a withdrawal of 100,000 annually.
  • Investment Gain: Annual gain based on the 8% investment return rate.
  • Ending Balance: The account balance at the end of each year, considering the beginning balance, savings or withdrawals, and investment gains.
The table begins with a 0beginningbalanceandan0 beginning balance and an 8,000 savings in the first year, leading to an ending balance of 8,000.Overtheyears,thesavingsincreaseduetotheannualgrowthrate,andtheinvestmentgainscontributetotheaccount′sgrowth.After30yearsofsavings,withdrawalsof8,000. Over the years, the savings increase due to the annual growth rate, and the investment gains contribute to the account's growth. After 30 years of savings, withdrawals of 100,000 per year begin, gradually reducing the account balance. The table concludes with the 60th year, showing a significant decrease in the ending balance due to the annual withdrawals.

Plot the ending account balance each year.

Simulate the retirement savings plan by drawing a normally distributed return each year with a mean of 8% and a standard deviation of 15%. Run 1,000 simulations. What is the mean ending balance and the median ending balance? Create a histogram of the 1,000 ending balances.

The mean ending balance from the 1,000 simulations is approximately 1,708,901,andthemedianendingbalanceisapproximately1,708,901, and the median ending balance is approximately -1,443,172. This suggests a wide range of outcomes, with the median indicating that in many simulations, the ending balance is negative, reflecting the high volatility introduced by the standard deviation of the returns.
The simulation of the retirement savings plan, with returns drawn from a normal distribution with a mean of 8% and a standard deviation of 15%, resulted in the following findings:
  • Mean Ending Balance: Approximately $1,708,901
  • Median Ending Balance: Approximately $-1,443,172
This indicates a wide variance in outcomes, with the median suggesting that many simulations end with a negative balance, highlighting the impact of investment return volatility.
The histogram of the 1,000 ending balances shows a broad distribution, with the mean and median marked for reference. The presence of negative balances in the distribution underscores the risk associated with the assumed level of volatility.
Get started with Vizly