Calculate return on investment

A tutorial for the roi (Return On Investment) command.

Cash-only investments

Let's consider the easy case first, where your assets and your investment is the same single commodity (in this case, USD), and whenever value of your investment changes, you record the change manually, balancing it against equity:unrealized gains.

Lets say that we found an investment in Snake Oil that is promising to give us 10% annually:

2019-01-01 Investing in Snake Oil
  assets:cash  -$100
  investment:snake oil

2019-12-24 Recording the growth of Snake Oil
  investment:snake oil   = $110
  equity:unrealized gains

For now, basic computation of the rate of return, as well as IRR and TWR, gives us the expected 10%:

$ hledger roi -Y --inv investment --pnl "unrealized"
+---++------------+------------++---------------+----------+-------------+-----++--------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) | PnL ||    IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+=====++========++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 |     $100 |        $110 | $10 || 10.00% ||     10.00% |   10.00% |
+---++------------+------------++---------------+----------+-------------+-----++--------++------------+----------+

However, lets say that shorty after investing in the Snake Oil we started to have second thoughts, so we prompty withdrew $90, leaving only $10 in. Before Christmas, though, we started to get the "fear of missing out", so we put the $90 back in. So for most of the year, our investment was just $10 dollars, and it gave us just $1 in return:

2019-01-01 Investing in Snake Oil
  assets:cash  -$100
  investment:snake oil

2019-01-02 Buyers remorse
  assets:cash  $90
  investment:snake oil
       
2019-12-30 Fear of missing out
  assets:cash  -$90
  investment:snake oil

2019-12-31 Recording the growth of Snake Oil
  investment:snake oil   = $101
  equity:unrealized gains

Now IRR and TWR are drastically different:

$ hledger roi -Y --inv investment --pnl "unrealized"
+---++------------+------------++---------------+----------+-------------+-----++-------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) | PnL ||   IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+=====++=======++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 |     $100 |        $101 |  $1 || 9.32% ||      1.00% |    1.00% |
+---++------------+------------++---------------+----------+-------------+-----++-------++------------+----------+

Here, IRR tells us that we made close to 10% on the $10 dollars that we had in the account most of the time. And TWR is ... just 1%? Why?

Based on the transactions in our journal, TWR "thinks" that we are buying back $90 worth of Snake Oil at the same price that it had at the beginning of the year, and then after that our $100 investment gets $1 increase in value, or 1% of $100. Let's take a closer look at what is happening here by asking for quarterly reports instead of annual:

$ hledger roi -Q --inv investment --pnl "unrealized"
+-------++------------+------------++---------------+----------+-------------+-----++--------++------------+----------+
|       ||      Begin |        End || Value (begin) | Cashflow | Value (end) | PnL ||    IRR || TWR/period | TWR/year |
+=======++============+============++===============+==========+=============+=====++========++============+==========+
|     1 || 2019-01-01 | 2019-03-31 ||             0 |      $10 |         $10 |   0 ||  0.00% ||      0.00% |    0.00% |
|     2 || 2019-04-01 | 2019-06-30 ||           $10 |        0 |         $10 |   0 ||  0.00% ||      0.00% |    0.00% |
|     3 || 2019-07-01 | 2019-09-30 ||           $10 |        0 |         $10 |   0 ||  0.00% ||      0.00% |    0.00% |
|     4 || 2019-10-01 | 2019-12-31 ||           $10 |      $90 |        $101 |  $1 || 37.80% ||      1.00% |    4.03% |
+-------++------------+------------++---------------+----------+-------------+-----++--------++------------+----------+
| Total || 2019-01-01 | 2019-12-31 ||             0 |     $100 |        $101 |  $1 ||  9.32% ||      1.00% |    1.00% |
+-------++------------+------------++---------------+----------+-------------+-----++--------++------------+----------+

Now both IRR and TWR are thrown off by the fact that all of the growth for our investment happens in Q4 2019. Reported rates are annualized, that is IRR computation is still yielding 9.32% and TWR is still 1%, but these rates are computed over three month period instead of twelve, so in order to get an annual rate they should be multiplied by four!

Let's try to keep a better record of how Snake Oil grew in value:

2019-01-01 Investing in Snake Oil
  assets:cash  -$100
  investment:snake oil

2019-01-02 Buyers remorse
  assets:cash  $90
  investment:snake oil

2019-02-28 Recording the growth of Snake Oil
  investment:snake oil  
  equity:unrealized gains  -$0.25

2019-06-30 Recording the growth of Snake Oil
  investment:snake oil  
  equity:unrealized gains  -$0.25

2019-09-30 Recording the growth of Snake Oil
  investment:snake oil  
  equity:unrealized gains  -$0.25

2019-12-30 Fear of missing out
  assets:cash  -$90
  investment:snake oil

2019-12-31 Recording the growth of Snake Oil
  investment:snake oil
  equity:unrealized gains  -$0.25

Would our quarterly report look better now? Almost:

$ hledger roi -Q --inv investment --pnl "unrealized"
+-------++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+
|       ||      Begin |        End || Value (begin) | Cashflow | Value (end) |   PnL ||    IRR || TWR/period | TWR/year |
+=======++============+============++===============+==========+=============+=======++========++============+==========+
|     1 || 2019-01-01 | 2019-03-31 ||             0 |   $10.00 |      $10.25 | $0.25 ||  9.53% ||      2.50% |   10.53% |
|     2 || 2019-04-01 | 2019-06-30 ||        $10.25 |        0 |      $10.50 | $0.25 || 10.15% ||      2.44% |   10.15% |
|     3 || 2019-07-01 | 2019-09-30 ||        $10.50 |        0 |      $10.75 | $0.25 ||  9.79% ||      2.38% |    9.78% |
|     4 || 2019-10-01 | 2019-12-31 ||        $10.75 |   $90.00 |     $101.00 | $0.25 ||  8.05% ||      0.25% |    1.00% |
+-------++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+
| Total || 2019-01-01 | 2019-12-31 ||             0 |  $100.00 |     $101.00 | $1.00 ||  9.32% ||      7.77% |    7.77% |
+-------++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+

Something is still wrong with TWR computation for Q4, and if you have been paying attention you know what it is already: big $90 buy-back is recorded prior to the only transaction that captures the change of value of Snake Oil that happened in this time period. Lets combine transactions from 30th and 31st of Dec into one:

2019-12-30 Fear of missing out and growth of Snake Oil
  assets:cash  -$90
  investment:snake oil
  equity:unrealized gains  -$0.25

Now growth of investment properly affects its price at the time of buy-back:

$ hledger roi -Q --inv investment --pnl "unrealized"
+-------++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+
|       ||      Begin |        End || Value (begin) | Cashflow | Value (end) |   PnL ||    IRR || TWR/period | TWR/year |
+=======++============+============++===============+==========+=============+=======++========++============+==========+
|     1 || 2019-01-01 | 2019-03-31 ||             0 |   $10.00 |      $10.25 | $0.25 ||  9.53% ||      2.50% |   10.53% |
|     2 || 2019-04-01 | 2019-06-30 ||        $10.25 |        0 |      $10.50 | $0.25 || 10.15% ||      2.44% |   10.15% |
|     3 || 2019-07-01 | 2019-09-30 ||        $10.50 |        0 |      $10.75 | $0.25 ||  9.79% ||      2.38% |    9.78% |
|     4 || 2019-10-01 | 2019-12-31 ||        $10.75 |   $90.00 |     $101.00 | $0.25 ||  8.05% ||      2.33% |    9.57% |
+-------++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+
| Total || 2019-01-01 | 2019-12-31 ||             0 |  $100.00 |     $101.00 | $1.00 ||  9.32% ||     10.00% |   10.00% |
+-------++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+

And for annual report, TWR now reports the exact profitability of our investment:

$ hledger roi -Y --inv investment --pnl "unrealized"
+---++------------+------------++---------------+----------+-------------+-------++-------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) |   PnL ||   IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+=======++=======++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 |  $100.00 |     $101.00 | $1.00 || 9.32% ||     10.00% |   10.00% |
+---++------------+------------++---------------+----------+-------------+-------++-------++------------+----------+

Using commodities and prices

Let's redo the same Snake Oil example, but creating a special commodity to track amount of Snake Oil we have.

We will use SNKOIL as a commodity name, and will assume that 1 SNKOIL = $1 at the beginning of 2019.

As before, we start with a simple example where we invest in SNKOIL, and by the end of 2019 our investment growth by 10%.

2019-01-01 Investing in Snake Oil
  assets:cash
  investment:snake oil   100 SNKOIL @@ $100

; Recording the growth of Snake Oil
P 2019-12-24  SNKOIL $1.1

We need to tell roi that we are interested in the growth of value of our investment with --value=then switch, which forces it to use prices that were in effect at each moment in time that roi inspects for its computations:

$ hledger roi -Y --inv investment --pnl "unrealized" --value=then
+---++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) |   PnL ||    IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+=======++========++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 |   $100.0 |      $110.0 | $10.0 || 10.00% ||     10.00% |   10.00% |
+---++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+

Following the story from the previous example, lets say that shorty after investing in the Snake Oil we started to have second thoughts, so we prompty withdrew $90, leaving only $10 in. Before Christmas, though, we started to get the "fear of missing out", so we put the $90 back in. So for most of the year, our investment was just $10 dollars (or, rather 10 SNKOIL):

2019-01-01 Investing in Snake Oil
  assets:cash
  investment:snake oil   100 SNKOIL @@ $100

2019-01-02 Buyers remorse
  assets:cash
  investment:snake oil   -90 SNKOIL @@ $90

2019-12-23 Fear of missing out
  assets:cash
  investment:snake oil  90 SNKOIL @@ $90

; Recording the growth of Snake Oil
P 2019-12-24  SNKOIL $1.1

These numbers do not look correct:

$ hledger roi -Y --inv investment --pnl "unrealized" --value=then
+---++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) |   PnL ||    IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+=======++========++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 |   $100.0 |      $110.0 | $10.0 || 83.66% ||     10.00% |   10.00% |
+---++------------+------------++---------------+----------+-------------+-------++--------++------------+----------+

That is because the "Fear of missing out" buy-back transaction is likely incorrect: based on the price in that transaction, it look like we are buying back $90 worth of Snake Oil at the same price that it had at the beginning of the year, and then after that our investment gets sudden increase in value. This completely throws off IRR computations.

Let's say that we kept a better record of SNKOIL prices and we can compute a more precise amount of SNKOIL we bough back at the end of the year:

2019-01-01 Investing in Snake Oil
 assets:cash
 investment:snake oil   100 SNKOIL @@ $100

2019-01-02 Buyers remorse
 assets:cash
 investment:snake oil   -90 SNKOIL @@ $90

; Recording the price of Snake Oil
P 2019-02-28 SNKOIL $1.025
P 2019-06-30 SNKOIL $1.05
P 2019-09-30 SNKOIL $1.075

2019-12-23 Fear of missing out
 assets:cash 
 investment:snake oil   83.72 SNKOIL @@ $90 ; $90/$1.075 = 83.72

; Recording the growth of Snake Oil
P 2019-12-24  SNKOIL $1.1

Now our IRR looks better:

$ hledger roi -Y --inv investment --pnl "unrealized" --value=then
+---++------------+------------++---------------+----------+-------------+--------++--------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) |    PnL ||    IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+========++========++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 | $100.000 |    $103.092 | $3.092 || 25.22% ||      3.09% |    3.09% |
+---++------------+------------++---------------+----------+-------------+--------++--------++------------+----------+

Though from IRR perspective it looks like we only had about $10 worth of SNKOIL for most of the year, and still managed to get a return of $3, which looks like about a quarter of $10 invested - hence 25.22% of return from IRR standpoint. Note that TWR gives us more sensive 3.09% of return, which is closer to what you would expect, and shows that TWR is indeed (way) less sensitive to the money movements.

Investments that pay interest out (loans, bonds, dividends)

Let's say that you have given someone a loan or put money in a savings account, or maybe bought bonds that pay out regular coupons, and now you have monthly/quarterly/annual payouts. What is the best way to record them so that we could compute ROI?

For the following example, we will assume that you put $100 into savings account that pays out $1 quarterly (so your interest is not added to your investment):

2019-01-01 Investment
  assets:cash
  investment:saving  $100

We need to make sure that:

  • Payout transactions are included in the analysis, so they must match the query given to --inv. This could be achieved if investment account is mentioned in our transaction.

  • Payout transactions do not change the value of the investment

These two bullet points are naturally translated to this transaction:

2019-03-31 Interest
  assets:cash         $1
  investment:saving   $0

This transaction is not balanced, though. We need to balance it with the "profit and loss" account:

2019-03-31 Interest
  assets:cash         $1
  investment:saving   $0
  equity:profit and loss

So, at the end of the quarter your investment grew $1 in value and that $1 was immediately paid out to you, and this transaction lines with the description pretty well.

Let's complete the journal with one year of payouts:

2019-01-01 Investment
  assets:cash
  investment:saving  $100

2019-03-31 Q1 Interest
  assets:cash         $1
  investment:saving   $0
  equity:profit and loss

2019-06-30 Q2 Interest
  assets:cash         $1
  investment:saving   $0
  equity:profit and loss

2019-09-30 Q3 Interest
  assets:cash         $1
  investment:saving   $0
  equity:profit and loss

2019-12-31 Q4 Interest
  assets:cash         $1
  investment:saving   $0
  equity:profit and loss

We can now compute ROI:

$ hledger roi -Y --inv investment --pnl "profit and loss"
+---++------------+------------++---------------+----------+-------------+-----++-------++------------+----------+
|   ||      Begin |        End || Value (begin) | Cashflow | Value (end) | PnL ||   IRR || TWR/period | TWR/year |
+===++============+============++===============+==========+=============+=====++=======++============+==========+
| 1 || 2019-01-01 | 2019-12-31 ||             0 |      $96 |        $100 |  $4 || 4.06% ||      4.06% |    4.06% |
+---++------------+------------++---------------+----------+-------------+-----++-------++------------+----------+

Cashflow is $100 paid in minus $4 of interest received back.