EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel PMT Function in Excel
Secondary Sidebar
Excel Functions
  • Financial Functions in Excel
    • PPMT Function in Excel
    • NPER Function in Excel
    • NPV Formula in Excel
    • MIRR Formula in Excel
    • Excel RATE Formula
    • Excel PV Formula
    • Excel PRICE Function
    • MIRR Excel Function
    • PV Excel Function
    • YIELD Excel Function
    • NPV Function in Excel
    • RATE Function in Excel
    • IRR Function in Excel
    • FV Function in Excel
    • PMT Function in Excel
    • XIRR In Excel
    • IPMT in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Excel for Finance Course Certification
  • Excel Data Analysis Training
  • Excel for Marketing Training

PMT Function in Excel

By Madhuri ThakurMadhuri Thakur

PMT Function in Excel

Excel PMT Function

PMT function in Excel is used to calculate the payments that need to be paid for any loan or investment amount at a fixed rate of interest with the same constant 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. The reason behind naming the function as PMT is because it calculates the payment amount.

PMT Formula in Excel

PMT Formula in Excel

PMT formula in Excel has the following arguments:

There are five parameters that are used in this PMT function. In which three are compulsory, and two are optional.

COMPULSORY PARAMETER:

Start Your Free Excel Course

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

  1. Rate: It is the interest rate that we need to pay 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: It is the number of periods in which the loan is to 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. 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.

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 loan is 12 months.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,725 ratings)

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 exact amount that has to pay 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 have to make sure that the interest rate should be monthly, which we to 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 as well, 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 amount we need to invest monthly to get the fixed amount in the future. There are lots of 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 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

In case the payments are made annually, we can use 5% as the interest rate.

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 are paying an equal monthly installment or investing monthly, then the cash is going out from our pocket; that is why the sign is in 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 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 are calculating monthly or 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 result.

Relevance and Uses

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

Things to Remember

  • #NUM! error –

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 are calculating monthly or quarterly payments, in this situation, users 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 and 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
Popular Course in this category
Excel for Finance Training (18 Courses, 7+ Projects)
  18 Online Courses |  7 Hands-on Projects |  85+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
4 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download PMT Function Excel Template

EDUCBA

Download PMT Function Excel Template

EDUCBA

डाउनलोड PMT Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more