Mortgage Calculations with Excel Formula (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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!


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.

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


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.

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


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.

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

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.

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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo