You have recently been hired as a consultant for a personal financial planning firm. One of your first projects is creating a retirement plan for a couple, Tom and Helena Keeley. They have just celebrated their 35th birthdays and they have decided to get serious about saving for retirement. Tom and Helena hope to retire 30 years from now (on their 65th birthdays), and they expect to live until age 85. Their hope is to be able to withdraw $150,000 a year from their retirement account – the first withdrawal will occur on their 65th birthdays, and the 20th and final withdrawal will occur on their 84th birthdays. On their 85th birthdays, the account is expected to have a zero value (i.e., they don’t expect to have any remaining funds left for their children’s inheritance). Tom and Helena currently have $100,000 saved in a retirement account, which consists of a portfolio of mutual funds that is expected to produce an annual return of 8%. To accomplish their goals, they would like to deposit an equal annual amount into their account starting one year from today (on their 36th birthdays) and continue to make those deposits through age 65. (Again, the account has an expected annual return of 8%.) Thus, they will make 30 annual end-of-year deposits to this account.

a. How much do Tom and Helena need to contribute to the account at the end of each of the next 30 years to accomplish their goals?

b. If they wanted to have $500,000 available for inheritance at age 85, how much would they need to contribute to the account at the end of each of the next 30 years? (Assume everything else stays the same.)

c. Tom and Helena have one last concern. They recognize that the value of their $150,000 annual withdrawals during retirement will steadily decline because of expected inflation. Assume that they want to have the value of these withdrawals increase by 5% a year during retirement to account for expected inflation. In other words, they want to withdraw $150,000 at age 65, $157,500 at age 66, and $157,500 × 1.05 at age 67, etc. Going back to the other original assumptions (8% return and no expected inheritance), how much would they need to contribute to the account at the end of each of the next 30 years to meet this revised goal which protects them against rising inflation? Set up this problem using Excel and please display work and detailed explanations!