What is the actual formula behind PMT function in Excel?

Question

What is the actual formula behind the PMT function in Excel? Does anyone have any idea? For example, consider a loan with the principal amount of $100,000/- with the interest rate of 12% to be repaid in an year (12 months) with one installment falling every month. Taking the PMT function into account, the answer would be $8,884.88 which is the monthly installment that takes into consideration both the interest as well as the principal amount. It is executed as =PMT(12%/12, 12, 100000). The answer arrives is the one mentioned above i.e. $8,884.88.

But what I want to know is that, what is the actual financial calculation going on behind this formula? If a layman would calculate it, it would be $12,000 amount of interest straight-away (assuming the interest is on per annum basis). Adding the principal amount into it, the answer would obviously be $100,000 + $12,000 = $112,000. Now if I simply divided that by 12, the answer would be $9,333.33. That means a disparity of $448.45. Where the h**l did this amount go? Or come from in the first place? Can anyone out there help out please and let me know the actual formula working behind the PMT function in Excel, with a practical example as above. It would be really appreciated.

Answer 1

There reason behind the difference in calculation is that PMT works on reducing balance and where as the calculation you mentioned is fixed interest calculation.

In case of reducing balance @12% you end up paying $6,168.55 of interest and with fixed you would pay $12,000.00.

If you add up $1,00,000 and interest i.e. $6618.55 it will come up to $1,06,618.55. divide it into 12 installments and the value is $8884.88 that is what you get with PMT.

Find out more, try IPMT to find out how to arrived at interest payable every month and PPMT to check principle payable every month in case of reducing balance interest rate.

Answer 2

PV  /  [(1- (1 / (1 + i)n )) / i]

Where PV=amt of loan (present value)

i=periodic interest rate

n=# of periods

so in excel you could enter this

=100000/((1-(1/(1+ (0.12/12)) ^ 12) )/(0.12/12))

(some spaces added to keep Answers from cutting it off)

and get the same answer as the PMT formula

-----more------>

FYI..the formula is what's known as an annuity.  There are formulas for present value of annuities, future value of annuities, periodic payments of an annuity, finding the interest rate of an annuity, etc.   Do a google search for annuity formulas and you'll find more details/examples.

Answer 3

Hi

The formula used by most functions is detailed in Excel's help topic for that formula.

-Jim

Answer 4

The =Pmt function returns the payment amount for a loan based on an interest rate and a constant payment schedule.

This is the Syntax:



Pmt( interest_rate, number_payments, PV, FV, Type )

interest_rate is the interest rate for the loan.

number_payments is the number of payments for the loan.

PV is the present value or principal of the loan.

FV is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, the Pmt function assumes a FV value of 0.

Type is optional. It indicates when the payments are due.

1 means payment are due at the beginning of each period (each month, for example)

0 means payment at the end of each period.

Let's see an eg:

This example returns the monthly payment on a $5,000 loan at an annual rate of 7.5%. The loan is paid off in 2 years (That is: 2 x 12). All payments are made at the beginning of the period.

=Pmt(7.5%/12, 2*12, 5000, 0, 1)

The 1 at the end means the installment is paid at the start of every month.

In Plain English:

7.5 (interest rate) divided 12 (monthly interest rate) for 2 years (2*12 months) for a loan of 5000, each payment is made at the start of the period.