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's Unpivot Columns, part of Power Query, is an awesome feature. It is the easier way to fix data that isn't in a tabular format.

Excel Conditional Formatting with the TODAY function | Duplicate Rule

Excel Conditional Formatting with the TODAY function | Duplicate Rule

The TODAY function is one of my favorite Date & Time functions in Excel. It returns the current date and will automatically update. We will use the TODAY function with Conditional Formatting and create three rules on a date field.

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Excel Accounts Payable / Accounts Receivable Exercise - Watch Chris Work

Accountants use Excel all the time. In this video, which is great for Accounting students, we look at the Accounts Receivable (AR) or Accounts Payable (AP) exercise.

Excel Recommend PivotTables - Improved Experience

Excel Recommend PivotTables - Improved Experience

Recommended PivotTables in Excel is getting a great update. To use Recommended PivotTables, click the Insert Tab, and click Recommended PivotTables.

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

If you want to calculate how many days are in each month for a year, it is easy to do with a PivotTable in Excel. I want to know how Sundays through Saturdays there are for each 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

I recently spoke at the Administrative Professional Day Conference hosted by the University of Georgia. After reviewing my survey results in Microsoft Forms, I realized I needed to analyze the survey data. Forms results are immediately available in Excel.

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