I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement...tory=moneylast

I've tried combining the payment and PV functions and I can get it to work
if return is 0% with some inflation rate, but I can't figure out hot to
factor the two together.

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.

What is the formula in Excel to generate this number?

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



In article ,
Ted Metro wrote:

I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement...tory=moneylast

I've tried combining the payment and PV functions and I can get it to work
if return is 0% with some inflation rate, but I can't figure out hot to
factor the two together.

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.

What is the formula in Excel to generate this number?

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 gives me a place to experiment though, thanks for the lead.

"JE McGimpsey" wrote:

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



In article ,
Ted Metro wrote:

I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement...tory=moneylast

I've tried combining the payment and PV functions and I can get it to work
if return is 0% with some inflation rate, but I can't figure out hot to
factor the two together.

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.

What is the formula in Excel to generate this number?

I got it!

=PMT((PV(inflation,1,-(interest rate-inflation rate)*100,0)/100),years to
last,-current value,0,1)



"Ted Metro" wrote:

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 gives me a place to experiment though, thanks for the lead.

"JE McGimpsey" wrote:

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



In article ,
Ted Metro wrote:

I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement...tory=moneylast

I've tried combining the payment and PV functions and I can get it to work
if return is 0% with some inflation rate, but I can't figure out hot to
factor the two together.

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.

What is the formula in Excel to generate this number?

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.

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.

Errata....

On Apr 7, 5:33 am, "joeu2004" wrote:
=round(pmt((1+5%)/(1+4%)-1, 2, -25000, 0, 1)
[....]
=round(pmt((1+5%)/(1+3%)-1, 30, -100000, 0, 1)

Well, at least my typos are consistent ;-). Of course, those should
be:

=round(pmt(...), 0)