The formula for calculating EPS suggested the Central Bank regulation No. 254-P as amended on 1.07.2007 G. Despite the complexity of the above calculations, the users of the computer, particularly Microsoft office, is a great advantage. Excel financial functions is the formula for calculating the net internal rate of return. In the English version it's XIRR, in Russian – "chistendom".
In the letter No. 175-T, dated 26 December 2006, the Central Bank clarifies how to calculate the examples. The effective interest rate on the loan can be calculated only on the basis of specific data. This is the sum of the initial contribution, the date of issuance of the loan, its term, payment frequency and basis of their calculation.
Example 1. The terms of loans as follows:- ∑ the loan – 12 660 rubles;- payment type – annuitant; - interest rate – 29%; - the maintenance fee is 1.9% of the loan amount per month;- loan term – 12 months;- the date the loan – 17.04.2012.
The effective interest calculation you can maintain after the Bank will provide a table of repayment of the loan. It create in Excel a table similar to that depicted in Table 1. According to the logic of the common man, the overpayment will be 4959,16 rubles, and this is only 39,17% per annum. However, the effective interest rate will be about 90%. To obtain this figure, in cell F19 following the procedure below: "Insert" - "Function" - "Financial" - "chistendom". The argument "Value" select entire column "Amount of payment" in addition to "total" in the argument "Date" is the entire column "Date of payment". The argument "Predp" (estimated return of investment) can be omitted. So, the effective interest rate in this case will be 89,78% per annum.
Example 2. The source data is the same, but the Bank will charge a lump sum Commission in the amount of 1.9 percent per year. Accordingly, the repayment of the loan will have a somewhat different (see Table 2) 12660 x 1.9% x 12 = 2886 roubles. This amount will display in the column amount of payment: to the original -12660 add 2886. Get -9774. You will immediately see that EPS will increase to almost 124%!
Example 3. The Bank offers the loan without a fee for maintaining the loan account. It would seem that in this case the stated rate should be effective. But everything is not so simple. Instead of the declared 29% APR you get of 33.1%. The Bank cheated you? Not at all. Function "chistendom" default puts the expected yield of 10% per annum. It turns out that the Bank receives a monthly payment amount, taking into account its reinvestment. And let the simple inhabitant is difficult to understand why in the end he will pay more than stated, the Bank's actions legitimate.