TransWikia.com

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

Personal Finance & Money Asked by ohboy21 on August 15, 2021

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!

3 Answers

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

eqn

  d = 100,000
∴ n = 20.9124 years

Furthermore, the balance b in year x is given by

eq2

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?

Correct answer by Chris Degnen on August 15, 2021

First, what you seek is identical to a mortgage.

enter image description here

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

Answered by JTP - Apologise to Monica on August 15, 2021

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

enter image description here

Answered by RonJohn on August 15, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP