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]