Report Calculations
Is it possible to see the math behind the calculations of the various columns in the Investment Performance Report?
Showing page 2 out of 2. View the first page
Comments are currently closed for this discussion. You can start a new one.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
31 Posted by Ian L on Jun 19, 2015 @ 06:43 PM
"Semi-actively" means I'm working on it at the same time as other issues while also doing my main job, which is customer support. It's not the same as not working on it at all. I guess I could have just said "actively" and saved the confusion.
That said, I think I've fixed the ROI bug in build 1235, which which is now available on the preview site . I'm still looking at edge cases that are harder to test in simple test files, so I'd love for you all to take a look and let me know how it looks to you.
Still working on the cost basis reported on the Capital Gains report.
s©tt, Moneydance has had the same lead developer since it was founded, along with a rotating cast of supporting developers. So, yes, your assumption is wrong. I would appreciate it if you could stop making assumptions about the people who work here and how we work internally. I believe Ben has made this request of you before, and I'd like to make it again.
Ian
Infinite Kind Support
32 Posted by JFG on Jun 19, 2015 @ 07:30 PM
Thanks for the response Ian. Rather than being defensive, you might start trying to be more open and inclusive with your user community. Despite YOUR assumptions, I am a big supporter of Moneydance and have been using it - and buying upgrades - for many years. That's the reason I am immensely frustrated at how your team has turned out "upgrades" for the past couple of years which have been VERY bad, in terms of user engagement and testing and bugs. Zero transparency. I am vocal, precisely because I am passionate about the product. But time and time again, I see your team respond defensively to a number of serious complaints from long-time users. You need to consider that.
33 Posted by dwg on Jun 19, 2015 @ 10:28 PM
I'm not quite convinced.
This is from one line of a report that shows changes for a single day, although Dividends stuff it up I'm using it to see the change in market value between days.
19/6/15: 27,387.88
Buys: 0
Sells: 0
Income: 0
20/6/15: 27,642.73
Gains: 0
Return Amt: 254.85
Return %: 0.93
Annual % (ROI): 2839.13
I still have a couple showing annual ROI with 12 significant digits, for example:
19/6/15: 615.00
Buys: 0
Sells: 0
Income: 0
20/6/15: 660.00
Gains: 0
Return Amt: 45.00
Return %: 7.32
Annual % (ROI): 335763727615.67
Des
34 Posted by sth on Jun 20, 2015 @ 07:41 PM
It is not far off? I would really like an explanation of exactly how MD is doing the calculation. So an estimated 1 year return could be done in a couple of ways. What I think is being done is
Annual return = exp (365*daily return)
This gives for the first example 2980% annual ROI which is close but not exactly what MD gets. Or if I work backwards, MD assumes a 359.8 day year.
Again transparency. But this is not really the right way. See wikipedia for IRR. If I buy 1 share a $1 and then a year later buy another share at the current price of $2, I now have $4 worth of stock purchased for $3 and the return is 33%. BUT it is actually much higher since that $1 was held for a year and went to $2 which is a 100% return.
DOES MD CALCULATE THE RETURN USING IRR OR DOES IT just take the beginning and ending values. The IRR is a valid way to calculate this annual return but one needs a more complicated sample than a 1 day report.
I don't speculate on internal personnel at MD, but I really don't want to speculate on how MD does its calculations. I want to know.
35 Posted by ljb on Jun 21, 2015 @ 12:30 AM
Initial results with 1236 are very good. My 3 simple test cases (which gave way-off results before) now get exactly the same ROI in the MD report as the XIRR() spreadsheet function.
I will try some more cases as well as some from this thread.
36 Posted by sth on Jun 21, 2015 @ 01:32 AM
Ian, Can I email you privately with some calculations that seem to be off?
37 Posted by ljb on Jun 21, 2015 @ 02:06 AM
Des,
Your first example is: 2015-06-19 value 27387.88; 2015-06-20 value 27642.73 and MD2015 is reporting Annual % (ROI) = 2839.13
MD2015(1236) is right - that is the correct value for IRR (internal rate of return). This assumes you are reporting through the second date, or you sell everything on that date for that amount and report to some future date.
The calculation goes like this: NPV (net present value) of 27642.73 with a return of 2839.13% for 1 day (0.00274 of a year) is:
which is (almost exactly) what you started with, so the NPVs sum to zero. Which means the IRR is 2839.13%.
But it makes more sense to look at it this way. You earned 0.93% in a day. Compound that for a year: (1+0.0093)**365-1 = about 2830%.
(Edit: corrected last equation, forgot to subtract 1 at the end.)
38 Posted by dwg on Jun 21, 2015 @ 09:44 PM
Hmm I think my rough mental arithmetic I did at the time was out of whack.
39 Posted by ljb on Jun 22, 2015 @ 01:15 AM
Des, I tried your second case (2015-06-19 value 615.00; 2015-06-20 value 660.00)
The spreadsheet XIRR() function returns an error code, and MD2015 reports a huge, apparently random number for ROI in the report.
It's a 7.32% return in 1 day. Compound that for a year: (1+0.0732)**365-1 and you get a huge number, around 1.58e11.
Since ROI is calculated by iterative approximation, a case like this will probably exceed the iteration limit without converging (that's what the spreadsheet error message I get means). Or it will just give a wild number that may or may not be close to the actual answer.
In my opinion, the right thing for MD to do in this case is to not report an ROI at all. But the calculation itself isn't at fault.
By the way... I had trouble getting these tests through MD2015. My security history entries kept getting lost, or old ones re-appeared, and changes to my memorized reports weren't sticking. (Intermittent - not repeatable - so these will be very hard to fix.) I ended up making a change, restarting MD, check, repeat. So even if ROI is fixed, there a long way to go with this before I would use it.
40 Posted by dwg on Jun 22, 2015 @ 01:38 AM
ljb,
I only use this report to get a guide whether the share prices have updated for the day. I would tend to only look at overall ROI after I have sold out of an investment.
While I hold the investment I'm more interested in separating income from capital gains so would tend to look at dividend Yield on one hand, so what the income return is like, then separately look at potential capital gains from selling.
Des
41 Posted by Ian L on Jun 24, 2015 @ 09:58 PM
Hi all,
A couple more notes, though I'm still working on cleaning things up.
sth, Moneydance does use IRR internally. So the reports should match what Excel's XIRR function spits out. Feel free to email me at ian @infinitekind.com if you have example cases that aren't working in the current preview.
ljb, you're right that Moneydance should not report a number when the equation fails to converge. I've flagged that to work into a future update. Regarding issues with changes not sticking, could you start a separate conversation on that? We've been stamping out those bugs when they pop up and I'd like to work with you on that, but I don't want to clutter up this discussion.
Also, I believe the Capital Gains report should now calculate the average cost basis correctly as of build 1239, which should be available on preview site shortly. This also fixes the gains reported on the Investment Performance report. The Portfolio report is still a bit off, but that's next on my list.
Ian
Infinite Kind Support
System closed this discussion on Mar 19, 2016 @ 04:56 AM.