A. What is the formula for calculating the monthly mortgage payment for a home loan mortgage loan?

Home Loan Mortgage Loan Monthly Payment Calculation Formula:Enter the loan amount, the mortgage monthly payment for the loan period

Two, mortgage home loan interest calculation

A positive answer

Wanting to calculate the interest interest on the mortgage home loan interest, you have to be clear about the type of mortgage, the loan amount, loan interest rate, loan term, loan repayment method these kinds of factors.

Two, specific analysis

1, loan type

There are three kinds of provident fund loans, purely commercial loans, combined loans, the biggest difference is the difference in the interest rate of the loan.

(1) The interest rate for the first set of CPF loans is 2.75% for 15 years, and 3.25% for 5 years and above; the second set of suites will be floated 1.1 times on the basis of the first set of home loans.

(2) Purely commercial loan rates are priced using LPR basis points, mainly with reference to the LPR rate for the same period last month.

For example, if the 5-year LPR is 4.45% and the bank gives a markup of 80 BP, the mortgage rate will be 5.25%.

(3) For a combination loan, a CPF loan is taken out first, and then a commercial loan is taken out for the portion where the quota is not enough, with different portions of the loan type corresponding to the corresponding interest rates.

2, loan period

The maximum loan period for all mortgage home loans is 30 years, but it is also based on the applicant's age at the time of the mortgage, the age of retirement to calculate.

According to the female 55 years of age retirement, male 60 years of age retirement to calculate, as long as the applicant's age at the time of the loan is not more than 35 years old still have the opportunity to loan 30 years, the specific period of time how much to combine with the repayment ability, credit and other factors to assess the overall situation.

3, loan amount

Commercial loan amount by the bank comprehensive applicant's repayment ability, personal credit and other factors to assess.

The amount of provident fund loan will also refer to the balance of the provident fund account, provident fund contribution base and other factors, and the amount given by the housing provident fund centers in different regions is also different.

4, repayment

There are two kinds of repayment methods

Equal interest, equal principal, the former loan principal in the early stage of the repayment of less, more interest; the latter monthly repayment of the principal is equal to the monthly repayment of the principal with the reduction of the principal decreasing.

The big data of online lending can reflect the real situation of online lending. If you have taken out too many loans, it is advisable to get a good understanding of your credit data. Look up: Bay Pointe Quick Check, which generates a big data report. Although there is a difference between the two, bank loans will also pay attention to big data credit in order to avoid the emergence of individuals who are unable to pay back bad debts, and bad credit can affect many aspects of the right.

Third, how to buy a mortgage interest?

On the calculation of interest on a buy-to-let mortgage loan, it mainly depends on what kind of repayment method is chosen.

And there are two main ways to repay a mortgage: equal interest and equal principal.

If you choose the equal principal and interest repayment method, the formula is: monthly repayment (principal interest) = [loan principal x monthly interest rate x (January interest rate)^ number of months of repayment] ÷ [(January interest rate)^ number of months of repayment - 1].

It can be concluded that: Total interest = Monthly repayment amount x number of months of repayment - Loan principal = [Loan principal x Monthly interest rate x (January interest rate)^Number of months of repayment] ÷ [(January interest rate)^Number of months of repayment - 1] x Number of months of repayment - Loan principal.

And if you choose the Equal Principal Repayment Method, then the formula is: Monthly Repayment (Principal Interest) = (Loan Principal ÷ Number of Months of Repayment) (Loan Principal - Accumulated Amount of Principal Returned) x Monthly Interest Rate.

Monthly repayment of interest = (loan principal - the accumulated amount of returned principal) × monthly interest rate, monthly repayment of principal = loan principal ÷ number of months of repayment.

Three, how do you calculate the formula for a home loan mortgage?

To teach you to make a mortgage calculation template.

There are two ways to pay off a mortgage: equal interest and equal principal.

1. Equal principal and interest:

The total monthly repayment amount is equal

Monthly loan interest is calculated according to the remaining loan principal at the beginning of the month and settled monthly

Therefore, the monthly repayment interest decreases month by month, and the principal increases month by month to maintain the total amount unchanged

2. Equal principal:

The principal is spread evenly over the course of each month

At the same time pay off the interest between the last transaction date and the current repayment date

The total monthly repayment amount decreases each month

The following figure can be very intuitive to understand the difference between the two repayment methods:

After understanding the mortgage repayment methods, we learn to make mortgage calculation templates by using a real case.

Case:

Open the second-hand house website, randomly click on a 13-year-old small house, the price is like this?

Let's use this as an example to calculate the monthly repayment amounts for Equal Interest and Equal Principal.

Steps:

1. First design the empty template, as follows:

2 to 5 lines in the blue background area for the manual input area, fill in according to the actual situation

The 10th line is the beginning of the calculation area, the green field according to the calculation of the equivalent principal and interest, and the yellow field according to the calculation of the equivalent amount of the principal

2. Fill in the manual input area:

House selling price: 18 million

Down payment ratio: 70%, the down payment threshold for the second suite

Loan interest rate: 5.88%, the benchmark interest rate

Loan life: 20 years

3. Setting up the data in columns A to D in the chart, the formula/method is as follows:

House selling price: = B2

Down payment amount: = B2B3

Loan amount: = A10-B10

Repayment months: 1 to 240 fill, fill the way you can see Excel 99% of the people ignored the "fill" function

4. Equal principal and interest formula settings:

Equal Principal and Interest Calculation Excel Repayment is very simple, because there are three dedicated function:

Monthly repayment = PMT (interest rate, total number of installments, principal,,)

Principal = PPMT (interest rate, the first few installments, the total number of installments, the principal)

Interest = IPMT (interest rate, the first few installments, the total number of installments, the principal)

Concretely applied to this example, the formula is as follows:

1) Total monthly repayment:

=-PMT($B$4/12,$B$512,$C$1010000,,)

Formula interpretation:

$B$4/12: 5.88% is the annualized rate of interest, which needs to be divided by 12, to get the monthly interest rate

$B$512: Similarly, the 20-year Multiply by 12 to get the total number of months you need to repay, i.e., the total number of installments

$C$1010000: Loan amount in "million yuan", multiplied by 10,000 to convert it into "yuan"

,,,,,,,: The last two optional parameters can not be filled, so the two "," left blank

PMT function is used to calculate the amount of deduction, so the default result is a negative number, in this case, we do not need a negative number to indicate that the formula in front of the addition of "-", converted to a positive number

PMT function is used to calculate the amount of deduction, so the default result is a negative number. Positive

2)Monthly principal amount:

=-PPMT($B$4/12,$D10,$B$512,$C$1010000)

Interpretation is the same as above, not much explanation

3)Monthly interest amount:

=-IPMT($B$4/12,$D10,$B$512,$C$1010000)

This function is used to calculate debit amount, so the default result is negative. C$1010000)

4)Pull down the F10:H10 formula and calculate the "Actual Total Payment Amount":

=A10F102012/10000

Explanation of the formula:

A10 is the down payment amount

F102012/10000. F102012/10000: Because the total monthly repayment amount is equal, so the total repayment amount for the monthly base repayment period, and then finally divided by 10,000 will be converted to the unit of "10,000 yuan"

5. Equivalent principal formula settings:

Equivalent principal is not a specialized function, so you need to use the formula to calculate:

1)Monthly principal amount:

=($C$1010000)/($B$512)

Formula interpretation:

($C$1010000): the loan amount, the unit converted to "$"

/($B$512): divided by the amount of "$"

/($B$512): divided by the amount of "$"

/($B$512): divided by the amount of "$"$"$". 512): divided by the total number of repayment periods

2) the amount of monthly interest:

=($C$1010000-($D10-1)K10)$B$4/12

Formula interpretation:

($C$1010000-($D10-1)K10): the amount of the loan, minus the total amount of the principal paid off to get the remaining principal repayable

$B$4/12: multiplied by the interest rate of the loan, annualized to the month, that is, to get the interest payable in the month

3) the total amount of monthly repayments:

=SUM(K10:L10)

4) the J10:L10 formula to pull down, and to calculate the "total amount of actual payments "

=A10SUM(J10:J249)/10000

Formula interpretation:

A10 for the down payment amount

SUM(J10:J249)/10000: monthly repayment amount is added to the sum of the total, divided by 10,000 will be converted to units of "10,000 yuan"

Four, mortgage loan formula

Monthly (quarterly) debt service amount = loan principal / debt service number of times (loan principal - the cumulative number of repaid principal) × monthly (quarterly) interest rate