WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » PPMT Function in Excel

PPMT Function in Excel

PPMT Function Excel

PPMT function in excel is a financial function that is used to calculate the payment for a given principal and the value returned by this function is an integer value. For instance, you can utilize PPMT function to get the principal amount of an installment for the first period, the last period, or any period in the between.

Syntax

PPMT Formula

Explanation

The PPMT function in excel has the same fields as the PPMT in Excel except for an extra field – ‘Per.’

PPMT

“Per” is the specific pay period for which one wants to compute the amount being paid towards the principal. FV in Excel is an optional argument; if omitted, the fv takes on the default value 0.

How to Use the PPMT Function in Excel? (with Examples)

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

Example #1

If we need to calculate the payments on the principal for months 1 and 2 on a $10,000 loan, which is to be paid off in full after 3 years with the monthly payment of $500. Interest is charged at a rate of 5% per year, and the loan repayments are to be made at the end of each month.

To calculate this, we will use the ppmt in excel.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

PPMT Example 1

PPMT Example 1-1

Applying the PPMT function with all input values as shown above for every month installment, the principal amount for each month

PPMT function in excel Example 1-2

Similarly, applying the PPMT function to other periods as well, we have the principal amount of each period, as shown below.

PPMT Example 1-3

As you can see above, for each period, the principal amount which totals the amount as the loan amount, which is $200000.

Example# 2

If the loan amount is $10,000 with an interest rate of 10% and the period of the loan is 2 years, then the principal amount for 1 month of the loan will be calculated using the ppmt in excel, as shown below.

PPMT Example 2

Using the PPMT function, we compute the principal amount for the 1 month.

PPMT Example 2-1

Here, the fv is optional, and since there is no future value, we took it as 0, and the type is 0 as the payment is made at the end of the month; even if we skip the last two arguments still, we will get the desired result.

PPMT function in excel Example 2-1

Things to Remember

  • The input rate has to be consistent. If the payments are made quarterly, so the annual interest rate will be converted into the quarterly rate that is (rate%/4) and the number of the period has to be converted from years to quarter (=per*4)
  • By convention, the loan value (pv) is entered as a negative value.

Recommended Articles

This has been a guide to PPMT Function in Excel. Here we discuss the PPMT Formula Excel and how to use PPMT Function along with practical examples and downloadable excel templates. You can have a look at other articles on excel functions –

  • Excel NPER Function
  • Excel PMT Formula
  • Mortgage Calculator Excel
  • Excel Mathematical Function
  • XML in Excel
4 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download PPMT Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More