Question

Retirement planning is a common application of time value of money analysis. In this exercise you need to build a model to calculate required retirement savings from a set of assumptions. Specifically, you must build a spreadsheet to calculate estimates of:

• Annual income needed in retirement. (based on a % of current salary)
• Total Savings you need to accumulate by the day you retire.
• Estimated annual contributions required before retirement needed to meet your retirement goal.

These calculations will be based on the following assumptions.   Your model must be constructed so new values will be calculated when any of the assumptions are changed.

Calculate the three items above for the following two scenarios of assumptions plus one scenario of your own:

Assumptions	Scenario 1	Scenario 2	Your Scenario
Fair Market Interest Rate	5%	5%
Current Age	23	35
Age at Retirement	65	65
Projected Age of Death	95	95
Current Annual Salary	$60,000	$60,000
% of Salary Needed in Retirement	80%	80%
Current Retirement Savings	$0	$20,000

Answer 1

Given: I have considered own scenario in Your Scenario column. You can change the numbers if you wish to.

Assumptions	Scenario 1	Scenario 2	Your Scenario
Fair Market Interest Rate	5%	5%	6%
Current Age	23	35	25
Age at Retirement	65	65	60
Projected Age of Death	95	95	95
Current Annual Salary	$60,000	$60,000	$75,000
% of Salary Needed in Retirement	80%	80%	90%
Current Retirement Savings	$0	$20,000	$10,000

Estimates to be calculated:

• Annual income needed in retirement. (based on a % of current salary)
• Total Savings you need to accumulate by the day you retire.
• Estimated annual contributions required before retirement needed to meet your retirement goal.

The Annual income needed in retirement is calculated by multiplying the Current Annual Salary with % of Salary needed in retirement.

Total Savings you need to accumulate by the day you retire can be calculated as:

Present Value of the annuity (Result from above calculation) we wish to receive from the age of retirement until we die.

This can be calculated using the PV formula which will be shown later in the solution.

Estimated annual calculation required before retirement needed to meet your retirement goal can be calculated as follows:

First, we find out the value of our current retirement savings at our retirement age.

We deduct this amount from the Total savings we need to accumulate (calculated in earlier step)

We will now find the annuity payments to be saved per year inorder to reach our retirement goal. We use PMT formula to get the value of these annuity payments.

The above steps, performed in the excel, will be shown as:

Assumptions	Scenario 1	Scenario 2	Your Scenario
Fair Market Interest Rate	5%	5%	6%
Current Age	23	35	25
Age at Retirement	65	65	60
Projected Age of Death	95	95	95
Current Annual Salary	$60,000	$60,000	$75,000
% of Salary Needed in Retirement	80%	80%	90%
Current Retirement Savings	$0	$20,000	$10,000
Annual income needed in retirement.	$48,000	$48,000	$67,500
Total Savings you need to accumulate by the day you retire.	$737,877.65	$737,877.65	$978,631.63
The Value of your Current Retirement Savings at your Retirement	$0.00	$86,438.85	$76,860.87
Estimated annual contributions required before retirement needed to meet your retirement goal.	$5,456.39	$9,805.09	$8,092.36

Excel Snip for the Same:

The formulas used in the above Excel are:

When the assumption Values are changed, automatically the result values are auto-calculated and populated when we link them using formulas as shown above.