Why Filipino Homeowners Still Rely on Excel for Housing Loan Calculations

Despite the rise of online tools, the Philippine housing loan Excel calculator remains one of the most searched financial templates in the country. Why? Because spreadsheets give you full control. You can change any assumption, model multiple scenarios side by side, and share the file with your spouse or financial advisor without needing an internet connection.

This guide walks you through exactly how to build your own housing loan calculator in Excel from scratch, explains every formula you need, and shows you where the DIY approach has limits — and when a live, bank-connected tool like Nook's free calculator is more reliable.

The Core Formula: How Philippine Bank Amortization Actually Works

Philippine banks use the declining balance method for home loan amortization. This means your monthly payment stays the same throughout the fixed-rate period, but the split between principal and interest changes every month — early payments are mostly interest, while later payments chip away more at the principal.

The monthly amortization formula is:

M = P × [r(1+r)^n] / [(1+r)^n − 1]

In Excel, you don't even need to type this formula manually. Use the built-in PMT function: =PMT(rate/12, term_years*12, -loan_amount). For example, if you borrowed 3,500,000 at 7.5% per annum for 20 years, you'd enter: =PMT(7.5%/12, 20*12, -3500000), which returns approximately 28,139 per month.

Step-by-Step: Building Your Excel Housing Loan Calculator

Step 1 — Set Up Your Inputs Section

Create a clean inputs block at the top of your spreadsheet. Label and populate these cells:

Step 2 — Calculate Summary Outputs

Just below your inputs, add these computed cells:

Using the example above (3,500,000 at 7.5% for 20 years), your summary would show: Monthly Payment = 28,139, Total Paid = 6,753,360, Total Interest = 3,253,360. That's nearly as much in interest as the loan itself — a sobering number that makes the case for refinancing to a lower rate.

Step 3 — Build the Full Amortization Schedule

This is the most powerful part. Create a table with columns: Month, Opening Balance, Monthly Payment, Interest Portion, Principal Portion, Closing Balance.

For month 1 on a 3,500,000 loan at 7.5%: Interest = 3,500,000 × (7.5%/12) = 21,875. Principal = 28,139 − 21,875 = 6,264. Closing Balance = 3,500,000 − 6,264 = 3,493,736. You'll notice how little principal is repaid in the early years — this is exactly why refinancing in the first 5–10 years delivers the greatest savings.

Step 4 — Add a Bank Comparison Tab

Create a second worksheet tab called "Bank Compare." List the banks you're considering across the columns (BDO, BPI, Security Bank, Metrobank, etc.) and use the same PMT formula for each with their respective rates. Include rows for: Indicative Rate, Monthly Payment, Total Interest over 20 years, and Processing Fees.

For a 3,500,000 loan over 20 years, here's what the comparison looks like at different rates:

That's a difference of over 1,543,200 in total interest between 9% and 5.99% — on a single loan. Suddenly, the value of shopping for the lowest rate becomes very concrete.

Step 5 — Model the Rate Repricing Shock

Most Philippine banks offer fixed rates for only 1–5 years, after which your loan re-prices to a higher variable rate. Add a second scenario to your amortization table:

To model this, calculate the outstanding principal balance at the end of Year 3 (read it from your amortization schedule — it will be approximately 3,287,000 for the example above), then run a fresh PMT calculation using that new balance, the new rate, and the remaining term (17 years). This is the repricing shock that catches many Filipino homeowners off guard — and it's precisely the right moment to consider refinancing. For a complete walkthrough of the refinancing process, read our guide on how to refinance your housing loan in the Philippines.

Free Excel Template: What to Include in Your Download

A well-built Philippine housing loan Excel template should contain at minimum four worksheets:

The Refinance Savings tab is particularly important. It should compute: Monthly Savings = Old Payment − New Payment; Total Savings over remaining term; Upfront Costs (processing fee, appraisal, notarial, etc.); and Break-even Month = Upfront Costs ÷ Monthly Savings. If your break-even is 14 months and you plan to stay in the property for another 10 years, refinancing is an obvious win.

The Limitations of DIY Excel Calculators

Excel is powerful, but it has real blind spots when it comes to Philippine home loan calculations:

When to Use Excel vs. When to Use Nook's Calculator

Use your Excel template when you want to deeply understand the math, model hypothetical scenarios, or educate yourself before talking to a bank. Use Nook's free online calculator when you want real, current rates from multiple Philippine banks and want to see what you'd actually qualify for today.

Nook's calculator connects directly to live bank offers. When you input your loan details, you're seeing the actual rates banks are prepared to offer — not a historical estimate. The best refinance rate currently available through Nook is 5.99% per annum. For most homeowners currently paying 7.5% to 10% (especially those who've been repriced after their fixed-rate period ended), that gap represents hundreds of thousands of pesos in savings over the remaining loan term.

Worked Example: Should Maria Refinance?

Maria has a home loan with an outstanding balance of 4,200,000. Her current rate was just repriced to 9.25% with 18 years remaining. Her current monthly payment is approximately 38,650.

If she refinances to 5.99% through Nook for the same 18-year term:

After just 11 months, every peso of monthly savings is net gain. Over 18 years, she keeps over 1.6 million pesos that would otherwise go to the bank as interest. Nook's service is completely free to Maria — Nook earns a referral fee from the bank, not from the borrower.

Key Takeaways