Mortgage Calculator Excel Template - Free Download | Page 2
4.8,
2773
votes
Please vote for this template if it helps you.
Page 1 of 11
Excel Skills UK | Mortgage Calculator Template
Instructions
www.excel-skills.co.uk
Mortgage calculators are sometimes also referred to as home loan calculators or bond calculators. The aim of this free Excel template is
to enable users to calculate monthly mortgage repayments, determine the affordability of a home loan, calculate the interest savings that
result from increased mortgage instalments and measure the sensitivity of mortgage repayments to changes in interest rates. After using
this template, you will also gain a better understanding of home loan amortization and specifically the timing of capital repayments on a
mortgage.
The following sheets are included in this template:
MortgageCalculator Sheet
All the calculations in this template are based on the values that are entered in the input cells from cell B4 to B9 on the
MortgageCalculator sheet (except for the net disposable income calculation). Input guidance is displayed below the selected input cell.
We have also added data validation to all input cells to ensure that only valid user input is accepted.
Calculation Results:
The monthly mortgage repayment amount is calculated from the mortgage principle amount (cell B4), mortgage period (cell B6) and the
annual interest rate (cell B5).
The total interest paid over the entire mortgage period is the total amount of interest that will have to be paid over the entire mortgage
The total mortgage repayment over the mortgage period is the sum of all the monthly mortgage repayment amounts. This amount
consists of all interest charges and capital repayments.
The monthly net disposable Income is calculated on the NetDisposable sheet - refer to this sheet for more information on the calculation.
The maximum mortgage qualification amount is calculated based on the net disposable income, annual interest rate and mortgage
period. It represents an estimate of the maximum mortgage amount that applicants can qualify for based on their combined monthly net
disposable income. There are a number of other factors that financial institutions will consider when determining the maximum mortgage
qualification amount - our calculation should therefore only be seen as an estimate which cannot be guaranteed.
The minimum required net disposable income is the minimum net disposable income that is required in order to qualify for the mortgage
principle amount that is entered in cell B4.
The interest rate safety percentage indicates the percentage by which interest rates have to increase before the monthly net disposable
income would not be sufficient to cover the required monthly mortgage repayments.
The increased instalment calculations in row 21 to 26 are based on the additional monthly mortgage repayment that is entered in cell B7.
The assumption is made that the entire additional mortgage repayments are deducted from the outstanding capital balance, thereby
resulting in a shorter mortgage repayment period. Note that the present value of the interest saving is calculated by discounting the
monthly interest savings by the average annual inflation rate over the entire mortgage period. It therefore represents the value of future
interest savings in today's monetary terms.
The interest rate sensitivity calculation measures the effect that changes in the mortgage interest rate have on monthly mortgage
repayments. The interest rate sensitivity percentage that is entered in cell B8 is used for this purpose.
The capital repayment chart is a visual display of the timing of capital repayments over the entire mortgage period.
The increased instalment interest saving chart is a visual display of the interest savings that result from effecting increased monthly
mortgage repayments.
NetDisposable Sheet
This sheet includes a detailed calculation of the monthly net disposable income. All values should be entered as positive values. Refer to
the guidance that has been included from row 38 downwards for more information on the input that is required in each input cell.
AnnualAmort Sheet
This sheet includes an annual amortization table that is based on the mortgage input values that are entered in cell B4 to B6 on the
MortgageCalculator sheet. We recommend that you pay special attention to the outstanding capital percentage in column G as it
indicates how the capital will be repaid over the entire mortgage period. You'll notice that during the first few years of the mortgage
repayment period, the monthly mortgage repayments consist almost entirely of interest.
MonthAmort Sheet
This sheet includes a monthly amortization table that is based on the mortgage input values that are entered in cell B4 to B6 on the
MortgageCalculator sheet.
Help
If you experience any difficulty while using this template, please e-mail us at support@excel-skills.co.uk for assistance.
source: dataease.com
