AT last years annuity method of repayment has become widespread in Russian market consumer lending. The peculiarity of this calculation method is that all payments have the same (equal) value, and the distribution of the amount of each payment between the loan body and accrued interest is different. In the first half of the settlement period, most of the payment is directed to the repayment of interest, in the second half the ratio is equalized and only in the last third of the period the payment distribution shifts in favor of the loan body.
The annuity payment is calculated based on the annuity coefficient of the following type:
K - annuity coefficient;
i - interest rate for one period;
n is the number of periods.
This is a classic calculation formula and each bank uses its own method of dividing repayment periods into periods (in days or months), so the results of calculations at the same rate may differ slightly.
The amount of payment for the annuity method of repayment depends on the calculated annuity ratio (K) and the size of the loan body and is determined as follows:
TC - the body of the loan (disbursed amount).
AP - annuity payment.
Next, we bring our mathematical formulas to a practical form. Since the interest rate is annual, and the loan is repaid monthly, i.e. 12 times a year, the formula for calculating the annuity payment takes the following form:
k - the number of months during which the loan is expected to be repaid.
As we said earlier, the amount of the annuity payment is distributed to repay the loan body and accrued interest. Since interest is calculated monthly on the balance of the loan debt, the formula for calculating it is as follows:
SZ - the amount of debt on the loan at the time of calculation
SP - the amount of interest accrued per month
Thus, the repayment of the loan body accounts for a part of the annuity payment amount, reduced by the amount of accrued interest.
Usually banks use exactly 12 months as a time base, however, some financial institutions make calculations based on the number of days in a year rather than months (usually 365 days): then the result is more accurate.
The practical application of this technique can be found in the following examples:
Not every Russian has the opportunity to make an expensive purchase. Many people who dream of buying new appliances or real estate are forced to take part in consumer or mortgage lending. Studying presented on the domestic financial market loan products, every Russian citizen is trying to save on interest. To find the most favorable loan in all respects, individuals need to know how to calculate monthly payments and interest rates. This can be done directly at the branch. financial institution or independently, using special formulas.
S = Sz * i * Kk / Kg, where
How to calculate the amount of accrued interest, you can consider the following example:
To calculate the annual interest, customers of a financial institution need to carefully study the loan agreement. The agreement usually specifies not only the amount of the loan, but also what amount must be repaid at the end of the contract. To make calculations, subtract the smaller amount from the larger amount, then divide the result by the duration of the loan program, then multiply the final figure by 100%.
There is another way to calculate. The borrower should sum up all monthly payments, after which add to the result additional payments(for example, additional fees, commissions, the amount of funds charged by the bank for servicing the loan program, etc.). After that, the result obtained must be divided by the term of the loan, and the final figure multiplied by 100%.
Today, the banking sector uses two main schemes for calculating interest on loan programs. In this case, we are talking about differentiated and annuity payments that borrowers are required to make once a month to the current account of their lender.
How the calculations are carried out, you can consider an example:
When calculating the amount of monthly payments (differentiated), banks use a different formula:
In order for potential borrowers to choose the most profitable interest calculation scheme, a comparison of both methods should be made. If the emphasis is on the amount of the overpayment, then it will be more profitable to draw up loan programs that provide for differentiated monthly payments. It should be noted that this method also has a disadvantage. Unlike annuity payments, with a differentiated method of repaying a loan, the main credit burden will be placed on the first months of using the program.
If we consider mortgage loan products, then the annuity method of repayment will be extremely unprofitable for them, since in this case individuals will have to overpay very large amounts Money.
Every person sooner or later begins to think about how to improve his living conditions. If he has a sufficient amount of savings, he can purchase a more spacious living space. In the event that individuals do not have the opportunity to save even a third of the value of a property, the only option to improve living conditions is to participate in mortgage lending.
Currently, in the domestic financial market, a huge number of banks offer mortgage loans to Russians. To choose the most profitable terms lending, individuals should independently calculate how much interest they will have to pay, for example, for 15 years. When making calculations, potential borrowers should take into account that the cost of a mortgage loan includes:
As a rule, mortgage loans can be repaid either by annuity or differentiated payments. It will be easier for potential borrowers to calculate the overpayment on a loan in the case of annuity payments. To do this, they need to use the formula:
X = (S*p) / (1-(1+p)^(1-m)), where:
When calculating differentiated payments, it is customary to use the following formula:
Advice: in case of mortgage loan, which provides for differentiated payments, it is better for potential borrowers to use a loan calculator. This is due to the fact that a complex formula is used to carry out the calculations. You can also contact the branch of the bank where you plan to issue mortgage program, where the specialist will calculate the amount of the monthly payment and answer all the questions of interest to the client, for example, is it possible.
Many Russian citizens who choose credit program, use the standard formula for calculating monthly payments. They take the loan amount as a basis, multiply it by the monthly interest rate, and multiply everything by the number of months of lending.
Advice: this formula can be applied in the case of annuity payments, in which the borrower will have to return a fixed amount of funds once a month. In the event that a bank has issued a loan on the terms of differentiated payments, the amount of monthly payments will be calculated using a different formula. It is also worth noting that when paying with differentiated payments, individuals will have to return a smaller amount to the lender every subsequent month.
When calculating differentiated payments to individuals, it is necessary to take into account one important point. The interest rate will be charged each month on the loan amount reduced by the monthly payments already made.
The amount of monthly payments (differentiated) will be calculated for each month:
| Loan duration | Calculation monthly interest | Monthly payment amount |
| January | 100 000 * 0,83% | 8,333.33 + 830 = 9,163.33 rubles |
| February | (100 000 – 8 333,33) * 0,83% = 91 666,67 * 0,83% | 8,333.33 + 760.83 = 9,094.16 rubles |
| March | (91 666,67 – 8 333,33) * 0,83% = 83 333,34 * 0,83% | 8,333.33 + 691.67 = 9,025.00 rubles |
| April | (83 333,34 – 8 333,33) * 0,83% = 75 000,01 * 0,83% | 8,333.33 + 622.00 = 8,955.33 rubles |
| May | (75 000,01 – 8 333,33) * 0,83% = 66 666,68 * 0,83% | 8,333.33 + 553.33 = 8,886.66 rubles |
| June | (66 666,68 – 8 862,87) * 0,83% = 58 333,35 * 0,83% | 8,333.33 + 484.17 = 8,817.50 rubles |
| July | (58 333,35 – 8 333,33) * 0,83% = 50 000,02 * 0,83% | 8,333.33 + 415.00 = 8,748.33 rubles |
| August | (50 000,02 – 8 333,33) * 0,83% = 41 666,69 * 0,83% | 8,333.33 + 345.83 = 8,679.16 rubles |
| September | (41 666,69 – 8 333,33) * 0,83% = 33 333,36 * 0,83% | 8,333.33 + 276.67 = 8,610.00 rubles |
| October | (28 787,94 – 8 333,33) * 0,83% = 25 000,03 * 0,83% | 8,333.33 + 207.50 = 8,540.83 rubles |
| November | (25 000,03 – 8 333,33) * 0,83% = 16 666,70 * 0,83% | 8,333.33 + 138.33 = 8,471.66 rubles |
| December | (12 121,28 – 8 333,33) * 0,83% = 8 333,37 * 0,83% | 8,333.33 + 69.17 = 8,402.50 rubles |
The example shows that every month the body of the loan to be repaid will remain unchanged, and the amount of accrued interest will change downwards.
In this program, you need to fill in the empty windows in which you must enter data:
After entering all the data, potential borrowers only need to click on the “calculate” button. Literally in a few seconds, information will be displayed on the monitor screen, which will allow individuals to give a financial assessment of the selected loan program.
Save the article in 2 clicks:
Every Russian who decides to take advantage of the available banking product, for example, must assess their financial capabilities before submitting an application. To do this, he needs to make calculations annual interest and monthly payments. Calculations will be possible only with the use of special formulas. Also individuals can use free loan calculators, which are located on the official websites of Russian banks. The calculations made will allow potential borrowers to understand whether they will be able to service the selected loan or whether they should look for a program with more affordable conditions.
In contact with
Before taking a loan, it would be nice to calculate all payments on it. This will save the borrower in the future from various unexpected troubles and disappointments when it turns out that the overpayment is too large. Excel tools can help in this calculation. Let's find out how to calculate annuity loan payments in this program.
First of all, it must be said that there are two types loan payments:
With a differentiated scheme, the client pays to the bank on a monthly basis an equal share of payments on the body of the loan plus interest payments. The amount of interest payments decreases every month, as the body of the loan from which they are calculated decreases. Thus, the total monthly payment is also reduced.
The annuity scheme uses a slightly different approach. The client monthly makes the same amount of the total payment, which consists of payments on the body of the loan and payment of interest. Initially, interest payments are calculated on the entire amount of the loan, but as the body decreases, the interest accrual also decreases. But the total amount of payment remains unchanged due to the monthly increase in the amount of payments on the body of the loan. So over time specific gravity percent in the total monthly payment falls, and the proportion of payment for the body grows. At the same time, the total monthly payment itself does not change throughout the entire loan term.
Just on the calculation of the annuity payment, we will stop. Moreover, this is relevant, since at present most banks use this particular scheme. It is also convenient for customers, because in this case the total amount of payment does not change, remaining fixed. Customers always know how much to pay.
To calculate the monthly installment when using the annuity scheme in Excel, there is a special function - PMT. It belongs to the category of financial operators. The formula for this function is as follows:
PMT(rate;nper;ps;bs;type)
As you can see, this function has a fairly large number of arguments. True, the last two of them are optional.
Argument "Bid" indicates the interest rate for a particular period. If, for example, an annual rate is used, but the loan is repaid monthly, then the annual rate should be divided by 12 and use the result as an argument. If a quarterly wage type is used, then the annual rate must be divided by 4 etc.
"Kper" denotes the total number of payment periods for the loan. That is, if a loan is taken for one year with a monthly payment, then the number of periods is considered 12 , if for two years, then the number of periods is 24 . If the loan is taken for two years with quarterly payment, then the number of periods is equal to 8 .
"ps" indicates the current present value. talking in simple words, this is the total amount of the loan at the beginning of lending, that is, the amount that you borrow, excluding interest and other additional payments.
"BS" is the future value. This value, which will be the body of the loan at the time of completion loan agreement. In most cases, this argument is «0» , since the borrower at the end of the loan period must fully pay off the lender. The specified argument is optional. Therefore, if it is omitted, it is considered equal to zero.
Argument "Type of" determines the calculation time: at the end or at the beginning of the period. In the first case, it takes the value «0» , and in the second "one". Most banking institutions use the option with payment at the end of the period. This argument is also optional, and if it is omitted, it is considered to be zero.
Now it's time to move on to specific example calculating the monthly premium using the PMT function. For the calculation, we use a table with initial data, where the interest rate on the loan is indicated ( 12% ), the value of the loan ( 500000 rubles) and loan term ( 24 months). The payment is made monthly at the end of each period.
In field "Bid" enter the percentage for the period. This can be done manually by simply setting the percentage, but we have it in a separate cell on the sheet, so we will give a link to it. Set the cursor in the field, and then click on the corresponding cell. But, as we remember, we have an annual interest rate set in the table, and the payment period is equal to a month. Therefore, we divide the annual rate, or rather the reference to the cell in which it is contained, by the number 12 corresponding to the number of months in a year. The division is performed directly in the field of the arguments window.
In field "Kper" loan period is set. He is equal to us 24 months. You can enter a number in the field 24 manually, but we, as in the previous case, indicate a link to the location of this indicator in the source table.
In field "ps" indicates the initial amount of the loan. She is equal 500000 rubles. As in the previous cases, we specify a link to the sheet element that contains this indicator.
In field "BS" the amount of the loan after its full payment is indicated. Remember, this value is almost always zero. Set the number in this field «0» . Although this argument can be omitted altogether.
In field "Type of" indicate at the beginning or at the end of the month payment is made. In our country, as in most cases, it is produced at the end of the month. Therefore, we set the number «0» . As in the case with the previous argument, you can enter nothing into this field, then the program will by default assume that it contains a value equal to zero.
After all the data is entered, click on the button OK.
And now, with the help of other Excel operators, we will make a monthly breakdown of payments in order to see how much in a particular month we pay for the body of the loan, and how much is the interest rate. For these purposes, we draw a table in Excel, which we will fill with data. The rows of this table will correspond to the corresponding period, that is, the month. Considering that our crediting period is 24 month, then the number of rows will also be appropriate. The columns show the loan principal repayment, the interest payment, the total monthly payment, which is the sum of the previous two columns, and the remaining amount due.
OSPLT(Rate;Period;Nper;Ps;Bs)
As you can see, the arguments of this function almost completely coincide with the arguments of the operator PMT, only instead of the optional argument "Type of" added required argument "Period". It indicates the number of the payment period, and in our particular case, the number of the month.
We fill in the fields of the function arguments window that are already familiar to us OSPLT the same data that was used for the function PMT. Just considering the fact that in the future copying the formula using the fill handle will be applied, you need to make all references in the fields absolute so that they do not change. This requires putting a dollar sign in front of each value of the vertical and horizontal coordinates. But it's easier to do this by simply highlighting the coordinates and pressing the function key F4. The dollar sign will be placed in the right places automatically. Also, do not forget that the annual rate must be divided by 12 .
After all the data that we talked about above is entered, click on the button OK.
RRP(Rate;Period;Nper;Ps;Bs)
As you can see, the arguments of this function are absolutely identical to the similar elements of the operator OSPLT. Therefore, we simply enter the same data into the window that we entered in the previous argument window. At the same time, do not forget that the link in the field "Period" must be relative, and in all other fields, the coordinates must be converted to absolute form. After that, click on the button OK.
SUM(number1, number2,…)
The arguments are references to cells that contain numbers. We place the cursor in the field "Number1". Then hold down the left mouse button and select the first two cells of the column on the sheet "Payment on the body of the loan". In the field, as we see, a link to the range is displayed. It consists of two parts, separated by a colon: a reference to the first cell in the range and to the last one. In order to be able to copy the specified formula using the fill marker in the future, we make the first part of the reference to the range absolute. Select it and press the function key F4. We leave the second part of the link relative. Now, when using the fill handle, the first cell in the range will be fixed and the last cell will expand as it moves down. This is what we need to achieve our goals. Next, click on the button OK.
Thus, we did not just calculate the payment on the loan, but organized a kind of loan calculator. Which will operate according to the annuity scheme. If, for example, we change the amount of the loan and the annual interest rate in the source table, then the data in the final table will be automatically recalculated. Therefore, it can be used not only once for a particular case, but can be used in various situations to calculate loan options for an annuity scheme.
As you can see, using Excel at home, you can easily calculate the total monthly loan payment according to the annuity scheme, using for this purpose the operator PMT. In addition, using the functions OSPLT and HPMT you can calculate the amount of payments on the body of the loan and on interest for the specified period. By applying all this baggage of functions together, it is possible to create a powerful loan calculator that can be used more than once to calculate an annuity payment.
Any loan has a number of parameters, which are highly undesirable to lose sight of, because in the end you can doom yourself to paying additional money to the bank. In the current practice of lending, when drawing up an agreement, more than a dozen of such parameters can be indicated, the most famous of which are the maximum loan amount, volume down payment, the size of the commission charged, sanctions for early settlement of the loan, etc.
Moreover, some of the conditions matter only for a certain time or are generally one-time, others remain relevant throughout the entire term of the loan agreement. For example, payment for consideration of the application is charged only once, a penalty for early repayment usually threatens the borrower only for a certain time, but the commission for servicing the account will be taken up to the full settlement of the loan taken.
A potential borrower is usually most interested in the interest rate on a loan, which is most often advertised by the banks themselves. Meanwhile, this the rate is not the determining parameter for determining total cost loan. Not less than importance has a type of loan repayment, which can be in two versions:
A feature of differentiated loan payments is the accrual of interest only on the unpaid part of the loan. The advantages of such a scheme include a gradual reduction in the burdensomeness of payments, since. interest payments will be reduced, and inflation will further reduce the value of these amounts. However, obtaining a loan with differential payments is quite difficult, since a potential borrower will have to prove his ability to repay the loan at the first time, when the interest amounts will be very significant.
Annuity payments imply loan payments in equal installments. It is according to this scheme that most often the calculation of bank loans takes place today.
However, the apparent simplicity of payment planning hides several unpleasant moments.
Firstly, with the annuity calculation scheme, the share of interest in the total amount of the monthly payment will be slightly higher than when using the differentiated method.
Secondly, for approximately the entire first half of the loan term, interest will make up the bulk of the payment structure.
And this is extremely unprofitable for customers, because if early repayment of the loan is necessary, the amount of the remaining principal debt will be greater than with a differentiated scheme. And the bank will not return the interest already paid in advance to the borrower. Therefore, before you take a loan with annuity payments, you must clearly understand the procedure for calculating loans.
As a rule, banks provide an annuity payment schedule for the convenience of their customers, but you can check their calculations yourself.
The amount of monthly annuity payments is calculated using the following formula:
x \u003d S * (P + (P / (1 + P) N -1)),
where x is the amount of the monthly payment, P is the monthly interest rate (annual rate / 12), N is the duration of the loan in months.
To calculate the percentage component of an annuity payment, you need to multiply the loan balance for the specified period by the annual interest rate and divide all this by 12 (the number of months in a year).
P n \u003d S n * P / 12
Here P n is the amount of accrued interest, S n is the amount of the remaining debt, P is the interest rate (annual).
To determine the part of the monthly payment that will go as the amount to repay the principal debt on loans, it is necessary to subtract the accrued interest from the total payment amount:
Here x is the monthly payment, p n is the interest by the time the nth payment is made, s is the part of the payment that goes towards repaying the principal debt.
To determine the part going to pay off the debt, it is necessary to subtract the accrued interest from the monthly payment. Since the value of s is influenced by previous payments on the loan, it should be calculated consecutively for each month starting from the very first one.
If a loan is taken in the amount of 100,000 at an annual interest rate of 10% for a period of 6 months, then the procedure for calculating annuity payments will be as follows.
First, the amount of the monthly payment is calculated:
300,000 * (0.008333 + (0.008333 / (1 + 0.008333)6 - 1)) = 17,156.14 rubles.
For the first month, the interest will be 833.33 rubles, since 100,000 * 0.1 / 12.
The amount of payments on the principal debt will be 16,322.81 rubles, since 17,156.14 - 833, 33 = 16,322.81.
For the second month, the balance of the principal amount of the debt will be 83,677.19 rubles, since 100,000 - 16,322.81 = 83,677.19.
Interest will amount to 697.31 rubles, since 83,677.19 * 0.1 / 12 = 697.31.
The amount of payments on the principal debt will be 16,458.83 rubles, since 17,156.14 - 697.31 = 16,458.83.
For the third month, the balance of the principal amount of the debt will be 67,218.36 rubles, since 83,677.19 - 16,458.83 = 67,218.36.
Interest will amount to 560.15 rubles, since 67,218.36 * 0.1 / 12 = 560.15.
The amount of payments on the principal debt will be 16,595.99 rubles, since 17,156.14 - 560.15 = 16,595.99.
For the fourth month, the balance of the principal amount of the debt will be 50,622.38 rubles, since 67,218.36 - 16,595.99 = 50,622.38.
Interest will amount to 421.85 rubles, since 50 622.38 * 0.1 / 12 = 421.85.
The amount of payments on the principal debt will be 16,734.29 rubles, since 17,156.14 - 421.85 = 16,734.29.
For the fifth month, the balance of the principal amount of the debt will be 33,888.09 rubles, since 50,622.38 - 16,734.29 = 33,888.09.
Interest will amount to 282.40 rubles, since 33,888.09 * 0.1/12 = 282.40.
The amount of payments on the principal debt will be 16,873.74 rubles, since 17,156.14 - 282.40 = 16,873.74.
By the last sixth month, the balance of the principal amount of the debt will be 17,014.35 rubles, since 33,888.09 - 16,873.74 = 17,014.35.
Interest will amount to 141.79 rubles, since 17,014.35 * 0.1 / 12 = 141.79.
The amount of payments on the principal debt will be 17,014.35 rubles, since 17,156.14 - 141.79 = 17,014.35.
Because the annuity payments slightly increase the total amount of interest paid, then the amount of this overpayment can be calculated. To do this, the monthly payment is multiplied by the number of payments, and the amount taken on credit is subtracted from the result. For this example, the amount of the overpayment would be as follows:
17 156,14 * 6 – 100 000 = 2936,84
As you know, your business starts with a plan. This link is all about how to write your business plan.
Since the calculation of annuity payments manually is too cumbersome, to reduce the likelihood of an error and speed up the whole process, you can use a special function in one of the spreadsheet processors. In particular, Excel uses the PMT function for this purpose.
To use it, you need to create a blank sheet and in one of the cells enter the PMT function with the appropriate parameters. For the example above, it would look like this:
PMT(10%/12; 6; -100000).
After the entry is completed, the cell will display the number of interest.
It is not necessary to use the percent sign in the first parameter, since you can immediately enter the result of the division. In addition, if it is not required to apply the results of the calculation in more complex mathematical constructions, then the minus sign for the last parameter is also optional.
If you need early repayment of the loan, the bank can offer one of two options:
Please note that some banks charge a fee for recalculating the annuity payment schedule or even for the very fact of early repayment. These questions, as well as other hidden fees and commissions, are best known before signing a loan agreement.
It is more profitable for someone to get rid of debts faster, for someone it is more important to redirect their funds from paying a loan to some other purpose. The choice of one or another method entirely depends on both the borrower and whether the bank provides such an opportunity.
What is more profitable directly for the recipient of borrowed funds annuity or differentiated payment type? A small comparative analysis shows the main differences between the two schemes:
However, the final choice of the repayment schedule and scheme remains with the potential borrower.
Credit calculator is a toolkit for calculating the main parameters of a loan, implemented through a web interface, usually a website banking institution. The online loan calculator is a quick way to plan repayments of both the principal amount of the loan and the interest accrued on the balance of the used credit limit.
Using our loan calculator, you can make payments using differentiated or annuity payments.
Annuity payment – monthly repayment received credit funds by making uniform fixed payments. Annuity repayment is represented by two parts - a fee for the use of credit funds and the amount that is directed to repay the loan itself.
Differentiated payment is carried out on a monthly basis, the amount of payment decreases in direct proportion to the period until the end of the loan agreement. The differentiated payment structure is also formed from two parts - a once set amount of debt repayment and a decreasing part of the loan cost, the calculation of which comes from the balance of the loan body.
Today, most credit institutions use the scheme of annuity payments in their practice.
Among other things, the loan calculator is an excellent comparative tool for various types of loans, which allows you to contact banking specialists only directly for the issuance of borrowed funds. Calculate a more profitable and convenient loan payment scheme on our loan calculator.
