PMT function use 360 days convention or 365 days convention

Mohitjain wrote:

i want to know what is the formula used in PMT function and PMT function use 360 days convention or 365 days convention

The formula for PMT is basically the formula that appears in the PV help page.  Solving for PMT with signed cash flows and rate <> 0:

PMT = -(pv*(1+rate)^nper + fv) * rate / ((1+rate)^nper - 1) / (1+rate*type)

PMT makes no distinction between 360-day and 365-day convention.  Instead, it assumes "nper" equal periods.

If you are trying to find equal payments to amortize a loan over unequal periods, for example the actual days of each month, you cannot use the PMT function.

First, you must look at the applicable regulations for your region to see how to compute and use the daily interest rate.  In the US, the annual interest rate would be divided by 365 (not 366 for leap years), then multiplied (not compounded) by the number of days between scheduled payments.

Then you could set up an amortization schedule for the complete term of the loan and use Solver to discover the payment.

However, that payment will depend on the precise payment due dates.

For example, consider a 15-year 100,000 loan at 4% annual interest rate with payments due on the 1st of the month.

Assuming 12 equal payments per year, PMT returns about 739.69.

But assuming a daily simple interest rate of 4%/365, Solver finds a payment of about 739.91 if the loan starts on July 1 2011, but about 739.87 if it starts on Aug 1 2011.

Note that if we used the result from PMT, the ending balance would be about 55.02 or 43.89 respectively, not zero.

(Computed payments were not rounded, as they must be in real life.  Solver sometimes gets confused when amounts are rounded.  Rounding payments to the cent changes the ending balance by -0.93 to 0.36.)