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 - Find & Highlight Duplicate Rows - 3 Methods | Conditional Formatting

Excel - Find & Highlight Duplicate Rows - 3 Methods | Conditional Formatting

How to use the TODAY function in Excel

How to use the TODAY function in Excel

Excel Convert Text to Numbers - Four easy methods

Excel Convert Text to Numbers - Four easy methods

Excel - 5 easy ways to identify numbers stored as text

Excel - 5 easy ways to identify numbers stored as text

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

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

Margin of Safety vs. Break-even point in Excel

Margin of Safety vs. Break-even point in Excel

Chris Menard

Chris Menard is a Senior Training Specialist at SurePoint Technologies. Chris is certified in Excel, Word, PowerPoint, and Outlook. Menard has a YouTube channel with other 600 technology videos covering Excel, Word, Zoom, Teams, Outlook, Gmail, Google Calendar, and other resources that over 7 million viewers have very appreciated. Because of Chris's certification and expertise with Microsoft, Chris is a proud member of Microsoft's Creator Team. Being a member of Microsoft's Creator Teams means many of his videos are available on Microsoft 365 YouTube channel and Microsoft support websites.

Categories