A bit of financial math: calculating inflation and interest
Recently, I had to check the calculations made by the public utilities of my city. There was a controversial issue, which can only be resolved by conducting correct calculations. Looking ahead, I will say that the public utilities are wrong. Therefore, here we will consider how to correctly calculate inflation costs and 3% per annum on debt for obligations, according to the Civil Code of Ukraine.
The first part describes the calculation of inflation costs, this is specific to Ukraine.
The second (larger) part will be devoted to the issue of calculating 3% per annum. It is also applicable to the Russian Federation, Belarus and Kazakhstan, but in these countries the rate is not fixed, but depends on the Central Bank refinancing rate. The rest of the country did not look.
The second part is very close to the calculation of interest on ordinary credit transactions (loans, loans, borrowings, etc.).
First a theory, a little code at the end.
All this under the cut.
So, I got into the hands of a document from utilities "Statement of accrual of inflation costs and 3% per annum." It was checked by me, and I found out that it was calculated using the wrong algorithm and is of interest, since it must implement the requirements of the article of the Civil Code of Ukraine.
What should be considered is written in the Civil Code of Ukraine:
Further I will talk about the calculation algorithm, and not about specific numbers.
Inflation is a process characterized by a depreciation of the currency, that is, a decrease in its purchasing power and a general increase in prices. Excluding inflation, the final results of cash flow calculations are very arbitrary.
Inflation Index (II), or Consumer Price Index (CPI), Consumer Price Index (CPI) is one of the types of price indices created to measure the average price level for goods and services (consumer basket) for a certain period in the economy.
AI is an indicator characterizing the dynamics of the general price level for goods and services that the population acquires for non-productive consumption. This is the growth index, which shows how many percent the average price increase for the period under review.
In Ukraine, AI and CPI are synonymous
Since the price index is a chain index, for periods following one after another, it is calculated by multiplying the indicators, for example, 101% * 102% = (1.01 * 1.02) * 100% = 103.02%.
AI is calculated by the State Statistics Committee of Ukraine and published in official periodicals. These key figures can be used to recalculate cash amounts.
What do communal workers think?
The last settlement date is December 7, 2016.
Let's see how the communal workers got inflation cost ratios?
It can be seen with the naked eye that in the third (and fifth column), instead of the integer part 1, there should be the integer part 0. How did they get the fractional part?
September 2016. Fractional part 0.028. The payment term for September is October 20, 2016. The fractional part corresponds to the inflation index published by the State Statistics Committee, for October 2016 - 102.8%
August 2016. Fractional part 0.0465. The payment term for August is September 20, 2016. The fractional part corresponds to the inflation index for September multiplied by II for October: 101.8% * 102.8% = 104.65%, or in shares, 1.018 * 1.028 = 1.046504. Public utilities are rounded up to 1.0465 and print this number, although the unit should be taken away, because in the next column they print only the amount of inflation costs, and not the accumulated amount.
July 2016 and earlier. I performed these calculations and restored the algorithm. Public utilities multiply inflation indices, starting from the month following the month of service provision (month of the due date) and ending with October 2016.
The start point is only partially true. As I will show later, in the general case, the calculation really needs to start from this month, but in our particular case, the first month should be skipped (equate AI to 100%).
The end point of the multiplication. Since the last day of calculation is in December, and you need to finish December, this is in the general case. As I will show later, in this particular case, December also needs to be skipped (also equate AI to 100%). And on December 7, the December AIs did not have time to publish.
Why is November not taken into account anywhere? I think they just forgot to enter it into the system.
Now let's see how to calculate the inflation index. As necessary - described in the Information Letter of the Supreme Economic Court of Ukraine No. 01-06 / 928/2012 dated 17.07.2012 [6].
I will give part of the paragraph:
Those. it’s correct to say this: for the provision of services in September 2016, consider inflation for three months (pass the cursor or cycle), from October to December, but in the first and last month, take AI equal to 1 (100%), and take into account only November.
In November 2016, AI amounted to 101.8%, i.e. the coefficient should be 0.0180, not 0.0280. I wrote about the lead unit before.
Let's see how the communal services got the coefficients and the amount of accrual of 3% per annum? This is the fifth column in the table in the figure above.
As I wrote earlier, there should not be a leading unit, there should be 0. We look at the fractional part, and notice that it is somehow suspiciously round - each previous month is 0.0025 more than the current month.
Using simple calculations, it can be determined that the public utilities, without further ado, divided 3% into 12 months, received 0.0025, or 0.25% per month, and accrue this percentage for each full and incomplete month of delay. Yes, if you look at October 2016, the payment deadline for which came on November 21, and the calculations were made on December 7, then for an incomplete month they counted the same 0.25%.
The approach admires brutal straightforwardness, but, as I will show later, financial mathematics does not know such an algorithm.
The legislation of Ukraine and financial mathematics do not give a clear answer to the question of how to charge 3% per annum? I have searched for several books on this subject, but books are ancient, dating back to 2002. Therefore, the algorithms from the books will be supplemented by the fact that it was possible to google from more modern and western articles. [1] was reprinted in 2007, and more recent Russian-language books could not be found. Perhaps because financial mathematics, as a science, originated a very long time ago, long before the advent of computers, concepts such as “credit” and “interest on credit” arose several hundred years ago. And the algorithms have not changed much since 2002.
But the Western view of the problem is different from that described in Russian-language books. I will cite information first from Russian-language books, and then from Western articles, indicating where they coincide.
What is written below relates to the specific case of accruing 3% per annum on debt, but can be generalized to the calculation of interest on other credit relations, if interest is accrued according to the simple interest rather than compound interest scheme.
According to Art. 625 Civil Code of Ukraine need to accrue 3% per year according to the simple interest scheme. To calculate the amount of interest, the amount of debt (or the body of the loan, in general) must be multiplied by the normalized annual interest rate of 3% and multiplied by the fraction of the year (duration in years), which amounted to the delay. This share may be more than 1 if the delay is more than a year.
Duration of delay is expressed in days. In a more general case, the loan term can be expressed in days (the situation when the loan term is expressed in months and years is not considered here). And the annual rate of 3% is the normalized rate of simple interest for the year. Therefore, you need to be able to bring the duration of the delay (credit) in days to the duration in years. This is not a trivial task, since the length of the year depends on its number.
Duration in days is an unambiguously defined measure of the duration of calendar intervals, as opposed to duration in years. The definition of the latter requires clarification in the form of a rule for converting duration in days to duration in years. A number of standard methods for converting duration in days to duration in years is based on dividing the number of days by the so-called “annual divisor” . The most typical values of the annual divisor are 360 and 365 days. In the simplest cases, the divisor is a constant and interval-independent number. The number of days in a period always acts as a dividend (numerator of a fraction).
And you need to decide on the procedure for calculating the terms in days. Let's consider two far-fetched examples:
What is the difference?
The fact is that the day is not a point on the timeline, but a gap that can be written in two ways (I recall that the square bracket at the border of the interval means that the point is in the interval, the parenthesis is the point not in the interval):
In the first case (1), when the time interval is not given explicitly, but in the form of an interval between dates, the exact number of days is usually calculated, including the first or last day, but not both. [4] says the same thing in other words: "The day the loan is issued and the repayment day is considered one day."
This method determines the so-called exact time. It can also be easily determined if both dates refer to the same year and a calendar is available showing the serial number of each day of the year. Then it is enough to subtract the serial number of the early date from the serial number of the late date and the result will give the duration of the period. In Oracle Database, you can simply subtract dates (DATE type) truncated to a day (with time discarded), one from the other, the difference is an integer number of days.
In the second case (2) we are talking about days, so we count two days.
Further, I proceed from the fact that the number of days you have already been determined, for example, in the case of communal services, when the payment deadline is September 20 (i.e. this is the last acceptable payment day), and it was paid on September 21, i.e. it is obvious that the delay must be taken equal to 1 day. In case you are faced with an indication of the interval not including the left border - just move the left border one day to the right.
How do we count the number of days and the numerator of a fraction? There is an exact calculation and an approximate calculation.
The text is further based on [1].
The choice of the exact number of days in a period as a divisor, and the two most common rules give as the denominator of the annual divisor 360 or 365:
Rule (R1) ACT / 365. For this rule, the duration in days is divided by the number 365. I note that the length of a leap year will be 366/365 = 1.00274, which is more than one. The ACT / 365 rule error will be the greater, the more leap years in a period. Therefore, the value of 3% per annum accrued by this rule will be more than 3%.
Rule (R2) ACT / 360. This is the so-called “Banking rule”, according to which, the duration in days is divided by 360. This rule even more increases the annual length of the gaps. For a non-leap year, its length will be 365/360 = 1.01389, and for a leap year 366/360 = 1.01667. Naturally, the longer the period, the greater the degree of “elongation” in years. This rule is most often used in calculations related to the money market, i.e. market short-term debt, such as deposits with banks, bills, commercial papers, certificates of deposit, etc. The value of 3% per annum for the year will be even greater than when using the previous rule.
If the annual divisor is taken equal to 360 days, the interest earned using such a divisor is called ordinary , or commercial . If the divisor is accepted equal to 365 or, as I will show later, 366 days, the interest is called exact.
Obviously, ordinary percentages are more than accurate.
Although the first rule is more accurate than the second, both are not accurate enough.
To eliminate the effect of leap years on outcome, there are two other rules.
Rule (R3) ACT / 365, Japan. The calculation is performed in the same way as for the ACT / 365 rule, but when calculating the interval duration (fraction numerator), leap dates are excluded. The annual divisor remains unchanged.
According to Wiki:
Another way is related to the actual change of the divisor.
Rule (R4) ACT / ACT basic. We divide the desired period into three parts:
The durations of the first and third parts are obtained by dividing the number of days by the annual divisor 365 or 366, depending on whether it is a leap year? The duration of the second period consists of an integer number of full calendar years, by definition, regardless of whether they are leap or not. Duration in years of the entire period is obtained by summing the durations of three parts, represented by three numbers, of which the second is a whole, and the first and third are fractional.
Rule (R5) ACT / ACT “short” modification, for periods less than a year. In this case, the duration in days is divided by the annual divisor 365 or 366, depending on whether there is a date on February 29 in the search period? The peculiarity of the short ACT / ACT rule is that if the interval does not fit into the calendar year (its ends belong to neighboring years), then applying the basic and short rules will lead to different results.
In Western practice, the rules are described differently. Often the same Russian-language rules are given different names, and, moreover, not one at a time.
The information is further based on [2]. This is Vicki, I checked all the sources and used other google information: in rules with an exact number of days - everything is correct, but in rules with an approximate number of days there are other interpretations of the rules, and often they change over time, therefore, to put the rules into practice, the approximate number of days this article is not enough.
Rule (W1) Actual / Actual ISDA. This rule gives the same result as (R4) “ACT / ACT main”, but is formulated differently: the interval is divided into leap and non-leap years, regardless of whether they are full or incomplete, further, the sum of days falling into leap years, divided by 365, and the sum of days in non-leap years is divided by 366, the two numbers are summed.
Other names of the rule in Western practice: Actual / Actual, Act / Act, Actual / 365, Act / 365. Yes, the last name is the same as the rule (R1), although the essence of the rule is different.
Rule (W2) Actual / 365 Fixed. This rule exactly repeats the rule (R1) ACT / 365 - the number of days is divided by 365.
Other names of the rule in Western practice: Act / 365 Fixed, A / 365 Fixed, A / 365F, English.
Rule (W3) Actual / 360. This rule repeats (R2) ACT / 360 - the exact number of days is divided by 360.
Rule (W4) Actual / 364. The number of days is divided by 364. It has no analogue in Russian-speaking practice.
Rule (W5) Actual / 365L. It consists of two tweaks:
Another name is ISMA-Year.
Rule (W6) Actual / Actual AFB. It consists of two tweaks:
There are nuances associated with counting years ago, if the counting was carried out on February 28, and after a whole number of full years ago, the year turned out to be a leap year. In this case, the date n years ago should be adopted on February 29. I will add that the question also arises if the countdown was conducted from February 29, and the number of full years ago is not a multiple of four, and accordingly the previous year was not a leap year. I did not go into details here. But you can refer to the links from [2].
And “cherry on the cake,” Rule (W7) 1/1. The number of days is divided by the annual divisor 365.25. Used for calculations related to inflation.
The rules listed above were based on the exact duration in days of calendar periods. However, sometimes there are schemes based on the so-called simplified, or approximate, counting of days. The idea of these schemes is to “equalize” the duration of all months to 30 days. Thus, the year will consist of 12 months of 30 days, i.e. from 360 days. Therefore, the annual divisor (denominator of the fraction) for these rules will always be 360, and the numerator is calculated in a special way.
Since the exact number of days of a loan in most cases, but of course, not always, is more than approximate (as can be easily seen by determining the average number of days in a month for a year, which is 30.44), the method of calculating interest with the exact number of days of a loan usually gives greater growth than approximate. [four]
The simplest version of this rule, the so-called Basic Rule 30/360 , is described as follows (hereinafter the information on the book [1] is Russian-language practice):
The basic rule is 30/360. The approximate number of days between dates is
This rule appeared before the creation of the first computing devices and significantly saved the cost of computing operations in financial practice. Although now the need for simplified methods has disappeared, nevertheless, having gained a foothold in practice, by tradition they are sometimes used at present.
The rule applies with a mandatory indication of a modification that indicates how to handle the last days of the months:
Given by [1].
Rule (R6) 30/360 ISDA. If d1 = 31, then d1 '= 30, otherwise d1' = d1. If d2 = 31 and d1 '= 30, then d2' = 30, otherwise d2 '= d2.
Rule (R7) 30E / 360. If d1 = 31, then d1 '= 30, otherwise d1' = d1. If d2 = 31, then d2 '= 30, otherwise d2' = d2.
This rule is a variant of ISDA rule 30/360, used mainly in Europe, hence the label “E” in the name of the rule. It differs from ISDA rule 30/360 only when the second date is also the 31st. The European version always converts it to the 30th number, regardless of the first date.
Rule (R8) 30/360 PSA. If d1 = 31 or d1 is the last day of February, then d1 '= 30, otherwise d1' = d1. If d2 = 31 and d1 '= 30, then d2' = 30, otherwise d2 '= d2.
The description of the 30/360 SIA rule in [1] is most likely an error, so I will not give it here.
Given according to [2].
Rule (W8) 30/360 Bond Basis. Same as (R6) 30/360 ISDA. Other name: 30A / 360.
Rule (W9) 30E / 360. Same as (R7) 30E / 360. Other names: 30/360 ICMA, 30S / 360, Eurobond basis (ISDA 2006), Special German.
Rule (W10) 30E / 360 ISDA. If d1 is the last day of the month, then d1 '= 30. If d2 is the last day of NOT February, then d2 '= 30.
Other names: 30E / 360 ISDA, Eurobond basis (ISDA 2000), German.
Rule (W11) 30/360 SIA. If d1 is the last day of February and d2 is the last day of February, then d2 '= 30. If d1 = 31 or the last day of February, then d1 '= 30, otherwise d1' = d1. If d1 '= 30 and d2 = 31, then d2' = 30, otherwise d2 '= d1.
The rules (R4) ACT / ACT, (W1) Actual / Actual ISDA, (W2) Actual / 365 Fixed are also called “English Practice”.
Rules (R2) ACT / 360 and (W3) Actual / 360 are also called “French practice”.
Rule (R7) 30E / 360, (W9) 30E / 360 and (W10) 30E / 360 ISDA are also called “German Practice” or “German Practice”.
So, taking into account the exact and ordinary interest, as well as the exact and approximate number of days for calculating interest, we get four methods for calculating simple interest (the names of the rules are indicated in Russian-speaking practice):
The first method most commonly used is called the banking rule, less often the second and third, and almost never the fourth [1]. And [4] says: “... a calculation option with exact interest and an approximate number of loan days is meaningless and does not apply.”
The above calculations of the approximate duration of periods in fractions of a year show the non-triviality and variety of the applied rules. Their application (as well as origin) depends on the country, currency, type of financial instrument, professional association, agreements. [one]
OUTPUT:
How to count 3% in my case with public utilities in Ukraine?
Analysis of the judicial practice of applying Art. 625 of the Civil Code of Ukraine says that the method (R1) ACT / 365 [3] is usually used, but this is not clearly formulated. I think that you still need to apply (R4) ACT / ACT, and here's why: if you accrue 3% for a full leap year using the ACT / 365 method, then the interest amount will be equal to 3.008219%, which is for debt (or the size of the loan body) of 1 million monetary units will give an excess of CU 82.19 over 3%, which are stipulated by the annual standardized interest rate under the Law. This will become especially important with progressive interest calculation.
I would appreciate comments, especially from bank employees.
Used sources
1) Bocharov, P.P., Kasimov, Yu.F. Financial mathematics. Moscow: Gardariki, 2002
2) Day count convention, en.wikipedia.org/wiki/Day_count_convention
3) Analysis of the application of Art. 625 of the Civil Code of Ukraine in civil proceedings, zib.com.ua/ua/74602-analiz_praktiki_zastosuvannya_st_625_civilnogo_kodeksu_ukrai.html
4) Chetyrkin E.M. Financial math. Moscow: Case, 2005
5) Day Count Conventions and Accrual Factors, docs.fincad.com/support/developerfunc/mathref/Daycount.htm
6) Information letter of the Higher Economic Court of Ukraine No. 01-06 / 928/2012 dated July 17, 2012, zakon.rada.gov.ua/laws/show/v_928600-12
And now a little code on Oracle Database:
Here is the package I made for my particular case - he calculates inflation costs and 3% per annum for Ukraine.
There is no aggregate function in Oracle Database that multiplies inflation values. Therefore, I use the summation of logarithms.
The first part describes the calculation of inflation costs, this is specific to Ukraine.
The second (larger) part will be devoted to the issue of calculating 3% per annum. It is also applicable to the Russian Federation, Belarus and Kazakhstan, but in these countries the rate is not fixed, but depends on the Central Bank refinancing rate. The rest of the country did not look.
The second part is very close to the calculation of interest on ordinary credit transactions (loans, loans, borrowings, etc.).
First a theory, a little code at the end.
All this under the cut.
So, I got into the hands of a document from utilities "Statement of accrual of inflation costs and 3% per annum." It was checked by me, and I found out that it was calculated using the wrong algorithm and is of interest, since it must implement the requirements of the article of the Civil Code of Ukraine.
What should be considered is written in the Civil Code of Ukraine:
Article 625 of the Civil Code of Ukraine. “Responsibility for violation of a monetary obligation”Based on the debt figures, public utilities calculated inflation costs and 3% per annum.
2. A debtor who has delayed the fulfillment of a monetary obligation shall, at the request of the creditor, be obliged to pay the amount of the debt taking into account the established inflation index for the entire delay period, as well as 3% per annum of the overdue amount ...
Inflation costs
Further I will talk about the calculation algorithm, and not about specific numbers.
Inflation is a process characterized by a depreciation of the currency, that is, a decrease in its purchasing power and a general increase in prices. Excluding inflation, the final results of cash flow calculations are very arbitrary.
Inflation Index (II), or Consumer Price Index (CPI), Consumer Price Index (CPI) is one of the types of price indices created to measure the average price level for goods and services (consumer basket) for a certain period in the economy.
AI is an indicator characterizing the dynamics of the general price level for goods and services that the population acquires for non-productive consumption. This is the growth index, which shows how many percent the average price increase for the period under review.
In Ukraine, AI and CPI are synonymous
Since the price index is a chain index, for periods following one after another, it is calculated by multiplying the indicators, for example, 101% * 102% = (1.01 * 1.02) * 100% = 103.02%.
AI is calculated by the State Statistics Committee of Ukraine and published in official periodicals. These key figures can be used to recalculate cash amounts.
What do communal workers think?
The last settlement date is December 7, 2016.
Let's see how the communal workers got inflation cost ratios?
It can be seen with the naked eye that in the third (and fifth column), instead of the integer part 1, there should be the integer part 0. How did they get the fractional part?
September 2016. Fractional part 0.028. The payment term for September is October 20, 2016. The fractional part corresponds to the inflation index published by the State Statistics Committee, for October 2016 - 102.8%
August 2016. Fractional part 0.0465. The payment term for August is September 20, 2016. The fractional part corresponds to the inflation index for September multiplied by II for October: 101.8% * 102.8% = 104.65%, or in shares, 1.018 * 1.028 = 1.046504. Public utilities are rounded up to 1.0465 and print this number, although the unit should be taken away, because in the next column they print only the amount of inflation costs, and not the accumulated amount.
July 2016 and earlier. I performed these calculations and restored the algorithm. Public utilities multiply inflation indices, starting from the month following the month of service provision (month of the due date) and ending with October 2016.
The start point is only partially true. As I will show later, in the general case, the calculation really needs to start from this month, but in our particular case, the first month should be skipped (equate AI to 100%).
The end point of the multiplication. Since the last day of calculation is in December, and you need to finish December, this is in the general case. As I will show later, in this particular case, December also needs to be skipped (also equate AI to 100%). And on December 7, the December AIs did not have time to publish.
Why is November not taken into account anywhere? I think they just forgot to enter it into the system.
Now let's see how to calculate the inflation index. As necessary - described in the Information Letter of the Supreme Economic Court of Ukraine No. 01-06 / 928/2012 dated 17.07.2012 [6].
I will give part of the paragraph:
The amount of debt that must be paid from 1 to 15 days of the month is indexed taking into account this month, and if the amount of debt must be paid from 16 to 31 days of the month, the calculation starts from the next month.In our case, the payment term is the 20th day of the month following the month of the provision of services, i.e. for the provision of services in September, the start date of settlements is October 21, 2016, and the deadline is December 7, 2016.
Similarly, if a debt has been repaid from 1 to 15 days of a month, inflation costs are calculated without taking this month into account, and if from 16 to 31 days of a month, inflation costs are calculated taking into account this month.
Those. it’s correct to say this: for the provision of services in September 2016, consider inflation for three months (pass the cursor or cycle), from October to December, but in the first and last month, take AI equal to 1 (100%), and take into account only November.
In November 2016, AI amounted to 101.8%, i.e. the coefficient should be 0.0180, not 0.0280. I wrote about the lead unit before.
3% per annum
Let's see how the communal services got the coefficients and the amount of accrual of 3% per annum? This is the fifth column in the table in the figure above.
As I wrote earlier, there should not be a leading unit, there should be 0. We look at the fractional part, and notice that it is somehow suspiciously round - each previous month is 0.0025 more than the current month.
Using simple calculations, it can be determined that the public utilities, without further ado, divided 3% into 12 months, received 0.0025, or 0.25% per month, and accrue this percentage for each full and incomplete month of delay. Yes, if you look at October 2016, the payment deadline for which came on November 21, and the calculations were made on December 7, then for an incomplete month they counted the same 0.25%.
The approach admires brutal straightforwardness, but, as I will show later, financial mathematics does not know such an algorithm.
The legislation of Ukraine and financial mathematics do not give a clear answer to the question of how to charge 3% per annum? I have searched for several books on this subject, but books are ancient, dating back to 2002. Therefore, the algorithms from the books will be supplemented by the fact that it was possible to google from more modern and western articles. [1] was reprinted in 2007, and more recent Russian-language books could not be found. Perhaps because financial mathematics, as a science, originated a very long time ago, long before the advent of computers, concepts such as “credit” and “interest on credit” arose several hundred years ago. And the algorithms have not changed much since 2002.
But the Western view of the problem is different from that described in Russian-language books. I will cite information first from Russian-language books, and then from Western articles, indicating where they coincide.
What is written below relates to the specific case of accruing 3% per annum on debt, but can be generalized to the calculation of interest on other credit relations, if interest is accrued according to the simple interest rather than compound interest scheme.
According to Art. 625 Civil Code of Ukraine need to accrue 3% per year according to the simple interest scheme. To calculate the amount of interest, the amount of debt (or the body of the loan, in general) must be multiplied by the normalized annual interest rate of 3% and multiplied by the fraction of the year (duration in years), which amounted to the delay. This share may be more than 1 if the delay is more than a year.
Duration of delay is expressed in days. In a more general case, the loan term can be expressed in days (the situation when the loan term is expressed in months and years is not considered here). And the annual rate of 3% is the normalized rate of simple interest for the year. Therefore, you need to be able to bring the duration of the delay (credit) in days to the duration in years. This is not a trivial task, since the length of the year depends on its number.
Duration in days is an unambiguously defined measure of the duration of calendar intervals, as opposed to duration in years. The definition of the latter requires clarification in the form of a rule for converting duration in days to duration in years. A number of standard methods for converting duration in days to duration in years is based on dividing the number of days by the so-called “annual divisor” . The most typical values of the annual divisor are 360 and 365 days. In the simplest cases, the divisor is a constant and interval-independent number. The number of days in a period always acts as a dividend (numerator of a fraction).
And you need to decide on the procedure for calculating the terms in days. Let's consider two far-fetched examples:
- The client receives a short-term loan from the bank from December 6, 2018 to December 7, 2018. What is the term of the contract and how many days do I need to accrue interest? Obviously - 1 day.
- The subscriber pre-orders the service from the telecom operator, with a validity period in the future from December 6, 2018 to December 7, 2018. What are the lines of service provision and for how many days do I have to take a subscription fee for the service? Obviously 2 days.
What is the difference?
The fact is that the day is not a point on the timeline, but a gap that can be written in two ways (I recall that the square bracket at the border of the interval means that the point is in the interval, the parenthesis is the point not in the interval):
- [06.12.2018 00.00.00, 12/07/2018 00.00.00) - here the right border of the interval is not included in the interval,
- [06.12.2018 00.00.00, 12/06/2018 23.59.59] - here the right border of the interval is included in the interval.
In the first case (1), when the time interval is not given explicitly, but in the form of an interval between dates, the exact number of days is usually calculated, including the first or last day, but not both. [4] says the same thing in other words: "The day the loan is issued and the repayment day is considered one day."
This method determines the so-called exact time. It can also be easily determined if both dates refer to the same year and a calendar is available showing the serial number of each day of the year. Then it is enough to subtract the serial number of the early date from the serial number of the late date and the result will give the duration of the period. In Oracle Database, you can simply subtract dates (DATE type) truncated to a day (with time discarded), one from the other, the difference is an integer number of days.
In the second case (2) we are talking about days, so we count two days.
Further, I proceed from the fact that the number of days you have already been determined, for example, in the case of communal services, when the payment deadline is September 20 (i.e. this is the last acceptable payment day), and it was paid on September 21, i.e. it is obvious that the delay must be taken equal to 1 day. In case you are faced with an indication of the interval not including the left border - just move the left border one day to the right.
How do we count the number of days and the numerator of a fraction? There is an exact calculation and an approximate calculation.
Accurate counting of days
Russian-speaking practice
The text is further based on [1].
The choice of the exact number of days in a period as a divisor, and the two most common rules give as the denominator of the annual divisor 360 or 365:
Rule (R1) ACT / 365. For this rule, the duration in days is divided by the number 365. I note that the length of a leap year will be 366/365 = 1.00274, which is more than one. The ACT / 365 rule error will be the greater, the more leap years in a period. Therefore, the value of 3% per annum accrued by this rule will be more than 3%.
Rule (R2) ACT / 360. This is the so-called “Banking rule”, according to which, the duration in days is divided by 360. This rule even more increases the annual length of the gaps. For a non-leap year, its length will be 365/360 = 1.01389, and for a leap year 366/360 = 1.01667. Naturally, the longer the period, the greater the degree of “elongation” in years. This rule is most often used in calculations related to the money market, i.e. market short-term debt, such as deposits with banks, bills, commercial papers, certificates of deposit, etc. The value of 3% per annum for the year will be even greater than when using the previous rule.
If the annual divisor is taken equal to 360 days, the interest earned using such a divisor is called ordinary , or commercial . If the divisor is accepted equal to 365 or, as I will show later, 366 days, the interest is called exact.
Obviously, ordinary percentages are more than accurate.
Although the first rule is more accurate than the second, both are not accurate enough.
To eliminate the effect of leap years on outcome, there are two other rules.
Rule (R3) ACT / 365, Japan. The calculation is performed in the same way as for the ACT / 365 rule, but when calculating the interval duration (fraction numerator), leap dates are excluded. The annual divisor remains unchanged.
According to Wiki:
In many European countries, until the 18th century, February 29 was considered to be a non-existent day, a date without legal status. Often, transactions were not made that day, payments were not made, they were not given in debt, etc., due to the fact that there were difficulties with the formal side of the issue and the settlement of cases in court.
Another way is related to the actual change of the divisor.
Rule (R4) ACT / ACT basic. We divide the desired period into three parts:
- the first part, from the start date to the end of the year in which the start date is located,
- the third part - from the beginning of the year in which the end date is located, to the end date,
- the second part - 0 or several full calendar years between the first and third parts.
The durations of the first and third parts are obtained by dividing the number of days by the annual divisor 365 or 366, depending on whether it is a leap year? The duration of the second period consists of an integer number of full calendar years, by definition, regardless of whether they are leap or not. Duration in years of the entire period is obtained by summing the durations of three parts, represented by three numbers, of which the second is a whole, and the first and third are fractional.
Rule (R5) ACT / ACT “short” modification, for periods less than a year. In this case, the duration in days is divided by the annual divisor 365 or 366, depending on whether there is a date on February 29 in the search period? The peculiarity of the short ACT / ACT rule is that if the interval does not fit into the calendar year (its ends belong to neighboring years), then applying the basic and short rules will lead to different results.
Western practice
In Western practice, the rules are described differently. Often the same Russian-language rules are given different names, and, moreover, not one at a time.
The information is further based on [2]. This is Vicki, I checked all the sources and used other google information: in rules with an exact number of days - everything is correct, but in rules with an approximate number of days there are other interpretations of the rules, and often they change over time, therefore, to put the rules into practice, the approximate number of days this article is not enough.
Rule (W1) Actual / Actual ISDA. This rule gives the same result as (R4) “ACT / ACT main”, but is formulated differently: the interval is divided into leap and non-leap years, regardless of whether they are full or incomplete, further, the sum of days falling into leap years, divided by 365, and the sum of days in non-leap years is divided by 366, the two numbers are summed.
Other names of the rule in Western practice: Actual / Actual, Act / Act, Actual / 365, Act / 365. Yes, the last name is the same as the rule (R1), although the essence of the rule is different.
Rule (W2) Actual / 365 Fixed. This rule exactly repeats the rule (R1) ACT / 365 - the number of days is divided by 365.
Other names of the rule in Western practice: Act / 365 Fixed, A / 365 Fixed, A / 365F, English.
Rule (W3) Actual / 360. This rule repeats (R2) ACT / 360 - the exact number of days is divided by 360.
Rule (W4) Actual / 364. The number of days is divided by 364. It has no analogue in Russian-speaking practice.
Rule (W5) Actual / 365L. It consists of two tweaks:
- For annual payments. If the date is February 29th within the period, then the number of days in the period is divided by the annual divisor 366, if not, by 365. In this sense, it coincides with the R / ACT ACT / ACT “short modification” rule.
- If the frequency of payments is different from the annual. If the end date is in a leap year (and is not necessarily equal to February 29), then the annual divisor is 366, otherwise - 365. In Russian-speaking practice, it has no analogue.
Another name is ISMA-Year.
Rule (W6) Actual / Actual AFB. It consists of two tweaks:
- Main, for periods less than a year: If the date is February 29th within the period, then the number of days in the period is divided by the annual divisor 366, if not - by 365. In this sense, it also coincides with the R / ACT / ACT “short modification” rule (R5) .
- Extended, for periods of more than a year: an integer full year is counted back from the end date to the start date. For a balance of less than a year, the main rule applies. The number of full years and the share of the year under the basic rule are summarized.
There are nuances associated with counting years ago, if the counting was carried out on February 28, and after a whole number of full years ago, the year turned out to be a leap year. In this case, the date n years ago should be adopted on February 29. I will add that the question also arises if the countdown was conducted from February 29, and the number of full years ago is not a multiple of four, and accordingly the previous year was not a leap year. I did not go into details here. But you can refer to the links from [2].
And “cherry on the cake,” Rule (W7) 1/1. The number of days is divided by the annual divisor 365.25. Used for calculations related to inflation.
Estimated days count
The rules listed above were based on the exact duration in days of calendar periods. However, sometimes there are schemes based on the so-called simplified, or approximate, counting of days. The idea of these schemes is to “equalize” the duration of all months to 30 days. Thus, the year will consist of 12 months of 30 days, i.e. from 360 days. Therefore, the annual divisor (denominator of the fraction) for these rules will always be 360, and the numerator is calculated in a special way.
Since the exact number of days of a loan in most cases, but of course, not always, is more than approximate (as can be easily seen by determining the average number of days in a month for a year, which is 30.44), the method of calculating interest with the exact number of days of a loan usually gives greater growth than approximate. [four]
The simplest version of this rule, the so-called Basic Rule 30/360 , is described as follows (hereinafter the information on the book [1] is Russian-language practice):
The basic rule is 30/360. The approximate number of days between dates is
360 * (y2 - y1) + 30 * (m2 - m1) + (d2 - d1),where y, m, d - year, month and day in the second and first dates.
This rule appeared before the creation of the first computing devices and significantly saved the cost of computing operations in financial practice. Although now the need for simplified methods has disappeared, nevertheless, having gained a foothold in practice, by tradition they are sometimes used at present.
The rule applies with a mandatory indication of a modification that indicates how to handle the last days of the months:
Russian-speaking practice
Given by [1].
Rule (R6) 30/360 ISDA. If d1 = 31, then d1 '= 30, otherwise d1' = d1. If d2 = 31 and d1 '= 30, then d2' = 30, otherwise d2 '= d2.
Rule (R7) 30E / 360. If d1 = 31, then d1 '= 30, otherwise d1' = d1. If d2 = 31, then d2 '= 30, otherwise d2' = d2.
This rule is a variant of ISDA rule 30/360, used mainly in Europe, hence the label “E” in the name of the rule. It differs from ISDA rule 30/360 only when the second date is also the 31st. The European version always converts it to the 30th number, regardless of the first date.
Rule (R8) 30/360 PSA. If d1 = 31 or d1 is the last day of February, then d1 '= 30, otherwise d1' = d1. If d2 = 31 and d1 '= 30, then d2' = 30, otherwise d2 '= d2.
The description of the 30/360 SIA rule in [1] is most likely an error, so I will not give it here.
Western practice
Given according to [2].
Rule (W8) 30/360 Bond Basis. Same as (R6) 30/360 ISDA. Other name: 30A / 360.
Rule (W9) 30E / 360. Same as (R7) 30E / 360. Other names: 30/360 ICMA, 30S / 360, Eurobond basis (ISDA 2006), Special German.
Rule (W10) 30E / 360 ISDA. If d1 is the last day of the month, then d1 '= 30. If d2 is the last day of NOT February, then d2 '= 30.
Other names: 30E / 360 ISDA, Eurobond basis (ISDA 2000), German.
Rule (W11) 30/360 SIA. If d1 is the last day of February and d2 is the last day of February, then d2 '= 30. If d1 = 31 or the last day of February, then d1 '= 30, otherwise d1' = d1. If d1 '= 30 and d2 = 31, then d2' = 30, otherwise d2 '= d1.
The rules (R4) ACT / ACT, (W1) Actual / Actual ISDA, (W2) Actual / 365 Fixed are also called “English Practice”.
Rules (R2) ACT / 360 and (W3) Actual / 360 are also called “French practice”.
Rule (R7) 30E / 360, (W9) 30E / 360 and (W10) 30E / 360 ISDA are also called “German Practice” or “German Practice”.
So, taking into account the exact and ordinary interest, as well as the exact and approximate number of days for calculating interest, we get four methods for calculating simple interest (the names of the rules are indicated in Russian-speaking practice):
- Normal interest with the exact number of days (ACT / 360 - banking rule),
- Exact percentages with the exact number of days (ACT / 365, ACT / ACT),
- Normal interest with an approximate number of days (30/360),
- Exact percentages with an approximate number of days.
The first method most commonly used is called the banking rule, less often the second and third, and almost never the fourth [1]. And [4] says: “... a calculation option with exact interest and an approximate number of loan days is meaningless and does not apply.”
The above calculations of the approximate duration of periods in fractions of a year show the non-triviality and variety of the applied rules. Their application (as well as origin) depends on the country, currency, type of financial instrument, professional association, agreements. [one]
OUTPUT:
How to count 3% in my case with public utilities in Ukraine?
Analysis of the judicial practice of applying Art. 625 of the Civil Code of Ukraine says that the method (R1) ACT / 365 [3] is usually used, but this is not clearly formulated. I think that you still need to apply (R4) ACT / ACT, and here's why: if you accrue 3% for a full leap year using the ACT / 365 method, then the interest amount will be equal to 3.008219%, which is for debt (or the size of the loan body) of 1 million monetary units will give an excess of CU 82.19 over 3%, which are stipulated by the annual standardized interest rate under the Law. This will become especially important with progressive interest calculation.
I would appreciate comments, especially from bank employees.
Used sources
1) Bocharov, P.P., Kasimov, Yu.F. Financial mathematics. Moscow: Gardariki, 2002
2) Day count convention, en.wikipedia.org/wiki/Day_count_convention
3) Analysis of the application of Art. 625 of the Civil Code of Ukraine in civil proceedings, zib.com.ua/ua/74602-analiz_praktiki_zastosuvannya_st_625_civilnogo_kodeksu_ukrai.html
4) Chetyrkin E.M. Financial math. Moscow: Case, 2005
5) Day Count Conventions and Accrual Factors, docs.fincad.com/support/developerfunc/mathref/Daycount.htm
6) Information letter of the Higher Economic Court of Ukraine No. 01-06 / 928/2012 dated July 17, 2012, zakon.rada.gov.ua/laws/show/v_928600-12
And now a little code on Oracle Database:
Here is the package I made for my particular case - he calculates inflation costs and 3% per annum for Ukraine.
There is no aggregate function in Oracle Database that multiplies inflation values. Therefore, I use the summation of logarithms.
Hidden text
CREATE OR REPLACE TYPE DEBT_OVERHEAD_T as object (ID number, DEBT_DATE date, OVERHEAD_AMOUNT number); CREATE OR REPLACE TYPE DEBT_WITH_INF_AMOUNT_T as object (ID number, DEBT_DATE date, DEBT_AMOUNT number, PAYMENT_DATE date, INF_MONTH date, INF_RATE number, SKIP_INFLATION char (1), INF_RATE_ACCUMULATED number , INF_COEF_ACCUMULATED number, INF_AMOUNT_ACCUMULATED number, INF_AMOUNT_MONTH number); CREATE OR REPLACE TYPE DEBT_WITH_INF_T as object (ID number, DEBT_DATE date, DEBT_AMOUNT number, PAYMENT_DATE date, INF_MONTH date, INF_RATE number, SKIP_INFLATION char (1)); CREATE OR REPLACE TYPE DEBT_WITH_PCT_AMOUNT_T as object (ID number, DEBT_DATE date, DEBT_AMOUNT number, PAYMENT_DATE date, PCT_YEAR number, PCT_YEAR_DAYS number, PCT_YEAR_PCT_PER_DAY number, PCT_YEAR_BEGIN_DATE date , PCT_YEAR_END_DATE date, PCT_YEAR_DAYS_CALC number, PCT_YEAR_PCT_PER_YEAR number, PCT_AMOUNT_PER_YEAR number); CREATE OR REPLACE TYPE DEBT_OVERHEAD_LIST_T as table of DEBT_OVERHEAD_T; CREATE OR REPLACE TYPE DEBT_WITH_INF_AMOUNT_LIST_T as table of DEBT_WITH_INF_AMOUNT_T; CREATE OR REPLACE TYPE DEBT_WITH_INF_LIST_T as table of DEBT_WITH_INF_T; CREATE OR REPLACE TYPE DEBT_WITH_PCT_AMOUNT_LIST_T as table of DEBT_WITH_PCT_AMOUNT_T; CREATE OR REPLACE PACKAGE CALC_OVERHEADS_P is function GET_DEBT_WITH_INF (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_WITH_INF_LIST_T; function GET_DEBT_WITH_INF_AMOUNT (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_WITH_INF_AMOUNT_LIST_T; function GET_DEBT_WITH_INF_AMOUNT_TOTAL (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_OVERHEAD_LIST_T; function GET_DEBT_WITH_PCT_AMOUNT (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_WITH_PCT_AMOUNT_LIST_T; function GET_DEBT_WITH_PCT_AMOUNT_TOTAL (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_OVERHEAD_LIST_T; end; / CREATE OR REPLACE PACKAGE BODY CALC_OVERHEADS_P is function GET_DEBT_WITH_INF (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_WITH_INF_LIST_T is ret_value DEBT_WITH_INF_LIST_T; begin with T1 as (select a.ID, a.DEBT_DATE, a.DEBT_AMOUNT, a.PAYMENT_DATE, b.INF_MONTH, c.AVAL as INF_RATE from DEBTS a cross apply (select add_months (trunc (a.PAYMENT_DATE, 'month'), rownum - 1) as INF_MONTH from dual connect by level <= months_between (trunc (p_last_collection_date, 'month'), trunc (a.PAYMENT_DATE, 'month')) + 1) b left join INFLATION c on c.ADATE = b.INF_MONTH and c.TYP = 'M' where a.DEBT_DATE between p_debt_date_begin and p_debt_date_end ) , T2 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, INF_MONTH, INF_RATE , case when trunc (PAYMENT_DATE, 'month') = INF_MONTH and extract (day from PAYMENT_DATE) between 16 and 31 then 'Y' when trunc (p_last_collection_date, 'month') = INF_MONTH and extract (day from p_last_collection_date) between 1 and 15 then 'Y' end as SKIP_INFLATION from T1) select DEBT_WITH_INF_T (ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, INF_MONTH, INF_RATE, SKIP_INFLATION) bulk collect into ret_value from T2; return ret_value; end; function GET_DEBT_WITH_INF_AMOUNT (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_WITH_INF_AMOUNT_LIST_T is ret_value DEBT_WITH_INF_AMOUNT_LIST_T; begin with T1 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, INF_MONTH, INF_RATE, SKIP_INFLATION , exp (sum (ln (case when SKIP_INFLATION = 'Y' or INF_RATE is null then 1 else INF_RATE / 100 end)) over (partition by ID order by INF_MONTH rows between unbounded preceding and current row)) * 100 as INF_RATE_ACCUMULATED from table (CALC_OVERHEADS_P.GET_DEBT_WITH_INF (p_debt_date_begin, p_debt_date_end, p_last_collection_date))) , T2 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, INF_MONTH, INF_RATE, SKIP_INFLATION, INF_RATE_ACCUMULATED , (INF_RATE_ACCUMULATED - 100) / 100 as INF_COEF_ACCUMULATED , round (((INF_RATE_ACCUMULATED - 100) / 100) * DEBT_AMOUNT, 2) as INF_AMOUNT_ACCUMULATED from T1) select DEBT_WITH_INF_AMOUNT_T (ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, INF_MONTH, INF_RATE, SKIP_INFLATION, INF_RATE_ACCUMULATED, INF_COEF_ACCUMULATED, INF_AMOUNT_ACCUMULATED , INF_AMOUNT_ACCUMULATED - lag (INF_AMOUNT_ACCUMULATED, 1, 0) over (partition by ID order by INF_MONTH)) bulk collect into ret_value from T2; return ret_value; end; function GET_DEBT_WITH_INF_AMOUNT_TOTAL (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_OVERHEAD_LIST_T is ret_value DEBT_OVERHEAD_LIST_T; begin with T1 as (select ID , DEBT_DATE , round ((exp (sum (ln (case when SKIP_INFLATION = 'Y' then 1 else INF_RATE / 100 end))) - 1) * DEBT_AMOUNT, 2) as INF_AMOUNT from table (CALC_OVERHEADS_P.GET_DEBT_WITH_INF (p_debt_date_begin, p_debt_date_end, p_last_collection_date)) group by ID, DEBT_DATE, DEBT_AMOUNT) select DEBT_OVERHEAD_T (ID, DEBT_DATE, INF_AMOUNT) bulk collect into ret_value from T1; return ret_value; end; function GET_DEBT_WITH_PCT_AMOUNT (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_WITH_PCT_AMOUNT_LIST_T is ret_value DEBT_WITH_PCT_AMOUNT_LIST_T; begin with T1 as (select * from DEBTS a cross apply (select extract (year from PAYMENT_DATE) + level - 1 as PCT_YEAR from dual connect by level <= extract (year from p_last_collection_date) - extract (year from PAYMENT_DATE) + 1) where DEBT_DATE between p_debt_date_begin and p_debt_date_end ) , T2 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, PCT_YEAR, to_date ('31.12.'||PCT_YEAR, 'DD.MM.YYYY') - to_date ('01.01.'||PCT_YEAR, 'DD.MM.YYYY') + 1 as PCT_YEAR_DAYS from T1) , T3 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, PCT_YEAR, PCT_YEAR_DAYS , 0.03 / PCT_YEAR_DAYS as PCT_YEAR_PCT_PER_DAY from T2) , T4 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, PCT_YEAR, PCT_YEAR_DAYS, PCT_YEAR_PCT_PER_DAY , greatest (PAYMENT_DATE + 1, to_date ('01.01.'||PCT_YEAR, 'DD-MM-YYYY')) as PCT_YEAR_BEGIN_DATE , least (to_date ('31.12.'||PCT_YEAR, 'DD-MM-YYYY'), p_last_collection_date) as PCT_YEAR_END_DATE from T3) , T5 as (select ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, PCT_YEAR, PCT_YEAR_DAYS, PCT_YEAR_PCT_PER_DAY, PCT_YEAR_BEGIN_DATE, PCT_YEAR_END_DATE , PCT_YEAR_END_DATE - PCT_YEAR_BEGIN_DATE + 1 as PCT_YEAR_DAYS_CALC , (PCT_YEAR_END_DATE - PCT_YEAR_BEGIN_DATE + 1) * PCT_YEAR_PCT_PER_DAY as PCT_YEAR_PCT_PER_YEAR , round ((PCT_YEAR_END_DATE - PCT_YEAR_BEGIN_DATE + 1) * PCT_YEAR_PCT_PER_DAY * DEBT_AMOUNT, 2) as PCT_AMOUNT_PER_YEAR from T4) select DEBT_WITH_PCT_AMOUNT_T (ID, DEBT_DATE, DEBT_AMOUNT, PAYMENT_DATE, PCT_YEAR, PCT_YEAR_DAYS, PCT_YEAR_PCT_PER_DAY, PCT_YEAR_BEGIN_DATE, PCT_YEAR_END_DATE, PCT_YEAR_DAYS_CALC, PCT_YEAR_PCT_PER_YEAR, PCT_AMOUNT_PER_YEAR) bulk collect into ret_value from T5; return ret_value; end; function GET_DEBT_WITH_PCT_AMOUNT_TOTAL (p_debt_date_begin date, p_debt_date_end date, p_last_collection_date date) return DEBT_OVERHEAD_LIST_T is ret_value DEBT_OVERHEAD_LIST_T; begin select DEBT_OVERHEAD_T (ID, DEBT_DATE, sum (PCT_AMOUNT_PER_YEAR)) bulk collect into ret_value from table (CALC_OVERHEADS_P.GET_DEBT_WITH_PCT_AMOUNT (p_debt_date_begin, p_debt_date_end, p_last_collection_date)) group by ID, DEBT_DATE; return ret_value; end; end;
All Articles