Originally Posted by joeu2004[_2_]

"Umesh Banga" wrote:
how can we calculate in excel that how long the
retirement savngs lets say $100,000 will last for?

First, you need to make assumptions about the growth rate (interst) and how
much you will withdraw each year.

Suppose the average growth rate is 4%. Your savings will last forever, even
grow, if your withdrawal is no more than $4000 per year (4% of $100,000).

But suppose you must withdraw $8000 per year. Then the NPER function tells
you how long your savings will last, to wit:

=NPER(4%,-8000,100000,0,1)

Note: That assumes the first withdrawal is at the beginning of the first
year.

However, it is unrealistic to expect a constant withdrawal. Assuming the
withdrawal represents expenses, expenses increase at an annual inflation
rate.

One way to tackle that is with an amortization schedule. For example,
suppose A1 contains the amount of retirement savings (100000), A2 contains
the average growth rate (4%), A3 contains the initial withdrawal (8000), and
A4 contains the average inflation rate (3%).

Then set up the following table:

D1: =A1
B2: 1
C2: =A3
D2: =(D1-C2)*(1+$A$2)
B3: =B2+1
C3: =C2*(1+$A$4)

Copy D2 and paste in D3. You will note that the references to D1 and C2
change accordingly.

Then select B3:D3 and drag the lower-right corner down 12 or more rows.

In this example, you should see that column D becomes negative when the
value in column B becomes 14.

Of course, you can make the table more robust. And there is probably a
Microsoft template that you could use. Use Excel Help or Google to search
for a template.