Loan Calculator

Monthly payment, total interest, and full amortization schedule.

100% in your browser — nothing uploaded

Calculate monthly payments and total interest on any loan

Loan amount (principal)

Annual interest rate (%)

Loan term (duration)

YearsMonths

Decimals: use a period or a comma — 99.5 or 99,5.

—Monthly payment

—Total paid

—Total interest

—Interest / Principal

Amortization schedule

Month	Payment	Principal	Interest	Balance

In Excel and Google Sheets

With the loan amount in A2, the annual rate in B2 (as 5 for 5%), and the number of months in C2, these formulas calculate the monthly payment and the principal/interest breakdown per period.

Monthly payment (positive number):
=-PMT(B2/1200, C2, A2)

Interest portion of month 1:
=-IPMT(B2/1200, 1, C2, A2)

Principal portion of month 1:
=-PPMT(B2/1200, 1, C2, A2)

Total interest over the life of the loan:
=-PMT(B2/1200, C2, A2)*C2 - A2

A2 = loan amount, B2 = annual rate (%), C2 = number of months. PMT returns a negative value (cash outflow); the leading minus sign flips it.

IPMT and PPMT take a period argument — change the 1 to the month number you want. To fill a full schedule, put 1 in D2 and drag down to D2+C2−1, then use $A$2, $B$2/1200, $C$2 with absolute references so the formula copies cleanly.

In SQL

To generate a full amortization table from SQL, use a recursive CTE. The first row computes the monthly payment with the standard formula; subsequent rows split each payment into principal and interest and reduce the remaining balance.

WITH RECURSIVE
  params AS (
    SELECT
      250000::numeric   AS principal,
      6.5               AS annual_rate,
      360               AS term_months
  ),
  loan AS (
    SELECT
      p.principal,
      p.annual_rate / 1200        AS monthly_rate,
      p.term_months,
      ROUND(
        p.principal
        * (p.annual_rate / 1200)
        * POWER(1 + p.annual_rate / 1200, p.term_months)
        / (POWER(1 + p.annual_rate / 1200, p.term_months) - 1),
        2
      ) AS payment
    FROM params p
  ),
  schedule AS (
    SELECT
      1                                        AS month_num,
      l.payment,
      ROUND(l.payment - l.principal * l.monthly_rate, 2)
                                               AS principal_part,
      ROUND(l.principal * l.monthly_rate, 2)   AS interest_part,
      ROUND(l.principal - (l.payment - l.principal * l.monthly_rate), 2)
                                               AS remaining
    FROM loan l

    UNION ALL

    SELECT
      s.month_num + 1,
      l.payment,
      ROUND(l.payment - s.remaining * l.monthly_rate, 2),
      ROUND(s.remaining * l.monthly_rate, 2),
      ROUND(s.remaining - (l.payment - s.remaining * l.monthly_rate), 2)
    FROM schedule s
    JOIN loan l ON TRUE
    WHERE s.month_num < l.term_months
  )
SELECT month_num, payment, principal_part, interest_part, remaining
FROM schedule
ORDER BY month_num;

Recursive CTE in PostgreSQL. Change the principal, annual_rate, and term_months values in the first WITH for your loan. Each row shows the exact breakdown for that month.

In SQL Server, replace ::numeric with CAST(... AS FLOAT) and LN() with LOG(). In MySQL, the recursive CTE syntax is identical but mind the recursion limit (SET @@cte_max_recursion_depth = 500 if the term exceeds 100 months).

About this loan calculator

Enter the loan amount, annual interest rate, and term (in years or months) to see the fixed monthly payment, total amount paid, total interest, and the interest-to-principal ratio. The calculator uses the standard amortization formula: M = P × r(1+r)^n / [(1+r)^n − 1], where P is principal, r is monthly rate, and n is total payments.

A full amortization table shows each month’s payment broken into principal and interest portions, plus the remaining balance. You can download the table as a CSV file for further analysis in Excel or a database. The tool includes Excel PMT/IPMT/PPMT functions and a SQL recursive CTE that generates the full amortization schedule.

FAQ

What interest rate should I enter?

Enter the annual percentage rate (APR) advertised by the lender. The calculator converts it to a monthly rate internally. Do not enter the monthly rate — if your APR is 6%, enter 6, not 0.5.

Why does more of my payment go to interest at the beginning?

In a fixed-rate amortized loan, each payment is the same amount, but the split between principal and interest changes. Early payments apply to a large balance, so interest is high. As you pay down the principal, less goes to interest and more goes to reducing the balance. The amortization table shows this shift month by month.

Can I use this for a mortgage?

Yes. Enter the loan amount (purchase price minus down payment), the annual interest rate, and the term (typically 15 or 30 years). The result is the principal-and-interest portion of your mortgage payment — it does not include property tax, insurance, or PMI.