Retirement Plan

Now that you have a house, its time for you to plan for retirement. Your plan is to take a certain amount from your salary at the end of each year and invest it in a 401(K) mutual fund. Then when you get sick of your job and want to retire, you will have a fund that you can withdraw from each year to live on.

Lets assume you want to retire at age 60 and your life expectancy is 90.

Suppose that your 401(K) mutual fund yields an interest rate of 6.6% APR compounded annually. Determine for yourself how much money you want to invest in the 401(K) each year. This is the payment that you will make at the end of each year. Also determine at what age you want to start paying into the 401(K) investment plan. Finally, determine how much money you will need to withdraw from the fund each year to live on when you are retired. An example could be to invest $2000 each year starting at age 21 and withdraw $20,000 each year during retirement.

What must you invest each year during the accumulation phase (now until retirement) so that you will have just enough money to live on during retirement, the payout phase?

We will answer this question by creating an Excel spreadsheet that keeps track of how much money is in your 401(K) during the accumulation phase and during the payout phase (during retirement).

1. First set up your parameters at the top of the spreadsheet. The parameters should be: the annual interest rate (r), the age that you start making payments into the 401(K), the amount of the payment at the end of each year, and the amount that you will withdraw each year during retirement. (You decide how much you will need each year in retirement.)
2. Next we will set up a spreadsheet that keeps track of the account balance during the years that you are adding money to the account. Create the following 4 columns:

Age Keeps track of your age.

Payment Keeps track of how much you invest in the 401(K) at the end of each year. This value will stay constant until the age when you retire. Assume that you make your first payment at the end of year 1.

Interest Keeps track of how much interest is earned during each year. Since you make your annual payments at the end of the year, the interest earned during the first year will be $0.

Balance Keeps track of the account balance in the 401(K) at the end of the year.

3. Fill out the spreadsheet for a few years to see how the account balance grows. Assume that you continue to make payments until the stated retirement age.

4. Now set up another spreadsheet to the right that keeps track of the account balance during the retirement years that you are withdrawing. Set up the following 4 columns:

Age Keeps track of your age -starting with the retirement age. Assume that your first withdrawal takes place at the end of the last year that you worked.

Withdrawal Keeps track of how much you take out of the 401(K) at the end of each year. This value will stay constant until the stated life expectancy age.

Interest Keeps track of how much interest is earned during each year. Since you already calculated the interest earned during your last year of work, you do not need to add any interest for the first row of this spreadsheet.

Balance Keeps track of the account balance in the 401(K) at the end of the year.

5. Fill out this spreadsheet until the life expectancy age. Your goal is to have zero dollars balance at the end.

6. Adjust each spreadsheet by changing the payment amount and withdraw amount so that you will have just enough money in your account to live on during the payout phase.

7. Calculate the total amount that you paid into the account, the total amount that you withdrew from the account, and the total interest made during the lifetime of the account, and compare these values.

Hi, guys, i am stuck at this question, kindly help me; kindly show your answer in excel