Gridwork template
Loan and mortgage calculator spreadsheet
Type the amount you borrow, the yearly interest rate and the number of years. PMT works out the monthly payment, the totals show what the loan costs in interest, and the schedule splits each payment into interest and principal.
What’s in it
The template opens with example numbers you can type over. Here’s the first sheet as it opens:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Loan or mortgage | ||||
| 2 | Amount borrowed | $250,000.00 | |||
| 3 | Interest rate (a year) | 4.5% | |||
| 4 | Years | 25 | |||
| 5 | Monthly payment | $1,389.58 | |||
| 6 | Total paid | $416,874.36 | |||
| 7 | Total interest | $166,874.36 | |||
| 8 | Month | Payment | Interest | Principal | Balance |
| 9 | 1 | $1,389.58 | $937.50 | $452.08 | $249,547.92 |
| 10 | 2 | $1,389.58 | $935.80 | $453.78 | $249,094.14 |
| 11 | 3 | $1,389.58 | $934.10 | $455.48 | $248,638.66 |
| 12 | 4 | $1,389.58 | $932.39 | $457.19 | $248,181.48 |
| 13 | 5 | $1,389.58 | $930.68 | $458.90 | $247,722.58 |
| 14 | 6 | $1,389.58 | $928.96 | $460.62 | $247,261.96 |
| 15 | 7 | $1,389.58 | $927.23 | $462.35 | $246,799.61 |
| 16 | 8 | $1,389.58 | $925.50 | $464.08 | $246,335.52 |
| 17 | 9 | $1,389.58 | $923.76 | $465.82 | $245,869.70 |
| 18 | 10 | $1,389.58 | $922.01 | $467.57 | $245,402.13 |
| 19 | 11 | $1,389.58 | $920.26 | $469.32 | $244,932.81 |
| 20 | 12 | $1,389.58 | $918.50 | $471.08 | $244,461.73 |
| 21 | 13 | $1,389.58 | $916.73 | $472.85 | $243,988.88 |
| 22 | 14 | $1,389.58 | $914.96 | $474.62 | $243,514.25 |
| 23 | 15 | $1,389.58 | $913.18 | $476.40 | $243,037.85 |
The formulas it uses
- B5:
=PMT(B3/12,B4*12,-B2)gives $1,389.58. PMT: the payment each period of a loan. - B6:
=B5*B4*12gives $416,874.36. - B7:
=B6-B2gives $166,874.36. - B9:
=$B$5gives $1,389.58. - C9:
=B2*$B$3/12gives $937.50. - D9:
=B9-C9gives $452.08.
Tips
- The rate in PMT is per month, so the template divides the yearly rate by 12 and multiplies the years by 12.
- Fill the schedule down further (select its last row, then Fill down) to see every month of a long mortgage.
- Try a shorter term or a lower rate in the top cells and watch the total interest change.
Questions
Is the payment right for my mortgage?
PMT gives the standard repayment for a fixed rate with equal monthly payments. Real mortgages can add fees, rate changes or insurance, so treat it as a guide and check with your lender.
How much do overpayments save?
Add a column for extra payments and take it off the balance each month: the schedule then shows the loan ending sooner and less interest overall.
Can I open it in Excel or Google Sheets?
Yes: in Gridwork, More then Save as Excel (.xlsx) keeps the formulas. It’s free, with no account, and the file stays on your device until you save it.