ExcelBanter - View Single Post

Ted Metro

Thanks joeu2004 -- good info !!

"joeu2004" wrote:

On Apr 6, 10:18 pm, Ted Metro
wrote:
"JE McGimpsey" wrote:
Ted Metro wrote:
I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement...tory=moneylast
[....]
If a portfolio is equal to 25,000 and it has to last 2 years (24 periods)
given a rate of return of 5% and an inflation rate of 4% the maximum
yearly withdrawal is $12,805.

Not sure how you got 12,805 - the web calculator gives me 12,560.
Which would correspond (approximately) with
=PMT((5%-4%),2,-25000,0,1) === 12,562.19

Thanks, yeah I'm tired it is ---- 12,560.
Something is very close, but not quite right because the error in that
formula becomes magnified as I use longer time horizons in the calculator.

That's because "5%-4%" is only a common approximation. Refer to
http://learningforlife.fsu.edu/cours...entReturns.htm .

=round(pmt((1+5%)/(1+4%)-1, 2, -25000, 0, 1)

yields 12,560.

For a more interesting and arguably more realistic example, try a PV
of 100,000 over 30 years with an average after-tax return of 5% and
average inflation of 3%.

=round(pmt((1+5%)/(1+3%)-1, 30, -100000, 0, 1)

yields 4345, which matches the result of the smartmoney.com
calculator.