In this article, we are going to learn about how to do mortgage calculations with Excel formula. We will explain the whole process with 5 suitable examples so that you can relate this to real-life examples.

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.

We will include links to a set of important topics related to mortgage calculation in this article. This will give you all-around working knowledge about mortgage calculations with Excel formulas.

For example, we will include links to a formula to calculate a 30-year fixed mortgage. This will introduce you to the process of calculating fixed mortgages. You can also learn about topics like mortgage principal and interest. A loan amortization schedule is also on the list. You may learn about loan amortization for different cases like variable interest rates, Balloon payments, and extra payments.

Finally, you will learn how to use formulas for a special case: car loan amortization.

So to get an overall working knowledge about mortgage calculations with Excel formula, letâ€™s dive into this article!

**Table of Contents**Expand

**Mortgage Calculations with Excel Formula: 5 Practical Examples**

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. Now, letâ€™s have a look at the steps below.

**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, in the previous methods dataset, the loan amount of $150,000 is in cell **C7**, the 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 resulting 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. To calculate 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 the 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.

**Download Practice Workbook**

You can download the workbook and practice with them.

**Conclusion**

Weâ€™ve discussed mortgage calculations with Excel formula in this article. Also, we have included other most practical and popular topics by providing hyperlinks. Topics like fixed mortgage calculation, and loan amortization schedule are on the list. We hope, with a little bit of practice, you can master the topics and solve your problems yourself. If you face any difficulties doing so, feel free to let us know in the comment section.

Have a good day!

## Excel Mortgage Formula: Knowledge Hub

- How to Use Formula for 30 Year Fixed Mortgage in Excel?
- How to Use Formula for Mortgage Principal and Interest in Excel?
- How to Use Formula for Car Loan Amortization in Excel?

**<< Go Back to ****Excel Formulas for Finance**** | ****Excel for Finance**** | ****Learn Excel**