Excel formula for saving/income

[bond]

Please could someone tell me the Excel formula for a draw down calculation. Eg If I have £100,000 saved and it is earning 3% per annum (with the interest being added back into the savings), but I want to withdraw £5000 per annum, how many years will I get out of it?

[factor-fiction]

When is interest added- monthly or at year end?

[HowardKennitby]

Assuming 3% net and £5,000 constant than about 30 years.

[factor-fiction]

And do you want to draw out at start or end of each year, or at teh start/end of each month

[bond]

Interest to be paid annually, and the 3% is net. I need the exact formula or format to type into an excel spreadsheet. Many thanks.

[factor-fiction]

Remember the £5000 pa won't be worth much in real terms in 30 years so you may be need to increase teh amount you take out later

[bond]

draw out at end of each year.

[factor-fiction]

To calculate amount at end of year 1:
cell A1: 100000, cell A2= A1*1.03-5000
cell B1= A2, cell B2 =A2*1.03-5000
cell C1 = B2, cell C2 = B2*1.03-5000

Just copy the row calculations down.

This will give balance at start and end of each year. You can add a column for the year if you want

[factor-fiction]

Money runs out at end of year 31

[bond]

Super, thanks factor30. I'd still be interested if there is an excel function (like "FV" or "PV") for this. Regards.

[HowardKennitby]

Factor

Much as what I said earlier :-)

[dr b]

the NPER function does the trick. =NPER(3%,-5000,100000)=30.999 years.

[bond]

Thanks to all. Love this website and the shared knowledge : )

[sunny-dave]

Blimey Dr B - I thought I knew excel pretty well, but NPER is a new one on me :)

*** Have a Gold Star ***