Business Finance - Accounting ASSIGNMENT 10

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.