Function or formula for reverse amortization

Posted by Dan LaChance on November 10, 2000 7:08 AM

I hope someone can help me.

Is there an Excel function or formula you know of,
that will calculate how many years or months
(period of time) a fixed sum of money earning a
fixed rate of return will last based on a fixed
amount of money withdrawn on fixed time intervals
(i.e., Monthly) and no other savings?

Any assistance would be much appreciated.

Thank you,

Dan LaChance

Posted by Tim Francis-Wright on November 11, 2000 5:54 PM

The NPER function will do what you need.
=NPER(rate, pmt, pv, fv, type)
You can specify the rate, fixed payment/withdrawal,
present value, future value, and type (end-of-period
or beginning-of-period payments/withdrawals).

For example,
NPER(5%/12,-100,10000,0,0)
=129.6285
Shows that a $10,000 nest egg, with $100
withdrawals at the end of every month will last
between 129 and 130 months in an account
earning 5% interest.

NPER(5%/12,-100,10000,0,1)
=128.9167
is the same with beginning-of-month withdrawals.