Retirement Savings

Date: 05/07/2001 at 01:39:29
From: Dan
Subject: Compound interest

How can I calculate the following on an Excel spreadsheet?

I want to retire at 65 and start my savings at age 25, with the 
following data:

I receive 5% interest compounded monthly. That rate remains constant 
through age 80.

I want a fixed monthly withdrawal of $1,000.00 from age 65 through age 
80. The interest is calculated at the end of the month, after a 
deposit or withdrawal.

I'll withdraw $1,000.00 on the 1st of the month and get interest 
credited on the balance at the end of the month before the next 
withdrawal. When they deposit money, they do so on the 1st of the 
month and receive interest at the end of the month.

I think I will need $192,000.00 in my account to retire. I know that I 
would have to save $400.00 per month if I did not receive the monthly 
compound interest - this is where I am having trouble, trying to 
figure out the interest, how is it will affect the monthly deposit, 
and what formula I would need in the spreadsheet.

Thanks for your help.

Date: 05/07/2001 at 14:56:07
From: Doctor Mitteldorf
Subject: Re: Compound interest

Dear Dan,

If you're just interested in answers, the easiest thing to do is to 
use a software tool designed for the purpose. I wrote one, which was 
distributed commercially until a couple of years ago; now it's 
available free from my page on the Math Forum Web site at:

   http://mathforum.org/~josh/   

(It's a DOS program called PerSense, and except for the fact that it 
runs as a DOS child under Windows, it's convenient, friendly, and a 
first-rate product.)

If you're interested in the theory, here's what to do. Make a column 
of payments on the spreadsheet, with their dates. For each payment, 
calculate the present value as =(payment)*exp(-rate*years). The true 
rate is a little less than your 5% monthly rate. (This point is 
explained in the PerSense help file. You can just use the number 
4.9896%.) The number of years is computed by your spreadsheet, 
subtracting today's date from the date of the payment and dividing by 
365.25.

Now you have the present value of all SAVINGS payments. The next step 
is to do the same thing for all PAYOUTS, which start when you're 65. 
Again, compute a present value for each, and a sum of the present 
values. When these two present values match, your savings exactly 
matches your needs. You can use the "solve for" feature of the 
spreadsheet to get the payments in and the payments out to match in 
present value.

The good news is that, even at a modest 5% interest rate, you'll only 
need to save $82.58 per month. The bad news is that we haven't 
accounted for inflation. The following is from the PerSense Investment 
Screen:

           +--DEPOSITS-------------------------------+ 
     Funds | Periodic Payments                       | 
      IN   +-From---+Through-PrYr--Amount---+Dollars-+ 
           | 1/ 1/07|12/ 1/46|12|      82.58|CURRENT |
           +--------+--------+--+-----------+--------+ 

           +--WITHDRAWALS----------------------------+ 
     Funds | Periodic Payments                       | 
      OUT  +-From---+Through-PrYr--Amount---+Dollars-+ 
           | 1/ 1/47|12/ 1/61|12|   1,000.00|CURRENT |
           +--------+--------+--+-----------+--------+ 

       Interest   +---------+    Inflation  +---------+ 
         rate %   |   5.0000|      rate %   |         | 
                  +---------+               +---------+ 

- Doctor Mitteldorf, The Math Forum
  http://mathforum.org/dr.math/