PPMT Function in Excel

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

PPMT Function Excel

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

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

PPMT Formula

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Explanation

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

ArgumentsDescription
RateInterest Rate of the Loan
PerSpecific payment period
NperIt is the total number of payment that has to be made
PV (Present Value)Amount of the loan (principal amount)
FV (Future Value)Amount as a future value that wants to have left after final payment
TypeWhether the payments are made at the beginning (1) or end of the month (0)

“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)

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

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.

PPMT Example 1
PPMT Example 1-1

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

PPMT function in excel Example 1-2

Similarly, applying the PPMT function to other periods, we also 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 $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.

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. 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, it will convert the annual interest rate into the quarterly rate (rate%/4), and the period number has to be converted from years to quarters (=per*4).
  • By convention, the loan value (pv) is entered as negative.

Recommended Articles

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: –

Reader Interactions

Comments

  1. Kashinath Basak says

    This article is very important to me.
    Thanks

    • Dheeraj Vaidya says

      Thanks for your kind words!

Leave a Reply

Your email address will not be published. Required fields are marked *