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.
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:
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)
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.
Given the following variables
s = initial balance r = periodic interest rate w = periodic withdrawal (at period end) b[n] = the balance in period n
b[n + 1] = b[n] (1 + r) - w and
b = s
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 = £920.00 b = £838.40 b = £755.17 b = £670.27
As per the Excel formula provided by OneTruDragonGirl
=FV(0.02, 4, 100, -1000)