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

Question

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!

Chris Degnen · Accepted Answer

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

d = 100,000
∴ n = 20.9124 years

Furthermore, the balance b in year x is given by

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

See also What is the formula for loan payoff date?

JTP - Apologise to Monica · Answer

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.

RonJohn · Answer

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%.

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

3 Answers

Add your own answers!

Ask a Question