The calculation of the monthly mortgage payment is especially important when investing in income-producing property. Such a calculation is important because the investor needs to evaluate whether the net operating income of the property to be acquired can cover the mortgage payment, which is determined by the loan amount, the mortgage rate and the term of the loan.
The loan payment figure is also required in order to estimate the maximum mortgage loan that an investor can get for financing the acquisition of an income-producing property. In good times, most lenders require a debt coverage ratio (DCR) of at least 1.2. The DCR is calculated usually as the ratio of the net operating income (NOI) of the propety over the mortgage payment. Of course, for this purpose, it is easier to start from the mortgage payment implied by the typical minimum DCR required by lenders in the marketplace, to estimate the implied mortgage payment and from that the implied total morgtgage loan. This is explained in a section below.
Monthly Mortgage Payment Calculation
The monthly mortgage payment calculation can be easily done in Excel by typing in any cell the following:
=pmt(rate;number of periods;loan amount)
The rate in the formula above represents the interest rate for the time unit (month, quarter, year, etc.) for which the payment is calculated. So, for estimating the monthly mortgage payment, we need to enter the monthly interest rate (we show below the formula for calculating the monthly interest rate from the annual rate). The number of periods represents the number of periods that represent the duration or term of the loan, in time units (months,years, quarters, etc.) for which we want to estimate the periodic payment. Hence, in this case that we want to calculate the monthly payment, we enter the duration of the loan in months. So for example, if the loan has a term of 10 years we enter in the above formula the number 120. It is important that the rate and the number of periods are consistent, that is, if a monthly interest rate is entered the loan duration needs to be entered in months as well.
As indicated above, in using the Excel function to estimate the monthly motgage payment we need to enter the monthly interest rate. However, the mortgage rate is usually quoted in annual terms The formula for calculating the monthly interest rate from the annual rate is:
Monthly rate = Annual Rate / 12
So, for example, if the annual rate is 6% then the corresponding monthly rate is:
Monthly rate = 0.06 / 12 = 0.005 =0.5%
The formulas for calculating the monthly mortgage payment are the following:
Monthly payment= Monthly Mortgage Constant (MMC) X Loan Amount
Mortgage Constant = Interest Rate / [1- [1/(1+Interest Rate)n ]]
In the above formula, for calculating the monthly mortgage constant we need to enter the monthly interest rate and the number of periods n, which represents the duration of the loan, in months.
Estimating the Maximum Mortgage Loan
As we indicated above, the maximum loan given by lenders for the acquisition of income-producing property is tied to the net operating income (NOI)of the property through the minimum debt coverage ratio (DCR) lenders are wiling to accept. Thus, when considering the acquisition of a buy-to-let property we need to know the net operating income of the property under consideration, in order to estimate the maximum loan that can be obtained for financing the acquisition of the property, given the prevailing DCRs used by lenders. With the DCR and the net operating income at hand we can calculated the annual mortgage payment that will correspond to the maximum loan that the lender can provide. This annual mortgage payment can be calculated as:
Maximum loan payment = Net Operating Income (NOI) / DCR
So if the annual NOI of the property to be acquired is £100,000 and the lenders minimum acceptable DCR is 1.25 then the maximum annual mortgage payment (MAMP) will be:
MAMP = 100,000 / 1.25 = 80,000
Comments