After a long career, Jennifer Eleanor is nearing retirement. She would like to explore the possibility of retiring 10 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 10 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 percent chance of avoiding a layoff and keeping her current job. If she keeps her job, there is a 20 percent chance (each year) of a promotion which includes a raise that would increase her take-home pay anywhere in the range of 10–20 percent (all values in the range equally likely). Without a promotion, annual raises are anywhere in the 0–5 percent 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 percent of its current value just before being laid off, but this could range as low as 50 percent or as high as 115 percent 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 percent in the retirement plan’s stock fund and 60 percent in the retirement plan’s bond fund. Historically the stock fund has had a mean annual return of 8 percent with a standard deviation of 16 percent. (Jennifer is assuming a normal distribution). The bond fund typically has had annual returns near 4 percent, but it has been as low as −2 percent and as high as 13 percent. (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 percent per year, but with much volatility so the standard deviation has been 6 percent. (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 percent. Jennifer expects that this rate may change up to 0.5 percent each year, going 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 percent.  

Jennifer deposits all her income in the savings account, pays for all expenses, and makes all retirement fund investments from the savings account. Jennifer’s regular household expenses rise and fall with her income (when she makes more, she tends to spend it). These household expenses have averaged 70 percent of her take-home pay with a standard deviation of 5 percent. (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 2’s savings interest rate is 2 percent, then $2,000 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 1,000 trials for the next 10 years. For simplicity of modeling, assume that any promotion, raise, or layoff (with the corresponding new job) occurs at year end and therefore won’t 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 10 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 10 years) so as to maximize the probability that Jennifer’s net worth will exceed $2 million.