What is XIRR formula in excel?
The Excel XIRR (Extended Internal Rate of Return) function is a function measuring the internal rate of return for a series of cash-flows that occur at irregular intervals.
Therefore, XIRR formula enables to determine the returns of a p2p platform or overal portfolio for any given period of time, expressing it as an annualised percentage. It allows investors to get a meaningful view on how perform their investments and help them to make decision based on the returns. Of course this is only one input that investor must take into account when deciding whether to further invest/disinvest, etc. Risks must also be taken into account.
Why tracking yourself your portfolio performance?
XIRR formula in excel is a very simple, yet effective, way to track your portfolio performance. Thefore why not using it? The most lazy investors would probably think most platforms provide already a XIRR calculation… So, why bother?
Well, because there are several potential flaws in the figures provided by platforms:
- Often platforms do not take into account cash drag, hence overestimating the returns.
- Often platforms take into account expected returns based on the loans in the portfolio, hence accounting for future cash-flows. This way of calculating also biasly imply a 0 default assumption!
- Sometimes they simply don’t make any sense 😅
I will now show you two examples coming from my own portfolio: Viventor and Estateguru. However, you should keep in mind that none of the platforms give figures matching actual profitability. In my opinion the XIRR figures provided by platforms are not an appropriate criteria for decision making.
Slice to the left to see the different screenshots.
Viventor: I don’t have a single loan above 14.1% and had in the past only small proportion of 15% and 16% loans (always below 20% of my portfolio). However, the platform gives me over 16% XIRR basically since day 1! My own calculations after 8 months on the plaftorm (June 2019 – February 2020) is 13.74% XIRR.
Estateguru: My NAR was stuck at 0% despite having received some interests payments in December 2019 and early January 2020 as well as my own referral incomes for joining the platform. I can understand that the platform would exclude the referral incomes from the calculation but I cannot find a reason for the absence of impact stemming from interests received for on-going projects. Suddenly my NAR jumped to 100.26%.. because 1 project was reimbursed after only 6 days and I received the minimum of 2 months period interests foreseen in the contract. Therefore, the NAR of my entire portfolio is based on this one finished project 🙃. It doesn’t seem about right!
How to use XIRR?
You will see this is very easy and consists in only a few steps to have a proper tracking table.
Determine the XIRR since inception
Create a table with the 4 columns as shown in the above picture.
In the “cash flow” column input each deposit with a ” + ” sign and each withdrawal with a ” – ” sign in front of the corresponding dates. You can chose to do the opposite but I find more logical that way since I will track the performance of the platform, a deposit means increase in the value when a withdrawal means a decrease. The dates should correspond to when the money reaches/ leaves the platform, not when it leaves/reaches your bank account (can take up to 2 days, sometimes even more).
In the last row input the excel formula: =today() in order to be able to compute the XIRR until today. In front of the date input the balance of your account with a “ – ” sign as it needs to have an opposite sign of the deposits for the calculation. You basically simulate the case where you would withdraw all from the platform today.
In the cell you want to determine the XIRR since inception, input the fomula: =XIRR(cash flow column,date column) as shown on the picture.
Input intermediaries monthly XIRR in the same table
In order to track within the same table intermediaries XIRR, for instance on a monthly basis, you will need to input twice the same date and assign to them the value of your account balance and its opposite. This way it will not mess up with the determination of your XIRR since inception. The two similar dates with opposite values will “annihilate” each other.
The XIRR formula will take into account all elements such as interests, fees, tax withholding, etc. directly in the balance value that you input at the end of each month. The impact of all these elements will be reflected in the XIRR figure.
The only thing to keep in mind is in case of a direct investment including a fee (few projects’ platforms enable a direct payment by SEPA transfer or by credit card), you will need to include the fee in the “deposit” value in your table. This way the fee will impact the balance value by partly offseting the interests of the period and de facto be reflected in the XIRR calculation.
Example: My Viventor tracking table using XIRR
*The XIRR for January is not a mistake. For more information you can see the explanation in my January 2020 Portfolio update.
As you can see my XIRR since inception reaches 13.74%, not over 16% as the platform displays. This is the perfect example why each investor should track the performance of his/her portfolio him/herself!
That’s all you need to know about how to track easily the performance of your p2p platforms. You can then aggregate the results in one table for determining the overall XIRR of your portfolio.