Retirement Fund
|
Parameters
|
|
|
Current Year
|
2016
|
|
Age
|
37
|
|
Current Savings
|
$259,000
|
|
Rate of Return on Retirement Savings
|
7.0%
|
|
Current Annual Salary
|
$145,000
|
|
Expected Annual Salary Increase
|
2.0%
|
|
% of Salary Contributed
|
6.0%
|
|
Expected Age of Retirement
|
65
|
|
% Employer Contribution
|
5.0%
|
|
Annual Employee (tax free) Contributions
|
$6,000
|
|
Inflation Rate
|
2.00%
|
2a) Prepare a spreadsheet indicating the value of retirement fund on a yearly basis when retiring at age of 65.
2b) What is the net present value of the retirement fund at retirement age assuming an inflation rate of 2.0%?
I need help figuring out how to prepare a table in excel for this. I completed one already but it was not what the professor was looking for. He showed the class how to do it but was going to fast in his lecture. I was only able to copy down what I could. This is what he told us to start with..
| Current Year |
Age |
Beginning savings |
Current Salary |
salary contributed |
Employer contribution |
Fixed contribution |
Ending savings |
|
|
|
| 2016 |
37 |
259000 |
145000 |
8700 |
7250 |
6000 |
300617 |
|
|
The final answer for part one is $4,225,518. In my origional excel, I basically added contributions per year along with the rate of return on retirement savings from the final amount in the retirement fund.
My question is, what calculations in excel should I use to get to my answer and how do I get to the final $4,225,518? Is there any way someone could show me the formulas in excel?