3

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
  • 4
    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.
    – Craig W
    Aug 14, 2021 at 17:01
  • The keyword that will find you calculators for this purpose is "amortization".
    – J...
    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.
    – ohboy21
    Aug 15, 2021 at 18:14

3 Answers 3

5

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?

3
  • I think the formula for b is missing a plus sign: d is added to, not multiplied by, the rest of the numerator.
    – nanoman
    Aug 14, 2021 at 20:42
  • @nanoman Indeed, thanks. I've fixed it. 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
    Aug 15, 2021 at 18:06
5

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.

1
  • 4
    10% of infinity +1
    – Rocky
    Aug 14, 2021 at 12:35
2

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .