You just turned 30 years old today, have just received your MBA, and have accepted your first job. You are permitted to put $19,500 per year into your 401-K plan. Assume that you make the payments at the end of each year (from the end of the year when you are age 30 to the end of the year when you are age 69; 40 payments in all). Every dollar in the plan earns 5% APR per year. You cannot make withdrawals until after you retire on your 70th birthday (40 years from today). During your retirement, you plan to withdraw funds from the account at the end of each year (so your first withdrawal is at the end of the year that you turn 70).

a. How much will you have in your 401K when you retire at age 70?

b. What constant amount will you be able to withdraw each year if you want the funds to last until the end of the year that you turn 100? (31 payments)

c. Suppose that you want to make monthly withdrawals at the end of each month starting at the end of the month after you turn 70. Hence, you will be making 372 withdrawals. If your savings still is earning 5% APR, how much can you withdraw at the end of each month?

d. Suppose that you want to make monthly withdrawals of $15,000 per month at the end of the month for 372 months, starting at the end of the month after you turn 70.

How much more must be saved at the end of each of your 40 years of working to enable you to make monthly withdrawals of $15,000 per month instead of the number that you found in part c, assuming that your savings earns the same 5% APR?

Part 2. Now assume that the amount you can contribute to your 401K retirement account grows 1% per year. Still assume that you make the payments at the end of each year (from the end of the year when you are age 30 to the end of the year when you are age 69; 40 payments in all)

You will make a payment of 19,500 at the end of the year you turn 30; then all future contributions will grow at 1% per year.

Every dollar in the plan earns 5% APR per year. You cannot make withdrawals until after you retire on your 70th birthday (40 years from today). During your retirement, you plan to withdraw funds from the account at the end of each year (so your first withdrawal is at the end of the year that you turn 70).

e. Now, how much will you have in your 401K when you retire at age 70?

You will make a payment of 19,500 at the end of the year you turn 30; then all future contributions will grow at 1% per year.

f. What constant amount will you be able to withdraw at the end of each year if you want the funds to last until you are age 100? (31 payments)

please answer in excel with functions visible