Sabrina Ayon
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!
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.
- 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.
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.
- 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.
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.
- 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.
Read More: Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
Excel Mortgage Formula: Knowledge Hub
- Formula for 30-Year Fixed Mortgage
- Formula for Mortgage Principal and Interest
- Interest Only Mortgage Calculator with Formula
- Loan Amortization Schedule with Variable Interest Rate
- Interest Only Amortization Schedule with Balloon Payment Calculator
- Amortization Schedule with Balloon Payment and Extra Payments
- Formula for Car Loan Amortization
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. You will get free of cost assistance from Team Exceldemy there. Have a good day!
Further Readings
- Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)
- Loan Amortization Schedule with Variable Interest Rate in Excel
- How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)
- Excel Interest Only Amortization Schedule with Balloon Payment Calculator
- Creation of a Mortgage Calculator with Taxes and Insurance in Excel
