How to Use the PMT Function in Excel: Video Tutorials with Examples

In this tutorial, we will explore the Excel PMT function. We’ll start by looking at the basics of the PMT function and then move on to more advanced functionality. Let’s begin!

The PMT function is a financial function in Excel that returns the periodic payment for a loan. However, it can be used in a variety of different scenarios. For example, it can be used to calculate payments for loans, mortgages, and annuity payments. If you have any of these scenarios in your Excel work, the PMT function can be useful.

We first need to understand the PMT function in Excel’s syntax. The syntax for the PMT function is as follows:

=PMT(rate, nper, pv, [fv], [type])

Here’s a breakdown of what each of these arguments means:

  • rate: This is the interest rate for the loan.
  • nper: This is the total number of payments for the loan.
  • pv: This is the present value of the loan or the total amount that needs to be paid off.
  • [fv]: This is an optional argument that represents the loan’s future value. This is the amount that you would like to have at the end of the loan period.
  • [type]: This is also an optional argument representing the payment type. It can be either 0 or 1, depending on the payment made at the end or beginning of the period.

Once we understand the syntax for the PMT function, we can start using it in our Excel sheets. Here’s an example of how we might use it to calculate the payment for a loan:

  1. First, we need to enter the interest rate, number of payments, and present value in separate cells.
  2. Next, we can use the PMT function to calculate the payment amount. For example, we might use the following formula:

=PMT(B3, B4, B2, B5)

Here, B3 is the interest rate, B4 represents the number of payments, B2 represents the present value, and B5 for the future value.

  1. Press enter to see the result. The result should be the periodic payment amount for the loan.

That’s it; now you got the annual payment. To get monthly or quarterly payments, just divide the rate (B3) by 12 or 4, respectively.

We can also use the PMT function to calculate the mortgage payment. To do so, you will need to divide the Rate by 12 months, and total period, present value (amount loaned) after subtracting the down payment (if there is any). The formula will look like this:

=PMT(F3/12,F4*12,F7)

To calculate annuity payments using PMT, we will need the rate, total duration, present value, and future value for this. We will also need to include the type for this (0 for at the end, 1 for the beginning period payment).

The formula will look like this for the ending period payment:

=PMT(B15,B16,B13,0)

Conclusion

PMT function in Excel is a powerful tool for calculating payment amounts for loans, mortgages, and annuities. By understanding the syntax and arguments for the PMT function, we can easily use it to calculate these payment amounts in our Excel sheets.