Tanjim Reza
Break-even analysis is one of the most effective tools to know when an institute or a person has covered all costs or loans. When it comes to mortgages, the break-even analysis calculates the number of months when the person will be able to cover the loans. In this article, I will show you all steps to perform a mortgage break-even analysis in Excel.
Download Practice Workbook
You can download our practice workbook from here for free!
What Is Mortgage Break-Even Analysis?
Mortgage break-even analysis is mainly an analysis of original amortization conditions and refinances amortization conditions. In this analysis, both conditions are analyzed well and the break-even period is calculated to decide if the refinance would be wiser or not for a borrower. To understand this analysis, we need to know the following terms properly.
Mortgage: A mortgage is actually a type of loan that is taken to buy a real estate property like a house or land. This deal is mainly between a lender and a borrower. The lender gives a large amount of money as a loan to the borrower to get some extra interest throughout the repayment schedule. And, if the borrower fails to repay the money timely, the lender gets the right to take over the real estate property.
Refinance Mortgage: Refinance mortgage is a new type of mortgage deal that is done during a running mortgage deal. The borrower does this sometimes to save extra interest money. In this dealing, the current principal and interest rates are changed. But, the borrower has to pay an extra fee to refinance the mortgage.
Break-Even Point: Break-even point is the point where the refinance mortgage cost is fulfilled by the savings from interest money.
A Real-Life Example of Mortgage Break-Even Analysis
Now, to make the topics clear, say, a person borrowed $400,000 at a 5% interest rate to buy a property. He has 30 years to pay this loan with monthly payments. So, he has a mortgage of $400,000. Now, in this way, he has to pay $2147 monthly.
Now, say, he is willing to shift to a new deal with a 3.5% interest rate. But he has to pay an extra $4000 to refinance the mortgage. Now, at this interest rate, he will have to pay $1796 monthly. This is the refinance mortgage.
Now, if he shifts to the new deal, he will save $351 every month. And, to provide the extra $4000 fee, he will need approximately 12 months with this saving. So, this 12 months is the break-even period for the borrower in this case.
Steps to Perform Mortgage Break-Even Analysis in Excel
Say, we have data for the Original Amortization in cells C4:C7 and Refinance Amortization in cells F4:F8 of the Dataset worksheet. Now, we need to perform the break-even analysis for this scenario. Follow the step-by-step guidelines to accomplish this.
📌 Step 1: Calculate Payment per Installment
First and foremost, you will need to calculate payment per installment to perform a mortgage break-even analysis. You can do this by using the PMT function.
- To do this, first, click on cell C9 of the Payment per Installment sheet and insert the following formula to calculate the payment per installment for the original amortization conditions.
=-PMT(C5/C6,C8,C4,0)- Subsequently, press the Enter key.
- Afterward, click on cell F9 of the same sheet and insert the formula below to calculate the refinanced mortgage payment per installment.
=-PMT(F5/F6,F8,F4,0)- Following, hit the Enter key.
Thus, both of the conditions’ payment per installment calculation are accomplished.
Note:
In the formula, we have used a negative sign. Because this is loan repayment which we consider a cash outflow. So, the result would be negative by default. To get rid of the negative sign, we used the negative sign before the formula.
Read More: How to Calculate Break Even Sales with Formula in Excel
📌 Step 2: Calculate Break-Even Period
After getting the installments, we need to calculate the break-even period now.
- In order to do this, click on cell C4 of the Break-Even Period sheet and enter the following formula in the formula bar.
='Payment per Installment'!C9- Subsequently, hit the Enter key.
- Similarly, click on cell C5 and apply the formula below.
='Payment per Installment'!F9
- Afterward, click on cell C6 and insert the following formula to calculate the difference between the two installments.
=C4-C5- Following, hit the Enter key.
- At this time, click on cell C7 and apply the formula below to extract the refinance cost value.
=Dataset!F8
- Last but not least, click on cell C8 and insert the following formula to calculate the break-even period for this scenario.
=C7/C6- Subsequently, hit the Enter key.
Thus, you will be able to perform your mortgage break-even analysis successfully and you will get your desired output.
Note:
As the installments are per month system here in this scenario, the break-even period will be in months.
Read More:Â How to Calculate Break Even Point in Excel (3 Effective Methods)
Conclusion
So, in this article, I have shown you all the steps to perform a mortgage break-even analysis in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.
And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!
