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.
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).