The pmt( function (short for payment) calculates the periodic payment required to pay off a loan.
Parameters
This function has five parameters:rate – is the interest rate of the loan (per period). For example, if there is one payment per year, this is the annual percentage rate. If there is one payment per month, then this is the monthly percentage rate.
periods – is the number of payments required to pay off the loan. For example, if this is a 36-month loan with one payment per month, this value is 36. If this is a 30-year loan with one payment per month this value is 1080.
payment – is the amount being borrowed, the original amount of the loan. For example, if you borrow $22,000 to purchase a car, the loan amount is 22000.
balloon – (future value) is the value of the loan at the end of the period. This is almost always zero.
begin – specifies whether payments are made at the beginning (1) or end of each period (0). Most loans are paid at the end of each period (the first payment is made at the end of the first month, etc.), in which case this value would be zero.
Description
The pmt( function calculates the periodic payment required to pay off a loan.
If the payment period is annually the calculation is simple. Suppose you take out a $50,000 loan at 12% for 10 years, with one payment per year. This formula will calculate the payments.
pmt(.12,10,50000,0,0) ☞ 8849.21
Most loans are paid more frequently than once a year - usually once a month. To calculate the payments for such a loan you must convert the annual percentage rate into a monthly percentage rate by dividing by 12. Suppose you are taking out a 36-month loan of $20,000 to purchase a car. If the annual interest rate is 13.5%, here is the formula for calculating the monthly payments:
pmt(0.135/12,36,20000,0,0) ☞ 678.71
Our final example is for a $180,000 real estate loan for 30 years at a fixed rate of 9%. In this case the number of years is multiplied by 12 to get the number of monthly payments, and the annual interest rate is divided by 12 to calculate the monthly interest rate.
pmt(.09/12,30*12,180000,0,0) ☞ 1448.32
Of course the pmt( function only works with fixed interest rates.
Note: The pmt( function is designed to be compatible with the Microsoft Excel function of the same name.
See Also
History
| Version | Status | Notes |
| 10.0 | No Change | Carried over from Panorama 6.0 |