Calculate my capital shortfall

[Durhamr]

I now know how to work out how long my capital will last from
previously posted question. – Thanks to everybody

But a new twist!! – Based on a capital sum of £100,000 returning a
annual interest rate of 5% and taking an income of £6,500 per annu
increasing by 2% each year my spread sheet gives me the following:

Years Capital Income
1 £100,000 £6,500
2 £93,500 £6,630
3 £91,545 £6,763
4 £89,360 £6,898
5 £86,930 £7,036

18 £25,069 £9,102
19 £17,221 £9,284
20 £8,799 £9,469

How can I work out how much extra capital I will need if the annua
interest remains at 5% and I continue to increase my income each yea
by 2% - but my starting income is £8,500 per annum?

Sorry it’s long winded

Best regards
Dust

 

[Norman Harker]

Hi Dusty!

The following UDF allows you to work out how much capital is required:

Function ValEsc(Term As Double, Review_Freq As Double, Equated_Rate As
Double, Growth_Rate As Double, Payment As Double) As Double
Dim Top
Dim Bottom
Top = 1 - ((1 + Growth_Rate) ^ Term / (1 + Equated_Rate) ^ Term)
Bottom = 1 - ((1 + Growth_Rate) ^ Review_Freq / (1 + Equated_Rate)
^ Review_Freq)
ValEsc = Payment * PV(Equated_Rate, Review_Freq, 1, 0, 0) * (Top /
Bottom)
End Function

Income is assumed annually in arrears.

=ValEsc(20,1,5%,2%,-6500)
Returns: 95325.11

With Goal Seek and cell references you could calculate the time 100000
will last.

With B1:
20
=ValEsc(20,1,5%,2%,-6500)
Returns 95325.11

Goal seek the formula cell equalling 100000 by changing A1 and you
get:
21.3553572211409

In other words, you run out before the end of 22 years.

To get how much you need to enjoy 8500 p.a. escalating at 2% for 20
years at 5% return:

=ValEsc(20,1,5%,2%,-8500)
Returns: 124655.92

It looks like the formulas can be resolved for the term but I don't
have the solution to hand. They can be simplified for annual reviews
but I prefer the facility of making them more flexible.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Durhamr]

Norman

Thanks for your help once again.
Best regards
Dusty