PPMT in Excel (Table of Contents)
PPMT Function in Excel
The PPMT function in excel returns the payment on the principal on the monthly loan payment based on an interest rate and a constant payment schedule. 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.
PPMT function in excel is categorized as financial function. PPMT in Excel helps you to compute how much principal is being paid in any given pay period. PPMT in Excel tells how much of your mortgage goes towards principal in the specified month.
PPMT Formula Excel
Explanation of PPMT Excel Function
The PPMT function in excel have the same fields as the PPMT in Excel except for an extra field – ‘Per’
“Per” is the specific payment period for which one wants to compute the amount being paid towards principal. FV(Future Value) is an optional argument, if omitted, the fv takes on the default value 0.
How to Use PPMT Function in Excel
Let’s take a few PPMT excel examples, before using the PPMT function in Excel workbook:
PPMT in Excel 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.
Applying the PPMT function with all input values as shown above for every month installment the principal amount for each month
Similarly, applying PPMT function to other periods as well we have the principal amount of each period is as shown below
As you can see above for each period the principal amount which totals the amount as the loan amount which is $200000.
PPMT in Excel Example# 2
If the loan amount is $10,000 with the interest rate of 10% and the period of the loan is 2 year, then the principal amount for 1 month of the loan will be calculated using the ppmt in excel as shown below.
Using the PPMT function we compute the principal amount for the 1 month
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
Things to Remember About PPMT Function in Excel
- 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
You can download this PPMT Function Excel template here – PPMT Excel Template
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 –
Leave a Reply