How to Use the PRINCIPAL PAYMENT (PPMT) Formula in Excel

PPMT Formula in Excel calculates the principal payment of a given period for an investment or loan based on periodic, constant payments and a constant interest rate.

Formula:

Formula Explanation

Rate (required): This is the interest rate per period

Per (required): The period for which you want to find the principal and must be in the range 1 to nper.

Nper (required): The total number of payment periods in the annuity.

Pv (required): The present value is the amount that the sum of the future payments is worth today.

Fv (optional): The future value or a cash balance you want to attain after the last payment is made. If FV is omitted, it is assumed to be 0.

Type (optional): The number 0 (zero) or 1 and indicates when payments are due. 0 (zero) for when “at the end of the period” and 1 (one) for “at the beginning of the period”

Example:

On January 1, 2020, FlexCo acquired equipment through a loan for $1,000,000. The loan is payable depending on the options selected below. The rate of interest varies by option from 5% to 10%. The individual applying for the loan would like to understand what the principal payment looks like under each of the options.

Solution:

The PPMT formula can be used to calculate the principal payment due under each of the loan options. In our solution, we use the 12th month as the example period.

In our formula is as follows:

Result

• Refer to the table with the highlighted colors as reference for the formula selections.
• We often see this type of formula being used when a loan amortization schedule is being prepared.
• We can use the principal payment formula in combination with the payment formula which is something we have gone through in a similar lesson in order to calculate both the interest portion of a loan and also the principal payment portion of the loan.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!