Summary

To calculate the principal portion of a loan payment in a given period, you can use the PPMT function. In the example shown, the formula in C10 is:

=PPMT(C6/12,1,C8,-C5)

Generic formula

=PPMT(rate,period,periods,-loan)

Explanation 

For this example, we want to calculate the principal portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this:

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest:

=C6/12

per - the period we want to work with. Supplied as 1 since we are interested in the principal amount of the first payment.

pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in front of C5 to supply -5000.

With these inputs, the PPMT function returns 74.465, which is automatically rounded to $74.47 when the Currency number format is applied.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.