Code. Models. Analysis. Decisions.

How to Use Excel PMT Function to Calculate a Loan Payment

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

What is the PMT Function in Excel

Video walk through example demonstrating how to use the PMT function to calculate a loan payment amount. For more advanced finacial modeling see the Loan Amortization tutorial

How is the Excel PMT function useful?

This function calculates a periodic (typically monthly) loan payment based on a constant interest rate. It can be used to calculate a mortgage loan payment, car loan payment or any other personal loan payment.

How do you use the Excel PMT Function?

The PMT Function takes three required arguments and up to two optional arguments. At a minimum, you must tell the PMT function the loan's annual interest rate(divided by number of payments per year), loan length and the present value (how much you borrowed). Present value is typically expressed as a negative number so the result of the PMT function will be positive.

Optionally, you can give the function a future value (how much you will owe at the end of the loan), but this is typically $0 and leaving it off tells Excel as much. You would use a future value other than $0 to evaluate a lease, for example in a leased vehicle you can enter the residual as the future value and in this way calculate an implied interest rate for the lease. This is also useful to compare purchasing with leasing. the optional future value argument can also be used for loans with balloon payments. The other optional argument, type, tell the function when interest start to accrue. Either 0 for immediately or 1 for at the end of the first period. In most loans the interest begins to accrue immediately, and the default value when leaving this argument out of the function is 0 to reflect this.

Excel PMT Function Syntax

=PMT(rate, nper, pv, [fv], [type])

  • rate - the interest rate of the loan, you are typically given an annual rate that must be converted to a monthly rate by dividing by 12 (or whatever the annual payment frequency is).
  • nper - the number of payments the loan has. For example, a 30 year loan has 360 payments.
  • pv - the present value of the loan (how much you borrowed), typically expressed as a negative number.
  • fv - [optional] the value of the loan after the final payment, typically 0. Generally left out of the function.
  • type - [optional] when interest starts to accrue, typically 0 for immediately. Generally left out of the function.