1

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.

  • Is this a college homework problem? – RonJohn Mar 5 at 9:46
  • Its for financial planning. – Steve de Peijper Mar 5 at 20:44
1

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

enter image description here

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

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.