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:
The present value of these payments is given by the annuity formula as shown below:
PV = Pmt x (1 - 1 / (1 + i)n) / i
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)
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).
About the Author
Chartered accountant Michael Brown is the founder and CEO of Double Entry Bookkeeping. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a degree from Loughborough University.