$Compounding interest of principal P, where a compounding withdrawal amount W get withdrawn from P before each compounding of P.$

Question

$Compounding interest of principal P, where a compounding withdrawal amount W get withdrawn from P before each compounding of P.$

In accordance with the title I need a single formula that expresses the balance of a principal after "t" amount of time (in years), where

V(t) is the balance after "t" amount of time,
P is the balance before any withdrawals (subtractions) or compoundings,
W is the amount to be withdrawn (subtracted) from P at each compounding,
r is the decimal percentage the principal growths with (each year),
i is the decimal percentage the withdrawal amount growths with (each year),
n is the number of times r or i is used to compound W or P (at each period t).
- If compounding takes place each month n would be = 12. Hence the decimal percentage growth would be r/12 and i/12 for principal and withdrawal amount respectively.

Here is what I can make happen in excel, that I want to be able to do in a single formula as well. The following is an example of two following compoundings (from the beginning of a period):

W amount is withdrawn from principal P --> P-W,
What is left of the principal P is compounded and gives me a new principal value --> (P-W)*(1+r/n),
W amount is compounded and thus giving me a new amount to withdraw next month --> (P-W)*(1+r/n), W*(1+i/n)

NEXT MONTH

W amount is withdrawn from the latest principal --> (P-W)*(1+r/n)-W*(1+i/n),
What is left of latest principal P is compounded and gives me a new principal value --> ((P-W)*(1+r/n)-W*(1+i/n))*(1+r/n),
Newest W amount is compounded and thus giving me a new amount to withdraw next month --> ((P-W)*(1+r/n)-W*(1+i/n))*(1+r/n), W*(1+i/n)*(1+i/n)

And so, it can go on expanding until t amount of time has passed.

Multivariable Calculus Algebra Functions

Flammen

7

Report

Answer

The answer is accepted.

Join Matchmaticians Affiliate Marketing Program to earn up to 50% commission on every question your affiliated users ask or answer.

Answer 1

Answers can be viewed only if

The questioner was satisfied and accepted the answer, or
The answer was disputed, but the judge evaluated it as 100% correct.

View the answer

Let $W_m$ to be the amount to be withdrawn after $m$ months with $W_1=W$. Then
\[W_{m+1}=(1+i/n)W_m \Rightarrow W_m=(1+i/n)^{m-1}W. (1)\]
Let $P_m$ to be the principal after $m$ withdrawals with $P_0=P$ (initial principle). Then
\[P_{m+1}=P_m(1+r/n)-W_{m+1}=P_m(1+r/n)-(1+i/n)^{m}W. (2)\]
Now note that
\[P_1=P(1+r/n)-W.\]
\[P_2=P_1(1+r/n)-(1+i/n)W=[P(1+r/n)-W](1+r/n)-(1+i/n)W\]
\[\Rightarrow P_2= P(1+r/n)^2-\left[(1+r/n)+(1+i/n)\right]W.\]
\[P_3=P_2(1+r/n)-(1+i/n)^{2}W\]
\[=[P(1+r/n)^2-(1+r/n)W-(1+i/n)W](1+r/n)-(1+i/n)^2W\]
So
\[P_3=P(1+r/n)^3-\left[(1+r/n)^2+(1+r/n)(1+i/n)+(1+i/n)^2\right]W.\]
Now we can start to see the pattern. Similarly
\[P_4=P(1+r/n)^4\]
\[-\left[(1+r/n)^3+ (1+r/n)^2(1+i/n)+(1+r/n)(1+i/n)^2+(1+i/n)^3\right]W.\]
In general we will have
\[P_m=P(1+r/n)^m-W\sum_{k=0}^{m-1}(1+r/n)^{m-k}(1+i/n)^{k},\]
which is the formula you are looking for. Here $P$ is the initial principle, and $P_m$ is the balance of the account after $m$ withdrawals. Note that in the above formula
\[\sum_{k=0}^{m-1}(1+r/n)^{m-k}(1+i/n)^{k}=(1+r/n)^{m-1}+(1+r/n)^{m-2}(1+i/n)^{1}\]
\[+(1+r/n)^{m-3}(1+i/n)^{2}+ \dots +(1+r/n)^{1}(1+i/n)^{m-2} +(1+i/n)^{m-1}.\]

Nicolasp

163

Flammen

0

Thanks you for your answer. I tested it up against an online calculator through desmos.com/calculator with success. Any idea how to use the formula in excel? I currently use the following for the W*SIGMA part. =W*SUMPRODUCT((1+r/n)^(m-FACT(-1+ROW(INDIRECT(0+1&":"&m-1+1)))))*SUMPRODUCT((1+i/n)^(FACT(-1+ROW(INDIRECT(0+1&":"&m-1+1))))) But results are slightly off.
Nicolasp

0

I am confident about the formula we have derived. Make sure the variables in our formula are the same as the online calculator. In particular in our formula W is the amount to be withdrawn after one month. Some calculators may use W_0 (an initial amount set at the initial time). Then W=(1+i/n)W_0.
Nicolasp

0

For the sigma notation in the formula, you may write all the numbers appearing inside the sigma notation in a column and then add them up. Please watch: https://www.youtube.com/watch?v=eaei7DN6Z9M
Flammen

0

The formula fits with the online calculator where I compared results - Well done NicolasP! I am NOT complaining.
Flammen

0

As for the YouTube video. That is a good starting point which has inspired the current Excel calculation mentioned in my first comment. My results in Excel are slightly off. It needs tweaking. On desmos.com/calculator I have used our formula and was able to match the result on a few compounding interest calculator sites. Once again. Thank you for your help! :-)
Nicolasp

0

Great! I am glad it worked!
Matchmaticians

0

Thank you for asking your question at Matchmaticians.com. Please support Matchmaticians community by introducing it to your friends/family who might need mathematical help.

$Compounding interest of principal P, where a compounding withdrawal amount W get withdrawn from P before each compounding of P.$

Answer

Related Questions

Search