Working out the payment on a loan, a lease or an instalment plan is one of the most common requests made to anyone who "knows Excel". Spreadsheets have financial functions designed for exactly this, but their arguments are easily confusing: annual versus monthly rates, negative signs, values that seem the wrong way round. Let us see how to use them correctly and how to build a complete amortisation schedule. The functions behave the same way in Excel, LibreOffice Calc and Google Sheets; here I use the English names, with the Italian Excel equivalent in brackets.
The PMT function
=PMT(rate, periods, present_value, [future_value], [type])
PMT (RATA) calculates the fixed payment on a loan with a fixed rate and regular payments, the so-called annuity or French amortisation. The arguments:
- rate: the interest rate per period;
- periods: the total number of payments;
- present_value: the loan amount;
- future_value: the residual value at the end, usually zero and therefore omitted;
- type: 0 if the payment is made at the end of the period, as is usual, 1 if at the start.
The rate must match the period
This is the most frequent mistake. If the nominal rate is annual and payments are monthly, the rate must be divided by 12 and the years multiplied by 12. For a 20,000 euro loan at 6% nominal annual over 5 years with monthly payments:
=PMT(6%/12, 5*12, 20000)
The result is about -386.66. Using 6% without dividing it would produce a huge, meaningless payment.
A clarification: dividing by 12 is the correct convention when the contract states a nominal annual rate compounded monthly. The APR and other effective rates follow different definitions; to compare with a real offer, refer to the schedule provided by the lender.
Why the result is negative
Financial functions think in terms of cash flows: money you receive is positive, money you pay is negative. If the loan, which you receive, is positive, the payment, which you make, comes out negative. To show it as positive you can enter the loan amount with a minus sign, =PMT(6%/12, 60, -20000), or put a minus sign in front of the function. What matters is being consistent throughout the sheet.
The rest of the family
| Function | Returns |
|---|---|
IPMT(rate, per, periods, pv) (INTERESSI) |
interest portion of payment number "per" |
PPMT(rate, per, periods, pv) (P.RATA) |
principal portion of payment number "per" |
NPER(rate, payment, pv) (NUM.RATE) |
number of payments needed |
RATE(periods, payment, pv) (TASSO) |
implied rate per period |
PV(rate, periods, payment) (VA) |
amount that can be borrowed for a given payment |
A few practical uses:
- How much can I borrow with a maximum payment of 300 euros a month for 4 years at 5%?
=PV(5%/12, 48, -300). - How many months to pay off 10,000 euros paying 250 euros a month at 4%?
=NPER(4%/12, -250, 10000). - What rate am I paying if for 15,000 euros I repay 320 euros for 60 months?
=RATE(60, -320, 15000)*12returns the implied nominal annual rate.
Building the amortisation schedule
An amortisation schedule shows, payment by payment, how much goes to interest, how much to principal and how much remains to be repaid. Put the parameters in separate cells:
| Cell | Parameter | Example |
|---|---|---|
| B1 | Amount | 20000 |
| B2 | Annual rate | 6% |
| B3 | Years | 5 |
| B4 | Payments per year | 12 |
| B5 | Payment | =-PMT(B2/B4, B3*B4, B1) |
Then the table, starting from row 8, with the columns: payment number, opening balance, interest, principal, closing balance.
A8: 1
B8: =$B$1
C8: =ROUND(B8*$B$2/$B$4, 2)
D8: =$B$5-C8
E8: =B8-D8
A9: =A8+1
B9: =E8
From C9 onwards the formulas in C, D and E are copied from row 8. Copying row 9 down to the sixtieth payment gives you the complete schedule. The logic is simple: each period's interest is calculated on the outstanding balance, the principal portion is the difference between payment and interest, and the balance falls by the principal portion.
Checks
- The balance after the last payment must be zero or differ by a few cents because of rounding. If you rounded the interest, adjust the last payment so it absorbs the difference.
- The sum of the principal column must equal the loan amount.
- The sum of the interest is the total interest cost, a useful figure for comparing offers with different terms.
- Cross-check: for any payment, IPMT and PPMT must return the same values as the table, rounding aside.
Early repayments and simulations
With the parameters kept separate, the schedule becomes a small simulator: change the term from 5 to 7 years and watch the payment fall and total interest rise. To simulate a partial repayment, add an "Extra payment" column that is subtracted from the outstanding balance, and recalculate the payment or the term from the following month.
In my work, when someone asks me to check a schedule they have received, I rebuild it with these formulas and compare the columns: differences show up immediately, and they almost always come down to rounding or to rate conventions.
In short
- PMT calculates the fixed payment; rate and number of periods must refer to the same frequency.
- The negative sign indicates a payment; choose a convention and stick to it.
- IPMT and PPMT split each payment into interest and principal.
- PV, NPER and RATE answer the reverse questions about amount, term and rate.
- In the amortisation schedule the final balance must be zero: that is the main check.
