This is a guest post by German investor Martin R..

P2P Loan Yield

On most p2p platforms (all of mine except Ablrate and Estateguru) principal is paid back monthly during the loan term. The remaining principal decreases every month, the interests do so
accordingly. Inexperienced people are frequently confused by that – a loan over 100 EUR, a term of 5 years and an interest rate of 10% doesn’t yield a profit of 50 EUR, but roughly half of that.
When you think about it for a moment the reason is evident: On average, the capital was only lend for 2.5 years, a part of the debt was already paid back with the first instalment. In exchange, the instalment – as sum of interests and payback – stays the same for the whole running time – minor deviations can occur because of dues of the platform.

Which leads us to a good approximate formula: The obtained interest is about half as high as they would be for a fixed deposit with the same conditions. As already mentioned, the stated yield is still right, though. There are many websites to calculate instalments on the internet you can use to play that through.

Admittedly, such calculations made beforehand become useless if losses or early paybacks occur. And actually, they always occur. How is it possible to stay informed about the current yield in that case?

Mostly, the provider offers calculated ROI calues in the account overview. The shown figures are rarely particularly meaningful, though. Auxmoney for example displays values which
noticeably exceed the interest rate of the lent money – of course that is impossible. There are bookings being conducted wrongly and early paybacks are taken into account as earnings –
that has been happening systematically for years and was never addressed or fixed.

Two ways of calculating yield

In principal, you have to distinguish between already obtained yields ( this is the figure shown by most providers) and the total yield expected at the end of the running time.

The first figure is a good review of the past, but could only be realised if you sold all
your remaining loan parts for their remaining nominal value. Usually, no losses are being considered, not even the already failed repayments. This means the calculated yield is generally too optimistic.
A yield (XIRR, RTI) shown by Bondora or Omaraha of 25% or even more may not be technically wrong, but is not the whole truth either.

Of course, the expected total yield is currently not definite. After all, both future losses and payments due to defaults can significantly affect the yield, meaning the values can only be estimated.
Many refer to a worst-case-scenario when they fully depreciate all credits in defaults and depreciate 50% of all credits that are overdue. But not even that is the whole truth, because usually some of the loans that are current now will fail as well.

The XIRR-function

Thus, you won´t be able to avoid doing your own calculations. Admittedly, it is not possible to do those manually or with help from a calculator for a single loan part with irregular paybacks, let alone a large number of credits.
You won’t be interested in individual credits (I personally own more than 2000 shares of 5-10€ on bondora), but in the average value. The calculation becomes possible by using a spreadsheet application such as Excel or the free LibreOffice Calc. It is “only” necessary to keep track of the respective cash flow (which is easier when the provider offers an own account for cash), and assume a sensible residual value. The program takes care of the rest.

In this example (using a German version of excel), you enter all bookings into column A; the respective amounts into column B.
In the process, incoming amounts are reckoned negatively, outgoing amounts (which actually leave the account) positively. Further calculation take place in the right half of the spreadsheet. Here there are just the current (cash) bank balance and the remaining value of the investments. Of course, you can calculate and consider corrections caused by overdue/failed investments.
All available capital is summed up and put into cell B34 – next to the current date =TODAY(). Below the actual calculation takes place:
=XIRR(B4:B34;A4:A31 ;01), so here, simply the rows of all amounts and respective dates are being shown. The last value is an estimation for the yield (0.1=10%). That only plays a role when a negative yield is expected. Without it, the calculation won’t work.
So it only necessary to have the discipline to enter all bookings in order to have an overview of all your yields.