(CIS331 -SystmMdlngThry) Retirement Planning

(CIS331 -SystmMdlngThry) Retirement Planning - Retirement...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Retirement Planning (A) Bob Davidson is a 46‐year‐old, married, tenured professor at a small New England college. His current salary is $116,000. His employer contributes an amount equal to 10 percent of his salary to a retirement fund, and Bob himself has been contributing $9,500 a year. The current value of his retirement fund is $167,000. Bob would like to know whether his current level of retirement savings is adequate. [p. 80] We will assume: • • • • • • Bob will retire at 67 He will save 5% of his gross income each year until retirement He will consume 70% of his final gross salary each year in retirement His assets will earn 8%/year up to retirement and 5%/year after. He will die at 75 His wife will die when he would have been 80 We will choose Assets at Death as the outcome measure: Influence Diagram R e t i r e m e n t P l a n n i n g A Page 1 Version 1 (M1) [note: hidden rows] Income = Prior year * (1 + growth rate) Saving out of income = (personal savings rate + employer savings rate) * income Retirement assets pre retirement = prior year * (1 + pre‐retirement return on assets) + savings Consumption post retirement = percent * ending income Retirement assets post retirement = assets * (1 + post‐retirement return on assets) ‐ consumption Implementation Details: D22 D23 E22 F22 F23 G44 G45 H44 H45 =D6 =D22*(1+$D$15) =($D$8+$D$18)*D22 =D7 =F22*(1+$D$12)+E23 =D14*D43 =G45 =F43*(1+$D$13)‐G44 =H44*(1+$D$13)‐G45 R e t i r e m e n t P l a n n i n g A Page 2 R e t i r e m e n t P l a n n i n g A Page 3 Insights from initial model: Model predicts Bob’s assets at age 80 will be $657,890 Savings rate (1 way data table) and chart savings rate is adequate Goal seek (shows 0.8% savings needed to “break even” on final assets) Tornado Bob’s assets at death are highly sensitive to savings rate $3,531,155 Final Assets $3,031,155 $2,531,155 $2,031,155 $1,531,155 $1,031,155 $531,155 $31,155 0.00% 5.00% 10.00% 15.00% 20.00% Savings rate R e t i r e m e n t P l a n n i n g A Page 4 Final Assets $0 pre_retirement Percent_of_final_income_spent Income_growth_rate Current_savings $500,000 $1,000,000 $1,500,000 7% 9% 63% 77% 4% $150,300 4% $183,700 Downside Upside Employer_savings_rate 9% 11% post_retirement 5% 6% Current_income $127,600 Savings_rate 4.50% $104,400 5.50% R e t i r e m e n t P l a n n i n g A Page 5 Version 2 (M2) In addition to wanting to know what his assets might be at death, Bob probably would like to know: • • • • • The number of years he could live comfortably in retirement The probability of running out of savings before he dies The maximum amount he can spend each year in retirement and not run out before a certain age The amount he will have saved when he retires Combination of savings rates and postretirement consumption rates that guarantee sufficient assets to a certain age R e t i r e m e n t P l a n n i n g A Page 6 Pre‐Retirement (Logic) D: Salary = previous year’s salary times the growth rate. E: Employer contribution = salary times the employer contribution rate. F: Personal contribution = salary times the personal contribution rate. G: Beginning of year assets = assets from the end of the previous year. H: End of year assets = assets at the end of year are assets from the beginning of the year, times the quantity one plus the rate of return, plus employer contributions and personal contributions. Hint: Age 46 ‐> =G26*(1+$D$10)+E26+F26 R e t i r e m e n t P l a n n i n g A Page 7 Post‐Retirement K: Spending = the first year of retirement is the final working year’s salary times the percent of final income spent; thereafter, spending grows at the rate of inflation. Hint: Age 46 ‐> =D16*VLOOKUP(J26,C26:D70,2,1) L: Withdrawal = spending divided by the quantity one minus the tax rate. M: Beginning of year assets = assets at the end of the previous year. N: End of year assets = assets from the beginning of the year, times the quantity one plus the rate of return, minus the withdrawal. O: Funds depleted? = retirement funds are depleted when end‐of‐year assets are negative. R e t i r e m e n t P l a n n i n g A Page 8 Implementation Hints: You might use a VLOOKUP in K26 and M26 to pick out the appropriate date to start the postretirement calculations depending on the year of retirement. K26 logic: Percent of final income spent * VLOOKUP(J26(the post retirement age), beginning age‐ >end of salary range, column number for salary) M26 logic: VLOOKUP(J26(the post retirement age), beginning age‐>end of year assets range, column number for end of year assets) You can use a combination of INDEX and MATCH in cell I6 to identify the year funds run out: INDEX(J26:J61, MATCH(1,O26:O61,0)) To find the age in the range J26:J61, based on the location calculated by the MATCH function. You might use an IF function in column O to identify when the end of year assets turn negative by using a 1 if they are negative and a 0 otherwise. Requirements: 1. Develop insights from creating a data table and tornado chart (use the “assumed” parameters as inputs) – use age 80 for final assets. 2. Use scenario manager to create a report based on the following with outcomes of final assets at age 80 and Year funds out: a. Base case – use the current parameters b. Worst case – savings rate = 5%, percent final income spent = 80%, retirement age = 64 c. Best case – savings rate = 12%, percent final income spent = 60%, retirement age = 68 3. Use @Risk to examine the problem stochastically assuming the pre‐retirement returns are normally distributed with mean of 8% and standard deviation of 2%, and the post‐retirement returns are triangularly distributed with a minimum of 2%, most likely of 5% and a maximum of 7%. Create a forecast chart. Comment on the probability of final assets being positive. R e t i r e m e n t P l a n n i n g A Page 9 ...
View Full Document

This note was uploaded on 01/19/2012 for the course COMPUTER S 100 taught by Professor Brown during the Spring '11 term at Strayer.

Ask a homework question - tutors are online