PMT Function

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

My client has used PMT function as part of their loan
calculations. I'm having to reproduce all calculations
into a stored procedure. I've been looking around for the
exact formula calculations that PMT does with the values
and I can't seem to find the calculations any where. Can
you help?
 
Sam said:
My client has used PMT function as part of their loan
calculations. I'm having to reproduce all calculations
into a stored procedure. I've been looking around for the
exact formula calculations that PMT does with the values
and I can't seem to find the calculations any where. Can
you help?
Click to expand...

I doubt very much that you can get hold of Microsoft's algorithms. The
formula Excel uses is probably of the form:

Payments = Loan amount / annuity factor

Where annuity factor is given by the formula

(1 - (1+I)^-N) / I

I is the interest rate
N is the number of payments

There are variations depending on whether payment is in advance or in
arrears

The above annuity factor is for arrears - multiply the annuity factor by
(1+I) for in advance payments

Also the MS function includes a possibility of a balloon payment at the end
of the mortgage but your client may not use that. If he does, reduce the
loan amount by

Balloon payment * (1+I)^-N

That's it.

GB
 
Look in Excel Help at the PV function. This gives you the algorithm. You
need to do a little algebra to derive the equation used for PMT.
 
Back
Top