Full cost of the loan (loan) - an example / calculation algorithm

Actually, here is the beast itself:

We understand the meaning of terms.

1. What is BP (base period)

   BP under a consumer credit (loan) contract is a standard time interval that occurs with the highest frequency in the payment schedule for a consumer credit (loan) contract. If there are no time intervals between payments of less than one year or equal to one year in the payment schedule for a consumer credit (loan) agreement, the BP is one year.
   In fact, BP is the most common time interval between payments. If there are no recurring time intervals in the payment schedule and another order is not established by the Bank of Russia, the base period is a time interval that is arithmetic average for all periods, rounded to the standard time interval. The standard time interval is day, month, year, and also a certain number of days or months, not exceeding the duration of one year. So you can define your BP. If monthly payments, then BP = 365/12 ~ = 30
   

2. What is PSB (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, ie:
   
   • For a standard payment schedule with monthly payments: ChBP = 12
   • Quarterly payments: ChBP = 4
   • Payments once a year or less: BSB = 1
   • If the payment schedule is tricky: for example, there are 2 payments once a quarter, and then 6 payments once a month, then 3 payments once a day, then the base period is 1 month. And ChBP = 12 (12 BP for a calendar year).
   
   

3. What is i (interest rate base period, expressed in decimal form)

   It is impossible to understand (at least to me). Perhaps there is some meaning in determining the number i, but it is not intuitively possible to grasp this meaning. How to count i - we will analyze in the next section.
   

How to count i

• m - the number 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 the loan).
• DP _to - the size of the k-th cash flow (issuance of a loan with a minus sign, returns with a plus sign).
• Q - the number of full base periods from the moment of issuance of the loan to the k-th cash flow. Q _to can be calculated by the formula:
  Q _to = floor [(DP _to -DF ₁ ) / BP], where
  
  • DP _to - the date of the k-th cash flow,
  • DP ₁ - the date of the first cash flow (i.e. the date of issue),
  • BP - the period of the base period,
  • floor [] - round down to the whole.
• E _to - here we will immediately write a formula so that your brain does not explode from the wording in the law:
  E _k = mod [(DP _to -DF ₁ ) / BP] / BP, where mod is the remainder of division

PSC calculation algorithm

Calculation procedure:

1. Calculate ChBP (the number of base periods). The number of base periods - how many such periods will fit in 365 days, i.e. BSB = floor [365 / BP].
2. For each k-th payment, consider the DP _k , Q _k , E _k .
3. We consider i as the methods of approximate calculation up to two decimal places.
4. Multiply ChBP * i * 100.

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