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):
  1. W amount is withdrawn from principal P --> P-W,
  2. What is left of the principal P is compounded and gives me a new principal value --> (P-W)*(1+r/n),
  3. 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
  1. W amount is withdrawn from the latest principal --> (P-W)*(1+r/n)-W*(1+i/n),
  2. 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),
  3. 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.

Answer

Answers can be viewed only if
  1. The questioner was satisfied and accepted the answer, or
  2. The answer was disputed, but the judge evaluated it as 100% correct.
View the answer
  • 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.

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

  • 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

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

  • 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! :-)

  • Great! I am glad it worked!

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

The answer is accepted.