Question

Lynn Price recently completed her MBA and accepted a job with an electronics manufacturing company. Although she likes her job, she is also looking forward to retiring one day. To ensure that her retirement is comfortable, Lynn intends to invest $3,000 of her salary into a tax-sheltered retirement fund at the end of each year. Lynn is not certain what rate of return this investment will earn each year, but she expects each year’s rate of return could be modeled appropriately as a normally distributed random variable with a mean of 12.5% and standard deviation of 2%. (this problem requires the use of Analytic Solver Platform)

If Lynn is 30 years old, how much money should she expect to have in her retirement fund (expected value) at age 60? Answer this question by using the appropriate Psi Statistical function

What is the probability that Lynn will have more than $1 million in her retirement fund when she reaches age 60? Answer this question by using the appropriate Psi Statistical function

Attach the screenshots of your spreadsheet model and the distribution graph for the fund she will have at age 60

Answer 1

I have answered the question below

Please up vote for the same and thanks!!!

Do reach out in the comments for any queries

Answer:

In accordance with the informatino available with in the data conditions, the expected mean value of the interest rate is 12.5% and the standard deviation of the interest rate is 2%

The expected investment that Lyn want to make there in the tax sheltered retirement fund is $3000.

1. If Current age of Lyn is 30 years, the expected returns that she can obtain at the age of 60 years can be calcualted by simulating the possible interests rates for 30 years from 30 year to 60 year time scale.

Following is the table that indicate these interest rates,

Formula for simulation is PsiNormal(0.125,0.02)

Values obtianed are as follows,

S.No	Interest
1	12.5
2	13.44
3	13.83
4	10.24
5	14.54
6	12.88
7	15.63
8	17.39
9	15.54
10	14
11	15.52
12	13.84
13	12.06
14	13.32
15	13.08
16	14.06
17	16.34
18	11.74
19	12.41
20	13.1
21	11.54
22	13.1
23	13.77
24	13.94
25	10.59
26	11.43
27	19.5
28	8.66
29	11.43
30	13.39
31	9.92

Based on above simulated interest return values,

Balance can be found as follows,

S.No	Age	Balance$	Contribution$	Investment return	Interest$	Balance$
1		0	3000	12.5	0	3000
2		3000	3000	13.44	403.2	6403.2
3		6403.2	3000	13.83	885.5626	10288.76
4		10288.76	3000	10.24	1053.569	14342.33
5		14342.33	3000	14.54	2085.375	19427.71
6		19427.71	3000	12.88	2502.289	24930
7		24930	3000	15.63	3896.558	31826.55
8		31826.55	3000	17.39	5534.638	40361.19
9		40361.19	3000	15.54	6272.129	49633.32
10		49633.32	3000	14	6948.665	59581.99
11		59581.99	3000	15.52	9247.124	71829.11
12		71829.11	3000	13.84	9941.149	84770.26
13		84770.26	3000	12.06	10223.29	97993.55
14		97993.55	3000	13.32	13052.74	114046.3
15		114046.3	3000	13.08	14917.26	131963.5
16		131963.5	3000	14.06	18554.07	153517.6
17		153517.6	3000	16.34	25084.78	181602.4
18		181602.4	3000	11.74	21320.12	205922.5
19		205922.5	3000	12.41	25554.99	234477.5
20		234477.5	3000	13.1	30716.55	268194.1
21		268194.1	3000	11.54	30949.59	302143.7
22		302143.7	3000	13.1	39580.82	344724.5
23		344724.5	3000	13.77	47468.56	395193
24		395193	3000	13.94	55089.91	453282.9
25		453282.9	3000	10.59	48002.66	504285.6
26		504285.6	3000	11.43	57639.85	564925.5
27		564925.5	3000	19.5	110160.5	678085.9
28		678085.9	3000	8.66	58722.24	739808.2
29		739808.2	3000	11.43	84560.07	827368.2
30		827368.2	3000	13.39	110784.6	941152.8
31		941152.8	3000	9.92	93362.36	1037515

1. The aveage and standard deviation of the ending balance value can be obtained from the formula PsiMean() and PSIStdDev() of the retirement age- accordingly the findings are,

Average = $900,565 and the std dev = $70,084

B. The probability that Lyn can have more than >1,000,000 in her retirement funding is 0.085 ---------------(aT AGE 60 year)