Another Excellent Email from TB on IRR | P2P Lending, Peer to Peer Lending, People to People Lending

I saw your last comment and wanted to provide you with an Excel sheet showing the implications of not counting the principle payments as cash flows when they are received.

In this sheet I have included a situation where there is a hypothetical Prosper account that is made up by one loan that will be paid in full and on time. The calculations would work the same with a portfolio of many loans. The assumptions that can be changed (loan amount and interest rate) is in blue font.

The sheet shows the hypothetical loan and how the amount outstanding declines over time as the principle is paid off. It also shows how the Prosper account balance for the lender would change over time as interest and principle get paid. In addition, it shows what the correct XIRR would be for the full term (it would be the same for each and every period for the durating of the loan as well) and what the XIRR calculation would show if it was calculated without counting the principle payments as the are received, but rather include them as part of the account balance (which I think is what you are suggesting in you last comment on your site).

The conclusion from the examples I have included in the Excel workbook is that if the principle payments are not counted when they are received, the XIRR will look like it is declining over time. This is incorrect, since it does not reflect that the principle payments are available to reinvest. If the principle payments are not counted as cash flow when they are received, the XIRR will implicitly assume that the paid principle just sits in the account balance earning zero return until the loan is fully paid off. This is not reasonable to me. I don’t understand how the account balance at the end (for XIRR purposes) could ever go negative by deducting the principle payments received, if you don’t assume defaults, since the amount you deduct would be exactly equal to the principle implicitly included in the ending account balance (either as cash or as loans outstanding).

Btw, I am not bothering you with this to prove a point. I am potentially looking to become a lender at Prosper (whenever they actually get around to approving me. Their customer service is severely lacking) and going through these return calculations is a very good exercise for me. I always compare all my investment alternatives on a risk-adjusted XIRR basis, since it is (IMO) the best way to do it, assuming that there are no capital constraints involved, but that is a different topic.

As always, I welcome comments.

Leave a Reply

Your email address will not be published. Required fields are marked *