When you login to your account at either Lending Club or Prosper you are presented with your annualized return number. Most investors just assume that number is equal to their actual return on all the money they have invested. But that is not necessarily the case.
Annualized Return Number is Based on Money Invested in Notes
For example, let’s assume you have a $5,000 account balance and a stated 10% annualized return number. But you only have $2,500 invested in notes and the other $2,500 is sitting there as cash earning 0%. In this case your actual return will be roughly half the 10% number quoted because both Lending Club and Prosper only look at the amount you have invested in notes. Also, if you sell notes on the trading platform the loss or gain from these notes will not be reflected in their numbers either.
So, if you use their number you may not get an accurate reflection of the total return on your investment. I wish both Lending Club and Prosper included your real historical return rather than just the return of the money you have invested in notes. But they do not provide this information so it is up to the individual investor to calculate their return themselves.
Use the XIRR() Function in Excel
Long time readers will have heard me talk about the Excel XIRR() function before. It is my favorite way to determine the actual return on my p2p lending investments. I use it to determine my returns that I share in my quarterly investment reports.
To use the XIRR() function you need to create a spreadsheet and enter in your numbers. The most accurate way to do this is to use the numbers from your monthly account statements. You can find your statements here at Lending Club and here at Prosper (these links will take you to your statements page if you are logged in to your account). You need your statement balance for the start and end of the period you are analyzing as well as the amounts and dates of any withdrawals or deposits you have made in this period – these are also included in your statement.
An Example of the XIRR() Function in Action
The table here shows an actual example from my main Lending Club account. I took my statement balance from June 2011 and then added the dates where I made deposits. I did not make any withdrawals from this account but if I did I would have included them here as a negative number along with the date I made the withdrawal. One quirky thing about XIRR() is that you need to include your closing balance as a negative number for the formula to work which is what I did with the total from my June 2012 statement. This produced a return number based on XIRR() of 10.19% for the previous 12 months.
This is the formula I used to do the calculation: =XIRR(B2:B9,A2:A9) where the amounts were in the B column and the dates were in the A column. There is a full explanation online about how to use the XIRR() function and here is an explanation from Microsoft as to how the XIRR() function works.
Most people will find their XIRR return will be lower than the return stated at Lending Club. In my case the numbers are pretty accurate. Lending Club is stating that my NAR is currently 10.09% which is within 0.1% of the XIRR number. I think the reason my actual return is slightly more is because of some trading activity on the secondary market. There was a time when my XIRR return was around 1.5%-2% lower than the number stated at Lending Club but since then I have remained fully invested at all times, investing in notes twice a week, and also I have made a small number of profitable secondary market trades.
In the next few days I will be giving a complete rundown of the returns from all my accounts at Lending Club and Prosper that I now do every quarter. I am just waiting for a couple more June statements to come in.
