# End balance after x years of an account with fixed interest rate and growing withdrawals

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.

Kind regards,

Steve.

• Is this a college homework problem? Mar 5, 2019 at 9:46
• Its for financial planning. Mar 5, 2019 at 20:44

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
``````
• Exactly what I was looking for. Searched the internet quite a while to find this. Many thanks!! Mar 5, 2019 at 20:42