Quick, clean, and to the point

Calculate interest for given period

Excel formula: Calculate interest for given period
Generic formula 

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


For this example, we want to calculate the interest 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:


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 IPMT function returns 74.465, which is rounded to $74.47 since the Currency number format is applied.

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.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.