Question

Consider the following information:

You will work for (maybe) 45 years and, then, retire.

You will live for an additional 25 years (more or less) after you retire.

You estimate that you will need $12,000 per month through your retirement years. (You are not absolutely sure about the amount or if you need it at the beginning or end of each month.)

You want to leave (about) $1,000,000 to your heirs upon your demise.

You expect to earn (around) 12% on your retirement fund during your working years and (at least) 2% after you retire.

You want to take an around-the-world cruise 1 year before you retire. Based on current prices and estimated inflation, the cruise for you and your travelling companion will cost (at least) $40,000.

In the worksheet labeled Retirement, construct a dynamic spreadsheet that incorporates the items listed above and allows for easy modifications of the inputs. Your output (clearly identified and labeled) should be the monthly savings required to meet your goals.

For this project, consider only the information and conditions provided. When constructing your worksheets, remember to consider economy and aesthetics. Your worksheets should be labeled so that an informed reader could open any worksheet and understand its purpose, inputs and outputs.

Answer 1

Soln : Step 1: As it is not given, age at present, we will consider it as 30 years. So, after 15 years , I got retired i.e. at 45 years.

We need amount of 12000 every month for 25 years and after death an amount of 1000000

interest rate till retirement on savings fund = 12% per annum = 1% per month and after retirement = 2%

Also, there is a cruise trip in 44th year cost me $40000

Step 2 :Now, let A be the amount to be saved every month

We can say that total amount in savings at age 45 years would be = A*(F/A,1%, 180)

Also, the net amount required after retirement to be calculated at age 45 years = 1000000*(P/F, 0.167%, 300) + (12000*(P/A, 0.167%, 300) + 40000)*(1+0.12)

Savings at 45 years = requirements at 45 years and after

A*(1.01¹⁸⁰ -1)/0.01 = 1000000/1.00167³⁰⁰ +(12000*(1.00167³⁰⁰-1)/(0.00167 *1.00167³⁰⁰) + 40000)*1.12

A* 499.58 = 606783.2 + ( 40000+ 2831161)*1.12

On solving we get A = 7651.39 = 7652 (approx)

Monthly savings required to meet the goals = $7652