What is in the file
The sheet has five rows of inputs at the top and then one row per month: payment, interest, principal and the balance still owed. The example is a loan of 25,000 at 7% over 5 years with equal payments, which is 495.03 a month and 4,701.80 in interest all told. None of it is typed in as a number. Every cell of the table is a formula pointing at the blue cells, so the example disappears the moment you enter your own loan.
It is a plain .xlsx with no macros, so there is no macro warning to click through. It opens in Excel, Google Sheets, Numbers and LibreOffice.
Changing the numbers
The blue cells are the only ones you need to touch:
- Loan amount in B2.
- Interest rate in B3, as a percentage. Type 4.5% or 0.045; Excel reads both as the same number.
- Term in B4, in whole years. The sheet has room for 50 years, enough for a mortgage.
- Repayment type in B5: type Equal payments or Equal principal, exactly as the cell beside it says.
Switching from equal payments to equal principal changes the principal column from an amount that grows each month to the same amount all the way, and the payment from a fixed amount to one that falls. For the example the payment goes from 562.50 in the first month to 419.10 in the last, and the interest comes to 4,447.92 instead of 4,701.80.
Shorten the term and the months after the new end leave the table; lengthen it and they come back. The rows are already there, holding formulas that show nothing while their month lies beyond the term.
The formulas in the cells
A month’s interest is what was left after the previous month, times the annual rate divided by twelve. The principal depends on the repayment type: with equal payments it is the month’s whole payment, which Excel finds with PMT, minus the interest; with equal principal it is the amount divided by the number of months. The payment is interest plus principal, and the balance is last month’s balance minus the principal.
The formulas are listed above, column by column, as the file holds them. A localized Excel shows the same formulas with its own function names the moment the file opens.
The outer condition is what makes the term live: month 1 shows only if 1 ≤ the term in months, month 2 only if 2 ≤ the term, and so on to month 600.
Checked in three programs
Every time the export changes, the file is recalculated and compared with our own engine in Excel’s syntax, in LibreOffice and in Apple Numbers. A sheet doing the same thing in all three is not a given: Numbers lacks some functions Excel has, and the first version of this sheet used one of them, which left a column frozen in Numbers. So the check opens the file for real in each and compares every cell.
Opening it in Google Sheets, Numbers or LibreOffice
- Google Sheets: File → Import, choose the file, and choose to create a new spreadsheet. The formulas come along.
- Numbers: double-click the file. Numbers recalculates everything on opening.
- LibreOffice Calc: open the file. LibreOffice shows the stored numbers until you change a cell, or until you set “Always recalculate on file load” under Tools → Options → LibreOffice Calc → Formula. The stored numbers are the ones the formulas give, so the difference shows only once you change something.
Want your own numbers right away?
The loan calculator runs the same schedule on the page, compares two rates or terms side by side, and downloads that exact schedule as Excel with your numbers already in the blue cells.
Frequently asked questions
Why do the columns show an error after I typed in B5?
The sheet knows only the two labels beside the cell, Equal payments and Equal principal. Any other word, or a typo, leaves the principal empty, and the payment and balance then show an error rather than guessing. Fix the word and the error goes.
Why is the last row not exactly 0?
Excel works with many decimals, and the last row can end on an amount that is zero to the eighth decimal but shows as −0.00 or 0.00. That is rounding, not debt.
Can I add fees?
Not in this template. The total loan cost calculator takes an arrangement fee and a monthly fee and downloads its schedule the same way, with the fees in a column of their own.
Does it work for a mortgage?
Yes, the schedule is the same: a mortgage is a loan with a long term, and the sheet has room for 50 years. What it does not do is a repayment requirement that steps down as the loan-to-value falls; that needs a rule set for the country, and the template stays rule-free.