Model Overview

The model runs three repayment scenarios simultaneously on a single loan, giving you a clear picture of how each structure affects your monthly cash flow, total interest cost, and outstanding balance over time.

Three Repayment Scenarios

Scenario 1 — End-of-Year Principal: Interest is paid monthly while principal is repaid as a lump sum at the end of each year. Keeps monthly payments low during the year but requires large year-end outflows.

Scenario 2 — Fixed Principal, Declining Interest: Equal principal installments paid each month, with interest calculated on the outstanding balance. Monthly payments start high and decrease steadily over time.

Scenario 3 — Fixed Installment (Annuity): A constant monthly payment throughout the loan term, with the principal/interest split shifting each period. The most common structure used by banks worldwide.

Key Features

Fixed & Floating Rate: Model a hybrid loan with a fixed rate for an initial period, automatically switching to a floating rate for the remainder of the term.
Extra Payments: Input an optional extra monthly payment and the start date — the model recalculates all three amortization schedules to show the interest savings and early payoff impact.
Grace Period: Configure an interest-only grace period (in months) at the start of the loan, during which no principal repayment is required.
Affordability Calculator: Reverse-calculate the maximum loan you can afford based on your net monthly income, existing debt obligations, and a target Debt-to-Income (DTI) ratio. Includes a Rent vs. Buy comparison that factors in home appreciation, property tax, and maintenance costs over a user-defined analysis period.
Dashboard & Charts: An executive summary tab displays key metrics (total interest, total amount paid, average monthly payment, interest as % of principal) across all three scenarios with visual charts for easy comparison.

Model Structure

Assumptions: Single input sheet covering loan amount, down payment, fixed/floating rates, fixed period, loan term, grace period, and extra payments.
3 Payment Scenarios: Full month-by-month amortization schedules for all three scenarios, calculated in parallel.
Affordability: Income-based borrowing capacity calculator with rent vs. buy analysis.
Dashboard: Summary comparison table and charts across all three scenarios.
Instructions: Color-coded user guide explaining input cells, formula cells, and navigation.

Who This Is For

First-time homebuyers evaluating mortgage options, real estate investors comparing financing structures, and financial advisors building client-facing loan analyses.

Disclaimer: This model is for illustrative and educational purposes only. Consult a licensed financial advisor before making mortgage decisions.

You must log in to submit a review.