in excel

Part 3

Upland Nursery has started a 401k plan for their assistant manager, Danielle Stormy. The historical returns from investing in this very specific 401lk plan have been provided. Calculate the average return, return standard deviation, coefficient of variation, and the typical year upper return, and typical year lower return. Her starting salary is $59,500 and she will likely receive a 1.5% raise each year. Upland will make a 5% contribution and Ms. Stormy will make a 3.5% contribution. Write a VB function to calculate the value of her 401k plan. The rate of return on the investments will be varied using scenario manager. If Ms. Stormy has discount rate of 7% per year what is the present value of this future value?

Use scenario manager to determine the future value of the 401k and the present value of the future value for the following cases:

Retire in 30 years, rate of return equals expected return

Retire in 20 years, rate of return expected return

Retire in 30 years, rate of return equals typical year upper return

Retire in 20 years, rate of return equals typical year upper return

Retire in 30 years, rate of return equals typical year lower return

Retire in 20 years, rate of return equals typical year lower return.

Can Ms. Stormy count on retiring with at $100,000 in present value terms?

Historical Data			Base Salary	59000
Year	Return on Investment		Raise	1.50%
1	6.71%		Return
2	1.45%		Her contribution	3.50%
3	11.94%		Upland contribution	5%
4	6.28%		Total Contribution	8.50%
5	9.72%		Years till Retire	30.00%
6	11.39%
7	6.54%		Value in 401k
8	5.43%		PV of Value in 401k
9	13.25%
10	4.04%
11	-0.40%
12	12.32%
13	8.50%
14	6.42%
15	13.06%
16	2.42%
17	6.77%
18	11.02%
19	11.35%
20	3.64%
Average Return
Standard Deviation of Return
CV
Typical Upper
Typical Lower