How to Create Fixed Rate Mortgage Calculator in Excel

Microsoft Excel computes costs associated with our mortgages, such as interest and monthly payments, which is one of its powerful features. If you are a little familiar with Excel functions, building a fixed rate mortgage calculator in Excel is simple. In this article, we will learn 5 easy steps to create a fixed rate mortgage calculator in Excel.

Before diving into the methods in the next section, let’s take a look at the screenshot that represents the overview of a fixed-rate mortgage calculator in Excel.

Introduction to Fixed Rate Mortgage

A house loan with a fixed interest rate for the duration of the loan is referred to as a Fixed Rate Mortgage. The Fixed Rate indicates that the interest rate on the mortgage remains the same throughout. For customers who want to know how much they will pay each month, Fixed Rate Mortgages are attractive options. Some key points:

The majority of fixed-rate loans amortize over time.
Once fixed, changes in the market don’t affect the interest rate.
Alternatively, mortgages with adjustable rates fluctuate in interest during the term of the loan.

How to Create Fixed Rate Mortgage Calculator in Excel (With Easy Steps)

Excel does not come with a built-in mortgage calculator. In order to build a mortgage calculator and compute the amortization loan table, we will use the PMT, IPMT, and PPMT functions in our spreadsheet. Moreover, we will utilize the Additional formula for calculation. A loan that is repaid in installments over the course of the loan’s term is simply referred to as an amortizing loan. So, without any further delay, let’s get straight to the steps.

Step 1: Introduce Dataset with Proper Parameters

The first step aims to create a dataset with correct information. Add the following headers in the below-mentioned columns.

Initially, introduce the header Loan Details and sub-headers Loan Amount, Yearly Loan Term, Yearly Payments, and Annual Interest Rate in column C.
Further, populate them with correct values.

Similarly, add the headers Payment No, Payment, Interest, Principal, Balance in B10, C10, D10, E10, and F10 respectively.
Afterward, put a series of numbers up to B18 under Payment No.

Step 2: Utilize PMT Function to Calculate Total Payments

The objective of the second step is to utilize the PMT function to find the payments for each period. The PMT function uses a constant interest rate to determine how much a loan will cost. Follow the below instructions to understand better.

Firstly, in cell C11, type the following formula:

=PMT($E$8/$E$7,$E$6*$E$7,$E$5)

Remember to use absolute cell references for these steps.
Later, press the Enter or Tab keys to get the output.
The formula’s output will be red, formatted as Currency, and rounded to two decimal places. When the result is automatically surrounded in parenthesis, it indicates that the loan amount is negative because the loan payment will be subtracted from the bank account.
Put a minus (–) sign before the PMT function if you wish to see the positive loan amount.

Meanwhile, use the AutoFill tool to drag the formula cell down.
Thus, we obtain the fixed payments over the 8 periods.

Step 3: Apply IPMT Function to Determine Interests

In the third step, we will thoroughly implement the IPMT function to calculate the interests over the 8 periods. Based on periodic, constant payments and a constant interest rate, the IPMT function returns the interest payment for a specified term for an investment. Let’s learn more about the following procedures.

To begin with, write the formula in D11,

=IPMT($E$8/$E$7,B11,$E$6*$E$7,$E$5)

Here, C8 is the annual Interest rate, and 8 is the period for which we wish to calculate the interest payment. E7 is the total number of payments during the whole period and E5 is the present value of the loan.
Pressing Enter will return the output.

Again, use the AutoFill icon to fill the rest of the column.

Step 4: Use PPMT Function to Count Principal

In our fourth step, we will calculate the principles using the PPMT function. For a given period, the PPMT function returns the principal payments. To demonstrate, follow the below instructions.

Type the PPMT formula in cell E11,

=PPMT($E$8/$E$7,B11,$E$6*$E$7,$E$5)

$E$8/$E$7; declares rate/nper as an annual capital payment is calculated. It can also be a weekly (rate/52), or monthly (rate/12) payment.
Next, press Enter or Tab.

After that, drag the formula cell down using the AutoFill tool.

Step 5: Insert Excel Formulas to Find Remaining Balance

In our last step, we will use 2 formulas to find the remaining balance after each period. Learn more about the formulas in the steps below.

Firstly, in cell F11, type:

=E5+E11

This adds the Loan Amount with the first obtained Principal number.
Now, press Enter to get the balance after the first payment.

Secondly, in F12:

=F11+E12

This adds up to the first balance and the second principal payment.
Press Enter.

Lastly, use the AutoFill tool until the balance becomes 0.

Thus, we obtain our desired mortgage calculator for a fixed rate loan.

Download Practice Workbook

Download this workbook and practice while going through the article.

Fixed Rate Mortage Calculator.xlsx

Conclusion

In conclusion, we have discussed some easy steps to create a fixed-rate mortgage calculator in Excel. Please, leave any further queries or recommendations in the comment box below.