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,933
  • 2
  • 26
  • 35
Barry Hamilton
  • 184
  • 1
  • 1
  • 4

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,562
  • 4
  • 55
  • 122
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,306
  • 14
  • 16
  • 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 '19 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 '19 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 '19 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 '19 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 '19 at 17:02
  • @DilipSarwate I see what you're saying now, I incorrectly used APR in the example at the end. Good catch. Also, yes, this assumes monthly compounding; compunding annually or continuously or any way but monthly would give a different result. However, at least for discrete compounding, this is illustrative of the method of calculation: it would be easy to change the system for annual, semimonthly, etc. compounding, assuming one knows how to get the rate and number of periods. I think this - showing why the formulas work, how to get them - is what other answers I've seen here lack. – Patrick87 Jan 20 '19 at 18:22
  • (possibly the asker agreed, at least) – Patrick87 Jan 20 '19 at 18:22