## How To Calculate Your ROI The Easy Way |

**Most people don't know if they're making money on their investments**. We rely on heuristics, like "I sold it for twice what I bought it" or "The fund reported a 13% return this year". Neither of these statements give you a real answer to how hard your money is working for you. The first completely ignores how long you were invested and the second uses an arbitrary time span which has nothing to do with the amount and timing of YOUR investment.

We don't calculate return on investment because **the math gives us a headache**. Here's why. This is the conventional formula for calculating **Return on Investment** that you learn in high school or from some spammy website:

ROI = profit / amount invested / time

It's not that the formula is incorrect (although the simple version above doesn't take into account any compounding over time), it is that **the formula just doesn't represent the real world**. We usually don't just invest a lump sum then cash out after x number of years. Most investments involve continuous give and take. We invest a bit, then some more, maybe we sell down or take dividends (or reinvest them) or spend money on capital improvements - and we do this at all sorts of random time intervals.

So that's going to make calculating our real return very difficult right?

Actually, **no**.

There is a very simple formula for getting YOUR real return on investment which uses only the amounts of money you spend on or take from an investment. There is no need to know the unit prices of your investments or franking levels or anything else beyond what you spent (or received) and when.

It's called **Internal Rate Of Return (IRR)**, and your spreadsheet will calculate it for from just a column of dates and cashflows. The formula works by figuring out the rate that would make all the discounted cashflows sum to zero. For example

1 Jan 2014 -$1000 Invest in bitcoin 31 Dec 2014 $1000 Cash out bitcoin IRR 0%

The values sum to zero, there is no return.

1 Jan 2014 -$1000 Invest in bitcoin 31 Dec 2014 $2000 Cash out bitcoin IRR 100%

The values sum to $1000, a 100% discount rate makes them sum to $0. Your return is therefore 100%

It's a bit strange making things sum to zero (unless you're an accountant). You could also think of it as discounting all the future benefits to equal the initial investment - although this again assumes a model of buy once sell later.

Summing to zero let's us be very flexible. For example, our naive formula at the top of the page would get this calculation totally wrong.

1 Jan 2014 -$1 000 Invest in bitcoin 30 Dec 2014 -$10 000 Put all my saving into bitcoin. I can't lose. 31 Dec 2014 $12 000 Current value of my bitcoin account. IRR 98%

The naive formula of (profit / investment / time) would say you've made $1000 profit on $11 000 investment in 1 year - that's **9.1%**. This totally ignores that you only had the $10 000 in there one day. IRR gets it right. The real return is **98%**.

All you need to do this is a spreadsheet. The formula is **XIRR(values, dates)**. Here are some more examples.

1 Jan 2014 -$1000 Initial investment 1 Jun 2014 $100 Dividends paid 1 Jun 2014 -$100 Dividends reinvested 31 Dec 2014 $2000 Account value IRR 100%

The dividend reinvestment cancels itself out. You don't need to track it - it'll be incorporated in the final account value.

1 Jan 2014 -$1000 Initial investment 1 Jun 2014 $100 Dividends paid 31 Dec 2014 $2000 Account value IRR 115%

To get the same value without reinvesting those dividends you must have had a higher return.

1 Jan 2014 -$1000 Initial investment 2 Jun 2014 -$1000 Invest more 31 Dec 2014 $3500 Account value IRR 106%

Counter-intuitively, that second investment is growing faster than 100%.

There are no excuses for not calculating your return on investment. **All you need are your cashflows and a spreadsheet**. It's a no-brainer.

That formula again? It's **XIRR(values, dates)**, check the help page for Microsoft Excel or OpenOffice, or if you want to get your hands dirty, read more about Internal Rate Of Return on Wikipedia.

I guess I'd call myself a problem solver. Either that, or I'm some sort of organic machine designed to convert oxygen into carbon dioxide. You could go either way on that. I'm into languages and stuff. I wrote a book. It's okay, I guess. What else? I like reading, swimming, eating, and playing music. Satisfied? Sheesh. |