PMT function

[yh266515]

Could someone please give me some advice of how to use PMT function
(or other function???)?

I can calculate the pmt if I have the loan amount, interest rate and
payment's period but I just don't know how to do these two scenarios:

Scenario 1:
I have a loan for $10,000. I want to pay $200 each month for the next
5 years (60 payments). I'd like to know what is the formula I should
use to calculate the interest rate.

Scenario 2:
Same as above, this time I know the interest rate would be 8%
annually, loan amt still $10,000 and payment $200 each month, how to
calculate to get the number of payments.

Your help is very much appreciated.

April

 

[JulieD]

Hi

use goal seek

assume loan amount in cell C7
assume annual interest rate in cell C9
assume term (months) in cell C10
and PMT formula in cell C11 (i nest it in an ABS function as i like to see
it + rather than -)

so scenario 1 would be
C7 10000
C9 1%
C10 60
C11 =ABS(PMT(C9/12,C10,C7))

click on C11
choose Data / Goal seek
set cell C11
to 200
by changing cell C9
click OK button and look at result

do the same for the 2nd scenario

Hope this helps

Cheers
JulieD

 

[Alex Delamain]

Goal seek actually appears under Tools rather than Data (at least on m
pc!

 

[Bernard Liengme]

For (1) use the RATE function
For (2) use the NPER function
Remember that with loan, the loan amount is entered as a positive number,
the payment as negative

Best wishes

 

[JulieD]

Hi Alex

thanks .. i always get those two confused

Cheers
JulieD