PMT Function in Excel

Excel is a great tool to calculate financial values like loan payments. Fortunately for you, it includes a series of built-in functions to do exactly that. One is the PMT function, which is easy to use for a simple loan payment calculation. Let’s learn how to use the PMT function in Microsoft Excel.

How to Use the PMT Function in Excel

Before diving into the PMT function and learning to use it, it’s helpful to understand the limitations of the function. In essence, PMT makes two assumptions: that your interest rate will remain the same, and that your payment will remain the same.

Let’s look at a simple PMT example, assuming an annual payment. PMT requires four inputs to return the correct annual payment information.

• The annual interest rate
• The duration of the loan
• The amount borrowed
• The future value, which is $0, assuming the loan will be fully paid off.

To use the PMT function, input each of these four values into its own cell. Here, let’s list them in cells A1:A4 – 5% annual interest, for an 18-year duration, with $200,000 borrowed and a future value of $0.

Now, it’s time to calculate your annual payment. To do that, click into any empty cell in your Excel workbook. Type an = sign, which tells Excel that you’re inputting a formula. Then, type in the PMT function:

=PMT(

Now, click into the first of the four data cells, A1. Type a comma, click on cell A2, and repeat, until your formula reads:

=PMT(A1,A2,A3,A4)

Finally, hit Enter on your keyboard, and Excel will calculate the annual payment for your loan. In this example, that payment will be $17,109.24.

As you can see, the PMT function is a quick and easy way to calculate loan payments in Excel. Use it next time you’re performing financial calculations like these.