PRINCIPAL PAYMENT (PPMT) Formula in Excel
Monday, Jun 22
commerce curve online accounting course

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!

0 Comments

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, “how did you do that??”
Plus weekly updates to help you learn Excel.

Download the eBook

Contact Us

Please reach out to us with questions and comments using the form.
Commerce Curve.

4 + 10 =