Week 2
A credit and its types of payments
Any credit consists of principal and accrued interest. The interest overpayment and monthly payments will differ depending on the loan repayment method you choose. There is an annuity and a differential loan repayment plan.
What are the differences between repayment schemes?
Considering the repayment scheme in the credit agreement is crucial to repaying debt comfortably. The differences are:
- With an annuity, the monthly payments will be the same throughout the term.
- payments include a portion of the credit body and interest accrued on its current value;
- the distribution of the parts of the body of the credit is unequal;
- at the beginning of the term, the part of the body of the credit, included in the monthly payment, is small in relation to the accrued interest;
- repayment schedule – equal installments on each payment date.
- A differentiated scheme of repayment size of monthly payment significantly decreases by the end of the term.
- payments include equal parts of the credit body and interest accrued on the rest of the debt.
- the body of the credit is divided into equal parts by the number of months (by the term of the credit);
- less and less interest is charged on the rest of the body of the credit on each payment date.
Advantages of annuity payment:
- reduced risk of nonpayment: fixed payments favorably affect budget planning;
- premature partial repayment reduces the amount of each subsequent payment.
Advantages of differential payment:
- the amount of the monthly payment decreases noticeably toward the end of the credit term;
- as a result, interest overpayments are lower than under the annuity scheme.
Resources:
Какой платеж по кредиту выгоднее: аннуитетный или дифференцированный? (credithub.ru)
Practical part
Assignment № 1.
Calculation of the monthly annuity payment on the credit received for a year.
Step 1.
Browse through banking websites on the internet. Choose one bank to calculate the credit. Research its interest rate, the amount it maxes out, and the time it maxes out.
This project looks at the Halyk Bank loan scheme as an example. Its interest rate is 25.5%, its maximum loan amount is 7,000,000₸ and its maximum loan term is 5 years.
Step 2.
Create and open a file named “credit” in Microsoft Excel.
Where:
S – credit amount;
i – interest rate;
N – payment period;
X – the amount you pay each month;
S – credit body;
P – interest amount;
M – repayment of the credit.
Step 3.
Enter the amount you borrowed and the interest rate in the table. In the example, the credit amount is 2,000,000₸, the interest rate is 25.5%, and the repayment term is 1 year.
Step 4.
Calculate the amount you pay each month. To do this, select Formulas→Insert Function→PMT. A new table will open. Fill in the data in the table as shown and click OK. Where:
“Rate” – the interest rate;
“Nper” – number of payment periods;
“PV” – loan amount.
“FV” – last balance, equals 0, i.e. nobody owes anything to anybody.
“Type” – method of accounting for monthly payments. If it equals 1, payments are accrued at the beginning of the month, if it equals 0, then at the end. Most banks work on the second option, so enter 0.
Step 5.
The payment for the second month is the same as the payment amount for the first month. Therefore, enter “=B6” in cell B7. And for the other months, use the autofill method starting in cell B7.
Step 6.
S1=2,000,000₸, and the interest amount is calculated using the formula P1=S1*i/12. You calculate the repaid part of the loan through M=X+P, then M1=X1+P1.
Step 7.
To find S2, add M1 and S1: =C6+E6.
To find P2, S2 – multiply the remaining loan amount by the interest rate and divide by 12 months: =C7*$B$2/12.
M2 will be equal to the sum of the monthly payment and interest amount: =B7+D7.
Step 8.
Mark the S2-M2 interval and use the autofill method for all months.
Step 9.
Calculate the monthly payment amounts, interest amounts, and repayment amounts for 12 months using the “AutoSum” function in the formulas section.
To summarize the calculation, most of the amount you pay each month at the beginning of the year is the interest amount and the rest is the repaid amount. By the end of the year, the interest amount will decrease, and you will notice an increase in the amount repaid. The interest amount is the amount with a surcharge on top of the amount you borrowed from the bank during the year.
Assignment №2.
Calculate the differential monthly payment on the loan taken out over a year (using the data from Assignment №1). After completing the calculation, determine which payment scheme is most efficient by comparing it to the result of the annuity payment.
Step 1.
In Excel, add a new sheet to the file, create a table as shown, and enter the data from task №1 into the table. Where A is the equal shares of the loan body.
Step 2.
A – equal shares of the loan body equal shares divided by 12 months: =$B$1/$B$3. For all months, use the auto-complete method, starting with cell B6.
S1=2.000.000₸.
And you calculate the interest amount using the formula P1=S1*i/12: ==C6*$B$2/12
The amount you pay for the first month is calculated according to the formula X=A+R: =B6+D6
Step 3.
To calculate S2, subtract A1 from S1: =C6-B6. For the remaining months, use the autofill method, starting from cell C7.
Step 4.
To calculate the interest amount and the amount you pay each month, mark P1 and X1 and calculate all months using the autocomplete feature.
Step 5.
Calculate using the autosum function to find out how much interest you have to pay in total and the amount of monthly payments in the differential payment scheme. To do this, go to the empty cell at the bottom of the column where the interest amounts are calculated, and select the auto-sum function in the formula section. And do the same process with the monthly payment amounts as well.
Step 6.
Based on your calculations, compare annuity and differential payment schemes. Which payment system, in your opinion, is the most effective? Make conclusions.