Any investment platform can avoid discussion about return on investments. There are several reasons why it is so.

Bad or not, but different investment platform uses different methods to calculate Return On Investments. Usually such methods are described and public, but users of every platform are claiming that data provided by platform are incorrect, ‘too optimistic’, ‘do not take into account defaults and overdue’, ‘platform hide some bad data’, etc. It is true to some extend, that platforms are not interested to start their presentations with pessimistic scenarios, therefore users should think themselves how to get real indicator of return.

It may be simple when you use one place to invest your money. But most of us at some point turns to situation when we have several investment lines – it may be deposit, funds, p2p lending, trading, mining of bitcoins or other investment projects. We need to

a) be able to compare what return gives us different investment (as example to compare investment A with investment B)

b) be able to calculate cumulative result of our investments.

More experienced users(all of us will become such at some time 🙂 ) will be willing to look on return by different aspects (dimensions) , at least by time or changes in strategy. Also, some of us like to know ‘optimistic”, “realistic” and “pessimistic” scenario of our return.

I use Excel or Libre Office Calc and their XIRR function to do such type of analyses. XIRR function calculates internal rate of return depending on dates of deposits or withdraw (cash flows). In simplified words – If I invested 5$ 3 month ago and got 10$ back today, my return (XIRR) will be better compare to case when I invested same 5$ 12 month ago and got 10$ back today.

Let’s look simplified scenario.

February 2nd, 2014 I have invested (did deposit) 1000$ (displayed in column B with minus). May 10th, 2014 I have added next 500$ and May 12th of 2014 I withdraw from platform 200$ (column B with plus). Today is November 12, 2015 and Platform tell me that *Value of my investments* is 2600$.

XIRR calculates that my Return on investments is 49.73%. Nice one, almost my dream 🙂

Some platforms don’t tell you *Value of my investment* **today**, but tell *Receivables amount* (Value of investments in future). In such cases I will use estimated time of my investments instead of TODAY. For example I may write in the last line =TODAY()+360 – assuming that I will get all my money after 360 days.

I can play with data and change date of withdraw to October 2nd, 2015, then my XIRR will show value 44.95% as return on my investments. It is logical. I got my money back later, so, overall return from invested money is lower than in first case.

Lets look at more realistic and advanced scenario. In real life we have some risk, associated with our investments. If I do P2P lending, part of my loans will start to be late or will go default. When loan is late there some chance that borrower will start to pay. When loan goes to default, there is much lower probability that borrower will repay, but we may count chance that platform will work on recovery of loan. Look on example of calculation in such scenario.

Most of platforms indicate what is amount of Free cash, Current loans (aka well performing), Late loans and Defaulted loans. We may have statistics from Platform or we can use our own assumptions about level of recovery. On my picture you see that I assume that 50% of late borrowers will pay again, and only 10% of defaulted loans will be recovered.

Value of my portfolio today is calculated as :

*Current_Portfolio=Well_performing_loans + Recovery_overdue% x Overdue_loans +Recovery_Defaulted% x Defaulted_loans+ Free cash*

Lets make life more interesting and look next case. I like to know my realistic, optimistic and pessimistic scenarios. So, I add a bit more:

Now I have added different rate of recovery for my scenarios (columns K,L, M) and I see in result line 3 different values of Return – realistic, optimistic and pessimistic. Voila, now I can sleep well or bad depending on scenario I believe more 🙂

Final final. You see line FOR COPY on my last picture? I’ve added formula and I copy this line to “history area” after I do periodic update of data. I am getting something like this:

Then, if I do updates regularly(what must be rule for us), then I can even build graphs telling about performance of my portfolio in time.

Excel gives you full freedom. You may add deposits/withdraw for different investments together and see consolidated picture. You may do calculation of different investments on different sheets and have join graphs to compare return of different investment. You may add more calculated columns in history area and calculate for example grow of overdue compare to grow of portfolio. In next articles I will explain how I do my calculation when I invest in different products and platforms.

And there is Excel Sample that I was using in my article and which you may use to start your own calculations. xiir-sample

Thank you in advance for your comments! Have a nice investments!

Great article for p2p bitcoin lenders … very helpful. Thanks Andrej!

Great Article sir , it would better if you post the excel sheets on forums at pop or at FB

Thank you, Suresh! Feel free to repost link to this article. I do not like to do it myself as it may be treated as spam 🙂

Hi Andrey!

I have used your formulas on my excel portfolio tracker spreadsheet and.. to my surprise it calculated a risk pondered Xirr of -4,48% on my Bondora investments. Is this normal?

I have used your 0,5 and 0,1 recovery rates for overdue and defaulted loans but I would like to use my account values. Any idea on how I can find those?

If this Xirr is real, I will definitely consider withdrawing my money from Bondora.. what a disappointment..

Thank you. 4% is not the worst case, especially if your account is less than 12month old. If you do manual investments, think how to improve strategy or switch to Portfolio Manager. But you need to count that then you will need at least 3y to see real return.

to change 50/10% – simply change numbers under recovery in my excel. Today I count that 60% of overdue will be recovered and 30% of defaulted