Having a mortgage calculator is very useful to calculate mortgage interest or total mortgage payments. One very easy way is to use Microsoft Excel and enter a formula. The Excel method of calculating repayments is very useful, as mortgage calculations are instant, and there is no need to use the internet.
Advantages of Using Excel as a Mortgage Calculator
Most people have access to Microsoft Excel, and the formula to work out repayments is very simple to use. The information obtained from the calculations can be used to compare the costs of several mortgages with each other, check affordability, and reduce the time spent with a broker. The calculations are also valid for personal loans, second mortgages, re-mortgages, and commercial mortgages.
What Is the Excel Function to Calculate Mortgage Repayments?
Excel has a function to calculate capital plus interest repayments, and a different function to calculate interest only repayments. These are called "PMT" (capital plus interest) and "ISPMT" (interest only repayment). As well as calculating the mortgage repayments, each can be used to work out the total amount paid on the mortgage.
Mortgage Repayment Calculation - Capital Plus Interest
Figure 1 shows how the function PMT is used in Excel when using the fx icon, but it can just be entered directly. There are five numbers that need to be entered:
1. The interest rate to be applied.
2. The number of mortgage or loan payments.
3. The loan or mortgage amount.
4. The desired balance after all payments are made. This is almost always zero.
5. The payment date i.e. Is payment made at the start or end or a pay period. This is almost always zero.
Figure 2 shows the titles in column "A", and the numbers required in column "B". To calculate the mortgage repayments, fill in the numbers in cells B1 to B5, and then type (or copy and paste) the following formula into any other cell:
=PMT(B1/12,B2,B3,B4,B5)
If the function icon is used (see Figure 2), the top box should contain:
B1/12, for monthly payments.
In cell B1, the interest rate for the loan or mortgage should be the annual interest rate. A mortgage rate of 5% is entered as 0.05, or 5/100.
In cell B2, enter the total number of payments. e.g. For a 25-year loan, paid monthly, enter 300 (=12*25).
Mortgage Repayment Calculation- Interest Only
Using the same titles and data that are used for calculating Capital and Interest repayments, the formula changes only slightly. The Function PMT is replaced with ISPMT, and there is no end-of term balance as no capital has to be repaid, only the interest on the mortgage.
=ISPMT(B1/12,1,B2,B3)
Limitations of Using Excel to Calculate Mortgage Repayments
For both types of mortgage calculation (or loan calculation), the functions in Excel are a good guide. They are, however, exactly that- a guide. Exact figures should always be sought from a mortgage broker, bank, building society or other professional service. The figures may differ because they do not include mortgage protection insurance, loan set-up fees, home insurance, or other costs. Also, the exact mortgage calculation methods used by every mortgage lender cannot be included in such a widely used program like Excel.
Using Excel to Do Mortgage Repayment Calculations
Microsoft Excel is a spreadsheet package that is available on most home computers. Versions of it are also on many types of PDA or other hand-held device. It is possible to calculate monthly repayments instantly, if the interest rates are known. It is also possible to calculate interest payments separately. It is best used as a guide to prepare for meeting with a professional mortgage advisor, or before getting mortgage quotations from a website. There are other articles that explain how much capital is repaid from a monthly mortgage payment, the rate that the capital part of the mortgage reduces, how much may be borrowed, and what the total amount is paid over the length of the mortgage. The term of a mortgage may be reduced by making overpayments, especially early in the term.
Many mortgage providers offer free online mortgage calculators. More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.
Martin Bell - Martin holds a B.Sc. degree in chemical engineering, and an M.Sc. degree in electronics and computing. He has spent more than 25 years ...
Join the Conversation