In **Microsoft Excel**, calculating a monthly mortgage payment has become a typical task for most modern folks. The fixed periodic payment and the outstanding loan balance are the main components of the mortgage formula. In this article, we will see some examples of excel mortgage formula.

**Table of Contents**hide

**Download Practice Workbook**

You can download the workbook and practice with them.

**5 Examples of Excel Mortgage Formula**

Let’s take a look at some examples to understand better how mortgages are calculated in excel.

**1. The Formula for Monthly Mortgage Payment in Excel**

Consider that, we want to start a business. For that, we need to take a loan. Now we would like to calculate the monthly mortgage payment. For example, we received a $150,000 term loan in cell **C7** to start the business. The annualized rate of interest in cell **C8 **is 6%, the loan duration in cell **C9** is 2 years and the loan must be repaid monthly, according to the terms of the sanction. Now, calculate the monthly mortgage payment using the information provided.

The** PMT function **in excel can be used to compute expected mortgage payments using a formula. The **PMT function**, one of the financial functions, calculates the payment for a loan based on constant payments and a constant interest rate, as illustrated in the example.

**STEPS:**

- Firstly, select the cell where we want to calculate the monthly payment. So, we select cell
**C13**.

- Next, we need to write down the formula. As we are using the
**PMT function**, the formula is:

`=PMT(C8/C10,C11,C7,0)`

- Then, press
**Enter**.

- Now, we can see in cell
**C13**, the monthly mortgage payment as a result.

**2. Excel Mortgage Formula to Fixed Periodic Payment**

Likewise, the previous methods dataset, loan amount $150,000 is in cell **C7**, rate of interest is in cell **C8 **which is 6%, the 2-year loan duration in cell **C9**, the number of payments per year in cell **C10,** and the total number of payments month is in cell **C11**. Now we want to calculate the fixed periodic payment in cell **C12.**

**STEPS:**

- In the beginning, select the cell where the result will be shown. So, in the first place, select cell
**C12**.

- The generic formula for fixed periodic payment is:

`=loan amount((rate of interest/number of payment per year)*(1+rate of interest/number of payment per year)^(number of payment per year*life loan))/((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1)`

- After that, we will write the formula below:

`=C7*((C8/C10)*(1+C8/C10)^(C10*C9))/((1+C8/C10)^(C10*C9)-1)`

- Then, press
**Enter**.

- Finally, the fixed periodic payment is shown as a result.

**3. Find Out Excel Outstanding Loan Balance**

To find out the outstanding loan balance we are using a similar dataset as used before with some modifications. Like, the loan amount is decreased here and the duration of the loan is increased. Now, we need to calculate the outstanding loan amount with just one intermediate period. The procedure is given below.

**STEPS:**

- As before, first, select the cell where the result will appear. We are selecting cell
**C13**.

- The generic formula for fixed periodic payment is:

`=loan amount((rate of interest/number of payment per year)*(1+rate of interest/number of payment per year)^(number of payment per year*life loan))-((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1))`

- Now, just write down the formula below.

`=(C7*((1+C8/C10)^(C10*C9)-(1+C8/C10)^(C10*C11))/((1+C8/C10)^(C10*C9)-1))`

- Then, press
**Enter**.

- At last, we will be able to see the resulted loan amount in cell
**C13**.

**4. Mortgage Formula to Calculate the Monthly Payments for a Credit Card Debt**

To calculate the monthly payments for a credit card debt, we will be using the **PMT function** again. For calculating this, we need the due balance and the annual interest rate which is sequentially in cells **C7 **and** C8**. Let’s have a look at the approaches to calculating the monthly credit card debt.

**STEPS:**

- Likewise, in the other examples, select cell
**C10**.

- After selecting the cell, write down the following formula:

`=PMT(C8/12,2*12,C7)`

- Then, press
**Enter**. - In the end, we will see the result.

**5. Excel Mortgage Formula for Principal Amount Repayment in 24th Month**

The principal to be repaid in the 24th month can be computed, by deducting the outstanding balance after two years from the outstanding balance after 23 months. We will use the dataset as similar as example 3 with the 2nd intermediate period of time. Now, let’s take a look at the strategies below.

**STEPS:**

- In the first place, we have to calculate the loan balance in period 1. For this, select cell
**C14**.

- The generic formula for fixed periodic payment is:

`=loan amount((1+rate of interest/number of payment per year)^(number of payment per year*life loan))-((1+rate of interest/number of payment per year)^(number of payment per year*intermediate period 2))/(((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1)`

- And, then write down the formula:

`=(C7*((1+C8/C10)^(C10*C9)-(1+C8/C10)^(C10*C11))/((1+C8/C10)^(C10*C9)-1))`

- Then, press
**Enter**. - The generic formula for fixed periodic payment is:

`=loan amount((1+rate of interest/number of payment per year)^(number of payment per year*life loan))-((1+rate of interest/number of payment per year)^(intermediate period 2))/((1+rate of interest/number of payment per year)^(number of payment per year*life loan)-1)`

- After that, for loan balance in period 2, the formula is:

`=(C7*((1+C8/C10)^(C10*C9)-(1+C8/C10)^(C12))/((1+C8/C10)^(C10*C9)-1))`

- Then, press
**Enter**.

- Now, for repayment subtract the loan balance in period 1 from the loan balance in period 2. The formula will be:

`=C15-C14`

- At this time, press
**Enter**. - And, finally, we will see the result of repayment.

**Conclusion**

Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the **ExcelDemy.com** blog!