nicknicknickandnick
New Member
- Joined
- Apr 29, 2002
- Messages
- 29
I was reading about simple interest and how the time as expressed in years has many alternative forms of expression. (For some detail, see http://en.wikipedia.org/wiki/Day_count_convention.)
In particular, I became interested in alternative ways to express what is called the actual/actual form, and wanted to know which of these Excel used when "basis 1" is chosen in functions such as ACCRINTM() and YEARFRAC(). My searching turned up nothing as far as how Excel's basis 1 actual/actual works, so I spent much time figuring it out. I conclude that Excel uses a form of actual/actual that I can not find a description or name for anywhere. I wonder if this form is unique to Excel, or is an actual (but seemingly undescribed on the internet) standard.
First, the principal reference to understand the three predominant forms of actual/actual is at http://www.isda.org/c_and_a/pdf/mktc1198.pdf. These forms are identified by organizations that use them: ISDA, ISMA, and AFB, named for the International Swaps and Derivatives Association, the International Securities Market Association, and the Association Française des Banques.
I made my own notes on these methods in order to understand them better. I also made notes on how Excel's basis 1 works. I decided since I couldn't find this Excel information anywhere, I may as well post it here. Here are the notes about Excel's basis 1 form of actual/actual:
Excel Basis 1 Form
Excel's basis 1 appears to be most similar to the AFB method, but more complicated, and does not ignore the ending date for purposes of figuring the denominator of the fraction that represents the number of days in year units.
To figure the numerator of the fraction, Excel counts the number of days between two dates in a standard manner by subtracting numbers that represent the dates so that the number of days is one day less than the number of days that would be counted if all the days were counted from the starting date to the ending date inclusively. However, for figuring the denominator, Excel does not ignore any day. Both the starting date and the ending date are as significant as the dates in between, and may affect the result when occurring on January 1, December 31, or February 29, depending on the length of the time period and on what year or years the dates occur in. (When I refer to the "time period", I mean every date from the starting date to the ending date, inclusive. When I refer to the length of the time period, I mean the number of days between the starting and ending dates as counted according to standard procedure. So the time period from January 1 to January 5 includes all five of January 1, 2, 3, 4, and 5, but the length of the time period is 5 - 1 = 4 days.)
If the time period is entirely within a year having 365 days, then basis 1 uses 365 days for the denominator. If the time period is entirely within a year having 366 days, then basis 1 uses 366 days for the denominator, regardless of whether or not February 29 is within the period.
If the length of the time period is no greater than 365 days and the time period is partly in a year with 365 days and partly in a year with 366 days but does not include February 29, then basis 1 uses 365 days for the denominator. If the length of the time period is no greater than 366 days and the time period is partly in a year with 365 days and partly in a year with 366 and does include February 29, then basis 1 uses 366 days for the denominator.
If the length of the time period is greater than 365 days and the time period is only in years with 365 days, then basis 1 uses 365 days for the denominator.
If the length of the time period is greater than 365 days and the time period does not include February 29, or if the length of the time period is greater than 366 days and the time period does include February 29; and the time period is partly in year(s) with 365 days and partly in year(s) with 366 days, then basis 1 uses the arithmetic mean of 365s and 366s for the denominator, weighted according to how many of each kind of year the time period is part of. So, when the period is partly in one year with 365 days and partly in one year with 366 days, basis 1 uses (365 + 366)/2 = 365.5. For another example, when the period is partly in two years with 365 days each and partly in one year with 366 days, basis 1 uses (2*365 + 366)/3 = 365.3333333333. The weighting for 365 and 366 is not related to the proportion of days of the time period that are actually in the respective years. For instance, if a time period of 400 days has 360 days in a year having 365 days and 40 days in a year having 366 days, basis 1 ignores the fact that the time period has more days in the year having 365 days, and simply uses (365 + 366)/2 = 365.5.
In particular, I became interested in alternative ways to express what is called the actual/actual form, and wanted to know which of these Excel used when "basis 1" is chosen in functions such as ACCRINTM() and YEARFRAC(). My searching turned up nothing as far as how Excel's basis 1 actual/actual works, so I spent much time figuring it out. I conclude that Excel uses a form of actual/actual that I can not find a description or name for anywhere. I wonder if this form is unique to Excel, or is an actual (but seemingly undescribed on the internet) standard.
First, the principal reference to understand the three predominant forms of actual/actual is at http://www.isda.org/c_and_a/pdf/mktc1198.pdf. These forms are identified by organizations that use them: ISDA, ISMA, and AFB, named for the International Swaps and Derivatives Association, the International Securities Market Association, and the Association Française des Banques.
I made my own notes on these methods in order to understand them better. I also made notes on how Excel's basis 1 works. I decided since I couldn't find this Excel information anywhere, I may as well post it here. Here are the notes about Excel's basis 1 form of actual/actual:
Excel Basis 1 Form
Excel's basis 1 appears to be most similar to the AFB method, but more complicated, and does not ignore the ending date for purposes of figuring the denominator of the fraction that represents the number of days in year units.
To figure the numerator of the fraction, Excel counts the number of days between two dates in a standard manner by subtracting numbers that represent the dates so that the number of days is one day less than the number of days that would be counted if all the days were counted from the starting date to the ending date inclusively. However, for figuring the denominator, Excel does not ignore any day. Both the starting date and the ending date are as significant as the dates in between, and may affect the result when occurring on January 1, December 31, or February 29, depending on the length of the time period and on what year or years the dates occur in. (When I refer to the "time period", I mean every date from the starting date to the ending date, inclusive. When I refer to the length of the time period, I mean the number of days between the starting and ending dates as counted according to standard procedure. So the time period from January 1 to January 5 includes all five of January 1, 2, 3, 4, and 5, but the length of the time period is 5 - 1 = 4 days.)
If the time period is entirely within a year having 365 days, then basis 1 uses 365 days for the denominator. If the time period is entirely within a year having 366 days, then basis 1 uses 366 days for the denominator, regardless of whether or not February 29 is within the period.
If the length of the time period is no greater than 365 days and the time period is partly in a year with 365 days and partly in a year with 366 days but does not include February 29, then basis 1 uses 365 days for the denominator. If the length of the time period is no greater than 366 days and the time period is partly in a year with 365 days and partly in a year with 366 and does include February 29, then basis 1 uses 366 days for the denominator.
If the length of the time period is greater than 365 days and the time period is only in years with 365 days, then basis 1 uses 365 days for the denominator.
If the length of the time period is greater than 365 days and the time period does not include February 29, or if the length of the time period is greater than 366 days and the time period does include February 29; and the time period is partly in year(s) with 365 days and partly in year(s) with 366 days, then basis 1 uses the arithmetic mean of 365s and 366s for the denominator, weighted according to how many of each kind of year the time period is part of. So, when the period is partly in one year with 365 days and partly in one year with 366 days, basis 1 uses (365 + 366)/2 = 365.5. For another example, when the period is partly in two years with 365 days each and partly in one year with 366 days, basis 1 uses (2*365 + 366)/3 = 365.3333333333. The weighting for 365 and 366 is not related to the proportion of days of the time period that are actually in the respective years. For instance, if a time period of 400 days has 360 days in a year having 365 days and 40 days in a year having 366 days, basis 1 ignores the fact that the time period has more days in the year having 365 days, and simply uses (365 + 366)/2 = 365.5.
