How to Calculate a Mortgage Payment

A mortgage is an interest in a property that is transferred from a borrower (the mortgagor) to a lender (mortgagee) as security for a mortgage loan. If the borrower does not repay the loan then the lender can under certain circumstances take the property.

The word mortgage itself literally means death pledge as the mortgage continues until either the loan is re-payed by the borrower or the property is subject to foreclosure and taken by the lender.

In return for providing the mortgage loan, the lender will charge the borrower interest (usually compounded monthly), and the borrower will repay the loan with regular periodic payments (again usually monthly).

As we have a series of periodic payments from the lender to the borrower and a periodic compounding interest rate, the mortgage payment can be regarded as an annuity.

If paid at the end of each period, the periodic mortgage payment can be calculated using the present value of an annuity formula.

The mortgage payment cash flow diagram would look as follows:

Mortgage payment cash flow diagram
Period 0 1 2 3 . . . n
Cash flow Loan ↑ Pmt ↓ Pmt ↓ Pmt ↓ ..Pmt ↓

The present value of these payments is given by the annuity formula as shown below:

PV = Pmt x (1 - 1 / (1 + i)n) / i
Variables used in the formula
PV = Present Value
Pmt = Periodic payment
i = Discount rate per period
n = Number of periods

This can be rearranged to solve for the payment (Pmt)

Pmt = PV x i / (1 - 1 / (1 + i)n)

If we treat the present value (PV) as the mortgage loan received at the start of period 1 (PV), the discount rate (i) as the mortgage interest rate per period, and n as the number of mortgage payments required under the agreement, then the variable in the mortgage payment calculation formula can be restated as follows:

Pmt = PV x i / (1 - 1 / (1 + i)n)
Variables used in the formula
PV = Mortgage loan amount
Pmt = Periodic mortgage payment
i = Mortgage interest rate per period
n = Number of mortgage payments required

Simple Annual Mortgage Payment Example

Suppose a business wants to buy a property costing 300,000 using a mortgage as security. The lender offers to provide a mortgage loan to the business at an interest rate of 5% over a term of 30 years in return for annual payments at the end of each year. The annual mortgage payment calculation is as follows:

PV = Mortgage loan amount = 300,000
Pmt = Periodic mortgage payment
i = Mortgage interest rate per period = 5% per year
n = Number of mortgage payments required = 30 years
Pmt = PV x i / (1 - 1 / (1 + i)n)
Pmt = 300,000 x 5% / (1 - 1 / (1 + 5%)30)
Pmt = 19,515.43 annual mortgage payment

A payment of 19,515.43 will be required annually at the end of each year for thirty years to pay this mortgage loan off.

Using Excel to Calculate the Mortgage Payment

The same result can be found using the Excel PMT function.

Monthly payment = -PMT(i,n,PV)
Monthly payment = -PMT(5%,30,300000)
Monthly payment = 19,515.43 annual mortgage payment, as before.

Monthly Mortgage Payment Example

Providing the compounding and payment period are the same, the formula can be used for any period length.

Suppose a business wants to buy a property costing 220,000 using a mortgage as security. The lender offers to provide a mortgage loan to the business at an interest rate of 6% over a term of 25 years in return for monthly payments at the end of each month. The monthly mortgage payment calculation is as follows:

PV = Mortgage loan amount = 220,000
Pmt = Periodic mortgage payment
i = Mortgage interest rate per period = 6%/12 = 0.50% per month
n = Number of mortgage payments required = 25 x 12 = 300 months
Pmt = PV x i / (1 - 1 / (1 + i)n)
Pmt = 220,000 x 0.50% / (1 - 1 / (1 + 0.50%)300)
Pmt = 1,417.46 monthly mortgage payment

A payment of 1,417.46 will be required monthly at the end of each month for twenty five years to pay this mortgage loan off.

What Happens if the Mortgage Rate Increases?

If in the example above the mortgage rate rises from 6% to 9% then the mortgage payment becomes

PV = Mortgage loan amount = 220,000
Pmt = Periodic mortgage payment
i = Mortgage interest rate per period = 9%/12 = 0.75% per month
n = Number of mortgage payments required = 25 x 12 = 300 months
Pmt = PV x i / (1 - 1 / (1 + i)n)
Pmt = 220,000 x 0.75% / (1 - 1 / (1 + 0.75%)300)
Pmt = 1,846.23 monthly mortgage payment

As the interest rate increases (from 6% to 9%), the monthly mortgage payment increases (from 1,417.46 to 1,846.23).

What Happens if the Mortgage Term Increases?

Using the same example again, if the mortgage term is increased from 25 years to 40 years, the monthly mortgage payment is calculated as follows:

PV = Mortgage loan amount = 220,000
Pmt = Periodic mortgage payment
i = Mortgage interest rate per period = 6%/12 = 0.50% per month
n = Number of mortgage payments required = 40 x 12 = 480 months
Pmt = PV x i / (1 - 1 / (1 + i)n)
Pmt = 220,000 x 0.50% / (1 - 1 / (1 + 0.50%)480)
Pmt = 1,210.47 monthly mortgage payment

As the mortgage loan term increase (from 25 years to 40 years), the monthly mortgage payment decreases (from 1,417.46 to 1,210.47).

How to Calculate a Mortgage Payment November 6th, 2016Team

You May Also Like


Related pages


gross profit margin formula exceleffective annual rate formulavertical analysis cash flow statementdifference between carriage and freightjournal entry accounts receivabledepreciation of fixed assets journal entryannuity in perpetuity formulaaccount receivables factoringfob buyerformula for double declining balance depreciationadjusting entries for accrued salariesprofit percentage calculation formulaoperating roaunit variable cost formulafreight fobbank reconciliation statement formatcalculating net present value in excelcontinuously compounding interest calculatordebtor meaning in accountingannuities formula calculatorquick ratio formula financehow to calculate direct material price variancevertical analysis for income statementinterview questions and answers for accounts payable positiondeferred billing definitionis interest receivable a current assetpros and cons of reversing entriesquick ratio formula examplemirr financejournal entries for accounts receivablehow do you calculate contribution margin ratiobookkeeping entriesreturn on capital employed examplehorizontal analysis of cash flow statementaccounting quizwip inventory definitionaccount receivable on balance sheetasset turnover rate formularoce calculation examplewhat is the meaning of brs in accountingbeginning stockholders equity formuladeferred tax liability journal entryobjectivity concept of accountingexamples of trade receivablesfree printable ledger sheetsnpv and profitability indexcalculate variable expensesfifo methodfixed asset turnover equationnominal and effective interest rates formulawhat is allowance methodgoods return voucher formatmerchandise inventory beginningexamples of journal entries with narrationaccounts payable ratiosmargin & markupdeferred rent journal entrylump sum formulathe dupont formulawhat is the formula for inventory turnoveradjusting entries inventorynoncontrollable costspresent value table of annuitychart of accounts for non profit organizationturnover calculatorhow to calculate asset turnoveraccounting rate of return examplemeaning of trade creditorsdeferred revenue definitionaccounting entries for finance leaseannuity discount ratecurrency translation adjustment journal entryadjusting prepaid expenses