PPMT Function Excel

For example, suppose the loan amount is $50,000 in cell B1, and the interest rate is 5% in cell B2. The period of the loan taken in cell B3 is 10 years. Then, we can calculate the principal amount for 1 month of the loan using the PPMT Excel function as follows:

=PPMT(B2/12,1,B3*12,B1)

= $321.99.

Syntax

Explanation

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

“Per” is the specific pay period for which one wants to compute the amount paid towards the principal. FV in ExcelFV In ExcelThe FV function in Excel is a built-in financial function that can also be referred to as the future value function. This function is very useful in calculating the future value of any investment made by anyone. It has some dependent arguments, which are the constant interest, periods, and payments.read more is an optional argument. If omitted, the fv takes on the default value 0.

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

Example #1

Suppose 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. 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’s installment, the principal amount for each month.

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

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

Example# 2

If the loan amount is $10,000 with an interest rate of 10% and the loan period is 2 years. 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. 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

This article is a guide to PPMT Function in Excel. Here, we discuss the PPMT formula Excel, how to use the PPMT function, and practical examples and downloadable Excel templates. You can have a look at other articles on Excel functions: –

  • Excel NPER FunctionExcel NPER FunctionNPER, commonly known as the number of payment periods for a loan, is a financial term and an inbuilt financial function in Excel that can be used to calculate NPER for any loan. This formula takes rate, payment made, present value and future value as input from a user.read moreExcel PMT FormulaExcel PMT FormulaPMT function is an advanced financial function to calculate the monthly payment against the simple loan amount. You have to provide basic information, including loan amount, interest rate, and duration of payment, and the function will calculate the payment as a result.read moreMortgage Calculator ExcelMortgage Calculator ExcelIn Excel, a mortgage calculator is not a built-in feature. The amortization schedule is required in order to create the categories column, into which all of the categories and data can be entered. Then, in one cell, we can use the mortgage calculation formula.read moreExcel Mathematical FunctionExcel Mathematical FunctionMathematical functions in excel refer to the different expressions used to apply various forms of calculation. The seven frequently used mathematical functions in MS excel are SUM, AVERAGE, AVERAGEIF, COUNTA, COUNTIF, MOD, and ROUND.read moreXML in ExcelXML In ExcelExcel has made it very easy for us to import the data in XML to excel in the form of tables or databases. XML is basically an external data and it can be imported to excel from the data tab under the “get external data tab from data from other sources” section.read more