Build a mortgage calculator spreadsheet you can actually play with
Online calculators give you one number and hide the rest. A mortgage calculator spreadsheet shows every payment, how much is interest, and what extra payments or a different rate would save. Tell your agent your loan and it builds the Excel file with live formulas, a full amortization schedule and a comparison of up to three offers.
The prompt
- Build me a mortgage calculator spreadsheet in Excel. Home price [HOME PRICE], down payment [DOWN PAYMENT], interest rate [INTEREST RATE], term [LOAN TERM], start date [START DATE]. Also include property tax [PROPERTY TAX], home insurance [INSURANCE] and [OTHER MONTHLY COSTS]. Sheet 1, Inputs and summary: monthly principal and interest, total monthly cost, total interest over the loan, payoff date. Sheet 2: a full amortization table, one row per month, with payment, interest, principal, extra payment and balance. Let me enter an extra monthly payment of [EXTRA PAYMENT] and show the interest saved and new payoff date. Sheet 3: compare these offers side by side: [OFFERS TO COMPARE]. Add a chart of balance over time. Use real Excel formulas (PMT, IPMT, PPMT), input cells in yellow, and explain each formula in a short notes tab.
What to change
- [HOME PRICE]: E.g. "$400,000".
- [DOWN PAYMENT]: Amount or %, e.g. "10%".
- [INTEREST RATE]: Annual rate from your lender, e.g. "6.25%".
- [LOAN TERM]: E.g. "30 years", "25 years", "15 years".
- [START DATE]: Month of the first payment, e.g. "January 2027".
- [PROPERTY TAX]: Yearly amount, e.g. "$4,800 a year". Leave blank if unknown.
- [INSURANCE]: Yearly premium, e.g. "$1,500 a year".
- [OTHER MONTHLY COSTS]: E.g. "PMI of $120/month", "HOA $200/month", or "nothing else".
- [EXTRA PAYMENT]: E.g. "$200/month", or "0" to add it later.
- [OFFERS TO COMPARE]: E.g. "Bank A 6.25% 30y, Bank B 5.9% 30y with $3,000 fees, Bank C 5.6% 15y".
Example result
- Summary (illustrative example)
- Price 400,000 | Down 10% (40,000) | Loan 360,000 | 6.25% | 30 years | First payment Jan 2027
- • Principal and interest: 2,216.58 per month
- • Property tax: 400 | Insurance: 125 | PMI: 120
- • Total monthly cost: 2,861.58
- • Total interest over 30 years: about 437,970
- • Payoff: December 2056
- Amortization table (first rows)
- • Jan 2027: payment 2,216.58, interest 1,875.00, principal 341.58, balance 359,658.42
- • Feb 2027: payment 2,216.58, interest 1,873.22, principal 343.36, balance 359,315.06
- • Mar 2027: payment 2,216.58, interest 1,871.43, principal 345.15, balance 358,969.91
- In the first year, about 84% of each payment is interest. By year 20 most of it goes to principal.
- With 200/month extra
- • Payoff about 6 years earlier
- • Interest saved: roughly 102,000
- • The exact numbers appear in the summary when you type your extra amount
- Offer comparison (30-year unless noted)
- • Bank A, 6.25%, no fees: 2,216.58/month
- • Bank B, 5.90%, 3,000 fees: 2,135.29/month. Saves about 81/month, so the fees pay back in about 37 months
- • Bank C, 5.60%, 15 years: 2,960.64/month. Higher payment, but total interest is far lower
- Formulas used
- • Monthly payment: =PMT(rate/12, years*12, -loan)
- • Interest in month n: =IPMT(rate/12, n, years*12, -loan)
- • Principal in month n: =PPMT(rate/12, n, years*12, -loan)
- • Balance: previous balance - principal - extra payment
- Notes
- PMI usually drops off once you reach about 20% equity, depending on your loan. Your lender's official Loan Estimate is the number to rely on.
How to do it with todo.is
- Copy the prompt and fill in the price, rate, term and any costs you know.
- Send it in todo.is or to your agent on Telegram or WhatsApp.
- Your agent builds the Excel file with formulas, an amortization table, a chart and a notes tab.
- Open it in Excel or Google Sheets and change the yellow cells. Ask your agent to add a refinance tab or rent-vs-buy comparison later.
Tips for a better result
- Compare offers by total cost over the years you expect to keep the loan, not just the monthly payment.
- Include taxes, insurance and PMI. Principal and interest alone can understate your monthly cost by hundreds.
- Check whether your lender allows overpayments without penalty before you plan on extra payments.
- Ask your agent to explain any line on your lender's Loan Estimate you don't understand.
mortgage calculator spreadsheet: FAQ
- How do I calculate a mortgage payment in Excel? Use =PMT(annual rate/12, number of months, -loan amount). For a 360,000 loan at 6.25% over 30 years that's =PMT(0.0625/12, 360, -360000).
- What is an amortization schedule? A table showing every payment over the life of the loan, split into interest and principal, with the remaining balance after each one.
- Does an extra payment really save that much? Early extra payments go straight to principal, so they cut the interest charged on every later month. The earlier you start, the bigger the saving.
- Is this spreadsheet financial advice? No. It's a calculator built from your inputs. Confirm figures with your lender and talk to a financial adviser for decisions about your situation.
JavaScript is required to use the todo.is app.