- 1Share
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.
- 1Share
Thanks Peter. I’m curious how accurate the NickleSteamroller.com analysis page is compared to XIRR()?
@Simon, Good question. I just uploaded this very account to Nickelsteamroller.com and it states that my estimated ROI is 8.02%. I do have a number of late loans right now (20 to be exact) and many of these will default and that is never reflected in the XIRR analysis. Even though this account is three years old as you can see in the table above the majority of the Notes I hold are less than six months old, so even though the average age of all notes is around 500 days more than 50% of the note volume is less than six months old. So, it is quite possible that my returns will reduce to the 8% number going forward.
Peter, how are you handling any bonuses? I’ve been using XIRR to track my Lending Club returns, and am currently recording two values, one including the initial $300 bonus for opening a new account as a deposit and one where it is simply part of the balance. Keeping it as part of the balance is technically correct, in that it’s money returned on my investment, but recording it as a deposit gives more accurate information about the actual return I’m getting from loans and secondary trading.
Peter – are your late notes just those that you could not sell on foliofn or are you not selling any or most of your late notes for other reasons?
One thing that may not be so obvious about XIRR() is that to get an accurate measure of your real rate of return you need to do the following things at tax time:
1) Compute your tax liability for the year omitting your lending club investments
2) Compute your tax liability for the year including your lending club investments
Presumably, your tax liability from #2 will be greater. The difference in these two numbers is the “tax cost” of your lending club activity for the past year.
3) At the time you file your tax return, you should include the “tax cost” of your lending club activity as a DEPOSIT in your xirr() calculation. Even though those funds are not going into your lending club account, it is part of the cost of your activity at LC, and you will see the inclusion of this “phantom” deposit reduce your total return. This new number is your “post tax” rate of return.
This method is how you would do a “rolling rate of return” computation. If you like to provide yourself with a sort of “annual report” then some additional gymnastics are required. Let’s say you want to compute your rate of return for calendar year 2012, and you end up paying $500 extra tax liability for your lending club investments — but you pay that amount in APRIL. For your “annual report” calculation, you would use your “post tax” rate of return seen in your “rolling” calculation (with the $500 charged in April) to calculate an equivalent (smaller) payment to be posted on Dec 31.
Myself — I think I’ll just stick with the rolling computation and not bother doing an annual report for myself. Life is too short. Some folks think the financial calculations are fun. I do not.
@Sean, I typically handle bonuses as an extra deposit because while it is a return on investment I am more interested in the return I receive from the notes themselves. I didn’t receive any bonuses in this time period.
@Walter, I don’t sell on Folio very much. I usually do just for an experiment. I know many investors swear by it but I am more interested in obtaining a good return by picking the best notes than by having to work to sell them on Folio. With over 3,000 notes spread over four accounts it would become quite a non-passive investment.
@Don, Thanks for the note about taxes. As with any investment your actual return will be dependent on your tax rate. I make no mention of it here because everyone’s tax situation is different and many people have p2p lending investments in an IRA. But you are quite right to mention it and if people want to determine their post-tax return they should do as you suggest.
Hi Peter,
thanks for your great post above.
I have started investing in lending club this year and increasing slowly every month. I know this may not be accurate question but how much % of total cash should one invest in lending club
Also how much % of my retirement savings would you recommend putting in traditional/roth ira with lending club? What is the downside that you think or difference from adding simple cash in smaller amounts like I am doing right now?
Ravi, Lending Club recommends no more than 10% of your liquid assets in p2p lending and I think that is a good guideline for most people. I actually wrote about that issue here: http://www.sociallending.net/investing-lending/what-percentage-of-your-investments-should-be-in-p2p-lending/
Investing in an IRA is certainly the best way to go because of the tax benefit. As for putting in a lump sum or small amounts over time I think it depends on your circumstances. As long as you stay fully diversified ($25 per loan unless you have more than $10K invested) it will make little difference in the long run.
Peter, thanks for this information. It is very useful. One question; now that Lending Club doesn’t include accrued interest in my account total, should I add it in before calculating ROI using the XIRR function? I’m only able to invest using the secondary market so am wondering which way you think is more accurate. Thank you.
Jerry
No, you should not add accrued interest back in. The statements have never included accrued interest and those are the numbers you should be using for your XIRR calculations.
What about the Accrued interest that I have coming but not received at end of month? Shouldn’t that be added to my EOM balance?
That is not how I like to do it. You could argue that this is your money and you could include it but I like to use the information on the Lending Club or Prosper statement. This does not include accrued interest. As long as you are using a consistent approach I think the simple way to do it is fine. There are far more complex valuation methodologies, the XIRR approach is simple and approachable for most investors.
Peter,
Thank you for the write up. One question. What # do you pull from your monthly statement? Do you pull the balance under account total?
Of so, wouldn’t this incorrectly skew your return as this does not account for past due notes?
I am a simple person. “Forget the math of time in using XIRR” I prefer to use ROI only. I invested 25,635.14, I received in a Cash Out of 27,348.20. a “profit” of 1,713.03 or 6.68%. Use my time for each payment and use XIRR it come out to 10.23%. Again I think real life is telling me I made 6.68%. Not buying XIRR.