Housing loan repayment calculation method

  • Created:
  • / Author: Aaron Lewis
We here again to buy a house loan mortgage repayment peak, is a concern for many people, back from the property company housing loan repayment table (see Table 1, table 2), I found the reverse formula, the corresponding table,I put the formula to transform, to clearly know the meaning and calculating method of theSo, not only can according to their own actual situation, their advantage of a take two loans, but also know the monthly repayment of bank calculation is correct, and thus do know the score. If you clearly know the ability to advance repayment, suggested a matching principal repayment, it can reduce the interest expense. Calculated monthly repayment amount in the end I gave the Excel formula and the C program, for your reference. The following useThe termIn accordance with the terms in the table, in order to standardize.
A housing loan two repayment methods and calculation formula
1,Matching serviceRepayment law, the repayment period interest payments, principal at least, after the gradual reduction of interest payments, principal gradually increased, but the amount equal to the monthly repayment amount (the + message). It is suitable for low income and savings fewer young people, because for small pressure will not lower the quality of life. Formula for the:
The monthly repayment of loan principal interest rate * * = (1 + monthly interest rate) ^ total repayment months / (1+ (interest rate) ^ alwaysMonthly number -1)
In the above formula, the number is fixed, so the repayment amount is fixed, we put the formula Transformation:
The monthly repayment amount =The principal of the loan interest rate * month+The principal of the loan interest rate / * ((1+ monthly interest rate) ^ total repayment months -1)
Among them: we call 'The principal of the loan interest rate * month'forMonthly interestSaid, 'The loan principal *Rate / ((1+ rates) ^ total repayment months -1)'forMonthly principal. The total for the monthly repayment amount, we call itPrincipal and interest(one month);Total interest= total principal and interest repayment months * - loan principal, you are spending all interest. "^" said index, the use of power in Excel, POW in C, need the header file #include .
　　
2,Matching principalRepayment law, the monthly repayment of the loan principal, interest on loans with principal month after month, monthly repayment amount (the + message) gradually reduce. Total interest payments is less than the matching service law. Suitable for high income and some savings middle-aged people. Formula for the:
The monthly repayment amount = loan principal / total repayment months + (the loan principal repayment of principal - accumulated) * monthly interest
Among them:Total loan principal repayment of principal = / total repayment months * have repayment months
In the above formula, have repayment months is changing, so the monthly repayment amount is changing, we put the formula to transform it becomes:
The monthly repayment amount =The loan principal / total repayment months+ (The principal of the loan interest rate * month-The loan principal / total alsoMonthly number * monthly interest* have repayment months)
Among them:The loan principal / total repayment months, we call itMonthly principal, is a fixed value;The principal of the loan interest rate * month, we call itThe first month of interest payable, is a fixed value;The loan principal /The total repayment months * monthly interest rate = monthly principal * monthly interest, we call itSpreads, is a fixed value; the first month of interest payable - spread * have repayment months, is the month to also interest, it is a variable that is:ArticleX month interest payable = first month interest payable - spread * (X-1);The total interest payable= all months monthly interest payable sum, is that you spending all interest.
　　
Example two two repayment methods

The table to the principal of 10000 yuan as an example of the provident fund loans, the current interest rate is, less than five years (including five years) 4.14% annual interest rate (interest rate 3.45 ‰), more than five years the annual interest rate of 4.59% (monthly interest rate 3.825 ‰)
　　Note: the commercial loans and provident fund loans is in accordance with the different interest rates to calculate the.
  Matching serviceRepayment Table 1  
　 The A column The B column The C column The D column The E column
1 Life Monthly principal Monthly interest Principal and interest Total interest
2 1   Expire Once Repayment of principal and interest 414
3 2 400.37 34.5 434.87 436.88
4 3 261.36 34.5    295.86 650.96
5 4 191.92 34.5 226.42 868.16
6 5 150.3 34.5  184.80

1088
 
We assume that,F2 cells are the principal of 10000 yuan,F4 is a monthly interest rate 0.00345,F6 is a monthly rate of 0.003825 yuan,B3 cellThe input formulaMonthly principal=$F$2*IF ($A3<=5, $F$4, $F$6) / (POWER (1+IF ($A3<=5, $F$4, $F$6), $A3*12) -1). C3 cell input formulaMonthly interest=$F$2*IF ($A3<=5, $F$4, $F$6)In cell D3, enter the formulaPrincipal and interest =B3+C3In cell E3, enter the formulaTotal interest=D3*A3*12-$F$2In 2 years, is the corresponding loan repayment. The 2 to the 5 row, first select the B3 to E3, and then drag the black E3, E6 can be loosely.
Among themB6 cellMonthly principal, 5 years 5*12=60 months, 10000*0.00345/ ((1+0.00345) ^ (5*12) -1) =150.3, C6 monthly interest =10000*0.00345=34.5, D6 as a month of principal and interest to =B6+C6=184.8, E6 as interest total =D6*5*12-10000=1087.86.
From the table, loans 10000 yuan, 5 years, the monthly repayment 184.8 yuan.

  Matching principalRepayment Table 2   
　 The A column The B column The C column The D column The E column
1 Life Monthly principal The first month of interest payable The total interest payable Spreads
2 1   Expire Once Repayment of principal and interest 　
3 2 416.67 34.5 431.25 1.44
4 3 277.78      34.5         638.25 0.96
5 4 208.33   34.5 845.25 0.72
6 5 166.67 34.5      1052.25    

0.58
 
We still assume,F2 cells are the principal of 10000 yuan,F4 is a monthly interest rate 0.00345,F8 is a monthly interest rate 0.003825,B3 cellThe input formulaMonthly principal=$F$2/ (A3*12). C3 cell input formulaThe first month of interest payable=$F$2*IF ($A3<=5, $F$4, $F$6) . D3 cellThe total interest payableIn addition, need Excel spreadsheet see Table 3 below, or other method, see the back program. E3 cell input formulaSpreads=$F$2/ (A3*12) *IF ($A3<=5, $F$4, $F$6).
Among themB6 cellIn 5 years, 5*12=60 month should also principal, 10000/ (5*12 =166.67 C6), the first month of interest payable 10000*0.00345=34.5. The D6 column is the total interest payable, including all the interest and other language or a Excel table (see Table 3) to complete the cycle accumulation. The E6 column is spread 10000/ (5*12) *0.00345=0.575, retained two decimal places is 0.58.
From the table, loans 10000 yuan, 5 years 60 months also:
The first month repayment 166.67+34.5=201.17 yuan.
The second month repayment 166.67+34.5-0.575* (2-1) =200.595 yuan.
The third month repayment 166.67+34.5-0.575* (3-1) =200.02 yuan.
......
The sixtieth month repayment 166.67+34.5-0.575* (60-1) =167.245 yuan.
60 months all add up to -10000 =1052.25 is the principal repayment of all interest.
Obviously, 1088-1052.25=35.75, principal of 10000 yuan 5 years, matching interest than matching the principal
Pay interest 35.75 yuan.
　Matching principalRepayment Table 3   The total amount of interest payable
　 The G column The H column The I column The J column
1 Month Monthly interest Monthly repayment The total amount of interest
2 1 34.5 201.17 1052.25
3 2 33.925 200.59
...
61 60 0.575 167.24

 
 
Enter the formula in cell H2Monthly interest=$C$6-$E$6* (G2-1), see Table 2 shows, C6, E6 respectively is 5 years of the first month of interest payable and spreads, the table of the G column is calculated month, so is the monthly interest.
Enter the formula in cell I2Monthly repayment=$B$6+H2, see Table 2 shows, the B6 cell is 5 year month should also principal, and add H2 of this table is the monthly repayment.
G2 to G61 is in arithmetic, and H2 to H61, I2 to I61 to a fixed reference table 2 cell, so in the above formula, all with a fixed reference symbol $. Select G2, H2, I2, mouse click I2 spots, until I61 can be loosely. Your monthly repayment according to I61 value of I2. In a cell without using any input, such as J2 =SUM (H2:H61) is 10000 yuan, the total interest is 5 years. The numerical manual write table D2 cell can be 2, and the rest on.
　　Be carefulIf the number of rows, a lot of, can select G2, and then the page until you see the G61, press the shift key and then select G61, all selected G2 to G61 cells, and then from the Excel menu, edit fill sequence, inSequences generated in theSelectColumn, inTypeSelectAutomatic filling, to press ok.
If the loan of 50000 yuan, the above items all multiplied by 5=50000/10000.
Such as the first month repayment 5*201.17=1005.85 yuan.
The sixtieth month repayment 5*167.245=836.225 yuan.
All the interest of 5*1052.25=5261.25.
 
Three two repayment methods VC implementation
 
#include    
 Float benjin=10000.; / / the loan principal, can be modified
 Float nian=30; / / the loan period, can be modified
 Double yuelilv1=3.45/1000.; //1-5's interest rate 3.45 ‰, with market transformation
 Double yuelilv6=3.825/1000.; //6 years the interest rate of 3.825 per thousand, with the market transformation
/ /Change the above 4 was obtained
 Double yuelilv=0.; / / month interest rates, with the market transformation
 If (nian<=5)        Yuelilv=yuelilv1;
 Else if (nian>5) Yuelilv=yuelilv6;
 Double licha=benjin/ (nian*12) *yuelilv; / / spread
 Double yuehuanbenjin=benjin/ (nian*12); / / month should also principal
 Double shouyuelixi=benjin*yuelilv; / / the first month interest payable
 Double yuefubenjin=0; / / monthly principal
 Double yuefulixi=0; / / monthly interest
 Double sum=0, temp; / / total interest
 Int k=0;
 FILE *fp;
/ /"Matching service repayment"
 Fp=fopen ("c:/ mortgage.Txt", "wt");
 If (FP)
 {
 Fprintf (FP, "housing loan%.2f yuan%d years%d months \n", Benjin, (int) Nian, (int) nian*12);
 Fprintf (FP, "===============================\n");
 Fprintf (fp,"A "matching principal repayment," list (for reference only)\n ");
 K=nian;
 Fprintf (FP, "year, month monthly principal, interest, principal and interest, the interest in \n");
 For (k=1; k<=nian; k++)
 {
  If (k<=5)Yuelilv=yuelilv1;
  Else if (k>5) yuelilv=yuelilv6;
  Yuefulixi=benjin*yuelilv; / / monthly interest
  Yuefubenjin=yuefulixi/ (pow ((double) (1.+yuelilv), (double) (k*12)) -1.); / / monthly principal
  Sum=yuefubenjin+yuefulixi; / / principal and interest
  Fprintf (FP, "D,%.2f,%.2f, %.2f,%.2f\n",
   K, yuefubenjin, yuefulixi, sum, sum* (k*12) -benjin);
 }
 Fprintf (FP, "--------------------------------\n");
 Fprintf (FP, ""Matching service"Loans have%d yuan,%d (%d months)Fixed repayment schedule\n",
   (int) Benjin, (int) Nian, (int) (nian*12));
 K=nian;
 Fprintf (FP, "year, month monthly principal, interest, principal and interest, the interest in \n");
 Fprintf (FP, "D,%.2f,%.2f,%.2f,%.2f\n",
  K, yuefubenjin, yuefulixi, sum, sum* (k*12) -benjin);
 }//if (FP)

/ /"Matching principal repayment"
 Fprintf (FP, "===============================\n");
 If (FP)
 {
 Fprintf (fp,"Two, "" matching principal repayment schedule (for reference only)\n ");
 Fprintf (FP, "year, month should also principal, the first month of interest payable, interest payable amount, spread \n");
 For (k=1; k<=nian; k++) / / year cycle
 {
  If (k<=5)>
  Else if (k>5)Yuelilv=yuelilv6;
  Licha=benjin/ (*yuelilv k*12); / / spread
  Yuehuanbenjin=benjin/ (k*12); / / month should also principal
  Shouyuelixi=benjin*yuelilv; / / the first month interest payable
  Sum=0;
  For (int j=1; j<=k*12; j++) / / month cycle
  {// Monthly principalThe first month of + interest payable - the interest differential
   Temp=yuehuanbenjin+shouyuelixi-licha* (J-1); / / monthly repayment amount
   Sum=sum+temp;
  }// month cycle
  Fprintf (FP, "d ,%.2f,%.2f ,%.2f,%.3f\n",
   K, yuehuanbenjin, shouyuelixi, sum-benjin, Licha);
 }// annual cycle
 Fprintf (FP, "--------------------------------\n");
 Fprintf (FP, "more than two tables to the principal%.2f yuan repayment law as an example of \n", Benjin);
 Fprintf (FP, "five years and below the annual interest rate (%.3f% rate%.3f ‰) \n",
  Yuelilv1*1000.*12./10., yuelilv1*1000.);
 Fprintf (FP, "more than five years the annual rate of%.3f% (monthly interest rate%.3f ‰) \n",
  Yuelilv6*1000.*12./10., yuelilv6*1000.);
 Fprintf (FP, "matching principal repayment in equal monthly lots, matching the principal for fixed principal + variable of interest, and \n");
 Fprintf (FP, "X month interest payable = first month interest payable - spread * (X-1)) \n");
 Fprintf (FP, "--------------------------------\n");
 Fprintf (FP, ""Matching principal"Loans have%d yuan,%d (%d months)Repayment schedule\n",
  (int) Benjin, (int) Nian, (int) (nian*12));
  K=nian;
  If (k<=5)>
  Else if (k>5)Yuelilv=yuelilv6;
  Licha=benjin/ (*yuelilv k*12); / / spread
  Yuehuanbenjin=benjin/ (k*12); / / month should also principal
  Shouyuelixi=benjin*yuelilv; / / the first month interest payable
  Sum=0;
 For (k=1; k<=nian*12; k++)
  {
  Temp=yuehuanbenjin+shouyuelixi-licha* (k-1); / / monthly repayment amount
   Sum=sum+temp; / / repayment accumulated
  Fprintf (FP, "d Repayment:%.2f \n", K, temp);
  }
  Fprintf (FP "loans,%d yuan,%d (=%d months) the total expenditure of interest =%.2f yuan \n",
  (int) Benjin, (int) Nian, (int) (nian*12), sum-benjin);
  Fprintf (FP, "--------------------------------\n");
  Fclose (FP);
 }// if (FP)
 Return;
 
In addition, according to the need, ITo simplify the calculation methods of two kinds of loans, and gives theMatching principalAndMatching serviceIn each month of the repaymentBen and Xi MingxiDetailed calculations, because the content is more, so in the 2007-7-3 to writeHousing loan repayment schedule calculationA paper. This not only understand this and interest is how to calculate, but also can handleThe interest rate changeLater, how to calculateSubsequent repayment calculation.