I am looking for a formula to calculate the balance on my savings account after x years whereby this savingsaccount has a fixed interest rate and my withdrawals increase each year (because of inflation) with a fixed rate.
I am not looking for a multi-row spreadsheet solution. I need one cell (excel) formula.
Thanks for your help!
Kind regards,
Steve.
The answer depends on the frequency of the withdrawals. If they are annual it's fairly simple. You can use this formula:-
b[n] = ((r + 1)^n (i s - r s + w) - w (i + 1)^n)/(i - r) Formula 1
where withdrawals are made at the end of each year, and
b[n] is the balance in year n
r is the effective annual interest rate
i is the effective annual rate of inflation
s is the initial savings amount
w is the annual withdrawal
E.g. with
s = 10000
w = 600
r = 0.05
i = 0.03
The balances at the end of year 1, 2 & 3 are
b1 = s (1 + r) - w = 9900
b2 = b1 (1 + r) - w (1 + i) = 9777
b3 = b2 (1 + r) - w (1 + i)^2 = 9629.31
With the formula you can directly obtain the year 3 balance:-
b[3] = ((r + 1)^3 (i s - r s + w) - w (i + 1)^3)/(i - r) = 9629.31
You could also use the above formula for monthly withdrawals (using monthly rates), but the withdrawals would increase throughout the year.
For monthly withdrawals increasing at annual intervals the following formula can be used:-
b[n] = (w ((m + 1)^y - 1) ((i + 1)^n - ((m + 1)^y)^n))/
(m (-i + (m + 1)^y - 1)) + s ((m + 1)^y)^n Formula 2
where
m is the periodic interest rate
w is the periodic withdrawal
y is the number of periods
E.g. with
s = 10000
w = 600/12
r = 0.05
y = 12
m = (1 + r)^(1/y) - 1
i = 0.03
the balance at the end of year 3 is
b[3] = (w ((m + 1)^y - 1) ((i + 1)^3 - ((m + 1)^y)^3))/
(m (-i + (m + 1)^y - 1)) + s ((m + 1)^y)^3 = 9585.09
Checking with a long-hand calculation:-
t1 = s (1 + m) - w
t2 = t1 (1 + m) - w
t3 = t2 (1 + m) - w
t4 = t3 (1 + m) - w
t5 = t4 (1 + m) - w
t6 = t5 (1 + m) - w
t7 = t6 (1 + m) - w
t8 = t7 (1 + m) - w
t9 = t8 (1 + m) - w
t10 = t9 (1 + m) - w
t11 = t10 (1 + m) - w
t12 = t11 (1 + m) - w = 9886.37
t1 = t12 (1 + m) - w (1 + i)
t2 = t1 (1 + m) - w (1 + i)
t3 = t2 (1 + m) - w (1 + i)
t4 = t3 (1 + m) - w (1 + i)
t5 = t4 (1 + m) - w (1 + i)
t6 = t5 (1 + m) - w (1 + i)
t7 = t6 (1 + m) - w (1 + i)
t8 = t7 (1 + m) - w (1 + i)
t9 = t8 (1 + m) - w (1 + i)
t10 = t9 (1 + m) - w (1 + i)
t11 = t10 (1 + m) - w (1 + i)
t12 = t11 (1 + m) - w (1 + i) = 9748.65
t1 = t12 (1 + m) - w (1 + i)^2
t2 = t1 (1 + m) - w (1 + i)^2
t3 = t2 (1 + m) - w (1 + i)^2
t4 = t3 (1 + m) - w (1 + i)^2
t5 = t4 (1 + m) - w (1 + i)^2
t6 = t5 (1 + m) - w (1 + i)^2
t7 = t6 (1 + m) - w (1 + i)^2
t8 = t7 (1 + m) - w (1 + i)^2
t9 = t8 (1 + m) - w (1 + i)^2
t10 = t9 (1 + m) - w (1 + i)^2
t11 = t10 (1 + m) - w (1 + i)^2
t12 = t11 (1 + m) - w (1 + i)^2 = 9585.09
The long-hand calculation confirms the formula.
This formula works for quarterly withdrawals too. Just set y = 4.
Derivation
Formula 1 is derived from the following difference equation
b[n + 1] = b[n] (1 + r) - w (1 + i)^n where b[0] = s
Formula 2 is derived in two steps. First the one-year total with x periods is derived:-
f[x + 1] = f[x] (1 + m) - w where f[0] = q
∴ f[x] = ((1 + m)^x (m q - w) + w)/m
Then this is included in a multi-year formula, with q = b[n]:-
b[n + 1] = ((1 + m)^y (m b[n] - w (1 + i)^n) + w (1 + i)^n)/m
where b[0] = s
∴ b[n] = (w ((m + 1)^y - 1) ((i + 1)^n - ((m + 1)^y)^n))/
(m (-i + (m + 1)^y - 1)) + s ((m + 1)^y)^n
