Assuming: the house price is 1.25 million, the down payment is 250,000, the loan is 1 million, the term is 30 years, the benchmark interest rate is 4.9%, and the equal principal and interest repayment is made.

Recently, the increase in mortgage interest rates in the real estate market has declined, and many banks have lowered the amplitude by 5-10 percentage points. How much mortgage interest can consumers pay less for this adjustment?

Assuming: the house price is 1.25 million, the down payment is 250,000, the loan is 1 million, the term is 30 years, the benchmark interest rate is 4.9%, and the equal principal and interest repayment is. The result is as follows:

In this case, if the mortgage loan of 1 million yuan is reduced from 30% to 20%, it can save 111,400 yuan in interest.

You can also note from the above table that when the base interest for loan execution increases by 30%, it will be 334,100 yuan more than if the interest is not increased.

approximate box calculation results

is calculated based on equal principal and interest. For every 10% decrease in the loan interest rate, the actual execution interest rate drops by 0.49%:

1, 30-year mortgage, the interest can be saved by about 11.3% of the loan amount;

2, 25-year mortgage, the interest can be saved by about 8.9% of the loan amount;

3, 20-year mortgage, the interest can be saved by about 6.8% of the loan amount;

Don’t look at a small 10% increase adjustment, the interest saved by home buyers can be a full year’s annual salary.

Excel calculation formula

 Total interest = SUM(IPMT(annual interest rate/12,{1: Total number of periods}, total number of periods, loan amount)

Because you need to use an array formula, press Ctrl+Enter after entering the finished formula in the cell, The cell will automatically add {} to display:

{=SUM(IPMT(annual interest rate/12, {1: period number}, total period number, loan amount)}

1, IPMT function: calculates the interest value for each period of equal principal and interest repayment.

=IPMT(annual interest rate/12, what period number, total period number, loan amount) 

2, {1: Total number of periods}: is an array representing the total number of periods from 1, 2, 3,........

If you calculate a 30-year loan, as follows:

=Row(A1:A360)

generates an array of {1, 2, 3,......359, 360}. Complete formula As follows:

{SUM(IPMT(annual interest rate/12, Row(A1:A360), total period, loan amount)}

 I am an elite financial Excel class, please follow me and share various knowledge in the financial field with the shortest and most understandable expression. Welcome to comment and leave a message below!