PMT Function in Excel - how to use it to calculate loan payment
Posted on: 08/10/2021
One of Excel's most popular financial functions is the PMT function. The PMT function will calculate your payments for a loan based on the interest rate, loan amount, and the number of periods. You would use the PMT function to calculate a car or house loan.

PMT function for a car loan
YouTube video
PMT Function Syntax
PMT(rate, nper, pv, [fv], [type])
Examples of the PMT Function
Example 1: =PMT(4.25%/12,30*12,350000)
Example 1 is a PMT function not referencing cells. You will get a negative answer since it is money you have to pay. To make the answer positive, use -350000 in the formula.
Example 2: =PMT(b4/12,b3*12,-b2)
Example 2 is referencing cells. See the screenshot below.
Five arguments in the PMT function
-
rate - The interest rate for the loan.
-
nper - The total number of payments for the loan.
-
pv - The present value, or the total value of all loan payments now.
-
fv - [optional] The future value or a cash balance you want after the last payment is made. Defaults to 0 (zero).
-
type - [optional] When payments are due. 0 = end of the period. 1 = beginning of the period. Default is 0.
Tip: Wondering why [fv] and [type] are in square brackets? The brackets mean they're optional. If you don't include values for fv and type in your formula, Excel assumes your balance will be $0 at the end of the loan, and that your payments are due at the end of the period.

PMT function for a house loan
Other Excel articles
Chris Menard
Chris Menard is a Microsoft Certified Trainer (MCT) and is employed full-time as a Trainer for BakerHostetler, one of the nation’s largest law firms. Menard has a YouTube channel with other 750 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 10 million viewers have appreciated.
Categories