PMT Function in Excel - how to use it to calculate loan payment

Posted on:  08/10/2021
PMT Function in Excel - how to use it to calculate loan payment

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.

Payment function in Excel

PMT function for a car loan

YouTube video

Other Excel articles

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

PMT function for a house loan

Other Excel articles

Excel - Get and Transform Data | Unpivot Columns with Two Header Rows

Excel - Get and Transform Data | Unpivot Columns with Two Header Rows

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Recommend PivotTables - Improved Experience

Excel Recommend PivotTables - Improved Experience

Excel PivotTable - Calculate the number of days of each week for every month of the year

Excel PivotTable - Calculate the number of days of each week for every month of the year

Analyze Results from Microsoft FORMS POLLS in Excel  | Watch Chris Work

Analyze Results from Microsoft FORMS POLLS in Excel | Watch Chris Work

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