Instruction
1
Calculate the monthly annuity payment on the loan. Example: the Customer receives a credit of 50 000 RUB., the date of the loan is August 15. Maturity date – the 16th of each month for the entire loan term. The loan term is 6 months. Interest rate 20% per annum. Then the annuity payment is equal to: AP = (0,2/12*(1+0,2/12)6) : ((1+0,2/12)6-1) * 50 000 = 8819,3 R. Round this number up to 8850 p. (in a big way to the last payment under the annuity accounted for the amount less than the monthly payment).
2
Build a table in Excel and put the data available. Complete the number of days τk. In this case, consider the fact that the payment date falling on a day off is automatically moved to the first working day following this date. Therefore, the payment in October falls on the number 17 – Monday, and the number of days between payments increased by one day.
3
Calculate in a table, all the remaining data using the following formulas and features of Excel:
The amount of the payment of interest for period: RK=(Ik *τk*Sk-1)/Tk
The repayment amount of the loan: Dk = APK - Pk
The loan amount to be paid(balance) : Sk = Sk-1 - Dk
R1 = (0,2*31*50000)/365 = 849,32 R.
D1 = 8850 – 849,32 = 8000,68 R.
S1 = 50000 - 8000,68 = 41999,32 R.
P2 = (0,2*31*41999,32)/365 = 713,41 R.
D2 = 8850 – 713,41 = 8136,59 R.
S2 = 41999,32 - 8136,59 = 33862,73 R.
P3 = (0,2*31*33862,73)/365 = 575,20 R.
D3 = 8850 – 575,20 = 8274,80 R.
S3 = 33862,73 - 8274,80 = 25587,93 R.
The same calculation again and for the fourth and fifth payment. In this case use the possibilities of the table editor, while carrying out calculations directly in the cell values.
The amount of the payment of interest for period: RK=(Ik *τk*Sk-1)/Tk
The repayment amount of the loan: Dk = APK - Pk
The loan amount to be paid(balance) : Sk = Sk-1 - Dk
R1 = (0,2*31*50000)/365 = 849,32 R.
D1 = 8850 – 849,32 = 8000,68 R.
S1 = 50000 - 8000,68 = 41999,32 R.
P2 = (0,2*31*41999,32)/365 = 713,41 R.
D2 = 8850 – 713,41 = 8136,59 R.
S2 = 41999,32 - 8136,59 = 33862,73 R.
P3 = (0,2*31*33862,73)/365 = 575,20 R.
D3 = 8850 – 575,20 = 8274,80 R.
S3 = 33862,73 - 8274,80 = 25587,93 R.
The same calculation again and for the fourth and fifth payment. In this case use the possibilities of the table editor, while carrying out calculations directly in the cell values.
4
Calculate last payment amount on the loan, as a result, the table will look as shown in the figure. The latter amount annuity payment (AP6 = 8746,10) is calculated as the sum of the loan balance (period 5 column 4) and the values of the interest accrued on this balance (6 column 7).