# Formula for "How long until balance 0" with compound interest and withdrawals?

I an looking for a formula which returns "n" for a given combination of

• Starting balance (1M)
• Withdrawal rate per year (80k)
• Expected interest (8%)

I looked around but couldn't find a formula which gives me "this lasts for x amount of years" or returns "infinity" if it will grow faster than I can withdraw!

• Note that unless you actually have \$1 million in an investment with a guaranteed 8% interest rate in perpetuity, this calculation is next to useless. You need to factor in the variance on the return of your investments, because a few bad years early on can doom you. Commented Aug 14, 2021 at 17:01
• The keyword that will find you calculators for this purpose is "amortization".
– J...
Commented Aug 15, 2021 at 16:15
• @Craig That's just an example. The real numbers are around 3M in funds and expected 5% returns esch year. Commented Aug 15, 2021 at 18:14

With

``````starting balance    s = 1,000,000
annual withdrawal   d =    80,000
annual interest     r =         8%
``````

The interest gained in a year is 80,000 and if the same amount is withdrawn at the end of the year the balance will be back to 1 million, so this can go on perpetually.

On the other hand if the annual withdrawal is 100,000 for example, the number of years before depletion is given by

$n=-\frac{log(1-\frac{rs}{d})}{log(1+r)}$

``````  d = 100,000
∴ n = 20.9124 years
``````

Furthermore, the balance `b` in year `x` is given by

$b=\frac{d+(1+r)^x(rs-d)}{r}$

So the balance in year 20 is

``````  x = 20
∴ b = 84,760.71
``````

Adding interest gives the amount that can be withdrawn in year 21.

``````  final withdrawal = b(1 + r) = 91,541.57
``````
• I think the formula for b is missing a plus sign: d is added to, not multiplied by, the rest of the numerator. Commented Aug 14, 2021 at 20:42
• @nanoman Indeed, thanks. I've fixed it. Commented Aug 14, 2021 at 21:15
• It matters when in the year the withdrawal occurs and when the interest is paid. If you withdraw 80k evenly over 12 months on the 1st of each month and the 8% annual interest is calculated daily then you will end up with ~996560 and not 1 million.
– MT0
Commented Aug 15, 2021 at 18:06

First, what you seek is identical to a mortgage.

\$1M, 8% rate, 30 year term (to start). The monthly payment is \$7338, and annual is \$88,052. As I push the term out to see how long the money will last, I realize that the withdrawal rate is equal to the interest, and infinity is indeed the answer. Unfortunately, after about 10% of infinity has passed, inflation will make that \$80K+ each year have no value at all.

There is an equation to calculate N, of course, but knowing this is the mortgage equation makes it easy to find online calculators to see how the numbers impact each other. Why is it the same as a mortgage?

I am the bank. I invest \$1M in your mortgage. You are paying me back on my investment, earning 8% per year, and giving me \$80,000, which of course is an 'interest only' loan. In real life, there would be a balloon payment after X years.

• 10% of infinity +1 Commented Aug 14, 2021 at 12:35

If your purpose is "just" to get the answer, then use the `=NPER()` function in a spreadsheet. (The payment number must be negative since it's an outflow.)

Note how the function throws an error starting at exactly 8%.