Mortgage Affordability Calculator And Formula
Maximum Mortgages By Interest Rate Loan Period and Repayment Amount
Nov 4, 2009
Just one formula shows how monthly payments, amount borrowed, interest rate, and mortgage term are related. The formula is normally used to calculate the monthly payment from the other three variables. This article shows how it may be re-arranged to show the amount that can be borrowed for a given monthly payment and interest rate.
Setting a Mortgage Budget
It is important to know what size of mortgage is affordable. One way to do this is to work out a "mortgage budget", by looking at income and outgoings. Income is usually in the form of a salary. Outgoings are taxes, food, car payments, etc. The difference between income and outgoings is available for other spending, such as a mortgage. There needs to be money set aside for contingencies, such as unforeseen costs or circumstances.
Mortgage and Loan Formula – Definitions of Terms Used
P – the Principal, or amount borrowed
r – the Interest Rate for the specified time period
a – the amount repaid each month (or other repayment period)
n – the number of payments to be made
It is assumed that the first three values are known, and that it is desired to calculate the Principal.
Formula To Calculate Maximum Mortgage
The formula to work out the amount that can be borrowed, when payment, number of payments, and interest rate are known is:
P = ( a / r ) x ( ( 1 + r )^n - 1) / ( 1 + r )^n
In Microsoft Excel, this can be calculated as follows:
Cell A1: Interest Rate per month. (See how to get this from the yearly rate at Mortgage Payments - How Much Comes Off The Loan?).
Cell A2: Number of payments. (e.g. 300 for a 25-year mortgage)
Cell A3: Monthly Payment that can be afforded (e.g. $1,000)
The formula in Excel is:
=(A2/A1)*((1+A1)^A3-1)/(1+A1)^A3
Changes In Interest Rates or Circumstances
Working out the maximum mortgage that is affordable is only part of the decision of how much to borrow. There are other factors such as what banks may actually lend, salary multiple, or credit history, which can reduce the amount that may be borrowed. The borrower may also wish to check the effect on payments if interest rates rise, or if his / her salary is reduced. The formula may be used to check how these factors affect the amount that could be borrowed.
Mortgage Borrowing Calculation Summary
The formula shown here enables the calculation of the maximum that may be borrowed for a given interest rate, number of payments, and monthly payment. The formula is derived by rearranging the formula used to work out monthly payments. It is an easy way to estimate the maximum amount that may be borrowed, but it does not give a recommendation: it is always essential to seek professional advice before making a final decision on any mortgage matter.
Many lenders provide online calculators to do the calculations shown here.
 |
