Question

Retirement Plan

Now that you have a house, it’s 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.

Let’s 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

Answer 1

AGE 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 ACCUMULATION PHASE PAYMENT INTEREST BALANCE os o$ 0$ 2,000 $ 132 $ 2,132 $ 2,000 $ 273 $ 4,405 $ 2,000 $ 423 $ 6,827 5 2,000 $ 583 $ 9,410 $ 2,000 $ 753 $ 12,163 $ 2,000 $ 935 $ 15,098 S 2,000 $ 1,128 $ 18,226 $ 2,000 $ 1,335 $ 21,561 $ 2,000 $ 1,555 $ 25,116 $ 2,000 $ 1,790 $ 28,906 $ 2,000 $ 2,040 $ 32,946 $ 2,000 $ 2,306 $ 37,252 $ 2,000 $ 2,591 $ 41,843 $ 2,000 $ 2,894 $ 46,736 S 2,000 $ 3,217 $ 51,953 $ 2,000 $ 3,561 $ 57,514 5 2,000 $ 3,928 $ 63,442 $ 2,000 $ 4,319 $ 69,761 S 2,000 $ 4,736 $ 76,497 S 2,000 $ 5,181 $ 83,678 $ 2,000 $ 5,655 $ 91,333 $ 2,000 $ 6,160 $ 99,493 $ 2,000 $ 6,699 $ 1,08,191 $ 2,000 $ 7,273 $ 1,17,464 $ 2,000 $ 7,885 $ 1,27,349 $ 2,000 $ 8,537 $ 1,37,886 $ 2,000 $ 9,232 $ 1,49,118 $ 2,000 $ 9,974 $ 1,61,092 $ 2,000 $ 10,764 $ 1,73,856 $ 2,000 $ 11,606 S 1,87,462 $ 2,000 $ 12,505 S 2,01,967 $ 2,000 $ 13,462 $ 2,17,429 $ 2,000 $ 14,482 $ 2,33,911 $ 2,000 $ 15,570 $ 2,51,481 $ 2,000 $ 16,730 $ 2,70,211 $ 2,000 $ 17,966 S 2,90,177 $ 2,000 $ 19,284 $ 3,11,461 $ 2,000 $ 20,688 S 3,34,149 $ 2,000 $ 22,186 $ 3,58,335 $ 2,000 $ 23,782 5 3,84,1175 RETIREMENT PHASE AGE 401K BALANCE BEGINNING WITHDRAWAL 61 3,84,117 -29,720 62 3,79,748 -29,720 3,75,091 -29,720 64 3,70,127 -29,720 65 3,64,835 -29,720 66 3,59,194 -29,720 67 3,53,181 -29,720 68 3,46,771 -29,720 69 3,39,937 -29,720 70 3,32,653 -29,720 71 3,24,888 -29,720 72 3,16,610 -29,720 73 3,07,786 -29,720 74 2.98,379 -29,720 75 2,88,352 -29,720 76 2,77,663 -29,720 77 2,66,269 -29,720 78 2,54,122 -29,720 79 2,41,174 -29,720 80 2,27,371 -29,720 81 2,12,658 -29,720 82 1,96,973 -29,720 1,80,253 -29,720 1,62,429 -29,720 85 1,43,429 -29,720 86 1,23,175 -29,720 87 1,01,585 -29,720 88 78,569 -29,720 89 54,034 -29,720 90 27.680 -29,720 INTEREST 25,352 25,063 24,756 24,428 24,079 23,707 23,310 22,887 22,436 21,955 21,443 20,896 20,314 19,693 19,031 18,326 17,574 16,772 15,917 15,007 14,035 13,000 11,897 10,720 9,466 8,130 6,705 5,186 3,566 1,840 END BALANCE 3,79,748 3,75,091 3,70,127 3,64,835 3,59,194 3,53,181 3,46,771 3,39,937 3,32,653 3,24,888 3,16,610 3,07,786 2,98,379 2,88,352 2,77,663 2,66,269 2,54,122 2,41,174 2,27,371 2,12,658 1,96,973 1,80,253 1,62,429 1,43,429 1,23,175 1,01,585 78,569 54,034 27,880 0 42 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60