Housing Loan Calculator Philippines Excel Template: Download & Compute Any Bank's Rates

Why Filipino Homeowners Need a Housing Loan Calculator in Excel

If you have a home loan in the Philippines, there is a good chance you are paying more interest than you need to. Most Filipino borrowers with loans from BDO, BPI, RCBC, Metrobank, or Security Bank are currently on rates between 7% and 10% per annum — and many have never sat down to calculate exactly how much that costs them over the life of their loan.

An Excel-based housing loan calculator gives you the power to model any scenario: different loan amounts, different interest rates, different bank repricing schedules, and even lump-sum prepayments. This guide walks you through how to build or use one, what formulas to use, and how to interpret the results so you can make smarter decisions about your home loan.

The Core Excel Formula: PMT

Every housing loan amortization calculation in the Philippines — whether you are computing BDO's fixed-3 rate or BPI's fixed-5 rate — starts with Excel's built-in PMT function. Here is the syntax:

=PMT(rate/12, nper, -pv)

• rate — your annual interest rate (e.g. 0.0799 for 7.99%)
• nper — total number of monthly payments (e.g. 240 for a 20-year loan)
• pv — the present value, or your outstanding loan balance (entered as a negative number so the result is positive)

For example, if you borrowed 3,500,000 at 7.99% per annum for 20 years, your formula would be:

=PMT(0.0799/12, 240, -3500000)

This returns a monthly amortization of approximately 29,290 pesos. Over 240 months, your total payments would be roughly 7,029,600 pesos — meaning you pay about 3,529,600 pesos in interest alone on a 3,500,000 loan.

Building Your Excel Template: Step-by-Step

Step 1 — Set Up Your Input Panel

In cells B1 through B5, create labeled input fields:

• B1: Loan Amount (e.g. 3,500,000)
• B2: Annual Interest Rate (e.g. 7.99%)
• B3: Loan Term in Years (e.g. 20)
• B4: Fixed Rate Period in Years (e.g. 3 for BDO's fixed-3 product)
• B5: Estimated Repriced Rate After Fixed Period (e.g. 9.50%)

Step 2 — Compute Monthly Payment and Totals

In cells B7 through B11, add these computed fields:

• B7 Monthly Payment: =PMT(B2/12, B3*12, -B1)
• B8 Total Payments: =B7*B3*12
• B9 Total Interest Paid: =B8-B1
• B10 Monthly Payment After Reprice: =PMT(B5/12, (B3-B4)*12, -PV(B2/12, B4*12, -B7))
• B11 Total Interest (Both Periods Combined): calculate separately and sum

Cell B10 is the most important formula for Philippine borrowers. It calculates your new monthly payment after your fixed-rate period ends and your bank reprices your loan to a (usually higher) floating rate. This is exactly how BDO, BPI, RCBC, and most other Philippine banks structure their home loan products.

Step 3 — Build Your Amortization Schedule

Starting in row 15, create columns for: Month, Opening Balance, Monthly Payment, Interest Portion, Principal Portion, and Closing Balance. Use these formulas for the first data row (Month 1):

• Opening Balance (C15): =B1
• Interest Portion (E15): =C15*(B2/12)
• Principal Portion (F15): =B7-E15
• Closing Balance (G15): =C15-F15

For Month 2 onward, set Opening Balance equal to the previous row's Closing Balance and drag the formulas down for all 240 rows (or however many months your loan runs). At the repricing month (e.g. row 51 for a 3-year fixed period), replace the Monthly Payment column with your B10 value and update the interest rate used in the Interest Portion formula.

Pre-Loaded Bank Rate Assumptions

To make your template useful right away, here are current indicative rates you can pre-load for the major Philippine banks. Note that actual rates change frequently — always confirm with the bank directly before making financial decisions.

BDO Housing Loan

• Fixed 1-year: ~7.25% p.a.
• Fixed 3-year: ~7.50% p.a.
• Fixed 5-year: ~7.75% p.a.
• Typical repricing rate after fixed period: 8.50%–9.50% p.a.

BPI Family Savings Bank

• Fixed 1-year: ~7.00% p.a.
• Fixed 3-year: ~7.25% p.a.
• Fixed 5-year: ~7.50% p.a.
• Typical repricing rate after fixed period: 8.25%–9.25% p.a.

RCBC Housing Loan

• Fixed 1-year: ~7.50% p.a.
• Fixed 3-year: ~7.75% p.a.
• Fixed 5-year: ~8.00% p.a.
• Typical repricing rate after fixed period: 8.75%–9.75% p.a.

To pre-load these into your Excel template, create a separate sheet called BankRates and use a dropdown list in B2 of your main sheet linked to a VLOOKUP or INDEX/MATCH formula that pulls the correct rate when you select a bank name. This turns your calculator into a dynamic comparison tool.

Real Example: Computing the Cost of Staying vs. Refinancing

Let us say you took out a 4,000,000 home loan five years ago at 8.50% per annum, on a 20-year term. You are now at the repricing stage and your bank has offered you a new rate of 9.25%.

Your current outstanding balance after 5 years (60 payments) is approximately 3,710,000 pesos. Using the PMT formula in Excel:

• Monthly payment at 9.25% for remaining 15 years (180 months): approximately 38,020 pesos
• Total payments remaining: approximately 6,843,600 pesos
• Total interest remaining: approximately 3,133,600 pesos

Now model a refinance to 5.99% per annum — the best rate currently available through Nook's refinance calculator — on the same 3,710,000 balance over 15 years:

• Monthly payment at 5.99%: approximately 31,310 pesos
• Total payments remaining: approximately 5,635,800 pesos
• Total interest remaining: approximately 1,925,800 pesos

That is an interest saving of approximately 1,207,800 pesos and a monthly cash flow improvement of roughly 6,710 pesos. Your Excel template should make this comparison instantly visible. If you want to factor in refinancing costs like processing fees and documentary stamps to find your break-even point, see our refinance break-even calculator.

Advanced Features to Add to Your Template

Extra Payment / Prepayment Module

Add an input cell for a monthly extra payment amount. Modify your amortization schedule so that the principal portion each month equals (Monthly Payment - Interest Portion + Extra Payment). This shows you how many months earlier you will pay off the loan and how much interest you will save. For a 3,500,000 loan at 7.99% over 20 years, adding just 5,000 pesos extra per month saves approximately 790,000 pesos in interest and cuts 4 years off the loan term.

Sensitivity Table

Use Excel's Data Table feature (What-If Analysis) to create a two-variable sensitivity table showing monthly payment across a range of interest rates (5.99% to 10.00%) and loan amounts (1,500,000 to 8,000,000). This is particularly useful when comparing offers from multiple banks.

Cumulative Interest Chart

Insert a line chart plotting cumulative interest paid over time for your current rate versus a refinanced rate. The visual gap between the two lines is a powerful motivator and makes it easy to explain the decision to a spouse or family member.

Common Mistakes When Using Housing Loan Calculators

• Using the wrong rate basis. Philippine bank rates are quoted per annum but compounded monthly. Always divide by 12 in your PMT formula — never use the annual rate directly.
• Ignoring the repricing cliff. Many borrowers compute only the fixed-rate period and forget that the rate — and therefore the monthly payment — will jump significantly when the fixed period ends. Always model both periods.
• Forgetting balloon payments. Some older loan structures from PNB or UCPB may have balloon payments at the end of a fixed term. Read your loan documents carefully.
• Not accounting for MRI and fire insurance. Monthly Redemption Insurance (MRI) and fire insurance premiums are added on top of your amortization and vary by bank and loan balance. Budget an additional 0.1% to 0.3% of the outstanding balance per annum for these costs.
• Using gross loan amount instead of outstanding balance. When modelling a refinance, always use your current outstanding balance, not your original loan amount. Your bank can provide this figure, or you can back-calculate it from your amortization schedule.

Where to Get Current Philippine Bank Rates

Philippine bank housing loan rates are not always published transparently online. The most reliable sources are: (1) calling the bank's mortgage hotline directly, (2) visiting a branch, or (3) using a free mortgage broker service like Nook, which has access to live rate sheets from all major Philippine banks and can present you with multiple offers in one comparison. To see how current Philippine home loan interest rates compare across banks right now, Nook publishes a regularly updated rate guide.

Building and maintaining an Excel calculator is a valuable exercise because it forces you to understand the mechanics of your loan. But for the actual business of getting a lower rate, a digital mortgage broker can do the heavy lifting — submitting your application to multiple banks simultaneously, negotiating on your behalf, and managing the paperwork — at zero cost to you.