Formulae help needed

[Jeff Blanchard]

Hi

Can any one help me to work out a formulae to do the following

1.I have $100,000 for my retirement pension.
2. If my investments have an effective earning rate of 6% per annum ,paid quartely
3. My draw down from my fund is say 8% per annum , paid quarterly

I need to work out a cash flow on a quarterly basis what my fund balance will be
I now it will last approx 23 years

Any help would be appreciated

Thanks
Jeff Blanchard
(e-mail address removed)

 

[Norman Harker]

Hi Jeff!

There is probably a formula solution but you wanted a cash flow.

D1:
100000
G1:
6%
H1:
8%
B2:
=D1*$G$1/4
C2:
=-D1*$H$1/4
D2:
=D1+C2+B2

Select B22 and copy down.

You'll find that in about 360 years the drawings reduce to below $1.

However, if you amend the above so that C2 is a constant 2% of $100000
per quarter
C2:
-$D$1*$H$1/4
Copied down as before.

Now the fund will last 23 1/2 before the balance reduces below the
required $2000.

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

Can any one help me to work out a formulae to do the following

1.I have $100,000 for my retirement pension.
2. If my investments have an effective earning rate of 6% per annum
,paid quartely
3. My draw down from my fund is say 8% per annum , paid quarterly

I need to work out a cash flow on a quarterly basis what my fund
balance will be
I now it will last approx 23 years

Any help would be appreciated

Thanks
Jeff Blanchard
(e-mail address removed)