Question regarding Excel Function ACCRINT

Question

https://support.office.com/en-us/article/ACCRINT-function-fe45d089-6722-4fb3-9379-e1f911d8dc74

(All "official" refereed to in this passage is talking about information on this page)

ACCRINT requires input of first_interest, which it explains as "The security's first interest date". This is a bit ambiguous as I am not sure whether it is "the date from which interest starts to accumulate" or "the date on which first interest payment is made." 

Based on the official example, I tend to think that it refers to "the date on which first interest payment is made". But for an ordinary bond, the first interest payment is just issue date + 1 year/frequency, so it seems this is unnecessary. 

Using the official example, I have below questions:

1. The cal_method argument said that it sepecifies the way to calculate total accrued interest when the date of settlement is later thanthe first_interest, but in this example, the settlement date is beforethe first_interest, why bother using “FALSE”/”TRUE” for cal_method in the second and thrid formula?
2. I tested by changing the settlement date to 2008/09/10 so that it is later than first_interst. But when I set cal_method to “TRUE”, the return is 52.778, which is 190 days of coupon (out of 360 days), this is not consistent with the description that “the accrued interest is calculated from the first_interest to settlement” ( as the first_interest is 2008/08/31). Then when I set cal_method to “FALSE”, the return is 52.5, equaling 189 days of coupon. To me, it looks like this has something to do with day-count convention: i,e, whether we consider a bond that is issued on 1^st^ day of March to pay first_interest on 31th of Aug or 1^st^ of Sep.
3. I further tested other data, and found out that if the first_interest is not on 31th day of a month (or 1^st^ of next month of such month that has 31 days), it won’t affect the result at all.
4. The accrued interest is roughly the number of days multiplied by interest per day by this function, so I don’t see how frequency would affect the result (I tested, it didn’t affect my result). Normally accrued interest should be the accumulated interest from last interest payment to settlement date. So I am wondering if there is a bug about this.

Thank you for your patience in reading through this thread. Appreciate if you could shed some light on. Thank you.

Answer 1

Carol wrote:

ACCRINT requires input of first_interest, which it explains as "The security's first interest date". This is a bit ambiguous [....I]n this example, the settlement date is beforethe first_interest, why bother using “FALSE”/”TRUE” for cal_method in the second and thrid formula? [....] Appreciate if you could shed some light on. [....] I am really quite confused

Your confusion and doubts are well-founded.  Excel help pages are rife with misstatements and poor examples.  I often perform tests in order to vet information and gain necessary insight.

Moreover, ACCRINT was originally provided in the Analysis ToolPak in Excel 2003 and perhaps earlier.  Typically, these are contributed by non-MSFT employees, and it appears that MSFT did not test and revise them when it incorporated them as standard functions in Excel 2007.  For example, IPMT(...,1) produces different results than we would obtain using FV(...,1).

That said, bear in mind that this is a peer-to-peer forum.  Almost none of us have any knowledge of the internal implementation.  And I tend to not trust those who claim to be MSFT employees. Most are third-party contractors.  Certainly take anything that I say with a grain of salt.

Carol wrote:

ACCRINT requires input of first_interest [....] I am not sure whether it is "the date from which interest starts to accumulate" or "the date on which first interest payment is made." [....] I tend to think that it refers to "the date on which first interest payment is made". But for an ordinary bond, the first interest payment is just issue date + 1 year/frequency, so it seems this is unnecessary.       

I agree with your interpretation.  I  would call the parameter "first coupon date".  However, I disagree with your conclusion that the parameter is redundant. 

First, you are ignoring the possibility of an odd first period.  See the following web page for an explanation:   http://help.derivativepricing.com/1318.htm.  I am not a bond expert; I prefer to invest in diversified bond funds instead of individual bonds.  But for original issue bonds, I can imagine that odd periods arise due to accounting practices, aligning coupon dates with fiscal periods.

Second, you are ignoring the possibility of selling a bond to a third party.  I believe the first parameter should be called "dated date", or "ownership date" in the vernacular.  Usually, that is the issue date when the bond is first sold by a company or government and when the bond is sold to a second party.  However, I believe there can be significant difference if the bond is sold again to a third party (e.g. a private sale).  In that case, I believe the "ownership date" would be the second party's settlement date, which might have no relationship to the original issue date and the first coupon date.

Also, see the following web page for an explanation: definition of "accrued interest"and "dated date" in https://www.treasurydirect.gov/indiv/research/res_glossary/glossary.htm.

Carol wrote:

I tested by changing the settlement date [....] I further tested other data, and found out [...] it won’t affect the result at all.

I have not had time to vet your conclusions.  But for testing purposes, I suggest using an annual rate of 3.65%, a day count basis of actual/365 (3), avoiding dates in leap years, and a par value of 10,000.  Thus, the daily interest amount is 1, the same as the day count.  That should make it easy to see correlations (or not).

Carol wrote:

The accrued interest is roughly the number of days multiplied by interest per day by this function, so I don’t see how frequency would affect the result

You might be right.  The day count basis should be sufficient to convert the annual rate to a daily rate.

Arguably, the coupon frequency might be needed to calculate to the "nominal first coupon date" (see the derivativepricing.com web page).  I have not yet "internalized" the significance of that calculation, yet.  It is a new one for me.

Off-hand, IMHO, it should be sufficient to know the "ownership date" (first parameter) and "first coupon date" (second parameter).  Together with the day count basis, that should be sufficient to calculate accrued interest under all conditions.  But again, I have not had time to vet the combination of parameters myself.

Hope this helps a little insofar as it confirms that you are right to question the description and even the behavior of this function.

Answer 2

Further, the Cal_method says "A value of TRUE (1) returns the total accrued interest from issue to settlement. A value of FALSE (0) returns the accrued interest from first_interest to settlement.". But in the example, the 3rd formula uses input of "TRUE" while the description says that the accrued interest is calculated from the first_interest to settlement. So I am really quite confused...