EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Financial Functions in Excel PMT Function in Excel
 

PMT Function in Excel

Madhuri Thakur
Article byMadhuri Thakur

Updated May 8, 2023

PMT Function in Excel

 

 

Excel PMT Function

PMT function in Excel calculates the payments that need to be paid for any loan or investment amount at a fixed interest rate with the same constant amount. The PMT function is named after its purpose: to calculate the payment amount. This is just EMI that we pay for our loan or invested amount when we opt for any policy or loan from a bank.

PMT Formula in Excel

PMT Formula in Excel

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

PMT formula in Excel has the following arguments:

Five parameters are used in this PMT function. Three are compulsory, and two are optional.

COMPULSORY PARAMETER:

  1. Rate: We need to pay the interest rate per period/time. If it is monthly payments, it will be like rate/12(rate divided by 12 months). If it’s quarterly, it will be rate/4(rate divided by 4 months).
  2. Nper: The number of periods the loan will be paid back.
  3. Pv: It is the present value of the loan.

OPTIONAL PARAMETER:

  1. [Fv]: It is the future value of payments we want after the loan is paid off. In this case, we only want to get the loan paid and nothing else; omit it or make it 0.
  2. [Type]: If the payment is due at the end of the month, omit this or make this 0. If 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.

How to Use the PMT Function in Excel?

Let us now see how to use this PMT function in Excel with the help of some examples:

You can download this PMT Function Excel Template here – PMT Function Excel Template

Example #1

Suppose we have to purchase a flat costing $9,00,000, and we know the interest rate, which is 9 percent, and the total months of the loan is 12 months.

In this case, we wanted to know the installment amount or EMI, which needs to pay each month for the loan amount of $9,00,000.

In this case, the PMT function helps determine the amount that must be paid each month.

Let’s see the below calculation:

PMT Example 1

Now we will see step by step calculation from the PMT formula in Excel to know the installment amount which needs to pay each month:

We must ensure that the interest rate should be monthly, which we calculate by dividing no. of months (12).

Here in the above example, we have to divide 9%/12 months.

i.e. F3/F7

PMT Example 1-1

Which results in 0.75%.

PMT Example 1-2

Now, we have to find out the no. of months for which the loan has been taken

=F5*F7 (i.e. 5 years *12 months)

PMT Example 1-3

Which results in 60 Months.

PMT Example 1-4

Now we will find the PMT by using the below formula.

=PMT(F4,F6,D5)

Excel PMT Function Example 1-5

Hence, $18,682.52 is the EMI that needs to pay each month.

PMT Example 1-6

So, we calculated the total amount payable, including interest and principal.

PMT Example 1-7

So the Result will be $1,120,951.18.

PMT Example 1-8

Example #2

PMT function in Excel also helps us calculate the monthly amount we need to invest in getting the fixed amount in the future. There are many situations in our life where we have to deal with it for a few purposes or goals; in this situation, the PMT function helps.

For example: Suppose we want to invest in getting $1,00,000 in 10 years when the annual interest rate is 5%.

Below is the calculation:

The interest rate is calculated to be 0.417% by 5% divided by 12 months (because we are investing monthly, if we want to invest quarterly, then divide it by 4)

Excel PMT Function Example 2-1

We can use 5% as the interest rate if the payments are made annually.

Below is the calculation:

PMT Example 2-2

Sign Convention:

As we can see in the above example that the output is negative because of Cash Outflows. If we pay an equal monthly installment or invest monthly, the cash is going out of our pocket; that is why the sign is negative.

PMT Function Error in Excel:

We can face the below error while performing the PMT function in Excel:

Common Errors:

#NUM! – It happens when the supplied value of the rate is less than or equal to -1;

The supplied value of nper is equal to 0.

NUM Error Example 3.1

#value! – It will occur if any of the supplied arguments are not numeric.

VALUE Error Example 3.2

So, with the help of the above, we came to know that the above are the few common errors.

Below are the few errors which is also encountered by the users while applying the PMT function in Excel:

Common Problem:

The result from the PMT function is much higher or lower than expected.

Possible Reason:

When users calculate monthly, quarterly, or annual payments, they sometimes forget to convert annual interest rates or the number of periods according to the requirement. Hence, it gives the wrong calculation or results.

Relevance and Uses

  • PMT function in Excel helps us give periodic loan payments.
  • PMT function allows us to determine the installment amount.
  • PMT function is generally used in financial institutions where a loan or investment is given.

Things to Remember

  • #NUM! error –

The situations are below:

  1. The given rate value is less than or equal to -1.
  2. The given nper value is equal to 0.
  • #VALUE! Error –

It occurs when any of the arguments provided are non-numeric.

  1. When users calculate monthly or quarterly payments, in this situation, they need to convert annual interest rates or the number of periods to months or quarters, as per their need.
  2. If users want to find out the total amount that was paid for the duration of the loan, we need to multiply the PMT as calculated by nper.

Recommended Articles

This has been a guide to PMT Functions in Excel. Here we discuss the PMT Formula in Excel, how to use the PMT function in Excel, and practical examples and downloadable Excel templates. You can also go through our other suggested articles –

  1. COLUMN Function in Excel
  2. NPER Function in Excel
  3. FV Function in Excel
  4. PRODUCT Function in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download PMT Function Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

Download PMT Function Excel Template

EDUCBA

डाउनलोड PMT Function Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW