The total cost of the loan (loan) - example / calculation algorithm

    Not so long ago, Federal Law No. 353 came into force, requiring financial institutions to disclose information about the so-called “Total cost of a loan (loan)” (hereinafter - CPM).
    In this article (in principle, referring only to workers in the financial sector), I would like to give an example of calculating CPM. Perhaps someone will come in handy.

    Important! Not so long ago, lawmakers made changes to the formula, which comes into force only on September 1, 2014. All of the following is suitable only for the new formula. The article describes exclusively the technical implementation of the calculation of CPM in accordance with the law.

    More important!All the information below is relevant for the case when the loan is issued in ONE payment, i.e. the borrower receives money once, and repayments occur according to a predetermined payment schedule. This option covers 99% of loans issued (credit cards do not count).

    Actually, here is the beast itself:

    Understanding the meaning of terms

    UCS is defined as the product of 3 quantities - i, CWP and the number 100. Let us analyze the terms and symbols used:
    1. What is BP (base period)
      BP under a consumer loan (loan) agreement is the standard time interval that occurs with the greatest frequency in the schedule of payments under a consumer loan (loan) agreement. If in the schedule of payments under a consumer loan (loan) agreement there are no time intervals between payments lasting less than one year or equal to one year, then BP - one year.
      In fact, BP is the most common time interval between payments. If there are no repeating time intervals in the payment schedule and a different procedure is not established by the Bank of Russia, the base period is the time interval, which is the arithmetic average of all periods, rounded up to the standard time interval. The standard time interval is the day, month, year, as well as a certain number of days or months, not exceeding the duration of one year. This way you can define your PSU. If the payments are monthly, then BP = 365/12 ~ = 30
    2. What is NWP (the number of base periods in a calendar year)
      The definition in the law is very vague, but as I understand it - this is the number of base periods that “fit” in one calendar year, that is:
      • For a standard payment schedule with monthly payments: NWP = 12
      • Quarterly Payments: NWP = 4
      • Payments once a year or less: NWP = 1
      • If the payment schedule is tricky: for example, first 2 payments are provided once a quarter, and then 6 payments once a month, then 3 payments once a day, then the base period is 1 month. A NWP = 12 (12 BP for a calendar year).

    3. What is i (interest rate of the base period, expressed in decimal form)
      It is impossible to understand (at least to me). Perhaps there is some sense in determining the number i, but it is not possible to catch this meaning intuitively. How to consider i - we will analyze in the next section.


    How to count i

    Let us later try to understand the “physical” meaning of the number i, and give it the following definition:
    The number i is calculated by solving the following equation:
    where:
    • m - the amount of cash flows, which is equal to the number of payments in the payment schedule plus one (another payment arises from the first payment - the issuance of a loan).
    • DP to - the size of the k-th cash flow (loan with a minus sign, repayments with a plus sign).
    • Q to - the number of complete base periods from the date of loan to the k-th cash flow. Q to can be calculated by the formula:
      Q to = floor [(DP to -DP 1 ) / BP], where
      • DP to - the date of the cash flow
      • DP 1 - the date of the first cash flow (i.e. the date of issue),
      • BP - the period of the base period,
      • floor [] - rounding down to the whole.
    • E k - here we will immediately write the formula so that your brain does not explode from the wording in the law:
      E k = mod [(DP to- DP 1 ) / BP] / BP, where mod is the remainder of the division


    PSK calculation algorithm

    Incoming data: two arrays. The key is the cash flow number, the values ​​are the payment dates and the payment amount.
    Outgoing data: UCS value (number).
    Calculation Procedure:
    1. We calculate the NWP (the number of base periods). The number of base periods - how many such periods will fit in 365 days, i.e. ChBP = floor [365 / BP].
    2. For each k-th payment we consider DP k , Q k , E k .
    3. By methods of approximate calculation, up to two decimal places are considered i.
    4. We multiply NWP * i * 100.

    The code!

    There is a ready-made solution in javascript, as well as in VBA (there will even be an excel-file for calculations).
    Why VBA and Excel?
    If you suddenly have a fire and nothing will work on September 1, 2014, the most reasonable thing is to send an excel-plate to the places where the contracts are concluded, so that you can calculate the CPM at least for the first time.

    In the examples, we have taken the schedule for a loan of 100,000 rubles for 3 months at a rate of 12% per annum. Date of issue - September 1, 2014:
    KeyCash flow dateCash flow amount
    009/01/2014-100,000
    110/01/201434 002.21
    211/01/201434 002.21
    312/01/201434 002.21
    Javascript solution
    the code
    function psk() {
         //входящие данные - даты платежей
        var dates = [
        new Date(2014, 8, 01),
        new Date(2014, 9, 01),
        new Date(2014, 10, 01),
        new Date(2014, 11, 01)];
        //входящие данные - суммы платежей
        var sum = [-100000,
        34002.21,
        34002.21,
        34002.21 ];
        var m = dates.length; // число платежей
        //Задаем базвый период bp
        bp=30;
        //Считаем число базовых периодов в году:
        var cbp = Math.round(365 / bp);
        //заполним массив с количеством дней с даты выдачи до даты к-го платежа
        var days = [];
        for (k = 0; k < m; k++) {
            days[k] = (dates[k] - dates[0]) / (24 * 60 * 60 * 1000);
        }
        //посчитаем Ек и Qк для каждого платежа
        var e = [];
        var q = [];
        for (k = 0; k < m; k++) {
            e[k] = (days[k] % bp) / bp;
            q[k] = Math.floor(days[k] / bp);
        }
        //Втупую методом перебора начиная с 0 ищем i до максимального приблежения с шагом s
        var i = 0;
        var x = 1;
        var x_m = 0;
        var s = 0.000001;
        while (x > 0) {
            x_m = x;
            x = 0;
            for (k = 0; k < m; k++) {
                x = x + sum[k] / ((1 + e[k] * i) * Math.pow(1 + i, q[k]));
            }
            i = i + s;
        }
        if (x > x_m) {
            i = i - s;
        }
        //считаем ПСК
        var psk = Math.floor(i * cbp * 100 * 1000) / 1000;
        //выводим ПСК
        alert("ПСК = " + psk + " %");
    }
    

    Demo on jsfiddle: jsfiddle.net/exmmo/m5kbb0up/7

    VBA + excel solution
    The code
    In column A, starting from the 2nd row are the dates of cash flows.
    In column B, starting from the 2nd row are the amounts of cash flows.
    Sub psk()
        Dim dates()
        Columns("A:A").Select
        dates() = Application.Transpose(Range(ActiveCell, Cells(Rows.count, ActiveCell.Column).End(xlUp)))
        Dim summa()
        Columns("B:B").Select
        summa = Application.Transpose(Range(ActiveCell, Cells(Rows.count, ActiveCell.Column).End(xlUp)))
        Dim m As Integer
        m = UBound(dates)
        bp = 30
        cbp = Round(365 / bp)
        ReDim Days(m)
        For k = 2 To m
            Days(k) = dates(k) - dates(2)
        Next
        ReDim e(m)
        ReDim q(m)
        For k = 2 To m
            q(k) = Days(k) \ bp
            e(k) = (Days(k) Mod bp) / bp
        Next
        i = 0
        x = 1
        x_m = 0
        s = 0.000001
        Do While x > 0
            x_m = x
            x = 0
            For k = 2 To m
                x = x + summa(k) / ((1 + e(k) * i) * ((1 + i) ^ q(k)))
            Next
            i = i + s
        Loop
        If x > x_m Then
            i = i - s
        End If
        psk = Round(i * cbp, 5)
        Cells(3, 7).Value = psk
    End Sub
    

    Demo in Excel + VBA: yadi.sk/i/oRTa8Id-a6UfV

    Conclusion

    The code is far from perfect, you can even say that it is miserable. For example, approximate calculation is performed in the most stupid way known to mankind. I ask you to understand and forgive, in the current situation, there is absolutely not enough time to write something presentable. Guilty, I’ll correct it.

    If you have any comments or you find a mistake - please report, I will be grateful. The most dangerous thing that can be is an initially incorrect interpretation of the text of the law.

    UPD UCK online calculator with user-friendly sane interface

    Also popular now: