FINANCE AND EXCEL ASSIGNMENT
Asgn10
Assignment 10: retirement
Current wealth ($)
1,000,000
Risk-free rate
6%
Parameters of risky investment
Output:
Expected annual return
8%
Average
Standard deviation of return
20%
Sigma
Proportion invested in risky
70%
Negative
bequest
Safety cushion
3
Annual withdrawal
150,000
Below: Data table
Year
Balance
beginning of year
Invested in risky
Invested in bonds
Ret. on risky fund
Balance end of year bf withdrawal
withdrawal
Left at end of 10 years
Simulation
1
700,000
300,000
NORMSINV()
150,000
1
2
2
3
3
4
4
5
5
6
6
7
7
8
8
9
9
10
10
0
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Suppose you retire with $1million at age of 65.
You plan to invest your retirement into a mixture of a risky fund and a fixed income fund, where the return of the risky fund follows a normal distribution.
You plans to withdraw $150,000 at the end of every year from this accout.
To prevent overwithdrawal at the end, you put a safety cushion of 3
If balance of the retirement fund at the end of year, before annual withdrawal is less than 3 times the planned annual withdrawal amount, you withdraw one-third of the remaining balance. (Hint: use IF function)
You want to find out how much you are left at the age of 75.
1. Simulate one time to find out the remaining balance at the age of 75 ( 10 years later)
2. Simulate 100 times, find out average, standard deviation, along with probabilty of the remaining fund is positive.
