%
Monthly payment
?
Total interest
?
Total repaid
?
Payment schedule
The three inputs, and what each one does
- The amount moves the payment proportionally. Borrowing twice as much doubles the payment, all else equal. This is the only one of the three that behaves that simply.
- The rate bites harder over longer terms. A point of extra interest on a 5-year loan is a nuisance; on a 25-year loan it is a large sum.
- A longer term lowers the payment and raises the cost. Always both. The total repaid figure is there so the second half is not invisible.
- The rate is annual, not monthly. Enter 7 for 7% a year. Dividing by twelve is done for you, which is the single most common error when people build this in a spreadsheet.
- It assumes a fixed rate and equal payments. Variable-rate loans, interest-only periods and payment holidays all change the answer and are not modelled here.
The equivalent in Excel and Google Sheets
Both use the same function: =PMT(rate/12, years*12, -amount). The minus sign in front of the amount is what makes the result come out positive; without it the payment appears as a negative number, which is a spreadsheet convention rather than a mistake.