**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 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.

## Download Practice Workbook

Download this workbook and practice while going through the article.

## 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.

## Step-by-Step Procedures to Create Fixed Rate Mortgage Calculator in Excel

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**.

**Read More: ****Early Mortgage Payoff Calculator in Excel (3 Practical Examples)**

### 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.

**Read More: ****Mortgage payoff calculator with extra principal payment (Free Template)**

### 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 in 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**,**8**is the period for which we wish to calculate the interest payment, and**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.

**Read More: ****Mortgage Calculator with Extra Payments and Lump Sum in Excel**

### 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**.

**Read More: ****Biweekly Mortgage Calculator with Extra Payments in Excel**

### 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 fixed rate loan.

**Read More: ****How to Create Offset Mortgage Calculator in Excel**

## Conclusion

In conclusion, we have discussed some easy steps to create a fixed-rate mortgage calculator in Excel. Not to mention, our **ExcelDemy website** shows various simple problem-solving methods like this. Please, leave any further queries or recommendations in the comment box below.