NZ Property Loan Amortisation Suite
Two complete loan schedules in one workbook — residential and commercial — with NZ-specific investment metrics, interest-only modelling, balloon refinance, and paydown charts built in.
- Separate residential and commercial amortisation schedules — up to 800 rows each
- Residential: interest-only period, LVR tracking, ICR, rental yield, DTI ratio
- Commercial: balloon/refinance term, DSCR, cap rate, NOI yield, RBNZ LVR limits
- Extra repayment modelling — see months saved and interest saved instantly
- Flexible payment frequency — monthly, bi-weekly, semi-monthly, or weekly
- Dedicated paydown charts for both loan types
- NZ RBNZ lending limits built in (≤ 80% residential, ≤ 65% commercial)
- No macros — standard Excel formulas only, fully editable
Built for How NZ Investors
Actually Borrow.
Residential and commercial loans work differently in New Zealand — different LVR limits, different lender metrics, different structures. This suite handles both correctly.
Residential Loan
Standard P&I or interest-only schedule for residential investment properties. Models the IO phase common in NZ investment lending before switching to full repayments.
- Interest-only period (years) — switches to P&I automatically
- LVR tracking vs RBNZ ≤ 80% residential investment limit
- Gross rental yield from weekly rent input
- Interest Coverage Ratio (ICR) — NZ lenders require ≥ 1.25x
- Debt-to-Income ratio (DTI) against gross annual income
- Extra repayment impact — months saved, interest saved
Commercial Loan
Commercial and industrial property loans with balloon refinance modelling. Calculates your residual balance at the balloon date for refinancing planning.
- Balloon term (3–5 years typical) — residual balance calculated automatically
- LVR tracking vs RBNZ ≤ 65% commercial investment limit
- DSCR (Debt Service Coverage Ratio) — lenders require ≥ 1.30x
- Capitalisation rate (NOI ÷ property value)
- NOI yield on loan balance
- Fully amortising option — set balloon term to 0
🏠 Home
Guided landing page with step-by-step setup instructions, NZ-specific feature highlights, colour coding reference, and disclaimer. Read this before entering data.
🏘️ Residential Loan
Full amortisation schedule for your residential investment property. Enter loan amount, rate, term, payment frequency, first payment date, IO period, and optional extra repayments. Up to 800 monthly payment rows.
🏢 Commercial Loan
Commercial and industrial loan schedule with balloon refinance modelling. Enter your amortisation period, balloon term, and commercial property metrics. Residual balance calculated automatically at balloon date.
📈 Residential Chart
Visual paydown charts for your residential loan — annual loan balance over time and cumulative interest vs principal split year by year. Includes LVR % trend across the full loan term.
📊 Commercial Chart
Visual paydown charts for your commercial loan — annual balance, cumulative interest and principal, and LVR trend to balloon date and beyond if fully amortising.
⚙️ Lists
Reference data for dropdown validation — payment frequency options (Monthly, Bi-weekly, Semi-monthly, Weekly) with their corresponding periods per year. Do not edit this tab.
LVR — Loan-to-Value Ratio
Mortgage balance ÷ property value. RBNZ limits residential investment lending to ≤ 80% LVR. Tracked automatically against your property value input.
ICR — Interest Coverage Ratio
Annual rental income ÷ annual interest cost. NZ lenders typically require ICR ≥ 1.25x for investment property approval. Calculated from your weekly rent and loan inputs.
DTI — Debt-to-Income Ratio
Total loan ÷ gross annual income. RBNZ monitors DTI at a system level. Enter your gross annual income to see your DTI position against your loan balance.
Gross Rental Yield
Annual gross rent ÷ property value. Calculated from your weekly rent input (weekly × 52). Gives a quick read on return before expenses and financing costs.
DSCR — Debt Service Coverage
Net Operating Income ÷ annual debt service (P&I). Commercial lenders typically require DSCR ≥ 1.30x. Enter your annual NOI to see whether your loan serviceability meets lender thresholds.
Cap Rate & NOI Yield
Capitalisation rate (NOI ÷ property value) shows investment return independent of financing. NOI yield on loan gives a return-on-debt view. Both calculated from your commercial inputs.
Excel Workbook (.xlsx)
NZ_Loan_Amortisation_Schedule_v1.0.xlsx — 6-tab workbook, standard formulas only, no macros. Blue cells = inputs, black = formulas.
Residential Schedule
Up to 800 rows covering P&I or IO+P&I repayments with opening balance, interest, principal, and closing balance per period.
Commercial Schedule
Full commercial amortisation with balloon refinance support. Residual balance at balloon date calculated automatically — critical for commercial refinancing planning.
Paydown Charts (×2)
Dedicated visual charts for both residential and commercial loans — annual balance trend, cumulative interest vs principal split, and LVR trajectory.
Instant Download
Single .xlsx file delivered immediately after purchase. No waiting, no shipping — download and start modelling your loans today.
Lifetime Access
One purchase. All future updates to this model are included at no extra cost — no subscription required.
| Requirement | Detail |
|---|---|
| Microsoft Excel (recommended) | Excel 2016 or later, Windows or Mac. Microsoft 365 provides the best experience with full conditional formatting support. |
| Google Sheets | Upload the .xlsx file to Google Drive → right-click → Open with Google Sheets. All formulas and most formatting transfer correctly. |
| Macros / Add-ins | None required. All calculations use standard Excel formulas only — no macros, no external data connections. |
| Payment Frequencies | Monthly, bi-weekly (26 p.a.), semi-monthly (24 p.a.), or weekly (52 p.a.) — selectable via dropdown in each schedule. |
| Schedule Length | Up to 800 rows per schedule — covers loan terms up to ~30 years monthly or ~15 years weekly. |
| Technical Skill Level | Basic Excel familiarity. Blue cells = your inputs. All calculations are automatic. |
| File Format | Single .xlsx file delivered via instant download link after purchase. |
