Refer to the Retirement Planning case. Review the problem statement and influence chart that were generated for this case in conjunction with the corresponding exercise in Chapter 2. (If this has not yet been done, develop the problem statement and influence chart as preliminary steps.) Design a spreadsheet to estimate the impact on Davidsons retirement of increasing his annual retirement savings by 10 percent.

Please answer using Excel (showing the formulas). A step-step process would be very much appreciated.

Bob Davidson is a 46-year-old tenured professor of market- Bob's TIAA-CREF holdings currently amount to ing at a small New England business school. He has a $137,000. These are invested in the TIAA long-term bond daughter, Sue, age 6, and a wife, Margaret, age 40. Margaret fund ( 20 percent) and the Global Equity Fund (80 peris a potter, a vocation from which she earns no appreciable cent). The Global Equity Fund is invested roughly 40 income. Before she was married and for the first few years of percent in U.S. equities and 60 percent in non-U.S. her marriage to Bob (she was married once previously), she equities. New contributions are also allocated in these worked at a variety of jobs, mostly involving software same proportions. programming and customer support. In addition to his retirement assets, Bob's net worth Bob's grandfather died at age 42; Bob's father died in consists of his home (purchase price $140,000 in 1987; Bob's 1980 at the age of 58 . Both died from cancer, although current equity is $40,000 ); $50,000 in a rainy-day fund unrelated instances of that disease. Bob's health has been (invested in a short-term money market mutual fund with excellent; he is an active runner and skier. There are no Fidelity Investments); and $24,000 in a Fidelity Growth and inherited diseases in the family with the exception of glau- Income Fund for his daughter's college tuition. He has a coma. Bob's most recent serum cholesterol count was 190 . term life insurance policy with a value of $580,000; this Bob's salary from the school where he works consists of a policy has no asset value but pays its face value (plus nine-month salary (currently $95,000 ), on which the school inflation) as long as Bob continues to pay the premiums. pays an additional 10 percent into a retirement fund. He also He has no outstanding debts in addition to his mortgage, regularly receives support for his research, which consists of other than monthly credit-card charges. an additional two-ninths of his regular salary, although the Should Bob die while insured, the proceeds on his life college does not pay retirement benefits on that portion of insurance are tax free to his wife. Similarly, if he dies before his income. (Research support is additional income; it is not retirement, his retirement assets go to his wife tax free. intended to cover the costs of research.) Over the 12 years Either one of them can convert retirement assets into annuhe has been at the college his salary has increased by 4 to 15 ities without any immediate taxation; the monthly income percent per year, although faculty salaries are subject to from the annuities is then taxed as ordinary income. severe compression, so he does not expect to receive such Bob's mother is 72 and in good health. She is retired and generous increases into the future. In addition to his salary, living in a co-op apartment in Manhattan. Her net worth is Bob typically earns $10,000 to 20,000 per year from con- on the order of $300,000. His mother-in-law, who is 70 , lives sulting, executive education, and other activities. with her second husband. Her husband is 87 and has suffi- In addition to the 10 percent regular contribution the cient assets to pay for nursing home care, if needed, for his school makes to Bob's retirement savings, Bob also con- likely remaining lifetime. Upon her husband's death, Bob's tributes a substantial amount. He is currently setting aside mother-in-law will receive ownership of their house in $7,500 per year (before taxes). The maximum tax-deferred Newton, Massachusetts, as well as one-third of his estate amount he can contribute is currently $10,000; this limit rises (the remaining two-thirds will go to his two children). Her with inflation. If he were to increase his savings toward net worth at that point is expected to be in the $300,000 retirement above the limit, he would have to invest after-tax 400,000 range. dollars. All of Bob's retirement savings are invested with Bob's goal is to work until he is 60 or 65 . He would like TIAA-CREF (Teachers Insurance and Annuity Associa- to save enough to pay for his daughter's college expenses, tion-College Retirement Equities Fund; home page: www. but not for her expenses beyond that point. He and his tiaa-cref.org), which provides various retirement, invest- wife would like to travel, and do so now as much as his job ment, and insurance services to university professors and and their family responsibilities permit. Upon retirement researchers. Bob has contributed to Social Security for he would like to be able to travel extensively, although he many years as required by law, but in light of the problems would be able to live quite modestly otherwise. He does with the Social Security trust fund he is uncertain as to the not foresee moving from the small town where he now level of benefits that he will actually receive upon retire- lives. ment. (The Social Security Administration's website is Bob has a number of questions about how he should plan www.ssa.gov.) for his retirement. Will the amount he is accumulating at his 443 444 MODELING CASES current rate of savings be adequate? How much should he live comfortably? What are the risks he faces, and how be setting aside each year? How much will he have to live on should his retirement planning take these risks into when he retires? How long after retirement will he be able to account