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.

marked as duplicate by Peter K., Hart CO, Nathan L, JoeTaxpayer investing Jan 18 at 16:12

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

  • Note that your r' might be 1 + (r - 1) / 12, rather than my number. This increases the above example to ~$5,368/mo. – Patrick87 Jan 18 at 14:53
  • Patrick it was exactly this I needed to conver to effective periodic rate before applying to the amortization formula. – Barry Hamilton Jan 18 at 15:28
  • If the APR is 5% compounded monthly , then r' is 5/12 and not the twelfth root of r. – Dilip Sarwate Jan 20 at 15:30
  • @DilipSarwate I contemplate that possibility in the comment above. It really comes down to what the r means. But yes, however determined, r' is the monthly rate and this calculation assumes monthly compounding. – Patrick87 Jan 20 at 16:37
  • The point is that if r is the effective annual yield (APY), then r' is the twelfth root of r if you want to assume that the yield is added on in 12 monthly installments, while if r is the "APR compounded monthly" then r' is r/12. It is not clear which the OP meant, and we get a completely different calculation if the yield is credited annually (as with dividend distributions from a mutual fund) while the withdrawals are monthly (as the OP desires). – Dilip Sarwate Jan 20 at 17:02
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.

  • 1
    I'm looking for a formula not a calculator? The loan amortization formula doesn't behave as expected. – Barry Hamilton Jan 18 at 14:18

Not the answer you're looking for? Browse other questions tagged or ask your own question.