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/
Search the Dr. Math Library:
Ask Dr. MathTM
© 1994- The Math Forum at NCTM. All rights reserved.