For today’s post, I want to respond to a reader question. Sean asked:
“Do you have the formula for Excel XIRR? I have two Lending Club accounts, am curious how to maximize returns, and believe calculating ROI is really helpful for that. Also, if you could explain why XIRR is a better indication of real return I’d appreciate it.”
If you want something done right, often it means doing it yourself, and calculating your peer to peer lending ROI (return on investment) is no exception. Lending Club and Prosper list a lender’s annualized return as the largest number on a lender’s account screen, and many assume this is an accurate number. The reality is that this number is often inflated.
We can do better. In today’s post we will examine three different ways to look at return, emphasizing how Excel’s XIRR function is the best method available.
Calculating P2P Lending ROI is Complicated
There is a reason a whole post can be written on this topic. For one thing, there are different points where the measurement can begin. Do I measure it from when the money is transferred over to Lending Club from my bank account? What about measuring it later on down the line, perhaps from when my loans get issued? And what about loans that are late? Do I value them the same as current loans that are being paid back on time?
Three Ways to Calculate Return on Investment
On-site (easiest and most inaccurate): Lending Club and Prosper have each provided a calculation they call annualized return. This method is based on the actual invested cash (Note: Prosper is more accurate than Lending Club). The problem is that Annualized Return does not take uninvested cash into consideration. Say you moved $10,000 into Lending Club and invested $25 in a single note earning 15%. Lending Club would say your return was 15%, even though $9975 is sitting there earning nothing and your real return is somewhere around 0.01%.- Portfolio Tools (more accurate): Third-party sites like NickelSteamroller offer Portfolio Analysis tools that can give us a better measurement. After we have uploaded our account’s notes.csv file, these tools helpfully reduce each note’s value by its status, meaning they devalue a note if it is in Grace Period or Late, something Lending Club and Prosper fail to do. These reductions are called loss factors, and they are based on the rates of recovery for the different loan statuses (see Lending Club’s loss factors below). This makes sense if you think about it. A $50 note is not really worth its full amount anymore if the borrower has stopped making their payments.
- XIRR() (most accurate): Microsoft Excel’s XIRR() function is the most accurate in that it measures the interest we have earned from the day we transfer money from our bank accounts. It is accurate in its simplicity: it does not consider individual notes but only measures how much money you started with compared to how much you have today. One of the best things about XIRR is that it works with the secondary market. If you buy or sell p2p lending notes on Foliofn, the on-site and third-party tools will stop being accurate. However, the XIRR method simply looks at the total value of your account, a figure that includes any notes bought or sold on Foliofn.
Calculating your Lending XIRR ROI in 3 Easy Steps
1. Create Spreadsheet: Open up a new spreadsheet in Microsoft Excel (or any Office program). Label one column Date and another Amount. It should look something like the image on the right. We will fill this spreadsheet with our bank account transactions to calculate XIRR, placing the date we made the transaction in column A and the transaction amount in column B.
2. List Transactions: Navigate to the Lending Club or Prosper websites to make a list of all the different transactions you have made. In Lending Club you will have to look through your statements like in the example below:
In the PDF statements, each transaction will look like this:
It can be frustrating to have to sort through all these statements, especially at first. Thankfully, we will eventually save this spreadsheet, so you will not have to look through these statements again as long as you keep the spreadsheet file handy.
In Prosper the transactions are listed on your Bank Account Transfers page:
When your bank spreadsheet is complete, it will look something like the example on the right. As you can see, I have three transactions listed. Two are deposits (4/1/2013 & 5/1/2013) and one is a withdrawal (6/1/2013). The deposits are typed in as positive sums; the withdrawal is a negative sum (which my version of Excel colors red). Finally, I made an entry with today’s date for the current total value of my account (bolded). Important: the total account value has to be typed in as a negative number for XIRR to work.
3. Calculate XIRR: Go to an empty cell and type in = (the equals sign) followed by XIRR. Then type (B2:Bx,A2:Ax). B2 equals the cell of your first amount and Bx is the cell of your last amount. A2 is the cell of your first date and Ax is the cell of your last date. So for our example I would type in =XIRR(B2:B5,A2:A5). The number that appears in the cell when I hit Enter is my XIRR ROI.
In this example, I would have an XIRR ROI of 8.53%, a number vastly more accurate than any on-site annualized return. I can save this spreadsheet for future use.
Option: Adding Your Own Loss Factors
We can make this number even more accurate by adding loss factors for the different loan statuses in our account. For instance, let’s say in the example above that within this person’s account ($7,045.98), $238 of this total was in notes with a Grace-Period status. We see in the chart below that 19% of Lending Club loans in Grace-Period are eventually lost and become Charged-Off.
To make our XIRR calculation more accurate, we could take the amount of Grace-Period notes ($239), discount it 19% ($239*0.19=$45.41), and subtract that amount from our total account value ($7045.98 minus $45.41 = $7000.57). We can do this for Late notes as well, and this would be a more-accurate account total we could use for our XIRR.
In the above spreadsheet, I’ve discounted my account value by $75.09. I got this total by combining the loss factors of Grace Period and Late notes together. With this discount, the new total value of the account would drop to $6,970.89 with an XIRR return of 4.33%.
Conclusion: The XIRR Learning Curve
This may be difficult for some people to understand, especially if you’re just starting out. Thankfully, once you have sorted through your account statements and recorded your deposits and withdrawals, calculating XIRR is a surprisingly easy thing to do (promise!). Any time you would want to calculate your XIRR, you would simply (1) open your saved spreadsheet, (2) add rows for any further transactions that have happened, and (3) redo the x cells in the =XIRR(B2:Bx,A2:Ax) calculation. Hit Enter. Voila! Your ROI is hyper-accurate and you are farther along in becoming a successful peer to peer lender.
Questions or comments? If you enjoyed this please Like or Tweet it below.
[image credit: Marcin Wichary “Calculator keyboard close-up” CC-BY 2.0]
Simon this is the most concise explanation I have ever seen. I bet every newcomers will benefit from this. Bad I didn’t have this explanation when I was incorporating my XIRR into my account reports. I would have a lot easier start.
Thanks Martin. That’s very kind of you.
An additional point of interest is that thousands of Lending Club lenders who can only trade on the secondary market have absolutely no clue about the return they’re earning.
Great article…..I just made my first deposit with Lending Club waiting for my initial batch of loan to become active.
I see your point about not taking “uninvested” cash into account when calculating your return, but at the same time, if that cash sits earning no interest, it’s no different than other savings I have sitting in my Fidelity money market account or my bank checking account……it’s simply idle cash. I think that it’s not whether one methodology is better than the other, it’s simply a matter of understanding the differences in each calculation and the variables involved. Your calc is better if you need a % return for ALL available cash…..if you’re not interested in a return % for idle cash, Lending Club isn’t far off the mark, assuming you understand their limitations with excluding late payments, etc.
Great article, great site……giving that I’m new to P2P lending, I’ve read through several of your articles and found them all to be helpful.
Thanks!
My pleasure Jeff. And good point with the idle cash concept. Perhaps another reason for measuring a more comprehensive ROI is to emphasize the penalty of not reinvesting returns. But it’s true, purely on invested cash, LC’s NAR is not a bad place to start.
Thanks for stopping by.
May be a little depressing, but why not also discount the current notes? By your method there should be a .56% charge against those too…
This is true :) Lenders debate about whether they should discount ‘current’ loans. I’m OK with not doing it, as current loans can typically fetch a 2-10% premium on the secondary market. But for ‘buy and hold’ lenders, discounting their current loans is probably a good idea.
Can you expand on this, it sounds counter intuitive. Or maybe I am not understanding it correctly. So if I buy a note on LC I can then turn around and sell it while it is current for more then I invested? If so how soon after you buy it can you sell it for this premium? THanks!
This is an awesome question. One I have spent the last 3 hrs. looking for an answer. I am new to LC and trying to come up with my strategy. Learning much from these posts and would like to see what people have to say about this question.
This is good analysis Simon thanks.
Anyone knows who performs better in borrower selection and screening – Lendingclub or Prosper ? I have accounts in both and investing since Feb 13. I thought it will be good to know from you guys what your actual experience has been – where did you have more defaults with time.
Honestly they feel quite similar. I do like Lending Club a bit more at the moment for two reasons. Their site has a better interface and they allow sale of late notes.
Great post & very helpful website you’ve got, Simon.
How do you account for reinvested earnings with XIRR? Would these amounts be treated as “deposits” or handled differently? Or perhaps XIRR takes this into consideration?
Any insight would be appreciated!
Hi Rick. That’s the beauty of XIRR – it takes earnings into account when you add your account total in the final Excel field.
Best, Simon
Hi Simon,
Great article! I use a peer to peer lending site based in New Zealand that’s fairly new to the market. For Account total do you use the outstanding principal figure or the value of issued notes?
Kind Regards
Hi Thomas. American sites like Lending Club have an “Account Value” figure that is the total of your invested cash (outstanding principal) as well as your uninvested cash. Basically, what is your entire account worth?
I’m not sure what your industry does in NewZealand, but I assume if you’re using XIRR, the idea is to try and summarize your entire account’s worth, not just a portion of it, for the best measurement of how your investment has grown.
Cheers,
Simon
You mention “I made an entry with today’s date for the current total value of my account”.
Where is Current Total Value of an account reported in Lending Club (I don’t see it in my summary – I see Account Total but it looks way too low). Does that number not include Folio notes pending settlement and have to be added in somewhere?
Hi RR. The value to use is your Account Total. This does not include Folio notes that have not yet completed sale. However, it’s a decent valuation of your account.
No need to add Folio in somewhere else. If you wait a bit for these secondary market transactions to settle, you’ll get the accurate reading you’re looking for.
Does the article need an update to reflect Lending Club’s Adjusted Net Annualized Return (Adjusted NAR)?
In your discussion of NSR’s Portfolio Tools, you write “After we have uploaded our account’s notes.csv file, these tools helpfully reduce each note’s value by its status, meaning they devalue a note if it is in Grace Period or Late, something Lending Club and Prosper fail to do.”
The large number which Lending Club now features on the Account Summary page is the Adjusted NAR and it takes a loan’s status into account.
Lending Club’s explanation of NAR and Adjusted NAR can be found at https://www.lendingclub.com/landing/about-nar-2.action
Indeed :)
I feel like Accrued Interest should be considered in account value (at least for current notes).
I’m also trying to figure a way to get XIRR to work on individual notes.
I’ll let you know if I figure something out.
I think you forgot to include some important factors .
I have no knowledge of the WebSites you mentioned
in your article (a really nice one , by the way) ,
but , in my country ,
the WebSite takes 1% from any money they pay you back ,
AND , they reduce 15% (of the interest only) for tax .
As a VBA programmer myself ,
i have no problem to correct your calculations ,
but , maybe you should explain that in your text / example .