Function for payments

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Can anyone please help?
What function is used to get the result of how much money
to invest yearly when my goal is after 20 years to have
$50,000.00?

I already invested $5,000 and,
The interest rate will be 12% (APR)

If I use =Pmt(0.12,20,0,50000,1) the results of $619.58 is
correct.
The 0 on the 4th column means I have nothing invested as
of now.

However if I use =Pmt(0.12,20,5000,50000,1) the results of
$1217.25 doesn't make any sense.

If I use the PPMT function, I get the same problem.
=PPmt(0.12,1,20,5000,50000,1) = $1217.25
=PPmt(0.12,1,20,0,50000,1) = $619.58

What is the right function?

Thanks

Joe
 
The PMT function is the right one to use, you just have a wrong sign.

Remember the convention, -ve means money out of your pocket, +ve means money
coming to you. So PV and FV need to have opposite signs. So,

=PMT(0.12,20,-5000,50000,1)

will give you the answer your are looking for ($21.92)
 
Thanks Fred
I got the answer of $21.92

However, I'm missing something, if I change it $6,000 like
=Pmt(0.12,20,-6000,50000,1)
The result = $97.61

Or if I change the (APR) to 15% like
=Pmt(0.15,20,-5000,50000,1)
The result = $270.20
Does this make sense?

Thanks Again
Joe
 
Yes, both results make sense. Again you are confusing the signs.

In your initial example, the result is -21.92. This means that, in addition
to the $5,000 seed money, you have to take $21.92 out of your pocket every
year to add to the investment.

In your next example, the result is +97.62. This means you get to withdraw
$97.62 every period. That's because $6000 by itself will grow to more than
$50,000 in 20 years at 12%.

Same thing with your final example. If you increase the rate, you certainly
get to increase the withdrawals.
 
Back
Top