How to Use Pmt Function in Excel

In this lesson, you can learn how to calculate loan installment using PMT function in Excel.

PMT function helps you to calculate the amount of the installment loan assuming a fixed interest rate and fixed monthly payment amount. ith this feature, you can also calculate the amount you need to deposit to save a certain amount at a certain time.

Payments calculated by PMT contain basis and exclude taxes and interest, and other fees associated with the loan.

PMT function syntax

This function has the following syntax:

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

  • Rate – the rate of interest loans.
  • Nper is the total number of installments
  • PV – current value (for example the amount of credit)
  • FV – value or future financial level you want to get after the last payment
  • Type is the number 0 or 1 indicates when the payment takes place 1-payment in advance, 0-payment of arrears.

How does the PMT function work?

The PMT function works by using a formula to calculate the periodic payment required to repay the loan. The formula takes into account the present value, the interest rate, the number of periods, and the future value (if specified).

The optional argument can be used to specify whether the interest is compounded annually or not. If the argument is FALSE, the interest is compounded annually. If the argument is TRUE, the interest is compounded monthly.

See also  How to Countif with Partial Match

Examples of Pmt function

Here are some examples of how to use the PMT function in Excel:

Calculating monthly installment

Calculate the monthly installment of the loan in the amount of 100 000 $. Interest is 7% per annum, and the loan must be paid within 15 months.

pmt function example

In any cell, enter the function: =PMT (7%/12, 15, -100000)

  • 7%/12 is interest rate per month
  • number of rates is 15
  • amount of credit is 100 000 (with minus becase that’s not your money and you have to pay it back)

Result is 6 982$

Calculating monthly installment paying in the beginnig of the month

Calculate the monthly payment amount for the same loan, if payments with payment in advance (in the beginning of the month)

Formula is: =PMT (7%/12, 15, -100000, 0, 1)

  • 7%/12 is interest rate per month
  • number of rates is 15
  • amount of credit is 100 000 (with minus becase that’s not your money and you have to pay it back)
  • future value is 0, because you want to pay back whole loan
  • 1 means payments in beginning of the period

Result is 6 941,51$

How much to save to become millionaire?

You want to save $ 1 000 000 over 10 years. Every year you deposit a fixed amount. The interest rate is 5% savings. Using the PMT function, calculate how much you should deposit per month.

Formula is: =PMT (5%/12, 120, 0, 1000000)

  • 5%/12 is interest rate per month
  • 120 is number of month (10 years * 12)
  • 0 is present value (you have any deposit now)
  • 1 000 000$ is future value
See also  How to use DATEDIF function

Result is -6 439,88$ (with minus because you have to pay it to the bank).