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
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
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.
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.
=PMT(rate, nper, pv, [fv], [type])