Loan amortization in a spreadsheet with PMT, IPMT and PPMT

The monthly payment on a fixed-rate loan is =-PMT(rate/12, years*12, loan). For $300,000 borrowed at 6.5% for 30 years that returns $1,896.20. Each month the lender charges interest on the remaining balance, which is balance * rate/12, and the rest of your payment reduces the balance. IPMT returns the interest part of any payment, PPMT returns the principal part, and the two always add up to PMT.

This article shows the formula behind those functions, the argument order and sign rules that trip people up, and how to turn them into a complete schedule you can check against a lender's statement.

The annuity formula

A level payment P that repays a loan L over n periods at a periodic rate r is:

P = L * r / (1 - (1 + r)^-n)

For a monthly loan, r is the annual rate divided by 12 and n is the number of years times 12. With the example loan:

• r = 0.065 / 12 = 0.0054167
• n = 30 * 12 = 360
• (1 + r)^-360 = 0.143025, so the denominator is 1 - 0.143025 = 0.856975
• The numerator is 300,000 * 0.0054167 = 1,625.00, which is also the first month's interest
• P = 1,625.00 / 0.856975 = 1,896.20

The formula divides by zero when the rate is 0%. PMT handles that case correctly and returns the loan divided by the number of payments.

PMT, IPMT and PPMT: arguments and signs

The three functions take almost the same arguments in Excel, LibreOffice and Google Sheets:

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

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

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

Two details cause most errors.

Argument order. IPMT and PPMT put per before nper. Copying the PMT arguments and adding the period at the end usually returns a wrong number rather than an error.

Signs. Spreadsheets treat money you receive as positive and money you pay as negative. If pv is the loan you received, the payment comes back negative. Either negate the result or negate the loan:

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

Both return 1,896.20 when B1 is 300,000, B2 is 6.5% and B3 is 30. IPMT and PPMT follow the same rule, so in each period PPMT + IPMT = PMT.

The other common error is a unit mismatch. =-PMT(0.065, 360, 300000) uses an annual rate with monthly periods and returns $19,500.00, a full year's interest charged every month. The rate and the period count must use the same unit.

For this loan:

In the first month 85.7% of the payment is interest. In the last month 0.5% is.

Build the schedule row by row

IPMT and PPMT can fill a schedule directly, but the balance method is easier to audit and handles extra payments. Set up the inputs:

• B1: loan amount, 300000
• B2: annual rate, 6.5%
• B3: term in years, 30
• B4: payment, =ROUND(-PMT(B2/12, B3*12, B1), 2)
• B5: extra payment per month, 0

Starting in row 8, with columns Period, Beginning balance, Interest, Principal, Payment and Ending balance (A to F), enter these in row 8 and fill down 360 rows:

Interest is the beginning balance times the monthly rate, the payment is the scheduled amount, principal is what is left of the payment after interest, and the ending balance carries to the next row. The MIN keeps a payment from exceeding what is owed, and the IF makes the final row pay off whatever remains. The figures below come from this exact layout, recalculated in LibreOffice.

Period 233, about 19.4 years in, is the first month where principal exceeds interest. The loan's total interest is =SUM(C8:C367), which comes to $382,636.71, or 127.5% of the amount borrowed. Total paid is $682,636.71.

To check any row independently, the balance after k payments is =FV(B2/12, k, B4, -B1). For k = 60 that gives $280,833.22, within 4 cents of the schedule's $280,833.26; the gap comes from rounding interest each month, as the next section explains.

Rounding and the final payment

Lenders compute the payment to the cent and round each month's interest to the cent. The exact payment is $1,896.2041, so the rounded payment is $0.0041 short every month. Compounded over 360 months, those shortfalls leave about $4.71 unpaid, so the last payment is larger: $1,890.67 balance plus $10.24 interest is $1,900.91. Without the IF in the payment formula, the schedule would end with a leftover balance of $4.71.

The same rounding explains why the schedule's total interest ($382,636.71) differs by a few dollars from the unrounded figure, payment * 360 - loan, which is $382,633.47. Your loan documents govern; the schedule should match them to within pennies.

Extra payments shorten the term

Enter an extra monthly amount in B5. It reduces principal faster, which lowers the interest charged in every later month:

The last payment is smaller than the rest: $635.32 in the $200 case. You can predict the payoff without a schedule using NPER: =NPER(B2/12, -(B4+B5), B1) returns 276.30 for the $200 case, which rounds up to 277 payments. The debt payoff article uses NPER the same way.

This assumes the lender applies the extra amount to principal. Some servicers apply it to the next scheduled payment instead, so confirm that before relying on the savings.

Comparing terms

Changing B3 compares terms without rebuilding anything. Same loan and rate:

Total interest here is the unrounded payment times the number of months, minus $300,000. The 15-year loan costs $717.12 more each month and saves $212,235.49 in interest over its life.

What a payment figure leaves out

PMT covers principal and interest only. Property tax, insurance, mortgage insurance and fees are separate lines. If the rate changes during the loan, recalculate from the current balance with the remaining term, or put the rate in a column and let it vary by row. The [type] argument set to 1 is for payments at the start of a period, which suits leases more often than loans.

A checklist

• Divide the annual rate by 12 and multiply years by 12, every time.
• Decide on a sign convention before you write the first formula, and check that the payment comes out positive where you want it.
• Round interest to cents in the schedule, and make the final row pay the remaining balance.
• Check the schedule: the ending balance after the last row should be 0.00, and one row spot-checked with FV should match.
• Test extra payments with NPER before you commit to them, then confirm with the lender how extras are applied.

The loan amortization schedule on Sheet Reserve is built on this structure, with the loan amount, rate, term and an optional extra payment as inputs. For the basics of the function, see how to calculate a loan payment with PMT and what is an amortization schedule.