Calculate Mortgage Loan Monthly breakdown with WPS2010 form

Now, a set of their own housing is one of the goals, and to young people say, buy a house almost all have to loan, the monthly supply is the most concerned about the matter, the monthly how to calculate? For how much? Which method of credit is the most affordable? Some of these problems may not be clear, the following is the use of WPS2010 form to calculate the monthly mortgage loan breakdown.

Calculation formula for monthly mortgage loan

1. Calculation formula of the repayment of mortgage loan matching principal and interest

Monthly Debt service amount =[Principal x monthly interest rate X (1+ month rate) repayment months/[1+ monthly interest rate] monthly number of repayment-1]

of which: monthly interest = remaining principal x loan monthly interest rate; monthly principal = Monthly amount per month-monthly interest.

Calculation principle: The Bank from the monthly contribution, the first to receive the remaining principal interest, after receiving the principal; interest in the monthly contribution of the proportion of the remaining principal in the reduction and decrease, the principal in the monthly contribution of the proportion of the increase, but the total amount of monthly contributions remain unchanged.

2. Calculation formula of equal principal repayment of mortgage loan

Monthly Debt service amount = (principal/repayment months) + (principal-cumulative principal) x-month interest rate

of which: the monthly principal = Total principal/repayment month, monthly interest = (Principal-cumulative already principal) X-month interest rate.

Calculation principle: The monthly return of this amount is always unchanged, the interest with the remaining principal reduction.

Second, use WPS2010 form to calculate mortgage loan monthly breakdown

is a commercial loan of 200000 yuan, the loan period of 20, the benchmark bank lending rate of 5.94% for the annual interest rate and 70 percent for example.

The first step, open the wps form, enter the loan basic information in line 1th, then fill in the A2 cell "20" (without quotes, the same below), fill in the B2 cell with "=a2*12", fill "=5.94%*0.7" in the C2 cell, and fill "D2" in the =c2/12 cell, E2 the cell to fill "200000", as shown in Figure 1.

　　

In the second step, enter the repayment method on line 3rd, enter the breakdown name in line 4th, and enter the data before the first repayment in line 5th, as shown in Figure 2.

　　

The third step, according to the matching principal and interest repayment calculation formula

In B6 cell, enter "= (e5*d$2* (1+d$2) ^ (B$2-A5))/((1+d$2) ^ (B$2-A5)-1)";

Enter "=e5*d$2" in the C6 cell;

Enter "=b6-c6" in the D6 cell;

Enter "=e5-d6" in the E6 cell.

The repayment details of the first month (period) are expanded, as shown in Figure 3.

　　

Then select (A5:A6) The range of cells, fill to a245 cell, and then select the (b6:e6) range of cells, fill the line of repayment period of 240 to get the itemized expansion of all repayments.

The fourth step, in the same way according to the matching principal repayment formula to calculate the matching principal repayment of the details of the expansion (see annex for example).

The fifth step, calculate the sum of two ways of repayment, from the results can be seen that the same amount of repayment of equal principal repayment benefits, but the repayment pressure in the previous years to slightly larger, such as Figure 4.