1. 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 young couple, Ben and Eileen Connelly.They have just celebrated their 35^thbirthdays and have decided to get serious about saving for retirement.

Ben and Eileen hope to retire 30 years from now (on their 65^thbirthdays), and they expect to live until age 90.Their hope is to be able to withdraw $160,000 a year from their retirement account - the first withdrawal will occur on their 65^thbirthdays, and the 25^thand final withdrawal will occur on their 89^thbirthdays.They expect to leave their children a total inheritance of $1.5 million to split amongst themselves.So, on their 90^thbirthdays, the account is expected to have a $1.5 million value (i.e., they expect to leave their children a total inheritance of $1.5 million).

Ben and Eileen currently have $750,000 saved in a retirement account, which consists of a portfolio of mutual funds that is expected to produce an annual return of 6%.To accomplish their goals, they would like to deposit an equal annual amount into their account starting one year from today (on their 36^thbirthdays) and continue to make those deposits through age 65.(Again, the account has an expected annual return of 6%.)Thus, they will make 30 annual end-of-year deposits to this account.

a.How much do Ben and Eileen need to deposit into the account at the end of each of the next 30 years to accomplish their goals?

Beginning amount will need to be $2,517,555.15 to accomplish these goals which means the payments into the account for the 30 years before retirement need to be $31,844.33. (see screenshot for excel work- formula used in red cell:=PMT(0.06,30,0,2517555.15,0))

b.Ben and Eileen recognize that the value of their $160,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 4% a year during retirement to account for expected inflation.In other words, they want to withdraw $160,000 at age 65, $166,400 at age 66, and $166,400 1.04 at age 67, etc.How much would they need to deposit into the account at the end of each of the next 30 years to meet this revised goal, which protects them against rising inflation?Assume they still plan to leave their children a total inheritance of $1.5 million.Set up this problem using Excel.Here you might find it helpful to refer to the Growing Annuity Example on the class e-Learning site.

Beginning amount will need to be $3,562,262.85 to accomplish these goals which means the payments into the account for the 30 years before retirement need to be $45,058.75. (see screenshot for excel work- formula used in red cell:=PMT(0.06,30,0,3562262.85,0))

My questions is-- I would think the beginning value of $750,000 needs to be accounted for, but the final amount ends up being a lot more than what I think they would need if I start with the $750k. Am I using the right formula and properly?