I took a copy of the spreadsheet you shared in the blog and modified it with my data. I get the same result as in my own Excel, but not the same result I see in Capitally. Hope this helps. This stuff is fascinating
Capitally calculates this a little differently. The money invested is assumed at the beginning of the day. The investment value is taken at end of day. So if you look at the period of 2023-01-05 : 2023-01-05 it will be a period of 1 day, while the spreadsheet approach would have a period of 0 days.
The day period taken for annualization was wrong by one day - the above example period would be 2 days (as it wrongly included the base date 2023-01-04 : 2023-01-05. It’s now fixed in version 1.36.2. (it includes Splits on Funds as well).
Capitally uses 364.25 as an average amount of days in a year for annualization
So to get the same result, you would need to use firstTransactionDate - 1 and 364.25 for the length of year. I think that yearFrac takes into account lengths of years between the dates.
It’s indeed fascinating. I’m constantly balancing between typical standard formulas and what actually makes sense when having a more complex portfolio, instead of an isolated case.
I updated the spreadsheet. Now using 121 days instead of 120. And using 364.25 as days in year. That gives me 58.85%. Capitally is still at 58.45% - no change there from last time - should there not have been a change with the fix you made?
Instead of 364.25, should it not use 365.25, because a normal year is 365 days and every fourth year is a leap year with 366 days, so the average will be 365.25.
My bad, it does use 365.25. I’ll correct it on the blog, as 365.25 is calendar-based, while 365.2425 is more accurate scientifically, but actually less-so if you annualize.
I just checked the code, and I’ve fixed all annualized metrics apart from ROI and TWR By the way, IRR was always correct in this regard.
I’ve just pushed a new version 1.36.3 - it shows 59.05% for annualized ROI.
Juggling all these numbers and complexity may be sometimes confusing. Many thanks for being patient and sharing the test case
Sorry for my delay in reply. I didn’t get the time to look at this until now. Yes, now it matches! Both ROI for period and per annum now matches my spreadsheet. Same for TWR.
What do you use for MWR? Is that an implementation that tries to replicate XIRR() in Excel? Do you have any plans to make Modified Dietz available?
MWR is calculated by converging on a single solution in a loop - which is roughly what XIRR does. The Newton-Raphson method is used to converge quicker with some slight modifications to quickly catch edge-cases that don’t produce a valid result.
I found Modified-Dietz to be highly inaccurate in all tested edge-cases. From my understanding, it’s mostly used when iterative approaches like above are not feasable.
By the way, IRR was always correct in this regard.