Thanks to Excel’s PMT function, computing the monthly payment of a loan with various terms is easy.
In summary, you provide the function with the basic loan information, including the loan amount, the interest rate, and the term, and the function will compute the payment.
You provide this basic loan information to the function through the function arguments, or parameters. There are actually five function arguments, three of the arguments are required and two are optional. An optional argument assumes a default value when omitted.
The function works like this:
=PMT(rate, nper, pv, [fv], [type])
- rate is the rate per period, and must be consistent with the nper argument and with the period you wish to return. In other words, if you want to compute an annual loan payment, then you should express this as an annual interest rate and nper should be expressed in years as well. If you are looking for a monthly payment, then you want to convert this rate into a monthly rate and should express nper in months as well.
- nper is the number of periods. This needs to be consistent with the period expected for the monthly payment and with the rate argument. If you want to return a monthly payment, express this argument in months, for example 360 for a 30 year term.
- pv is the amount of the loan, or, present value.
- [fv] is the optional argument for future value. In most cases, this will be 0 and since it is an optional argument if you omit it, the default value is 0.
- [type] is an optional argument to define when the payment occurs. 0 or omitted tells the function the payment is at the end of each period, and 1 means the beginning of the period.
Let’s see how this might work in a worksheet.
Let’s try to determine the monthly payment of a home loan.
We are borrowing $200,000 for 30 years at 4%.
Since we want the monthly payment, we know we need to express the function arguments in monthly periods. So, we would set up the function as follows:
- .04/12 is the annual interest rate divided by 12 so that it is expressed as a monthly rate
- 30*12 is the number of periods, 30 years expressed as the number of months
- 200000 is the amount borrowed
Of course, it is more likely that we would place the basic loan values into cells, and then use the cell references in the formula, something more like what is shown in the screenshot below.
For the most part, Excel’s financial functions work on a cash flow model, and since the loan amount is a positive inflow, the subsequent payments are an outflow. This is why the monthly payment amount is returned as a negative number. One small tweak is that we’ll flip the sign of the returned value by preceding the function with a negative, as follows:
- B1/12 is the annual interest rate divided by 12 to convert to a monthly rate, since we want a monthly payment to be returned
- B2*12 is the number of years multiplied by 12 so that the argument is expressed in months
- B3 is the amount of the loan
And, the final sheet is shown below: