Several P2P lending sites display the XIRR (extended internal rate of return) of your portfolio. However many of them don’t provide this information.
Here I will show you how to easily compute it yourself using Google Sheets. It will allow you to easily compare returns between your different P2P lending portfolios.
You could also use a spreadsheet program on your computer, but Google products give you access from anywhere, and you won’t lose data if your computer dies.
Theory behind XIRR computation
The XIRR answers a basic question : given a number of deposits / withdrawals and the current value of the portfolio, at which interest rate would I need to invest the deposits in order to reach the current value ?
Note that it doesn’t mention anything about the actual investments taking place in your portfolio. Indeed, it values an theoretical portfolio in a uniform way, which allows us to compare the returns from different portfolios just by knowing the deposits, withdrawals and current value.
Sample computation : Swaper XIRR
I’ll use my Swaper account as an example of XIRR computation.
Find the required data
Log on to your Swaper account. If you don’t have one, don’t worry; I’ll display the necessary data below.
Go to “Overview” -> “Account Statement”. First enter a “From Date” earlier than your first deposit. Under “Transaction Type”, select “Deposits” and “Withdrawals” then click “Search”.
In my case, it displays my initial deposit and three withdrawals.
The current value of your account can be found under the “Overview” -> “Summary” tab. As I’m writing this article it’s 517€.
Now that we have all necessary data, let’s move on to the dirty part !
Input the data on Google Sheets
The sample spreadsheet I created can be found at Google Sheets.
Go to Google Drive, create a new spreadsheet.
When inputing the data, we must follow some basic conventions. Deposits as well as the current portfolio value should be entered as negative values, while withdrawals should be positive.
On my spreadsheet I sorted the lines according to the date, but it’s not necessary for the computation to work. I only did so in order to enhance readability.
I inputted the date for the last line (current value) in a special way : instead of typing today’s date manually, I use the formula “=now()”.
On the next column, I finally entered the formula for the XIRR : “=XIRR(D2:D6;C2:C6)” where D2:D6 is the amounts range and C2:C6 the dates range.
The XIRR is initially displayed as a decimal number. Select the cell and choose “Format” -> “Number” -> “Percent” to switch to a more user-friendly format.
Similarly, I also formated all dates using “Format” -> “Number” -> “Date”.
Here’s the resulting sheet : (ignore the rightmost part which we’ll use in a moment)
Note that the result differs slightly from the one computed on Swaper’s website : 11.32% on Google Sheets versus 12.09% on Swaper’s website.
Checking the result
We’ll check this result by using the definition of XIRR I provided earlier. We need to actualize each amount using the FV function, which computes the future value of an invested amount.
In a column at the right of the first amount, enter the following formula : “=FV($D$8/12, int((now() – C2)/30), 0, D2)“.
The parameters are :
- the interest rate; in this case I referenced the cell containing the XIRR computed by Google Sheets. Note that I escaped “D8” into “$D$8” so that it will be immutable when I copy this formula
- the number of repayments : I divided the number of days between the date and today by 30, which once truncated is a rough estimate of the number of months elapsed since this date
- the payment amount, which is 0 as we don’t withdraw or add money
- the initial value
I then expanded this formula in order to compute it for all amounts, and added one cell containing the total. The sum of all future values is 520.58, close enough to the actual 517€. This small difference is in part due to the way we computed the number of months.
Finding the required data on your P2P lending websites
Bondora
You have to generate a new report. This has the advantage of allowing you to copy-paste data from the generated report directly into Google sheets !
Mintos
Under “Account Statement”, choose the proper start date. I had to select several payment types : “Deposits” and “Withdrawals” of course, but also “Currency exchange fee”, “Incoming currency exchange transaction” and “Outgoing currency exchange transaction”. This was necessary as I transferred some money from my Euro portfolio to my GEL portfolio and vice-versa.
Just like with Bondora, you can download the result.
Omaraha
I didn’t find any way to get a detailed account statement. As a result I had to use my bank account statements instead !
Robocash
Go to “Account statement”, and choose the right start date. The resulting statement is downloadable. Deposits are labeled as “Adding Funds”
DoFinance
Under the “Transactions” tab, you can choose to view either the deposits or the withdrawals.
Discrepancy with the “official” XIRR
As mentioned above, my computed XIRR for Swaper is lower than on the website. I’ll get in touch with them for an explanation.
Similarly, my result for Grupeer is 12.46%, which seems a bit weird as all the loans yield more than 13% and there’s been no default. This is because my Grupeer portfolio is made of few large investments, leading to only a few large repayments during each month. Between two repayments, the XIRR drops.
Most other portfolios contains much more loans, which allow the XIRR value to fluctuate smoothly during each month.
XIRR on alternative investments
P2P lending companies reviews
I used to quote the XIRR displayed by the website. I will update the articles in order to reflect the computed XIRR.
Monthly P2P portfolio review
In a similar way, the I will now use my own XIRR computation when displaying returns. This will allow to compare P2P lending companies more accurately.
Long-term investments and XIRR
Real-estate investments usually pay interests only once the project is completed. As a result, I’ll only display XIRR for BulkEstate and CrowdEstate once they’re meaningful.
In conclusion
Tracking XIRR monthly with this method is easy. It’s very easy to update it each month by taking into account the deposits and withdrawals, and updating the current value.
This will allow you to track your portfolio performance in a much better way !