EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel PPMT 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

PPMT Function in Excel

By Pradeep SPradeep S

PPMT Function in Excel

Excel PPMT Function (Table of Contents)

  • Definition of PPMT Function in Excel
  • How to Use PPMT Function in Excel?

Introduction to PPMT Function in Excel

PPMT is a financial measure to check out the principal amount of a given loan amount. When you pay periodic payments on a loan taken from the bank, a specific amount or part of each payment usually goes towards the interest (it’s a fee charged for borrowing the loan) and the remaining amount of the payment to the loan principal amount (it is the amount you originally have taken or borrowed). In excel, we can find out both interest & principal amount. Most commonly used function by bank professionals, financial analysts & consultants in real estate. PPMT function is used as both worksheet & VBA function.

It’s an inbuilt or pre-built integrated function in excel that is categorized under the Financial function.

PPMT excel 1-1

Start Your Free Excel Course

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

Definition of PPMT Function in Excel

PPMT return payment of a principal amount for an investment is based on a periodic, constant payment schedule and a fixed interest rate for a given duration or time. It indicates how much of the principal amount is being paid in any given pay period.

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,596 ratings)

Syntax of PPMT Function

PPMT Syntax

Arguments of PPMT Function

  • Rate: It’s the interest rate of the loan.
  • Per (Period): It’s a target or specific payment period; it usually indicates how much principal amount has been repaid.
  • Nper (Number of a payment period): Total number of payments to be made for the loan, i.e., It is the number of payment periods during the timezone of the loan suppose, if payments are monthly for 3 years, this would be entered 3*12 to calculate 36 payment periods.
  • PV (Present value): It is the present value or amount of a loan till now.
  • FV (Future value): An optional argument or parameter that indicates the loan’s future value at the end of last or nper payments.
  • Type (optional): An optional argument or parameter that indicates when the payments are made, i.e. either at the beginning (1) of a month or at the end (0). If you enter the value 0 or blank value, then payments are due at the end of each period. If you enter the value 1, payments are due at the beginning of each period.

Note:

  • Rate: It should be either entered as a percentage or a decimal number, i.e. if you are making an annual payment at the annual rate of interest of 9%, then it should be entered as 9% OR 0.09 OR monthly payment of the same loan with the same interest rate, then you should enter as 9%/12.
  • Per (Period): It should be entered in the range of value 1 to NPER value.
  • FV ( Future Value): If you skip or omit this value, the [fv] value will be 0 by default.
  • Type: If you skip or omit this value, [Type] value will be 0 by default.

How to Use the PPMT Function in Excel?

PPMT Function in Excel is very simple and easy. Let’s understand how to use the PPMT function in Excel with some examples.

You can download this PPMT- Function- Excel -Template here – PPMT- Function- Excel -Template

Example #1 – Calculate the PPMT Value for a 5 Years Duration of Loan

The below-mentioned example contains the below-mentioned details of the loan. Here, I need to find out the principal amount by using the PPMT function.

Example 1-1

  • Now, select the “Insert Function” button (fx) option under the formula toolbar, a dialog box will appear; in the search for a function text box, you can enter the keyword “PPMT” and click on the go, automatically, function name will appear in “Select a function” box.

PPMT function in excel example 1-2

Now, you can double click on the PPMT function in the “Select a function” box. A formula window popup appears where you need to input the formula or syntax argument of the PPMT Function. i.e. =PPMT (rate, per, nper, pv, [fv], [type]).

  • Rate: It’s an interest rate of a loan; here, the annual interest rate is 11.99%; therefore, we need to enter it as 11.99%/12.

PPMT function in excel example 1-3

  • Per: It’s a target or specific payment period, i.e. here, the period will be 60 months.

 PPMT function in excel example 1-4

  • Nper (Number of a payment period): Total number of payments to be made for the loan; here, the payments are made monthly for 5 years, so it will be 60 months to enter as 12*5.

PPMT function in excel example 1-5

  • PV (Present value): It is the present value or amount of loan; here, you can enter it as a cell reference of the loan amount (C7) or its value 520000.

PPMT function in excel example 1-6

  • FV (Future value): It is an optional argument, where this parameter can be skipped or omitted; here, you can enter a value as 0 or [fv] value will be 0 by default, we need to assume it as zero balance after the last payment or at the end of NPER payments.

PPMT function in excel example 1-7

  • Once after entering all the arguments, we can click on Ok =PPMT (11.99%/12,60,12*5, C7,0), i.e. returns the PPMT or principle amount or value per month. i.e. -11,450 in cell B7.

PPMT function in excel example 1-8

Note: It will return the negative value, which indicates outgoing payments, to get a more precise value; we have to enter the loan amount, i.e. present value, as negative. So that it will give a positive value. i.e. =PPMT (11.99%/12,60,12*5, -C7,0)i.e. returns a positive value 11,450.

Example #2 – Calculate the PPMT Value for a 3 Years Duration of Loan

We can calculate the PPMT value for a 3 years duration of the loan; just we need to change the PER & NPER values.

PER & NPER values

Using PPMT Function in cell B7.

PPMT function

After using the PPMT Function in cell B7, the answer is shown below.

Monthly Payment

i.e. =PPMT (11.99%/12,36,12*3, B1,0) returns the PPMT value -17,098.

Things to Remember

  • If you enter any of the Values or arguments in PPMT as non-numeric, then it will result in a “Value error”.
  • If the Per (Period) argument in the PPMT function is less than 0 or greater than the NPER value, then it returns a “#NUM! error”
  • You need to enter the correct cell reference; If the cell reference is incorrect, then it will return VALUE! error, even if you forget to convert an interest rate or divide the interest rate argument by 12 or 6 0r 4 (based on annual or quarterly payment), it will result in VALUE! error.

Note: It should always be entered in the range of 1 to NPER.

Recommended Articles

This is a guide to PPMT Function in Excel. Here we discuss How to use the PPMT function in excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel NORMSINV
  2. COUNTA Function in Excel
  3. Excel IF Function
  4. NPER Function in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for Finance Training (18 Courses, 7+ Projects)4.9
Excel Data Analysis Training (17 Courses, 8+ Projects)4.8
Excel for Marketing Training (8 Courses, 13+ Projects)4.7
0 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 PPMT- Function- Excel -Template

EDUCBA

Download PPMT- Function- Excel -Template

EDUCBA

डाउनलोड PPMT- 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