Reworded trying to clarify. Also corrected example to correctly state 4 years and 150 days.

I'm struggling with how to solve for a level withdrawal that will reduce a starting balance amount to zero over n years and m days assuming interest rate x. Withdrawals and interest happen annually. The first withdrawal happens right away and is subject to no interest, the twist comes with the second withdrawal which happens after m days which would be a partial years worth of interest (if m is not 365), and the withdrawals thereafter happen annually. Partial year interest rate would be calculated as (1+i)^(m/365).

Is there a single formula or combination of formulas I can use that will give me the level withdrawal amount if I know the initial amount, the interest rate and the number of years and days?

Here's an example with everything known: Deposit 100,000, interest rate 3%, 4 years and 150 days, withdrawal $17667.40.

100,000 82332.6 (subtracted 17667.40 after 0 days) 83338.83 (add 150 days of interest on 82332.6) 65671.43 (subtracted 17667.40 after 150 days) 67641.57 (add 1 years interest on 65671.43 after 1 years and 150 days) 49974.17 (subtracted 17667.40 after 1 year and 150 days ) 51473.40 (add 1 years interest on 49974.17 after 2 years and 150 days) 33805.99 (subtracted 17667.40 after 2 years and 150 days) 34820.18 (add 1 years interest on 33805.99 after 3 years and 150 days) 17152.78 (subtracted 17667.40 after 3 years and 150) 17667.36 (add 1 years interest on 17152.78 after 4 years and 150 days) -0.03 (subtracted 17667.40 after 4 years and 150 days, close enough to zero, withdrawal is between 17667.40 and 17667.39)

  • $\begingroup$ To be clear: you have a starting bank balance $X_0$, and to get the balance in year $t$, you take $X_t$, add on the interest from that year, then subtract the withdrawal amount? And your question is how to calculate the withdrawal amount so that you reduce the balance to zero after $n$ years, with the additional complication that your 'year 1' might not be a full year? $\endgroup$ – Chris Taylor Jun 9 '11 at 14:23
  • $\begingroup$ Close but not quite I think, here is a sample order of operations to help explain my question better. deposit X0, subtract withdrawal amt y to yield X1, credit interest for partial year on principal of X1, subtract withdrawal amt y to yield X2, credit interest for full year on principal of X2, subtract withdrawal amt y to yield Xn, credit interest for full year on Xn... etc. Then knowing the interest rate, number of years n and initial deposit, how do I find withdrawal amount y? $\endgroup$ – brentj Jun 9 '11 at 15:34

First I'll do this assuming that there is no immediate withdrawal or unscheduled partial-term second withdrawal since we can account for this by hand adding a few terms and altering the deposit amount afterward.

Here's a quick run through a specific example. Say you deposited \$100,000, and were going to withdrawal \$5000 after every interest period, and you received 6% interest each period. At each stage, the current money is increased by the interest (multiply by $1.06$), and the withdrawal is subtracted, so your account would go like this:

$$ \begin{array}{rl} \text{Deposit, withdrawal 0:}&100000 \\ \text{After withdrawal 1:}&(100000)(1.06) - 5000 \\ \text{After withdrawal 2:}&(100000)(1.06)^2 - (5000)(1.06) - 5000\\ \text{After withdrawal 3:}&(100000)(1.06)^3 - (5000)(1.06)^2 - (5000)(1.06) - 5000\\ &\vdots \\ \text{After withdrawal n:}&(100000)(1.06)^n - (5000)(1.06)^{n-1} - \dots - (5000)(1.06)^0\\ =&(100000)(1.06)^n - (5000)\sum_{k=0}^{n-1}(1.06)^k \\ \end{array} $$

This should suggest what happens in general, and in fact we can just replace those numbers with variables and see that the same thing happens. Let $d$ be the initial deposit, $r$ be the interest rate for each year-long-term as a decimal like 1.06 for 6%, and let $w$ be the withdrawal amount each term:

$$ \begin{array}{rl} \text{Deposit, withdrawal 0:}&d \\ \text{After withdrawal 1:}&dr - w \\ \text{After withdrawal 2:}&dr^2 - wr - w\\ \text{After withdrawal 3:}&dr^3 - wr^2 - wr - w\\ &\vdots \\ \text{After withdrawal n:}&dr^n - wr^{n-1} - \dots - wr^0\\ =&dr^n - w\sum_{k=0}^{n-1}r^k \\ \end{array} $$

We can evaluate that sum since its a geometric series, so after $n$ years and withdrawals, the amount of money is:

$$ dr^n - w(\frac{r^n - 1}{r - 1}) $$

Now, in your weird not-the-same-frequency withdrawal case, we need to stick two extra terms on the front. The first two withdrawals can be interpreted as changing the initial deposit for this term we just came up with.

The first withdrawal makes our deposit $d-w$ instead of $d$. The $m$ days of interest correspond to letting our $d-w$ accrue $r^{(m/365)}$, so this changes the deposit to $(d-w) r^{(m/365)}$. The second withdrawal at this point changes it to $(d-w) r^{(m/365)} - w$. So we'll substitute this in for $d$ where it occurred in the earlier equation.

If you've been skipping all of this so far, here's the equation. Plug in the variables, set it to $0$, and solve for $w$.

$$ ((d-w) r^{(m/365)} - w)r^n - w(\frac{r^n - 1}{r - 1}) $$

At this point we go to a computer algebra system and ask it to simply this expression or solve it for $w$ because we're lazy (but not so lazy as to go find out if the CAS or Excel had a built in function for doing all of this for us).

Edit, actually did the above, and it pops out:

$$ w = \frac{d (r-1) r^{\frac{m}{365}+n}}{-r^{\frac{m}{365}+n}+r^{\frac{m}{365}+n+1}+r^{n+1}-1} $$

In the particular case of your example in the OP, we want to solve for $w$ in

$$ ((100000-w) (1.03)^{(150/365)} - w)(1.03)^4 - w(\frac{(1.03)^4 - 1}{(1.03) - 1})=0 $$

which by the above means we want

$$ \frac{100000 (1.03-1) 1.03^{\frac{150}{365}+4}}{-1.03^{\frac{150}{365}+4}+1.03^{\frac{150}{365}+4+1}+1.03^{4+1}-1} $$

which gives $w = 17667.39411...$. (here is a link to wolframalpha for evaluating it http://bit.ly/lof47Q )

Note I used $n=4$ years here. In your example you seem to have only gone $4$ terms despite saying 150 days plus five years. If you use $n=5$ you get $15355.50$.

  • $\begingroup$ @brentj I edited the post to add the actual formula for w; figured I'd point it out to you so you can double check it against however you're finding the roots yourself. $\endgroup$ – matt Jun 10 '11 at 5:18
  • $\begingroup$ Upvoted again for taking the extra steps of solving for W and providing link to Wolfram. $\endgroup$ – brentj Jun 10 '11 at 14:38

OK, suppose the initial balance is $B$, the annual interest rate is $r$, payment in the amount of $x$ is made every $m$ days, the total number of payments to be made is $s$. Let me write $v=1+(mr/365)$. At time zero, you pay $x$ and bring the balance down to $B-x$. After $m$ days, the balance has grown to $(B-x)v$, you pay $x$ to bring it down to $$(B-x)v-x=vB-(v+1)x$$ After $2m$ days it grows to $v^2B-(v^2+v)x$, and you bring it down to $$v^2B-(v^2+v+1)x$$ Now you can see what's happening; after $s$ payments, the balance is $$v^{s-1}B-(v^{s-1}+v^{s-2}+\cdots+1)x=v^{s-1}B-{v^s-1\over v-1}x$$ Set this equal to zero, and solve for $x$.

  • $\begingroup$ this is close, but not quite the answer I'm looking for. It computes a payment of 17922.09 for my example. I think where it gets off track is the first v the m can be between 1...365 and there after, m = 365. Thanks though, it gave me some ideas to look at. $\endgroup$ – brentj Jun 10 '11 at 3:49
  • $\begingroup$ @brentj, sorry, didn't read closely enough, thought all the payments were $m$ days apart, missed that after the second they're annual. $\endgroup$ – Gerry Myerson Jun 10 '11 at 5:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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