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 |