Question

This annual figure from #3 ($16,509.66) is more than the Prof.’s current annual contribution, which makes her feel a little anxious about her future planned retirement. Also, Prof. Business’ annual retirement account contribution is based on a percentage of her salary and will increase as her salary increases. So, let’s re-plan her retirement income. Let’s account for the fact that her and the University’s contributions to Prof. Business’ University retirement plan are based on a certain percentage of her salary and will increase as her salary increases. Based on this formula, her first upcoming end of the year deposit will be $20,200 and let’s assume that her annual deposit and salary will grow at a 2% annual rate over the remaining 7 years (8 total deposits) to Prof. Business’ retirement. These deposits are in addition to the $640,000 she currently has today in the University retirement plan. The Rate of Return is 7.50%. Answer the following based on these assumptions using Excel.

a) How much money will Prof. Business have in her retirement account immediately after her last deposit 8 years from today?

b) What would be the equal annual payment from her 20-year retirement annuity whose first payment occurs exactly 8 years from today?

Answer 1

A) Since , the answer demanded is to be done is excel.

Step 1: Input all the given values in the cells.

Step 2 : Calculate the deposit of each year by increasing the previous year amount by 2 %..

Step 3 : Calculate the accumulated value of each deposit at the end of 8 years from now. Step 4 : Calculate the accumulated value of the current balance after 8 years.
Step 5 : Sum the accumulated values to obtain the total deposit after 8 years.

B) To obtain the equal annual payment from her 20 year retirement annuity :

We simply obtain the annuity factor which is stated in the excel sheet given below.

And then divide the Amount at the present with the annuity factor to obtain the equal installment.

INSTALLMENT = (PRESENT VALUE OF AMOUNT) / (ANNUITY FACTOR)