3

I am trying to find a formula for calculating the annual or quarterly principle remaining in a compounded interest investment account with no additional investments but with monthly withdrawals. Basically, what will I have in my invested retirement accounts each year while I am withdrawing money on a monthly basis. I am looking for the formula, not looking for an online calculator. Thanks.

  • Consider, a mortgage adds interest monthly, and a payment reduces the balance owed. The math is identical to what you're trying to do. – JTP - Apologise to Monica Feb 7 '17 at 21:55
  • Of course if either returns or amount withdrawn varies from month to month, you need to decide whether to use an approximate expected average for those numbers or compute it one month at a time, which calls for a program or spreadsheet rather than a simple compounding formula. – keshlam Feb 7 '17 at 22:02
  • I created a spreadsheet that annualizes monthly expenses then calculates yearly balance and cost of living increases over 35 years and provides for variable withdrawals and variable rates of return (by blocks of years) and then plots out annual cost of living and account balance over the 35 years. Without the formula for future value, I was calculating investment gain at the beginning of each year and at the end of each year, after deducting cost of living, and using the average of the two. As compared with using the proper formula, the old method was under reporting the balance, so...thanks! – LarryC Feb 8 '17 at 15:49
3

To calculate the balance (not just principal) remaining, type into your favorite spreadsheet program:

=FV(Rate,Periods,Withdrawal,PV)

Rate = type in the MONTHLY interest rate (so, if you expect to get 6% per year, 
type in 6%/12 or 0.5%)
Periods = type in the number of MONTHS elapsed since the initial investment
Withdrawal = type in as a POSITIVE number the monthly withdrawal amount
PV = type in as a NEGATIVE number the (present) value of the initial investment

It is important that the periods for "Periods" and "Rate" match up. If you use your annual rate with quarterly periods, you will get a horribly wrong answer.

So, if you invest $1000 today, expect 6% interest per year (0.5% interest per month), withdraw $10 at the end of each month, and want to know what your investment balance will be 2 years (24 months) from now, you would type:

=FV(0.5%,24,10,-1000)

And you would get a result of $872.84.

Or, to compute it manually, use the formula found here by poster uart:

This is often taught in high-school here as a application of geomentric series.

The derivation goes like this.

Using the notation :

r = 1 + interest_rate_per_term_as_decimal
p = present value
a = payment per term
eot1 denotes the FV at end of term 1 etc.

eot1: rp + a
eot2: r(rp + a) + a = r^2p + ra + a
eot3: r(r^2p + ra + a) + a = r^3p + r^2a + ra + a ...
eotn: r^np + (r^(n-1) + r^(n-2) + ... 1)a = p r^n + a (r^n - 1)/(r-1)

That is,
FV = p r^n + a (r^n - 1)/(r-1).
This is precisely what exel [sic] computes for the case of payments made at the end of each term (payment type = 0). It's easy enough to repeat the calculations as above for the case of payments made at the beginning of each term.

This won't work for changing interest rates or changing withdrawal amounts. For something like that, it would be better for you (if you don't want online calculators) to set up a table in a spreadsheet so you can adjust different periods manually.

  • +1 yes, 1000 * 1.005^24 - 10 (1.005^24 - 1)/0.005 = 872.82 – Chris Degnen Feb 7 '17 at 23:25
  • That is the formula I was looking for. I did find the Excel Fv function, which makes life easy, but I did want to know how it calculating the result. – LarryC Feb 8 '17 at 15:50
  • @LarryC, please mark my answer as correct if it answers your question. Thanks! – OneTruDragonGirl Feb 8 '17 at 19:27
1

Given the following variables

s = initial balance
r = periodic interest rate
w = periodic withdrawal (at period end)
b[n] = the balance in period n

Where b[n + 1] = b[n] (1 + r) - w and b[0] = s

then b[n] = ((1 + r)^n (r s - w) + w)/r

For example, illustrating with some figures.

s = £1000
r = 0.02 = 2% per quarter
w = £100 per quarter

The balances in the first four quarters (n = 1, 2, 3, 4) are

b[1] = £920.00
b[2] = £838.40
b[3] = £755.17
b[4] = £670.27

Check

As per the Excel formula provided by OneTruDragonGirl

=FV(0.02, 4, 100, -1000)

£670.27

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.