Excel Interest Calculator (PMT, IPMT, PPMT)
Copy ready-made PMT, IPMT and PPMT formulas to build a loan repayment schedule in Excel or Google Sheets โ or just use the online calculator below.
Excel calculatorn tiedot
The Excel formulas for loan interest
Excel and Google Sheets have built-in functions for loan maths. PMT(rate, nper, pv) gives the monthly payment; IPMT(rate, per, nper, pv) gives the interest part of a chosen payment; and PPMT(rate, per, nper, pv) gives the capital part.
Use a monthly rate and number of payments. For a £10,000 loan at 6% over 5 years: =PMT(6%/12, 5*12, -10000) returns about £193.33 a month.
Build a repayment schedule in Excel
- Put the loan amount, annual rate and term in three cells.
- In the first payment row, use
=IPMT(rate/12, period, term*12, -principal)for interest and=PPMT(...)for capital. - Track the running balance by subtracting each period's capital from the previous balance.
- Copy the row down for every month to see the full amortisation schedule.
Frequently asked questions โ Excel Interest Calculator (PMT, IPMT, PPMT)
PMT(rate, nper, pv) returns the fixed periodic payment on a loan. Use a monthly rate (annual/12) and the total number of monthly payments. Enter the present value (loan amount) as a negative number so the payment comes out positive.
Use IPMT for the interest portion and PPMT for the capital portion of a given payment. Both take the period number as an argument, so you can list every month and see how the split changes over the term.
Yes. PMT, IPMT and PPMT work identically in Google Sheets, so you can copy the same formulas across. The online calculator on this page uses the same maths if you'd rather not build a spreadsheet.