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

Actually, here is the beast itself:

Understanding the meaning of terms

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

• 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 ₁ ) / BP], where
  
  • DP _to - the date of the cash flow
  • DP ₁ - 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 ₁ ) / BP] / BP, where mod is the remainder of the division

PSK calculation algorithm

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!

Javascript solution

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 + " %");
}

VBA + excel solution

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

Conclusion