Excel financial function: PMT

[Guest]

Why does PMT(S, R/12, N) in Excel not equal S*(x-1)/(x^(N+1)-x) where
x = (1 + R/100)^(-1/12) ?
S= Amount of a loan
R = Annual interest rate
N = Number of months
I have derived the formula for the payment by equating the loan amount with
the sum of payment future values then some algebra.

 

[Mangesh Yadav via OfficeKB.com]

It was bit difficult for me to understand your formula. But to explain the
PMT formula from excel, EMI = PMT(S, r, t*12)

R = annualised rate
r = (1+R)^(1/12)-1 ---> monthly rate
t = period in years

I would manually write down the formula as:
EMI = S * r / (1-(1+r)^(-t*12))

And these answers do match.

In your case, what is the difference you are getting. second thing: in the
PMT formula you write R/12, whereas while calculating the x, you are doing
(1 + R/100)^(-1/12) instead of simply diving by 12. This could be a reason.

Mangesh