Below is a mortgage calculator which can be used to calculate the monthly payment, and see the payment schedule based on the data provided.
Once the results are generated based on your input, you can copy the data by selecting it (Select All by using the CTRL+A shortcut two times), and right click-copy/or using a copy shortcut (CTRL+C).
Please keep reading if you wish to learn how to create a mortgage calculator using Excel! We will show you all the tricks and formulas used to create this magical calculator 🙂
Disclaimer – This calculator should be used for educational purposes only. We will not take any responsibility for any error or mistake, so please make sure to consult with professional consultants before taking any decision regarding a mortgage or a loan!
(P.S. you can also calculate simple loans with this calculator – Just type 0 in the down payment. )
Now let’s learn how to build your own mortgage calculator in Excel!
First, let’s see the mortgage calculator filled with some sample values, and with the formulas that we used to crunch all the numbers!
Please note that for each of the formulas you will see below, we provide an explanation. For example, explanation number 1 will explain to you how we calculated the Loan Amount. Please keep reading! 🙂
1. Loan Amount calculation
To calculate the loan amount, we take the total home price and subtract the down payment from it. So if our Home Price is 1,100,000 and we already paid 100,000, we have 1,000,000 left to pay.
You can see that we added some IF and ISBLANK functions to the basic calculation:
=IF(D6-D7<0,”Can’t be negative!”,IF(ISBLANK(D6),””,D6-D7))
If you are not sure how the IF function works, click here for a great IF tutorial with exercises 🙂
We use the IF function to first check that the loan amount is not negative (This would happen, for example, if the down payment was greater than the house price). If the result is negative, something’s wrong, therefore we return “Can’t be negative!” – The user has to re-check his/her data.
Next, if we passed our first test, we will also check that cell D6 which refers to the house price is not blank, meaning that the user didn’t forget to type the price 🙂
If all tests pass, we will show the result to the user.
2. Financing % calculation
The Financing % shows us the percentage of financing used in this deal, or in other words, how much (in %) of the total price of the house is taken as a loan:
We basically divide the loan amount (D8) by the total value of the house (D6).
The IFERROR function is used so that we don’t show erroneous results (If, for example, the house price is 0, we will get a Division by 0 error… ). If indeed we got an error, we will return an empty cell, which is represented in Excel by 2 double quotes “”
3. Number of payments calculation
This one is quite straightforward. We take the number of years (D11) and multiply it by 12, to get the number of monthly payments.
4. Monthly Payment calculation
This is the important part – how much will we pay each month? Let’s see:
The formula used to calculate the monthly payment is called PMT. The PMT formula is used for returning a fixed monthly payment.
Payment for such a loan consists of two parts: principal and interest.
The principal amount is the amount you return each month from the money you borrowed. Each principal payment will decrease your loan balance.
The interest amount is what you have to pay as interest for the balance that hasn’t been paid yet – There ain’t no such thing as a free lunch and Loans aren’t free (unfortunately…), so there’s a price to pay for the money you borrowed. The interest payment you pay each month doesn’t decrease the remaining balance – It’s extra money you need to pay each month! 🙁
Each month, the interest amount and the principal amount will change. But when combined together, they will always be equal to the fixed monthly payment. Magic, isn’t it? 🙂
The following are the PMT formula arguments:
rate – The periodic interest rate. In our example, we type D10/12 – we take the annual interest rate and divide it by 12, to get the monthly interest rate.
nper – The number of periods for payment. We type D11*12 – We multiply the years by 12 to get the number of months we need to pay our mortgage.
pv – The Present Value (PV) of the loan, or basically, the amount of money we borrowed. Note that we added a minus ( – ) sign before the PV amount, in order to show the payment in positive numbers…
We didn’t include two optional parameters here – FV (Future Value) & type (When is the payment made in each period). We assume that FV is 0, meaning that by the end of the loan, the balance should be 0. As for the type, it could be either 0 (payment at the end of the period) or 1 (payment at the beginning of the period). Throughout this example, we assume that we pay at the end of the period (July 31, Aug 31, etc…). As 0 is the default value, we can safely omit this.
5. Total Interest Paid
To calculate the total interest amounts paid throughout the period, we can use the CUMIPMT formula, which returns the cumulative interest paid on a loan between two periods:
These are the arguments of the CUMIPMT formula:
Rate – The interest rate. As before, for our example, we divide the yearly interest rate by 12 to get the monthly interest paid.
Nper – Number of periods of the loan. We take the number of years and multiply it by 12 to get the number of months.
PV – The present value of the loan – what’s the amount of loan we are taking?
Start_Period – The first period we wish to take for our calculation. In our example, we’ll type 1, for the first period.
End_Period – The last period we wish to take for our calculation. In our example, it will be the last period of the loan – Which we can find by multiplying the total years by 12.
Type – When is the payment made each period? Is it at the beginning of the period? Then type 1. If it’s at the end of the period, type 0. We type 0 as we previously saw that our interest is paid at the end of the month.
Note that we added a minus sign before the CUMIPMT formula to have the result returned as a positive number.
6. Total Annual Payment
The total annual payment is the monthly payment multiplied by 12.
7. Total Payments
The total payments are calculated by multiplying the Total Annual Payment by the number of years.
Now let’s get to the fun part – Creating the amortization schedule!
Throughout the next formulas, we will be using dynamic array functions quite heavily, as we can type each function only once and the schedule will be magically filled with the relevant data in the relevant cells 🙂
8. Calculating the Payment Number
In order to generate the column with the payment period numbers, we can use the SEQUENCE function, which is used for generating sequences based on a certain logic.
SEQUENCE is a dynamic array function, which means that it can create dynamic arrays, or in other words – we type the function once, and we don’t need to copy the formula to the cells below as we usually do in normal formulas. Based on the arguments provided to the function, SEQUENCE will populate the requested cells and will expand or shrink accordingly.
In our case, we tell the SEQUENCE function that we wish to generate a sequence of rows, starting from 1 and ending in the last payment period (years multiplied by 12 months). Note that we didn’t type the starting number of 1 – It is the default for this function unless stated otherwise. To learn more about the uses of the SEQUENCE function and to practice it, click here.
But wait, why did we type =IF(NOT(ISNUMBER(($D$14))),”” ?
We could do just fine without it, but if we use this, we will make sure that the range will not be populated unless there is a number in cell D14 (The monthly payment). This will help us in a case where not all the data was entered, or some data caused calculation issues (such as generating a negative loan number, etc.)
The ISNUMBER function is used to check if a certain value is considered a number or not by excel, while the NOT function gives us the opposite of what we entered into it (FALSE becomes TRUE, and TRUE becomes FALSE). So if ISNUMBER evaluates to TRUE, then NOT will ask to see if the value IS NOT A NUMBER – FALSE. If it’s indeed not a number, we understand that we can’t fill the rest of the column, so we return blank (double quotes “”) instead.
9. Calculating the year number
To get the year number using the period number, we divide the period number by 12 and round up the result using the roundup function. So period 6 is year 1 -> 6/12 = 0.5 -> round up to 1 -> 1.
Notice that the ROUNDUP function uses two arguments – The first argument is the value which we wish to round up, and the second argument is the number of digits to which we want to round the number. As we are looking for integer numbers, we type 0 – We don’t want any digits after the decimal point.
But wait – What’s the deal with B30#? What is that weird hashtag sign?
The hashtag sign (#) is a new feature that can be used when referring to ranges that were created by dynamic array functions. The # tells Excel that it needs to look for the last cell in the spilled range that was created. So you don’t need to manually type the last cell and adjust it each time – It will dynamically adjust thanks to the power of the dynamic array functions! So if the last cell in that range is B100, Then B30# basically means B30:B100.
Please note that the # sign can’t be used when referring to “regular” ranges, as Excel won’t be able to identify where these ranges end.
10. Calculating the Starting Balance for each period
To calculate the starting balance for each period, we can use the PV (Present Value) function, which helps us calculate the Present Value of the loan.
The PV Arguments are as follows:
Rate – That’s our interest rate. We take the yearly interest rate and divide it by 12 to get the monthly interest rate.
Nper – That’s the number of periods of our loan. We are going to play with this number, as each period’s PV is determined by the number of payments left to pay. If, for example, we want to calculate the PV for the beginning of the 10th period, we will need to transform the number of payments to N+1-10 (Where N is the number of payments). We are adding +1 because we are asking to calculate the starting balance for the period (which is, basically, the ending balance of the previous period). For calculating the ending balance, +1 is not required.
We are using the hashtag again in B30# – This refers to the payment number column. As this is a spilled range, we can use hashtags to easily refer to the entire column!
pmt – That’s the fixed payment that we have already found out by using the PMT function before.
Like the PMT function, the PV function also has optional arguments – FV, The Future Value of the loan, which when omitted is 0 (That’s our ending balance). The last argument is type – which tells Excel when the payment is made – At the beginning (1) or the end (0) of the period. The default is 0 – End of the period.
Again, to return a positive PV value, we add a minus sign before the PV function.
(P.S. If all this stuff is confusing to you, don’t feel too bad – We are accountants and that’s still confusing to us as well! Thank god we have Excel and we don’t need to calculate this stuff with old-fashioned financial calculators 🙂 )
11. Calculating the Interest Amount paid each period
To calculate the interest amount we need to pay each period, we use the IPMT function. We provide it with the rate, the payment number (per), the total payment periods (nper), and the present value (PV). As each period has a different allocation of interest and principal paid (which will always result in the exact same PMT for all periods), we use the per argument to state the current period of each payment.
12. Calculating the Principal Amount paid for each period
Quite the same as for the interest amount we just calculated, we can use the PPMT function, with the exact same arguments, to find the principal amount paid for each period.
13. Formula used to calculate the total payment
Basically, we just sum the interest paid and the principal paid for each period. We could alternatively just refer to the result we got from the PMT function – We did this in another way just to double check the correctness of our periodical interest & principal calculations (When combined, they should always be equal to the sum of the fixed payment!)
14. Formula used to calculate the ending balance
The ending balance of each period is the starting balance, minus the principal amount that we paid (Remember that we keep the interest payment outside – These are not decreasing our balance, these are extra payments!)
Alternately, we could use the PV function or the FV function here. If you are looking for further practice, feel free to try to rewrite this column using any of these functions 🙂
Data Validations used
Apart from using various sorts of functions, we also applied Data Validation rules on the input cells of our model (the yellow ones) – to make sure that users do not accidentally type incorrect stuff there. You can add Data Validation rules by clicking Data -> Data Validation:
Here are the rules that we applied:
Home Price – Must be a number greater than 0
Down Payment – Must be a number greater than 0
Interest Rate – Must be greater or equal to 0 (Assuming that you are lucky to get a 0% interest loan 🙂 )
Loan Term – Any integer number between 1 to 100 years (We don’t recommend taking loans for more than 100 years… 🙂 )
That’s it, now you are ready to build your own mortgage/loan calculator. We hope that you enjoyed this tutorial, and we encourage you to share it with your colleagues, friends, and family so they can enjoy it as well!