Excel is one of the most powerful tools for understanding and managing a mortgage loan. With just a few formulas, you can calculate monthly payments, compare loan terms, and even build a full amortization schedule. This guide will show you step by step how to use Excel to calculate a mortgage loan quickly and accurately. For more ways to calculate and compare loans, visit our mortgage loan calculation process
1. Gather Your Mortgage Details
Before setting up your Excel sheet, you’ll need the following:
-
Loan amount (Principal) – The total amount borrowed.
-
Annual interest rate – Your mortgage rate expressed as a percentage.
-
Loan term – The number of years to repay (e.g., 15 or 30).
-
Payment frequency – Usually monthly.
2. Use the PMT Function in Excel
Excel has a built-in function for loan payments called PMT.
Formula syntax:
=PMT(rate, nper, pv)
Where:
-
rate = interest rate per period (annual rate ÷ 12 for monthly)
-
nper = total number of periods (years × 12)
-
pv = present value (loan amount, entered as a negative number)
Example
Loan = $250,000
Annual interest = 6% → 0.06 ÷ 12 = 0.005 monthly
Loan term = 30 years → 360 payments
=PMT(0.06/12, 360, -250000)
Result: $1,498.88 per month
3. Building a Basic Amortization Schedule
You can go beyond the monthly payment and see how each payment splits between interest and principal. Here’s how:
-
Set up columns for: Payment #, Beginning Balance, Interest, Principal, Ending Balance.
-
Calculate interest: Beginning Balance × Monthly Interest Rate.
-
Calculate principal: Monthly Payment – Interest.
-
Update balance: Beginning Balance – Principal.
-
Repeat for each row until the balance reaches zero.
Example Table
| Payment # | Beginning Balance | Interest | Principal | Ending Balance |
|---|---|---|---|---|
| 1 | $250,000 | $1,250 | $248.88 | $249,751.12 |
| 2 | $249,751.12 | $1,248.76 | $250.12 | $249,501.00 |
4. Testing Scenarios in Excel
One of Excel’s strengths is what-if analysis. You can:
-
Compare 15 vs. 30-year terms side by side.
-
See how a higher or lower interest rate affects payments.
-
Add extra principal payments to see how much faster you could pay off your loan.
Tip: Use Data → What-If Analysis → Data Tables for side-by-side comparisons.
5. Why Use Excel Instead of a Calculator?
-
Customizable – You decide what data to include.
-
Transparent – You can see every step, not just the final number.
-
Repeatable – Once built, you can reuse your spreadsheet for any loan scenario.
-
Insightful – Great for exploring “what if” strategies like refinancing or early payoff.
Conclusion
Excel makes mortgage loan calculations simple, flexible, and powerful. Whether you just need a quick monthly payment or a full amortization breakdown, Excel gives you the tools to understand and take control of your mortgage.