TIME VALUE OF MONEY ASSIGNMENT

Suppose that you have a brother who is 42 today and that his salary for the year ending today is $155,000. Suppose that he is paid on an annual basis at the end of each year and that he will receive $155,000 today (reflecting his salary for the past years work), out of which he will make a deposit to his account. He plans to retire at age 70 (when he will earn his last salary and make his last deposit into his retirement account) and will make his first withdrawal from his account at age 71 with annual withdrawals in subsequent years. He expects to need an amount as retirement income at that date that is 87.5% of his salary from the last year that he works. All living expenses between now and retirement will be covered by his annual salary in each year. All living expenses after retirement will be covered by his retirement income in each year, except for the medical expenses described below. He has saved $450,000 to date. Assume that he will save 15% of his annual income while he works, and that he will not save any additional amount after he retires (although he will continue to earn a return on his investments).

Assume that, given the state of health care costs and a family history of healthcare issues, he will need to have an amount of $750,000 available by age 66 to cover healthcare costs above and beyond what his health insurance plan is likely to cover. Treat this figure as an expected one-time outflow at this point. Of course, actual future health care costs are unlikely to be a one-time event and to differ from this amount. What we are doing is treating the $750,000 as the present (or future) value at age 66 of all possible additional costs over time. Save based on the prediction that the one-time expense will occur at that time. Assume that these health care costs will be paid out of his retirement account.

You project that his salary will grow at a rate of 5% and that his retirement income needs will grow at 2.5to reflect long-term inflation forecasts. The higher growth rate in his salary reflects your expectation that he will receive promotions and performance raises and that absent promotions and performance pay his salary will keep pace with inflation. The appropriate interest rate for his working life is 9% and declines to 6.25% after he retires. Both rates of return are nominal (i.e., include compensation for expected inflation). Thus you will not need to adjust the rates of return for expected inflation.

Assume that he will live until age 88 and that he wishes to have a balance of $2 million remaining at 88. This balance is available as a safety net if he lives longer than expected and as a bequest to his children if he doesnt live past 88.

He would like to build a retirement home at age 60 in coastal North Carolina using funds from his retirement account. Based on his saving behavior and expected needs, how much could he afford to spend on the home at that time? Assume that other housing needs will be covered by his income while he works. Ignore the residual value of the house at 88. We will assume that he could sell the house for funds if he lives longer than expected or leave it to his children (in addition to the $2 million mentioned above) if he doesnt live past 88.

Build a spreadsheet model to answer this question. Solve directly for the amount using the TVM equations. Also create a row in your spreadsheet model that tracks the amount in his investment account balance. The balance in each year brings the prior years balance forward and adjusts for any additional cash inflow or outflow in the current year. Create a line graph that uses his account balance on the vertical axis and age on the horizontal axis. At what age is his account balance at its maximum? Is this at age 69, 70, 71, or some other point? Explain your answer. Describe other relevant features of your graph of the account balance across time.