Skillfully use WPS form to make account list

Nowadays, it is the most common way for young people to buy houses, and for each bank, the interest on loans and the monthly payments are calculated. You'll still be confused if you only count on the sales of bank personnel. The following small editor for you to introduce how the WPS form in the production of mortgage loan monthly breakdown.

Calculation formula for monthly mortgage loan

Form

1. Mortgage loan matching principal and interest repayment calculation formula

Monthly Debt service amount =[Principal x monthly interest rate X (1+ month rate) repayment months/[(1+ Month interest rate) Repayment month number-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/monthly repayment) + (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, using WPS 2010 Form Calculation 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 the figure.

Loan Schedule

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

Repayment schedule

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.

Then get the first month (period) of the repayment details swell, as shown.

Repayment

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 the two ways of repayment, from the results can be seen that the equivalent of the repayment of the principal payments than the equivalent repayment benefits, but the repayment pressure in the previous years to slightly larger.

The above is the way to make a detailed mortgage monthly account form through the WPS form. By telling you the formula and the production method above, you should see a detailed account table that can be visually compared to see which method is the most cost-effective.

Form making