Have had positive returns, need help understanding why beangrow shows -100% IRR

[Eric Morgan]

I'm using the beangrow plugin for fava. It shows the following numbers. I've double checked that yield is correct using a different library. But I don't understand why IRR is -100.00%. My calculations suggest it should be 285.78%.



Here is the same data visualized with the beangrow tool.



Here is the beangrow config file

investments {
  investment {
    currency: "MIPS"
    asset_account: "Assets:MIPS"
    cash_accounts: "Assets:Cash:SEK:BankA:AccountA"
    cash_accounts:  "Assets:Cash:SEK:BankB:AccountB"
  }
groups {
  group {
    name: "stocks.MIPS"
    investment: "Assets:MIPS"
    currency: "SEK"
  }
}

Here are the entries on that account copied from fava

2022-06-22
Open
Assets:MIPS
2022-06-22
*
Purchase MIPS
si
108057.6 SEK
108057.6 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
224.0 MIPS
461.7 SEK
Assets:Cash:SEK:BankB:AccountB
-103480.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
0.20 SEK
Expenses:Comissions:SEK:BankB:AccountB
59.0 SEK
2022-06-22
*
Purchase MIPS
si
120600.0 SEK
228657.6 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
250.0 MIPS
458.7 SEK
Assets:Cash:SEK:BankB:AccountB
-114738.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
Expenses:Comissions:SEK:BankB:AccountB
63.0 SEK
2022-06-22
*
Purchase MIPS
si
120600.0 SEK
349257.6 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
250.0 MIPS
460.6 SEK
Assets:Cash:SEK:BankB:AccountB
-115213.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
Expenses:Comissions:SEK:BankB:AccountB
63.0 SEK
2022-06-22
*
Purchase MIPS
si
117705.6 SEK
466963.2 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
244.0 MIPS
456.1 SEK
Assets:Cash:SEK:BankB:AccountB
-111349.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
-0.40 SEK
Expenses:Comissions:SEK:BankB:AccountB
61.0 SEK
2022-06-22
*
Purchase MIPS
si
96480.0 SEK
563443.2 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
200.0 MIPS
457.6 SEK
Assets:Cash:SEK:BankB:AccountB
-91579.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
Expenses:Comissions:SEK:BankB:AccountB
59.0 SEK
2022-06-22
*
Purchase MIPS
si
14472.0 SEK
577915.2 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
30.0 MIPS
456.7 SEK
Assets:Cash:SEK:BankB:AccountB
-13760.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
Expenses:Comissions:SEK:BankB:AccountB
59.0 SEK
2022-07-04
*
Purchase MIPS
si
134790.0 SEK
673051.4 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
300.0 MIPS
446.1 SEK
Assets:Cash:SEK:BankB:AccountB
-133904.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
Expenses:Comissions:SEK:BankB:AccountB
74.0 SEK
2022-07-08
*
Purchase MIPS
si
212728.8 SEK
916189.6 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
453.0 MIPS
464.8 SEK
Assets:Cash:SEK:BankB:AccountB
-210655.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
1.60 SEK
Expenses:Comissions:SEK:BankB:AccountB
99.0 SEK
2022-08-08
*
Sale MIPS
si
-277326.6 SEK
917075.6 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
-453.0 MIPS
608.0 SEK
Assets:Cash:SEK:BankB:AccountB
275325.0 SEK
Income:Financial:Rounding:SEK:BankB:AccountB
Expenses:Comissions:SEK:BankB:AccountB
99.0 SEK
2022-08-22
*
Sale MIPS
si
-820005.2 SEK
signature:
ASSET_CASH_EXPENSES_INCOME
Assets:MIPS
-1498.0 MIPS
540.0 SEK
Assets:Cash:SEK:BankA:AccountA
808721.0 SEK
Income:Financial:Rounding:SEK:BankA:AccountA
Expenses:Comissions:SEK:BankA:AccountA
199.0 SEK

The Yield and IRR has been correct for other investments I've looked at in my data.

Appreciate any help on this. Thanks!

[Eric Morgan]

Here is another attempt at inserting images

[Eric Morgan]

This is an attempt at making my original question easier to answer by providing a minimal example.

Here is a beancount file with one purchas and sale. 

option "operating_currency" "SEK"

2010-01-01 custom "fava-extension" "fava_portfolio_returns" "{
  'beangrow_config': '/data/beancount/beangrow.pbtxt',
}"

2010-01-01 open Assets:MIPS                                     MIPS
2010-01-01 open Assets:Cash:SEK                                 SEK

2010-01-01 commodity MIPS
  price: "SEK:yahoo/MIPS.ST"

2022-07-08 * "Purchase MIPS"
  Assets:MIPS                                       453.0 MIPS @ 464.8 SEK
  Assets:Cash:SEK                               -210655.0 SEK

2022-08-08 * "Sale MIPS"
  Assets:MIPS                                     -453.0 MIPS @ 608.0 SEK
  Assets:Cash:SEK                               275325.0 SEK

Here is a beangrow config file with one investment and group

investments {
  investment {
    currency: "MIPS"
    asset_account: "Assets:MIPS"
    cash_accounts: "Assets:Cash:SEK"
  }
}

groups {
  group {
    name: "stocks.MIPS"
    investment: "Assets:MIPS"
    currency: "SEK"
  }
}

Here is the output

Investment              stocks.MIPS
Units                   -
Cash In                 210655.0 SEK
Cash Out                275325.0 SEK
Market Value            -
Returns                 64670.0 SEK
Yield                   30.70 %
IRR                     -100.00 %


I would expect that since the investment returned 30% in a month, the annual IRR should be higher than 30%. Does -100% indicate a bug in beangrow or am I thinking incorrectly about this?

[Eric Morgan]

Further testing shows that prices are used to determine IRR, so my example wasn't complete. Thus you don't need to answer my question. Perhaps I had a problem with price data.

[Martin Blais]

Debuggability should be improved for sure.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/beancount/183f903d-f646-456a-be4a-3773633a0c14n%40googlegroups.com.

[Eric Morgan]

I'll just add that this issue has caused me to put using beancount on hold (since my OP on November 3rd). Prior to this issue I have spent at least 120 hours writing my own importers for banks I'm using. The end goal was to calculate an aggregate IRR for all my investments.

I think my minimal example earlier in this conversation was complete. Since there is no remaining balance of stocks, I'm pretty sure prices should not be used for calculating IRR. Also there was nothing wrong with the prices that could explain a -100% IRR.

You've created much more for the world than I have, and I'm sure you have a lot to do with beancount 3. If you do find time to address this IRR issue it would mean a lot for my use case.

[Martin Blais]

I built beangrow years ago and got everything working for my use case back then but I haven't maintained that state since. I switched to Johnny in 2021 when I had a properly busy trading year before I returned to work in finance and I'm certain beangrow would not handle the complex options trades I had at the time. I haven't had time to bring things to working order since, interest dedicating my available cycles to work and machine learning topics. I don't have much time anymore. Maybe one day, but honestly this needs someone to go deep and improve it some more.

To view this discussion visit https://groups.google.com/d/msgid/beancount/1d45ff0b-1ceb-434d-a1f8-00c0e45adfd9n%40googlegroups.com.

[David Avraamides]

> I'm pretty sure prices should not be used for calculating IRR. 

If you still hold the position on the final date of the IRR calculation period, you need a price to create a final cash flow that is equivalent to selling your remaining position at that ending mark. Likewise, you can do this for your starting point if you don't want to start your IRR calculation on the initial purchase date. IRRs are often used for reporting over standard periods, not just the full life of an investment.

[Eric Morgan]

Thanks for the information David. In the minimal example I posted earlier the entire position has been sold. Further I haven't specified the IRR period. I assume that by default it calculates over the full life of the investment.

[Red S]

Hi Eric,
As Martin mentioned, Beangrow is code that Martin shared at some point years ago. I would consider it highly experimental, and fringe part of the larger Beancount ecosystem. I personally take that to mean “I’d be very pleasantly surprised if this worked out of the box” :). Beancount itself is mature, well maintained (I personally use v2), and has a large number of active users.

All that said, I ran your example (thanks for a simple case!), and can confirm this seems like a bug. It seems like an issue around root finding in the XIRR calculation which is done with scipy.optimize.fsolve() and an NPV formula. With your given example, the iterative process incorrectly converges towards -1 (-100%). Try running your example with the sale on Dec 31, so the time period between your purchase and sale is longer, and the formula converges to the correct IRR.

I lack the time right now to figure out why that happens numerically, but I imagine if that part of the code were replaced with an XIRR library, it would work fine. I’ve had a personal interest in getting Beangrow to work for some of my projects, but I’ve lacked the time to contribute to it. If you want to debug and contribute to that code base, that would be awesome. Your example is a great starting point. There is not much code in there, it is fairly straightforward, and there is Martin’s excellent documentation to help you understand the code well.

-Red

​

[Red S]

Hi Eric,

Applying this patch should help. It uses XIRR from this library. It certainly resolves your test case, and works on several accounts of mine (but not all). However, I’ve barely tested it, and there are cases where it might not work. Nor do I have an explanation for why the test case you presented wasn't caught earlier. Posting it here for you or anyone who is inspired to dig deeper into this issue.

--- /home/user/beangrow/beangrow/returns.py +++ returns.py @@ -17,7 +17,7 @@ from beancount.core.inventory import Inventory from beancount.core.number import ZERO from beancount.core.position import Position -from scipy.optimize import fsolve +import pyxirr from beangrow.investments import AccountData, CashFlow, Cat, compute_balance_at @@ -196,14 +196,10 @@ years = [(flow.date - end_date).days / 365 for flow in dated_flows] years = np.array(years) - # Start with something reasonably normal. - estimated_irr = 0.2 * np.sign(np.sum(cash_flows)) + date_list = [d.date for d in dated_flows] + irr = pyxirr.xirr(date_list, cash_flows) + return irr - # Solve for the root of the NPV equation. - irr, *_ = fsolve( - net_present_value, x0=estimated_irr, args=(cash_flows, years), full_output=True - ) - return irr.item()

​

[Eric Morgan]

Thanks to everyone for input in this thread! Thanks Red for the code change! I'll try that later today and over a few weeks. If it doesn't work for all of my trades I'll see if I can refine it further.

Another question: Given that there are some bugs in the beangrow code and it is not actively maintained, is there any other tool in the beancount ecosystem that can calculate IRR per group of investments in a similar way to beangrow, but correctly?

[Red S]

Thanks to everyone for input in this thread! Thanks Red for the code change! I'll try that later today and over a few weeks. If it doesn't work for all of my trades I'll see if I can refine it further.

Another question: Given that there are some bugs in the beangrow code and it is not actively maintained, is there any other tool in the beancount ecosystem that can calculate IRR per group of investments in a similar way to beangrow, but correctly?

Relevant thread: https://groups.google.com/g/beancount/c/DUa_yfTlWzk/m/-Wo90fXhAwAJ 

[Red S]

On Thursday, November 28, 2024 at 1:07:03 PM UTC-8 Eric Morgan wrote:

Thanks to everyone for input in this thread! Thanks Red for the code change! I'll try that later today and over a few weeks. If it doesn't work for all of my trades I'll see if I can refine it further.

Another question: Given that there are some bugs in the beangrow code and it is not actively maintained, is there any other tool in the beancount ecosystem that can calculate IRR per group of investments in a similar way to beangrow, but correctly?

The challenging and overwhelmingly major part of getting returns computations right is building the cash flows correctly. Beangrow does that well, and that seems to be functioning correctly. Solving for the XIRR formula is a small part at the end. I'm not even fully convinced this bug always existed. Anyway, IMHO, I'd recommend using beangrow, and fixing whatever minor thing needs to be fixed if at all you're handy with a bit of code.

If that's not an option for you, another idea is to simply take the cashflows that Beangrow generates and stick them into Excel and use Excel's XIRR.