Case 13-3 Financial Planning for Retirement After a long career, Jennifer Eleanor is nearing retirement. She would like to explore the possibility of retiring ten years from now. She feels that she can comfortably retire if she can accumulate a $2 million net worth. Throughout her career, Jennifer has frequently made use of her spreadsheet modeling skills that she first learned as a business student, so she decides to apply this approach to analyze her financial situation in ten years. Jennifer works in the financial industry and currently her salary is such that she earns $100,000 annually in take-home pay (after all taxes). However, there is lots of volatility in her line of work. While raises and promotions are possible, she is painfully aware that job layoffs are not uncommon either. Jennifer estimates that each year she has an 85% chance of avoiding a layoff and keeping her current job. If she keeps her job, there is a 20% chance (each year) of a promotion which includes a raise that would increase her take-home pay anywhere in the range of 10-20% (all values in the range equally likely). Without a promotion, annual raises are anywhere in the 0-5% range (all values in the range equally likely). On the other hand, if she is laid off, then Jennifer will seek new work. She has sufficient job skills that she is confident she can find a new job quickly, but the new salary is very uncertain and she is likely to end up in a lower paying job. It is most likely she would end up with take-home pay near 90% of its current value just before being laid off, but this could range as low as 50% or as high as 115% of its current value. (Jennifer is assuming a triangular distribution.) Jennifer has a self-funded retirement fund that is currently valued at $500,000. At the start of each year, starting one year from now, Jennifer plans to invest an additional $10,000 and then re-balance the fund so that it is invested 40% in the retirement plans stock fund and 60% in the retirement plans bond fund. Historically the stock fund has had a mean annual return of 8% with a standard deviation of 16%. (Jennifer is assuming a normal distribution). The bond fund typically has had annual returns near 4%, but it has been as low as 2% and as high as 13%. (She is assuming a triangular distribution.) Jennifer owns her own home free and clear (no mortgage). It was recently appraised at a value of $600,000. The local housing market has seen prices rising at an average rate of 3% per year, but with much volatility so the standard deviation has been 6%. (She is assuming a normal distribution.) Jennifer currently has $100,000 in cash, invested in a high-interest savings account. The interest rate is currently 2%. Jennifer expects that this rate may change up to 0.5% each year, going either either up or down, and anywhere in this range is equally likely. However, she expects that the interest rate would never drop below 0.5%. Jennifer deposits all her income in the savings account, pays for all expenses, and makes all retirement fund investments from the savings account. Jennifers regular household expenses rise and fall with her income (when she makes more, she tends to spend it). These household expenses have averaged 70% of her take-home pay with a standard deviation of 5%. (She is assuming a normal distribution.) Beyond regular household expenses, there are also occasional major repairs that are needed on her house. These seem to occur entirely randomly (i.e., no matter the history of repairs, the next major repair is just as likely to happen at any minute). She averages about one major repair per year and these usually cost around $10,000, so she assumes a fixed cost per repair. After all income is deposited and expenses paid, any balance remaining in the savings account is carried forward to the following year and also earns savings interest annually at the start of the next year (see the preceding paragraph about interest rates). For example, if the ending balance after year 2 is $100,000, and year 2s savings interest rate is 2%, then $2000 of savings interest is earned at the beginning of year 3. Assume no interest for year 1, as the $100,000 beginning cash balance has already incorporated any savings interest earned.

a. Use Analytic Solver to simulate 1000 trials for the next ten years. For simplicity of modeling, assume that any promotion, raise, or layoff (with the corresponding new job) occurs at year end and therefore wont start affecting take-home pay until the following year. Also, for simplicity, assume that all cash flows (income, expenses, investments, etc.) occur simultaneously at the start of each year. Based on the results of the simulation run, what is Jennifer's average net worth (accounting for her retirement fund value, housing value, and savings) at the end of ten years?

b. Based on the results of the simulation run, what is the probability that Jennifer's net worth will exceed $2 million?

c. Use the Solver in Analytic Solver to determine the optimal stock-bond breakdown in the retirement fund (whatever stock-bond mix is chosen, assume the same mix is used for all ten years) so as to maximize the probability that Jennifer's net worth will exceed $2 million.