123 reputation
5
bio website
location
age
visits member for 1 year, 8 months
seen Jan 28 '13 at 14:07

Jan
28
comment What's the algorithm behind Excel's ACCRINT?
It's all about the community...
Jan
28
comment What's the algorithm behind Excel's ACCRINT?
I did not have enough reputation to upvote, but now I do. Done. Thanks again.
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
Awesome find! I think this will help a lot, and the license is compatible with what I'm doing (Creative Commons Attribution). Thank you very much!
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
I totally agree with you. I'll document whatever I find so that we can clarify all this. Thanks for your help, much appreciated.
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
Sorry, I misinterpreted what you wrote before. You wrote that the "function" that Excel uses is right, not its "implementation". My bad. And yes indeed, copying the wrong approach might be wrong to begin with, but then who can you trust, because as far as ACCRINT is concerned, Excel, Google Spreadsheets, Numbers, Gnumeric, and OpenOffice Calc all return different results. Does it mean ACCRINT should be banned? And if so, what's the alternative? Where can I find an authoritative source that properly defines with pseudocode how such a function should be implemented?
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
I totally agree with you that a proper function for calculating accrued interests should take holidays into account. But that's not what I'm trying to build. Libraries like QuantLib do that really well. What I'm trying to build is a faithful clone of Excel's ACCRINT, covering all possible combinations of parameters, including those that might not make any sense from a purely financial standpoint. And because ACCRINT is not properly documented, it's quite a challenging reverse-engineering exercise...
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
I really don't think Excel uses holiday schedules for ACCRINT. In fact, I'm willing to bet pretty much anything that it does not.
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
And you're right, Excel is 100% correct, because Excel defines the absolute truth as far as re-implementing Excel's function goes. It does not matter so much what Excel attempted to do in the first place. Since it's used by the majority of users for doing financial computations, as far as we're concerned, it's the truth, whether it makes sense or not...
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
It does not, because Excel does not either. Holiday schedules with Excel have to be explicitly specified, as does NETWORKDAYS for example.
Jan
22
comment What's the algorithm behind Excel's ACCRINT?
The problem is not with the day count convention. Excel's implementation of ACCRINT is based on its implementation of YEARFRAC. We implemented YEARFRAC from David Wheeler's pseudocode, which was validated with over 32 millions tests. We're pretty confident about this one. The issue is that nobody seems to really understand what the first_interest parameter is supposed to do. As far as the required values come from, they're extracted from Excel (Win, Mac, and Web versions are all consistent with each other).
Jan
21
comment What's the algorithm behind Excel's ACCRINT?
Thanks! I'll take a look. Regarding proper day counting, I implemented David Wheeler's algorithm, which seems to work: stoic.com/formula/function.html?name=YEARFRAC
Jan
21
comment What's the algorithm behind Excel's ACCRINT?
Yes, indeed, but nobody seems to understand how to implement it, and the formula does not take into account a lot of corner cases. Implementing the day counting convention in the context of the calculation of the accrued interest (not just calculating a YEARFRAC) seems to be the challenge.