Updated August 22, 2023
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 principal money over an agreed period of time. The part of principal payment slowly reduces the loan balance to 0. If extra principal payments are made, the remaining balance will reduce more quickly than the loan period. The lender, usually Banks or other financial institutions, takes three elements and uses 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 your loan repayment. This calculation appears cumbersome to understand for a layman.
With the help of Excel, you can create a spreadsheet and calculate your monthly payments.
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 calculates the monthly payments made towards a loan or mortgage repayment.
=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, other optional elements can be used for 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 will 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 below:
In cell C8, we will write 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. pv will be 200000, the amount borrowed. Fv and type are optional so 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. We can do it if we want to know the amount of principal and interest included in this monthly payment. For this purpose, we have two other functions: PPMT and IPMT.
The 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, the Monthly payment in C8, the principal in D8, and the Interest in E8. In cell B9, under the heading Payment no., we will write 1 as for the first payment.
In cell C9, we will calculate monthly payments 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 1. 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. Here is another example to get more clarity on the above-discussed functions.
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 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 follows:
So now we get $1309.53 as PPMT and $166.67 as IPMT, which will add $1476.20 (monthly payment). To show all the payments, we will make the values dynamic in both PPMT and IPMT functions, as shown below.
The 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 payments increases, the portion of the principle increases, and that of interest decreases.
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 on 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 –
