Formulae help needed

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff Blanchard - 07 Apr 2004 09:30 GMT

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
blanch@wantree.com.au

Reply to this Message

Norman Harker - 07 Apr 2004 10:22 GMT

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 B2:D2 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.

Signature

Regards
Norman Harker MVP (Excel)
Sydney, Australia
njharker@optusnet.com.au
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
blanch@wantree.com.au

Reply to this Message