3

Is there a formula that allows me to calculate the following:-

I have an investment balance of P growing at annual rate of r, what is the monthly withdrawal so that at the end of T years the balance is 0.

Peter K.
  • 3,983
  • 2
  • 27
  • 36
Barry Hamilton
  • 184
  • 1
  • 1
  • 5

2 Answers2

0

Yes, assuming that the annual rate r is being quoted as an APR compounded monthly (that is, at the end of the month, the balance you have increases by r/12th of the balance at the beginning of the month before being brought down by your monthly withdrawal, you can use the loan amortization formula that calculates the monthly payment due on a loan of $P at an APR of r compounded monthly and due to be paid off in T years. Any number of bank loan calculators freey available on the web will do the calculation for you.

Dilip Sarwate
  • 31,788
  • 4
  • 56
  • 124
0

First, let's convert everything to months:

r' = r ^ (1/12), the monthly rate
T' = 12 * T, the number of months

The convention is that r and r' are (1 + rate as a decimal). We can assume monthly compounding for simplicity's sake. We can write a system:

B(0) = P
B(i + 1) = r' * B(i) - x
B(T') = r' * B(T' - 1) - x = 0

Here, x is the constant monthly withdrawal. To get a closed-form expression, we write out a few terms:

i    B(i)
0    P
1    r'P - x
2    r'r'P - r'x - x
3    r'r'r'P - r'r'x - r'x - x
...
k    (r' ^ k) * P - x * (r' ^ k - 1) / (r' - 1)

Here, we used the partial sum formula for a geometric series to get a closed-form expression for the sum of powers of r'. Note: if r' = 1, that is, the interest rate is 0%, this does not work; however, in that case, x = P / T'. Now we simply substitute this in fir the term k = T' and solve for x:

(r' ^ T') * P - x * (r' ^ T' - 1) / (r' - 1) = 0
x = P * (r' ^ T') * (r' - 1) / (r' ^ T' - 1)

This matches our intuition in a couple of important ways: - x is directly proportional to P - x is strictly greater than P * (r' - 1), which would be the withdrawal rate to leave the principal balance unchanged.

For instance: if you have $1,000,000 and a safe 5% APR compounded monthly and you want to take equal withdrawals monthly for 30 years, then your monthly withdrawal is about $5,300.

Note: this is the same calculation for doing mortgage amortization, just in reverse.

Patrick87
  • 2,316
  • 16
  • 16