Menu

XIRR Magic: Calculating Returns on Lending Club & Prosper

XIRR ROI for Lending Club and Prosper

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

  • Net Annualized ReturnOn-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

XIRR Spreadsheet11. 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:

Lending Club Bank History

In the PDF statements, each transaction will look like this:

Lending Club Withdrawl Example

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:

Prosper Bank History

XIRR Spreadsheet2When 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.

XIRR Spreadsheet3

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.

Lending Club Loss Factors 7-2013

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.

XIRR Spreadsheet4

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]

Like what you read?
Join over 1,000 readers who get quality lending articles by email. Subscription includes a free download of my peer to peer lending eBook ($25 value).
Like what you read?
Join over 1,000 readers who get quality lending articles by email. Subscription includes a free download of my peer to peer lending eBook ($25 value).

{ 10 comments… add one }

  • Martin July 17, 2013, 10:06 PM

    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.

    • Simon Cunningham July 17, 2013, 11:00 PM

      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.

  • Jeff Coleman July 23, 2013, 7:45 AM

    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!

    • Simon Cunningham July 23, 2013, 9:05 AM

      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.

  • Mike Hardy August 12, 2013, 8:00 PM

    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…

    • Simon Cunningham August 12, 2013, 8:34 PM

      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.

  • T Sander November 11, 2013, 9:28 AM

    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.

    • Simon Cunningham November 11, 2013, 9:30 AM

      Honestly they feel quite similar. I do like Lending Club a bit more at the moment for two reasons. They are profitable and they allow sale of late notes.

  • Rick A February 19, 2014, 6:09 AM

    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!

    • Simon Cunningham February 19, 2014, 10:02 AM

      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

Leave a Comment