How to Calculate Monthly Loan Interest in Excel

A loan payment may look like one fixed number each month, but it usually contains two parts: interest and principal. Understanding how those parts change over time can help you compare loans, check a repayment schedule, or plan extra payments.

Excel makes the calculation straightforward once you know which formula to use. This guide covers the most common approaches, explains what each formula returns, and walks through a practical example.

First, distinguish interest from the payment

A monthly loan payment is not the same thing as monthly interest.

• Monthly interest is the cost of borrowing for that period. It is usually calculated on the remaining loan balance.
• Principal repayment is the portion of the payment that reduces the amount owed.
• Total payment is the sum of interest and principal.

For a standard fixed-rate loan with monthly payments, the interest portion generally decreases over time as the balance falls, while the principal portion increases.

Set up your loan inputs

A simple Excel worksheet might use these cells:

Enter the interest rate as a percentage, such as 6%, not as 6 unless you adjust the formula accordingly.

For a monthly repayment schedule, the periodic interest rate is the annual rate divided by 12:

=B2/12

The total number of monthly payments is the loan term in years multiplied by 12:

=B3*12

If payments occur more or less frequently than monthly, use the actual number of payments per year instead.

Calculate the monthly payment with PMT

Excel’s PMT function calculates the regular payment needed to repay a loan with a constant interest rate and constant payments.

=PMT(rate, nper, pv, [fv], [type])

Where:

• rate is the interest rate for each payment period
• nper is the total number of payment periods
• pv is the present value, or amount borrowed
• fv is the desired balance after the final payment; it is usually 0
• type specifies whether payments are due at the end or beginning of a period

For the example above:

=PMT(B2/12, B3*12, -B1)

The minus sign before the loan amount makes the result display as a positive payment. With a $20,000 loan at 6% annual interest over five years, the monthly payment is approximately $386.66.

The PMT result is the total monthly payment, not the interest alone. To find the interest portion, use IPMT or calculate it from the outstanding balance.

Calculate interest for a specific month with IPMT

Excel’s IPMT function returns the interest portion of a payment for a chosen period.

=IPMT(rate, per, nper, pv, [fv], [type])

Using the example inputs, the interest in the first month is:

=IPMT(B2/12, 1, B3*12, -B1)

The per argument is the payment number: 1 for the first month, 2 for the second, and so on.

You can copy the formula down a column, changing the period number for each row. For example, if the month number is in cell A7, use:

=IPMT($B$2/12, A7, $B$3*12, -$B$1)

Excel may show the interest as a negative value because it treats loan payments as cash flowing out. To display interest as a positive amount, reverse the sign:

=-IPMT($B$2/12, A7, $B$3*12, -$B$1)

A flexible alternative: calculate interest from the balance

For many repayment schedules, the clearest formula is:

Interest for the month = Beginning balance × Monthly interest rate

In Excel, if the beginning balance is in B7 and the annual interest rate is in B2, enter:

=B7*$B$2/12

This method is especially useful when you want to see the balance change from month to month or model extra payments.

A basic amortization table can include these columns:

For the first payment row, you could use:

Payment number (A7):

=1

Beginning balance (B7):

=$B$1

Payment (C7):

=-PMT($B$2/12, $B$3*12, $B$1)

Interest (D7):

=B7*$B$2/12

Principal repaid (E7):

=C7-D7

Ending balance (F7):

=B7-E7

For the next row, set the payment number to =A7+1, the beginning balance to the previous ending balance (=F7), and copy the remaining formulas down. Because the beginning balance declines, the interest amount should generally decline as well.

You can also use PPMT to calculate the principal portion directly:

=PPMT(rate, per, nper, pv, [fv], [type])

For example:

=-PPMT($B$2/12, A7, $B$3*12, $B$1)

The payment should equal principal plus interest, allowing for small rounding differences:

Payment ≈ Principal + Interest

Using IPMT and PPMT together

If you want a compact schedule without calculating interest from the beginning balance, use IPMT and PPMT in separate columns.

For a payment number in A7:

=-IPMT($B$2/12, A7, $B$3*12, $B$1)

=-PPMT($B$2/12, A7, $B$3*12, $B$1)

The first formula returns the period’s interest, and the second returns the period’s principal repayment as a positive amount. Their sum should match the regular payment calculated with PMT, subject to rounding.

Common mistakes to avoid

Dividing by 12 when the rate is already monthly

Divide by 12 only when your input is an annual nominal rate and payments are monthly. If you have already entered a monthly rate, dividing it again will understate interest.

Using years instead of payment periods

For a five-year loan paid monthly, nper is 60, not 5. In general:

Number of payments = Loan term in years × Payments per year

Confusing the interest amount with the full payment

PMT returns the total payment. Use IPMT or the balance-times-rate calculation to isolate interest.

Ignoring cash-flow signs

Excel’s financial functions use cash-flow conventions. If the loan amount and payment have the same sign, a formula may return a negative result or appear inconsistent. A common approach is to enter the present value as negative in PMT or negate the result of IPMT and PPMT.

Rounding every intermediate calculation

Formatting a cell to display two decimal places is not the same as rounding the underlying value. For a more accurate schedule, keep full precision in the formulas and format the displayed values as currency. Actual lenders may apply their own rounding and payment-posting rules, so an Excel schedule may differ slightly from a lender’s statement.

What if the loan has a different payment schedule?

The formulas can handle payment frequencies other than monthly, provided the rate and number of periods match that frequency. For example, with quarterly payments, use the rate per quarter and the total number of quarters.

For a nominal annual rate and m payments per year:

Periodic rate = Annual rate ÷ m
Number of periods = Years × m

Some loans compound interest differently from the payment frequency or use daily accrual. In those cases, dividing the annual rate by 12 may not reproduce the lender’s calculation exactly. Check the loan agreement for its stated rate and accrual method.

Conclusion

To calculate monthly loan interest in Excel, start with the rate and payment schedule that match your loan. Use PMT to calculate the overall payment, IPMT to find interest in a particular month, or multiply the beginning balance by the monthly rate for a transparent amortization schedule. Keep the payment frequency, signs, and number of periods consistent, and your worksheet can show exactly how each payment is divided between interest and principal.

Sources

• Microsoft Support, PMT function: https://support.microsoft.com/en-us/office/pmt-function-0214da64-9a63-4996-bc20-214433fa6441
• Microsoft Support, IPMT function: https://support.microsoft.com/en-us/office/ipmt-function-5cce0ad6-8402-4a41-8d29-61a0b054cb6f
• Microsoft Support, PPMT function: https://support.microsoft.com/en-us/office/ppmt-function-c370d9e3-7749-4ca4-beea-b06c6ac95e1b