Retirement savings in excel

[Umesh Banga]

hi,

how can we calculate in excel that how long the retirement savngs let
say $100,000 will last for

 

[joeu2004]

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 B33 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.

 

[Umesh Banga]

awesome thanks.

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) an
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% o
$100,000).

But suppose you must withdraw $8000 per year. Then the NPER functio
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 th
first
year.

However, it is unrealistic to expect a constant withdrawal. Assumin
the
withdrawal represents expenses, expenses increase at an annual inflatio

rate.

One way to tackle that is with an amortization schedule. For example,
suppose A1 contains the amount of retirement savings (100000), A
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 C

change accordingly.

Then select B33 and drag the lower-right corner down 12 or more rows.

In this example, you should see that column D becomes negative when th

value in column B becomes 14.

Of course, you can make the table more robust. And there is probably

Microsoft template that you could use. Use Excel Help or Google t
search
for a template