The total repayment amount (principal+interest) of each installment is X,
Then:
After the first repayment, the total amount owed is q1= a * (1+β)-x.
After the second repayment, the total amount owed is Q2 = q1* (1+β)-x = [a * (1+β)-x] * (1+β)-x.
? = a *( 1+β)^ 2-[ 1+( 1+β)]* x
After the third repayment, the total amount owed is Q3 = Q2 * (1+β)-X.
? = { a *( 1+β)^ 2-[ 1+( 1+β)]* x } *( 1+β)-x
? = a *( 1+β)^ 3-[( 1+β)^ 2+( 1+β)+ 1]* x
It can be concluded that after the k th repayment,
Total amount owed Qk = Qk- 1 * (1+β)-x = ...
? = a *( 1+β)^ k-[( 1+β)^(k- 1)+( 1+β)^(k-2)+...+ 1] * x .
? We found that the geometric series is in []. Have we forgotten the summation formula of equal ratio series?
? Let's deduce it together. Let y= 1+β,
Then sk =1+y+y 2+...+y (k-1), y * sk = y+y 2+...+y (k-1)+y k,
The difference between the two formulas is y * sk-sk = y k- 1, so sk = (y k- 1)/(y- 1).
Continue to use qk = a * (1+β) k-{[(1+β) k-1]/β} * x,
The loan ends after the k th repayment, so Qk = 0, that is, a * (1+β) k-{[(1+β) k-1]/β} * x = 0.
? It is concluded that the total repayment amount of principal and interest in each installment is x = a * β * (1+β) k/[(1+β) k-1], which is the total repayment amount in each installment.
The formula of equal principal and interest, the total repayment amount x of each installment is already available, so what is the repayment principal of each installment?
Assuming that the repayment principal of the n th installment is Pn,
Then:
Principal to be repaid in the first installment P 1 = x-A * β.
The principal to be repaid in the second phase P2 = x-(A-P 1) * β.
= x - {A - [x - A * β]} * β
= x - A * β + (x - A * β) * β
? = p 1+p 1 *β= p 1 *( 1+β)
The principal to be repaid in the third installment P3 = x-(A-P 1-P2) * β.
= x-{ A-p 1-p 1 *( 1+β)} *β
= x-A *β+p 1 *β+p 1 *( 1+β)*β
= P 1 * ( 1 + β) ^ 2
? Then it can be guessed that the principal to be repaid in the nth installment PN = p1* (1+β) (n-1).
? Let's demonstrate this formula. Assuming that the formula holds,
? Then p (n+1) = x-[a-p1-p2-...-pn] * β.
= x-{ A-p 1 *[ 1+( 1+β)+...+ ( 1 + β) ^ (n - 1)]} * β
= x-{ a-p 1 *[( 1+β)^ n- 1]/β} *β
= x-a *β+p 1 *[( 1+β)^ n- 1]= p 1 *( 1+β)^ n
From this, it can be concluded that the repayment principal of each installment in equal principal and interest repayment is pn = p1* (1+β) (n-1).
1. Of the principal and interest equal to the down payment, the down payment may be less than one month. At this time, the principal can be obtained in strict accordance with the above formula.
However, the interest must not be calculated according to the full moon (the interest of each repayment period is in the form of schedule-month). At this time, the interest of the first installment needs to be calculated according to the actual use days.
Assuming that the actual repayment days in the first installment are t, the interest in the first installment is L 1 = A * β * t/30.
How to calculate the actual use days of the first phase?
The calculation of the actual use days in the first phase is "monthly calculation". First, it is found that the repayment date t 1 of the first installment corresponds to the repayment date t0 of the previous installment (if t0 does not exist in the current month, it will be postponed by one day, that is, next month 1 day), and then the difference between the value date y and t0 is compared. The actual use days of the first installment are t = 30-(y-t0).
Example:
1) The value date is 20 18-02- 15, and the first repayment date is 20 18-03- 10, so t0 is 2018-02-/kloc.
? The first practical use days t = 30-(2018-02-15-2018-02-10) = 25.
? 2) If the value date is 20 18-03-02 and the first repayment date is 20 18-03-3 1, then t0 is 20 18-03-0 1.
? (If 20 18-02-3 1 does not exist, it will be postponed for one day. )
The actual use days of the first phase t = 30-(2018-03-02-2018-03-01) = 29.
2. Final principal
Because the repayment principal of each period is the rounded value after formula calculation, there is a problem of precision loss.
? Therefore, the final repayment principal amount is PK = a-p1-p2-...-p (k-1).
? Assume that the total loan amount is A, the monthly interest rate is β, and the number of loan periods is K,
? The total repayment amount (principal+interest) of each installment is X,
? The principal to be repaid in the nth installment is Pn, and the interest to be repaid in the nth installment is Ln.
Then:
? Principal Pn (1
? The k-th repayment principal PK = a-p1-p2-...-p (k-1)
? 1 installment repayment interest L 1 = A * β * t/30, and installment 2 to installment k repayment interest Ln = x-Pn.
? 1 Total principal and interest of installment repayment w 1 = P 1+L 1? The total repayment principal and interest of the 2nd to K installments Wn = X.
Use the function PPMT(rate, per, nper, pv, fv, type) to calculate the principal, and use the IPMT function to calculate the interest.
? 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)
The PMT function in Excel realizes the calculation of loan interest through one-dimensional and two-dimensional simulation operations.
? PMT function can be based on interest rate and equal installment payment,
? According to the loan interest rate, regular repayment and loan amount, calculate the loan amount that should be repaid in each period (usually every month).
? Format and application of PMT function: PMT(Rate, Nper, Pv, Fv, Type)
The meaning of each parameter is as follows:
? Interest rate: interest rate per period,
For example, if you borrow a loan with an annual interest rate of 8.4% to buy a house and repay the loan on a monthly basis,
The monthly interest rate is 8.4%/ 12 (0.7%).
? Users can enter 8.4%/ 12, 0.7% or 0.007 as the rate value in the formula.
? Nper: the number of loan periods, that is, the total payment period of the loan.
For example, a monthly housing loan of 10 years has * * * repayment periods of 10× 12 (i.e. 120).
? You can enter 120 as the value of Nper in the formula.
? Pv: the present value, or the cumulative sum of the present values of a series of future payments, that is, the loan amount.
? Fv: refers to the future final value, or the expected cash balance after the last payment.
? If Fv is omitted, its value is assumed to be zero.
? That is, the future value of loans is zero, and the value of general bank loans is zero.