How to make accounts in the form of WPS?

First, how to make accounts in the WPS form?

Calculation of monthly mortgage subsidiary ledger with WPS20 10 form

Take the commercial loan of 200,000 yuan, the loan term of 20 years, the annual interest rate of the benchmark bank loan of 5.94%, and then give a 30% discount as an example.

Step 1: Open the WPS form and enter the basic loan information in line 1; Then fill in "20" (without quotation marks, the same below) in cell a2, "=A2 12" in cell b2, "5.94%0.7" in cell c2, "C2/ 12" in cell d2 and "200000" in cell e2.

Step 2: Enter the repayment method in line 3, the name of subsidiary ledger in line 4, and the data before the first repayment in line 5, as shown in Figure 2.

Step 3, according to the calculation formula of equal principal and interest repayment

Enter "= (E5d $2 (1d $2) (b $2-a5))/(1d $2) (b $2-a5)-1)" in cell b6.

Enter "= E5d $2" in c6 cell;

Enter "= B6-C6" in cell d6;

Enter "=E5-D6" in the e6 cell.

Then get the repayment details of the first month (period), as shown in Figure 3.

4. Select the (a5:a6) cell area and fill in the a245 cell; Then select the cell range (b6:e6) and fill it in the rows of 240 repayment periods, and you will get the detailed expansion of all repayments.

Step 4: Similarly, according to the calculation formula of average capital repayment method, calculate the detailed development of average capital repayment method (see the attached example for details).

The fifth step is to calculate the sum of the two repayment methods. As can be seen from the results, equal principal repayment is more affordable than equal principal and interest repayment, but the repayment pressure is slightly higher than in previous years, as shown in Figure 4.

Second, how to use WPS20 10 form to calculate the monthly mortgage subsidiary ledger?

I. Calculation formula of monthly mortgage loan payment

1. Calculation formula of equal principal and interest repayment of mortgage loan

Monthly repayment amount = [principal× monthly interest rate× (1interest rate) repayment months ]/[( 1 interest rate) repayment months-1]

In which: monthly interest = residual principal × monthly interest rate of loan; Monthly principal = monthly contribution-monthly interest.

Calculation principle: from the beginning of monthly contribution, the bank collects the interest of the remaining principal first, and then the principal; Although with the decrease of residual principal, the proportion of interest in monthly payment decreases and the proportion of principal in monthly payment increases, the total monthly payment remains unchanged.

2. Calculation formula of average capital repayment method of mortgage loan.

Monthly principal and interest repayment amount = (principal/repayment months) (principal-accumulated repaid principal) × monthly interest rate.

Where: monthly principal = total principal/repayment months; Monthly interest = (principal-accumulated principal repayment) × monthly interest rate.

Calculation principle: the amount of principal returned every month is always the same, and the interest decreases with the decrease of the remaining principal.

Second, use WPS20 10 to calculate the monthly mortgage subsidiary ledger.

Take the commercial loan of 200,000 yuan, the loan term of 20 years, the annual interest rate of the benchmark bank loan of 5.94%, and then give a 30% discount as an example.

Step 1: Open the WPS form and enter the basic loan information in line 1; Then fill in "20" (without quotation marks, the same below) in cell a2, "=A2 12" in cell b2, "5.94%0.7" in cell c2, "C2/ 12" in cell d2 and "200000" in cell e2.

Step 2: Enter the repayment method in line 3, the name of subsidiary ledger in line 4, and the data before the first repayment in line 5, as shown in Figure 2.

Step 3, according to the calculation formula of equal principal and interest repayment

Enter "= (E5d $2 (1d $2) (b $2-a5))/(1d $2) (b $2-a5)-1)" in cell b6.

Enter "= E5d $2" in c6 cell;

Enter "= B6-C6" in cell d6;

Enter "=E5-D6" in the e6 cell.

Then get the repayment details of the first month (period), as shown in Figure 3.

Select (a5:a6) cell area and fill in a245 cell; Then select the cell range (b6:e6) and fill it in the rows of 240 repayment periods, and you will get the detailed expansion of all repayments. http://

Step 4: Similarly, according to the calculation formula of average capital repayment method, calculate the detailed development of average capital repayment method (see the attached example for details).

The fifth step is to calculate the sum of the two repayment methods. As can be seen from the results, equal principal repayment is more affordable than equal principal and interest repayment, but the repayment pressure is slightly higher than in previous years, as shown in Figure 4.

Third, how to calculate the number of hotel accommodation days in the wps form?

Calculation method of hotel accommodation days in wps table;

Make a spreadsheet, list the room number vertically, the date horizontally, and the last date, so that you can count the number of days you live in each month.

The form is easy to understand and clear at a glance.