Mortgage Calculations with Excel Formula (5 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. In this article, we will see some examples of excel mortgage formula.


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

The Formula for Monthly Mortgage Payment in Excel

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

The Formula for Monthly Mortgage Payment in Excel

Read More: How to Use Formula for Mortgage Principal and Interest in Excel


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.

Excel Mortgage Formula to Fixed Periodic Payment

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

Excel Mortgage Formula to Fixed Periodic Payment

Read More: How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods) 


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.

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

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

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


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.

Excel Mortgage Formula for Principal Amount Repayment in 24th Month

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

Excel Mortgage Formula for Principal Amount Repayment in 24th Month

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

Principal Amount Repayment in 24th Month

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

Read More: Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel


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!


Further Readings

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo