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/