Excel Mortgage Calculator (Table of Contents)
Overview of Excel Mortgage Calculator
We all take mortgages/loans for our needs. It could be for buying a house, a car loan, a personal loan, etc. We take long term loan which extends up to 5, 10 or even 20 years.
We have to repay these loans in monthly installments. This includes interest and a part of principle money over an agreed period of time. The part of principle payment slowly reduces the loan balance, finally to 0. In case extra principal payments are made, the remaining balance will reduce more quickly than the loan time period. The lender, usually Banks or other financial institutions, takes three elements and use them in a formula to calculate the monthly payment.
These three key elements are:
- Principle (Amount of loan)
- Interest rate
- Time period (Number of years or months for which you have borrowed the loan)
These elements are used in formulas to calculate the monthly payments for the repayment of your loan. This calculation appears cumbersome to understand for a layman.
With the help of Excel, you can create a spreadsheet and calculate the monthly payments for yourself.
How to Calculate Monthly Payments for a Loan in Excel?
We can calculate the monthly payments for the loan/mortgage using built-in functions like PMT and other functions like IPMT and PPMT.
PMT function is used to calculate the monthly payments made towards the repayment of a loan or mortgage.
=PMT(Rate, nper, pv)
The PMT function requires 3 elements to calculate the monthly payments:
- RATE: Rate of interest of the loan. If the rate is 4% per annum monthly, it will be 4/12, which is .33% percent per month.
- NPER: the number of periods for loan repayment. For example – for 5 years, we have 60 monthly periods.
- PV: Present value of the loan. It is the amount borrowed.
However, some other optional elements can be used for some specific calculations, if needed.
- FV: The future value of the investment after all the periodic payments are made. It is usually 0.
- TYPE: “0” or “1” is used to ascertain whether the payment is to be made at the beginning or end of the month.
How to Use the Formula to get the Amount Monthly Payment?
Now, we will see how to use the PMT function to calculate the monthly payment.
Let’s take an example to understand how this function works.
Example #1
Supp+ose we have taken a home loan for $2,00000 for 10 years at a 6% interest rate. Let’s make a table in Excel as below.
Now to calculate the monthly payment, we will input all the data points in the function as below:
In cell C8, we will begin writing the formula by pressing = and then writing PMT. We will then input the data points as per the syntax. It is to note that since our loan is based on monthly payments, we have to divide the interest rate by 12 and multiply the number of years by 12 (to give us the total number of monthly payments).
Hence, the rate of 6% will become .5% (6%/12) monthly, and the time period will become 120 monthly periods. pv will be 200000, the amount borrowed. Fv and type are optional in this case, so that we will leave them. Once we have input the data in the formula, we will press Enter. We will see the below result.
For the loan amounting to $200000, at a 6% interest rate for 10 years, the monthly payment will be $2,220.41
This is how we calculate monthly payments using the PMT function in Excel. This monthly payment includes a part of the principal amount and interest as well. If we want to know the amount of principal and the amount of interest included in this monthly payment, we can do it. For this purpose, we have two other functions, which are PPMT and IPMT.
PPMT function is used to calculate the principal portion of the payment, while the IPMT function is used to calculate the interest portion of the payment. Now we will see how to use these functions to know the composition of the monthly payment.
Taking the above example, we will now find the PPMT and IPMT. We will write the Payment number in cell B8, Monthly payment in C8, the principle in D8 and Interest in E8. In cell B9, under the heading Payment no., we will write 1 as for first payment.
Now, in cell C9, we will calculate monthly payment with the PMT function.
To calculate the principal amount in the monthly payment, we will use the PPMT function. We will write the function in cell D9, as shown below.
In the PPMT function, we will input the data as per syntax. The rate will be 6%/12 to get a monthly rate of interest. Then in “per”, we will write the payment number, which is 1 in this case. Then time (nper) 10 years *12 to convert it into no. of months and finally the principal amount (pv).
Press Enter to get the PPMT.
Lastly, we will calculate the interesting part in monthly payment by IPMT function in cell E9.
We will write =IPMT in cell E9 and input the data in the same way as we did in the PPMT function. Press Enter, and we will get the IPMT.
This shows that in the monthly payment of $2,220.41, $1,220.41 is the principal part, and $1,000 is the interest. To get more clarity of all the above-discussed functions, here is another example.
Example #2
Mark has taken a car loan for $50,000 at 4% for 3 years.
We will create a table in Excel as below:
So we have two tables; the smaller one will show the monthly payment PMT (Cell I3). The bigger table shows the total of 36 payments for the loan amount that represents both the principal and interest portions.
First of all, we will calculate PMT in cell I3, as shown below:
Now, we will calculate PPMT in cell G10.
Press Enter to get PPMT.
We will now calculate IPMT in cell H10 as:
So now we get $1309.53 as PPMT and $166.67 as IPMT, which will add to become $1476.20 (monthly payment). To show all the payments, we will make the values dynamic in both PPMT and IPMT function, as shown below.
IPMT function is shown below.
Now we will drag both PPMT (G10) and IPMT(H10) downwards till the last payment(36th).
As the number of payment increases, the portion of the principle is increasing, and that of interest is decreasing.
This is how we can create a mortgage calculator in Excel.
Things to Remember about Excel Mortgage Calculator
- The Excel shows the monthly payment for the mortgage as a negative figure. This is because this is the money being spent. However, if you want, you can make it positive also by adding – sign before the loan amount.
- One of the common errors that we often make when using the PMT function is that we don’t close the parenthesis, and hence we get the error message.
- Be careful in adjusting the interest rate as per monthly basis (dividing by 12) and loan time period from years to no. of months ( multiplying by 12).
Recommended Articles
This is a guide to Excel Mortgage Calculator. Here we discuss how to calculate monthly payments for a loan with examples and an excel template. You can also go through our other suggested articles to learn more –
