This is Finance homework.

profileJack110
Q8.xlsx
Home>Business & Finance homework help>This is Finance homework.

senseInfo

selectionIndex checkSelected bookName sheetName cellAddress rangeAddress analysisString minPercent maxPercent minValue maxValue baseValue numIntervals varyWhenStepping isInput groupCount groupIndex formulaIndex ioIndex intIndex intervalMode useCellBase tableRange
0 0
senseTotal: 1 .
selectionIndex checkSelected bookName sheetName cellAddress rangeAddress analysisString minPercent maxPercent minValue maxValue baseValue numIntervals varyWhenStepping isInput groupCount groupIndex formulaIndex ioIndex intIndex intervalMode useCellBase tableRange
1 TRUE 0 Finally retired 10% $D$11 Range: $L$17:$L$26 -10.00% 10.00% 0.15 10 TRUE FALSE 1 0 1 0 Contribution percentage 0.15 0 0 0 6 TRUE '[My Modeling retirement plan.xlsx]Finally retired'!$L$17:$L$26 -0.1 -6.66666666666667E-02 -3.33333333333333E-02 0 3.33333333333333E-02 6.66666666666667E-02 0.1 0.13 0.14 0.15 0.16 0.17 0.18 0.19 0.2 0.21 0.22 Range: 0.13 Range: 0.14 Range: 0.15 Range: 0.16 Range: 0.17 Range: 0.18 Range: 0.19 Range: 0.20 Range: 0.21 Range: 0.22

Question 7

Question 8 ( 4 points)
Problem: Calculate the retirement age and the total worth of your retirement fund upon your retirement given the following info. You are 32 years old now and the retirement age
in your country is 65, but there is a 75% chance that it will be changed to 72 years. You contribute 10% of your salary to the retirement
fund each year. Your annual salary this year is $75,000, and you expect it to increase by a growth rate per year governed by a Lognormal(5%,10.5%) distribution
in real terms (above inflation). You estimate that the return on the pension fund will be minimum 2%, most likely 3.5% and maximum 7% (assuming a Pert distribution).
Your age, years 32 m s
Annual salary $75,000 Annual increase 5.0% 10.5%
Contribution percentage 10%
Retirement age in years 65 min ml max
Retirement age if changed, years 72 Pension fund's return per year 2.0% 3.5% 7.0%
Probability of the change 75%
Retirement age
Total worth
Age Year Annual salary Contribution Pension's fund return Contribution (accrued)
32 1 $75,000 $7,500
33
34
35
36
37
38
39
40
41
42 D19 D10
Author: Author: Reference formual from D19
43 E19 D19*$D$11
Author: Author: Reference formula from E 19
44 F19

Author: Author: Reference formula from F19		 45 G19

Author: Author: Reference formula from G 19		 46
47 Choose the following Simulation Settings:
48 Iterations: 5,000
49 Simulations: 1
50 Sampling Type: Latin Hypercube
51 Simulation not Running, Distributions return=Static Values then When RiskStatic is not defined use Expected Values
52 Generator: Marsenne Twister
53 Initial seed: 1 All use same seed.
54 Collect distribution samples: All
55 Smart sensitivity analysis: Enabled
56
57 Run a Monte Carlo simulation using @Risk and interpret the results:
58
59 1. Show the Simulation Setting windows (Screenshots) and Generate and attach all @Risk Outputs Excel Reports:
60 Quick reports, Input & Output Results Summary, Detailed Statistics (Screenshots)
61
62 2. What are the Mean, St Dev, Min and Max
63 Mean St Dev Min Max
64
65 3. Show the Output Histogram (Screenshots)
66 What is the probability that the total accumulated worth upon retirement will be higher than the contribution.
67
68 4. What percentage of your salary you should put aside to achive a $2,500,000 at retirement.
69
70 5. Given the assumptions mentioned in the problem, at what age you will achieve
71 the same $2,500,000 upon retirement.
72
73
74 Add your output results windows to this workbook:
75
76
77
78
79
80
81
82
83
84