Internal Rate of Return in Advanced Portfolio

[Wm...]

Fri, 13 Nov 2015 23:54:39 <20151114075438.GA14796 at iliad.caltech.edu>
Thomas Anderson <tanderson at exherbo.org> wrote...

>On Fri, Nov 13, 2015 at 10:12:45PM +0000, Wm... wrote:
>> I installed 2.2.7 on a spare Win XP system and a test suggests the IRR
>> figures are reasonable.
>
>I am on linux only systems and installing the needed (old) libraries
>for 2.2.7 looks painful if even
>possible. I will try again tomorrow.

That's why I went for the path of least resistance.  I reckon a Win VM
would probably be easier than rolling back and getting all the libraries
lined up in a neat row.    YMMV.

Time passes ... I was going to recommend modern.ie as a good VM source
but it looks as though MS have done unpleasant things to it since I last
downloaded from there.  Ho hum, my copy is safe.

>> I used the flow in
>> ===
>> https://support.office.com/en-us/article/IRR-function-64925eaa-9988-495b-
>> b290-3ad0c163c1bc
>> ===
>> rather than real data as my example and get
>>
>> an IRR of   8.66% vs their   8.7% after 5 years
>> an IRR of  -2.12% vs their  -2.1% after 4 years
>> an IRR of -44.30% vs their -44.4% after 2 years (no user guesses)
>>
>> which is close enough not to matter.
>
>I'd lean towards the code being correct as well. There are iterative
>techniques in play here and
>choosing a stopping criterion is needed; there may be a difference in
>how the code acts vs. Excel,
>and even in what Excel says it is doing and what it is doing in actuality.

Indeed.

>> Generally I'm now thinking this might be a worthwhile addition.
>>
>> A thought: presuming the IRR is a good number should it be added to the
>> extant Advanced Portfolio report or should we make a new report?  I'm
>> wondering if the Advanced Portfolio report isn't getting overloaded, people
>> are moaning about corner cases already, I think.
>
>I have been thinking about this more and I think one way to avoid
>corner cases with IRR is to
>simply make it calculate XIRR. This handles the non-dividend
>contribution case easily.

Apols for any confusion but the corner cases I meant were to do with the
current Advanced Portfolio not necessarily producing the numbers
everyone expects at a first attempt and people having to do fiddling to
make things work, e.g. attaching investment returns to the asset they
derived from.

> With IRR you
>must have regular contributions/withdrawals which makes things less
>precise.

The report I'm testing doesn't work for non-priced investments at all as
far as I can tell and starts drifting if the income periods are varied.
The point being that may or may not be what a user is expecting.

> The calculations are
>not very different, as you can imagine.
>
>As for putting it in a different report, perhaps, To my mind it may
>depend on how much
>configuration IRR/XIRR needs at the report-level.

OK, XIRR makes more sense in a real world.  Now which XIRR do we
implement?  It seems that MS have changed Excel's [1] calculations along
the way.  Do we implement an academically pure XIRR or one that users of
one spreadsheet or another expect?

[1] for the record I prefer LibreOffice, I'm trying to think about the
feedback we might get from users.

An alternative is GnuCash producing a very simple report that can be
cut-n-pasted or exported-n-imported into a spreadsheet so the user can
decide for themselves which IRR or XIRR suits their presumptions best
[2]

I'm still wary of blackboxing a non-trivial financial number and was
devil's advocating a bit in my earlier post.

Think about the joy of the separate report approach for a moment, we can
then have columns for

IRR (the way it was done in 2.2.7 that never got far)
IRR (Excel version something)
IRR (Excel version something else)
XIRR (the way you want it)
XIRR (the way I want it)
XIRR (the way fred's broker does it because he likes things to match up
        so GnuCash must have been doing it wrong)

and so on.

If you think I'm exaggerating the issue just say so.

[2] date and amount should do it, the rest is candy

-- 
Wm...