Mortgage Break-Even Analysis in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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 the steps to perform a mortgage break-even analysis in Excel.


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


How to Perform Mortgage Break-Even Analysis in Excel: Easy Steps

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.

Sample Dataset to Perform Mortgage Break Even Analysis in Excel


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

Calculate Payment per Installment for Original Amortization

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

Calculate Payment per Installment for Refinanced Amortization

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.


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

Payment per Installment

  • Similarly, click on cell C5 and apply the formula below.
='Payment per Installment'!F9

Refinanced Payment per Installment

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

Difference between Payment per Installments to Perform Mortgage Break-Even Analysis

  • At this time, click on cell C7 and apply the formula below to extract the refinance cost value.
=Dataset!F8

Refinance Cost to Perform Mortgage Break-Even Analysis in Excel

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

Calculating Break-Even Period of Mortgage Break-Even Analysis in Excel

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.


Download Practice Workbook

You can download our practice workbook from here for free!


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo