There are two types: mortgage loan and average capital. The specific formula is as follows: equal amount ×( 1 interest rate) repayment months ÷ [( 1 interest rate) repayment months-1] average capital/repayment months) (principal-accumulated repaid principal )× repayment months is quadratic.
Second, how to calculate the mortgage loan?
Is it better to transfer this position to JIAOZHOU supply and demand information real estate? Many people know this, right?
Third, how to calculate the mortgage?
Teach you to make a mortgage calculation template.
There are two ways to repay the mortgage: equal principal and interest and average capital.
1. Equal principal and interest:
The total monthly repayment 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, keeping the total amount unchanged.
2. Average capital:
Distribute the principal equally to each month.
At the same time, pay off the interest between the last trading day and the repayment date.
The total monthly repayment decreases month by month.
The following figure can be very intuitive to understand the difference between the two repayment methods:
After understanding the way of mortgage repayment, we learn to make a mortgage calculation template with an actual case.
Case:
Open the second-hand housing website and click on the small house with the age of 13. Is this the price?
Take this as an example to calculate the equal principal and interest and the average monthly repayment amount of capital.
Operating steps:
1. First design an empty template, as shown below:
The blue background area in line 2 to line 5 is a manual input area, which should be filled in according to the actual situation.
10 line starts to calculate the area, the green area is calculated according to the equal principal and interest, and the yellow area is calculated according to the average capital.
2. Fill in the manual input area:
House price:180,000.
Down payment ratio: 70%, the down payment threshold for the second suite.
Loan interest rate: 5.88%, the benchmark interest rate.
Loan term: 20 years
3. Set the data from column A to column D in the figure. The formula/method is as follows:
House price: =B2
Down payment amount: =B2B3
Loan amount: =A 10-B 10
Number of repayment months: 1 to 240. For the filling method, please refer to the "filling" function ignored by 99% people in Excel.
4. Matching principal and interest formula setting:
Excel is very simple to calculate the equivalent principal and interest repayment amount, because there are three special functions:
Monthly repayment amount = =PMT (interest rate per period, total number of periods, principal,,)
Principal = =PPMT (interest rate per period, which period, total number of periods, principal)
Interest = =IPMT (interest rate per period, which period, total number of periods, principal)
Specifically applied to this example, the formula is as follows:
1) Total monthly repayment:
=-Payment ($4/12, $5 12, 10 10000, …)
Formula explanation:
$ b $4/ 12: 5.88% is the annualized interest rate, which needs to be divided by 12 to get the monthly interest rate.
$B$5 12: Similarly, 20 years multiplied by 12 can get the total number of months to be repaid, that is, the total number of installments.
$C$ 10 10000: loan amount, with the unit of "ten thousand yuan", multiplied by 10000, and converted into "yuan".
,,: The last two optional parameters can be left blank, so the two ","are also left blank.
The PMT function is used to calculate the deduction, so the default result is negative. In this case, we don't need a negative representation, so we put a "-"before the formula to convert it into a positive number.
2) Monthly principal amount:
=-ppmt (US$ 4 to 12 Canadian dollars, 10 Canadian dollars, 5 12 Canadian dollars,10/0000 Canadian dollars)
The explanation is the same as above, not much explanation.
3) Monthly interest amount:
=-IPMT (US$ 4 to 12 Canadian dollars, 10 Canadian dollars, 5 12 Canadian dollars,10/0000 Canadian dollars)
4) drop down the formula F 10:H 10 to calculate the "actual total payment":
= a 10f 1020 12/ 10000
Formula explanation:
A 10 is the down payment amount.
F102012/10000: Because the monthly repayment amount is equal, the total repayment amount is the monthly repayment period, and then divided by10000, the unit is converted into "ten thousand yuan".
5. Average capital formula setting:
Average capital has no special function, so it needs to be calculated by formula:
1) Monthly principal amount:
=($ C $ 10 10000)/($ B $ 5 12)
Formula explanation:
($C$ 10 10000): loan amount, the unit is converted into "yuan".
/($B$5 12): divided by the total repayment period.
2) Monthly interest amount:
=($ C $ 10 10000-($ d 10- 1)k 10)$ B $ 4/ 12
Formula explanation:
($ c $1010000-($ d1kloc-0/) k10): the loan amount is subtracted from the paid-off total principal to obtain the remaining principal.
$B$4/ 12: Multiply the loan interest rate and convert it into a month on an annualized basis to get the interest payable in the current month.
3) Total monthly repayment:
=SUM(K 10:L 10)
4) drop down the formula of J 10: L 10 to calculate the "actual total payment":
= a 10 sum(j 10:J249)/ 10000
Formula explanation:
A 10 is the down payment amount.
Sum (j10: j249)/10000: sum the monthly repayment amount and divide it by10000 to convert the unit into "ten thousand yuan".
Fourth, how to calculate the mortgage formula of mortgage
The calculation method of mortgage interest rate is equal principal and interest method: add up the total amount of mortgage principal and interest and distribute it evenly to each month of repayment period; Average repayment method: distribute the principal monthly and pay off the interest from the previous trading day to the repayment date.
1. Average capital method: monthly repayment amount = monthly interest rate of principal/residual principal; Total interest = monthly interest rate of principal (loan months /20.5).
2. Matching principal and interest method: monthly repayment amount = monthly interest rate of principal [( 1 interest rate) n/[( 1 interest rate) n/[( 1), where n represents the number of loan months and n represents the power of n, such as 240, that is, 240 power (20 years and 240 months of loans). Monthly interest rate = annual interest rate/12; Total interest = monthly repayment amount-loan months-principal.
Extended data:
Mortgage interest algorithm:
1, you can refer to the conversion according to the actual situation. At present, there are two repayment methods: matching principal and interest and average capital. Interest = actual days of principal, daily interest rate or total interest = monthly repayment amount, loan months-principal.
2. If the loan is 6,543,800 yuan, the loan term is 20 years, and the interest rate is 5.47%, the interest is 646,865.60 yuan. The average principal repayment interest is 549,278.73 yuan.