Solve for level payment with a twist

Question

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)

Answer 1

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$.

Answer 2

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$.