Present value and future value

[Ted Metro]

I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement/now/index.cfm?story=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?

 

[JE McGimpsey]

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

 

[Ted Metro]

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.

 

[Ted Metro]

I got it!

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

 

[joeu2004]

I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement/now/index.cfm?story=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/course/fp101/InvestmentReturns.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.

 

[Ted Metro]

Thanks joeu2004 -- good info !!

I can't figure out the formula to get this calculator to work
http://www.smartmoney.com/retirement/now/index.cfm?story=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/course/fp101/InvestmentReturns.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.

 

[joeu2004]

Errata....

=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)