Using Excel PMT function to Calculate Loan Payment Amount

Excel PMT function is one of the many financial functions available in Excel.

It helps you calculate the payment you need to make for a loan when you know the total loan amount, interest rate, and the number of constant payments.

For example, suppose you buy a house for USD 200,000. Since you don’t have that kind of cash, you get a home loan at a 4% annual interest rate.

Now, you have to pay the loan installments every month for the next 20 years.

Excel PMT function can calculate the exact amount you need to pay every month.

PMT Function Syntax

Below is the syntax of PMT function in Excel:

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

  • rate: It is the interest rate you need to pay per period. For example, if it’s monthly payments, it will be rate/12. Similarly, if it’s quarterly, it will be rate/4.
  • nper: It is the number of periods in which the loan is to be paid back.
  • pv: It is the present value of the loan. In the above house loan example, this would be USD 200,000.
  • fv: [optional argument] It is the future value of your payments you want after the loan is paid off. In case you only want to get the loan paid and nothing else, omit it or make it 0.
  • type: [optional argument] If the payment is due at the end of the month, omit this or make this 0. In case the payment is due at the beginning of the month, make this 1. For example, if payment is due on 31st January, this will be 0, but if it’s due on 1st January, make this 1.

PMT Function Examples

PMT function can be used in many different ways in Excel.

Below are some examples of using it.

Example 1 – Calculating the Monthly Loan Amount in a House Mortgage

Suppose you have a house loan of $200,000 that needs to be paid back in 20 years when the payment is made every month, and the interest rate is 4%.

Here are details regarding the arguments:

  • rate –  4%/12 (since this the payment is monthly, you need to use the monthly rate).
  • nper – 20*12 (since the loan is to be paid for 20 years every month)
  • pv – $200,000 (this is the loan value that I get today)

You can omit the optional arguments as these are not needed.

Below is the formula that will calculate the loan payment amount using the PMT function:

=PMT(C3,C4,C2)

Excel PMT Function - home loan payment calculation

Note that the loan payment is negative as it’s a cash outflow. If you want it to be positive, make the loan amount negative.

Also, remember that the interest rate remains constant throughout the period.

Example 2 – Monthly Payment to Grow Your Investment to USD 100,000

You can also use the PMT function to calculate how much you should invest per month to get a certain amount in the future.

For example, suppose you want to invest in a way to get USD 100,000 in 10 years when the annual interest rate is 5%.

Here is the formula that will calculate it:

=PMT(C3,C4,,C2)

Excel PMT Function - investment calculation

Note that since the payments are monthly, the interest is taken as 5%/12.

In case the payments are made annually, you can use 5% as the interest rate (as shown below).

Excel PMT Function - yearly investment

You May Also Like the Following Excel Tutorials:

  • Shrikant Saxena

    I just shared it on facebook because it’s so useful and informative. I had to learn it the hard way, I wish that I knew about your website back then 😀

    Keep sharing these tips, I really appreciate your tips.

    • Thanks for sharing Shrikant! Glad you found it useful

      • Shrikant Saxena

        Welcome Sumit, but you deserved it. Do you have some tips on how to print sub total on every page of an invoice? I’ve created an invoice template for a client in excel, but he wants that the subtotal should be printed on every page. I’d love to have your creative insights on it. Thanks