• Subscribe
  • Contact Us
  • About LendIt Fintech News
  • Home
  • Menu Item
  • Menu Item
  • Menu Item
  • Menu Item

Lend Academy

LendIt Fintech News: Daily Coverage of Fintech & Online Lending


  • Editorial
  • Daily News
  • Podcast
  • Investor Forum
  • Events

How to Calculate Your Real P2P Lending Return with XIRR

July 9, 2012 By Peter Renton 21 Comments

Views: 2,500

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.

Filed Under: Investing/Lending Tagged With: return, ROI, XIRR

Views: 2,500

Comments

  1. Simon says

    July 9, 2012 at 12:03 pm

    Thanks Peter. I’m curious how accurate the NickleSteamroller.com analysis page is compared to XIRR()?

    Reply
  2. Peter Renton says

    July 9, 2012 at 12:19 pm

    @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.

    Reply
  3. Sean says

    July 9, 2012 at 1:07 pm

    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.

    Reply
  4. Walter says

    July 9, 2012 at 8:30 pm

    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?

    Reply
  5. Don says

    July 10, 2012 at 7:26 am

    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.

    Reply
  6. Peter Renton says

    July 10, 2012 at 9:55 am

    @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.

    Reply
  7. Ravi says

    September 25, 2012 at 9:01 am

    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?

    Reply
    • Peter Renton says

      September 27, 2012 at 4:05 am

      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: https://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.

      Reply
  8. Jerry says

    October 18, 2013 at 5:06 pm

    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

    Reply
    • Peter Renton says

      October 18, 2013 at 8:49 pm

      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.

      Reply
  9. Larry says

    January 31, 2017 at 10:11 am

    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?

    Reply
    • Peter Renton says

      January 31, 2017 at 11:27 pm

      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.

      Reply
  10. Scottt Hull says

    March 9, 2017 at 7:47 am

    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?

    Reply
  11. fred ford says

    July 18, 2017 at 1:16 pm

    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.

    Reply

Trackbacks

  1. My Quarterly P2P Lending Results – Q2 2012 says:
    July 11, 2012 at 8:32 am

    […] this week I gave some detail as to how I calculate my real p2p lending returns with the XIRR function, so I use this same method to calculate my returns across all my accounts. Speaking of which I […]

    Reply
  2. My Quarterly P2P Lending Results – Q3 2012 says:
    October 15, 2012 at 12:46 pm

    […] any additions I made to the accounts, total interest earned and my real return calculated by the XIRR method. The Return on Site shows the return that was displayed at Lending Club or Prosper at the end of […]

    Reply
  3. Four Thoughts on Consumer Debt Notes says:
    October 23, 2012 at 12:08 pm

    […] Lending Club NAR may not reflect your real return. It is always useful to calculate your actual return on your p2p lending investments and not just rely on what Lending Club or Prosper tells […]

    Reply
  4. The Super Simple High Return Strategy for Lending Club and Prosper says:
    December 19, 2012 at 11:42 am

    […] reality though, the average investor is probably earning a real return that is 1-2% less than those numbers. Now, 7% can seem like a great return today given the […]

    Reply
  5. My Quarterly P2P Lending Results – Q4 2012 says:
    January 15, 2013 at 3:04 pm

    […] last 12 months, additions I have made, total interest earned and my real return that I calculate by the XIRR method. The Return on Site number shows the official return that was displayed at Lending Club and Prosper […]

    Reply
  6. Comparison of Different P2P Lending Investment Strategies says:
    May 10, 2013 at 6:59 am

    […] ROI we used Excel’s XIRR function, which takes a periodic set of cash flows (investments and repayments on various dates) and uses a […]

    Reply
  7. Statistics reports: Your annualized net return on investment - Bondora Blog says:
    October 14, 2016 at 6:49 am

    […] calculations using XIRR function in Excel. Some of these examples you can see from articles of Lend Academy and P2P Lending […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Investor Intelligence

Peter Renton's Returns

Investor Forum

Lending Club Review

Prosper Review

Investor Resources

Most Popular Editorials

The Pure Marketplace Lending Model is Dead, the Hybrid Takes its Place

The 2018 Lending Club and Prosper Tax Guide

My Returns at Lending Club and Prosper

Map of Available States for Lending Club and Prosper Investors

Banks and Marketplace Lending Platforms: Ideal Partners?

Subscribe to the Podcast

Subscribe to the Lend Academy Podcast on iTunes
Subscribe to the Lend Academy Podcast
List of Podcast Episodes

Archives

Follow @LendAcademy Follow @LendIt

ABOUT LENDIT FINTECH NEWS

LendIt Fintech News, Powered by Lend Academy, has been bringing you all the news and information about fintech and online lending since 2010 when it was founded by Peter Renton. We not only have the industry’s most active news site, but also the largest investor forum and the first and most popular podcast.

We are a team of fintech enthusiasts who have been covering the industry for many years. With a deep knowledge of online lending, digital banking, blockchain, artificial intelligence and more our team covers the daily news and writes in-depth editorials.

Recent Editorials

  • Top 10 Fintech News Stories for the Week Ending April 10, 2021
  • Podcast 293: Atif Siddiqi of Branch
  • Caring Consumer Collections Policies Gain Traction
  • Top 10 Fintech News Stories for the Week Ending April 3, 2021
  • Podcast 292: Ken Rees of Covered Care

Copyright © 2021 · Metro Pro Theme on Genesis Framework · WordPress · Log in