Exceljet

Quick, clean, and to the point

Calculate principal for given period

Excel formula: Calculate principal for given period
Generic formula 
=PPMT(rate,period,periods,-loan)
Explanation 

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)

How this formula works

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 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 rounded to $74.47 since the Currency number format is applied.
 

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.