123 reputation
6
bio website
location
age
visits member for 1 year, 11 months
seen Dec 6 at 23:31

Big Data Spreadsheet Developer


Dec
8
awarded  Autobiographer
Jan
28
comment What's the algorithm behind Excel's ACCRINT?
It's all about the community...
Jan
28
awarded  Supporter
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
awarded  Scholar
Jan
22
accepted What's the algorithm behind Excel's ACCRINT?
Jan
22
awarded  Commentator
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
awarded  Editor
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
revised What's the algorithm behind Excel's ACCRINT?
added 1093 characters in body
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.
Jan
21
awarded  Student