Excel template for IRR calculation for P2P investing

  • Contributor


    As several people asked me through private message, I am posting here a simple Excel template for IRR calculation.

    You can choose the level of detail you want (it is always a tradeoff between calculation accuracy and time spent feeding the file).

    Example: you can register a "Received" based on when did you actually receive the inflows in your balance in the platform (e,g if today FS is crediting you back RM27, tomorrow RM 22. You could update the file up to that level of detail and the IRR would become more and more accurate. Like that, you can have a much better vision, as compared to if you were to record only the withdrawals out of the platform.

    In my case, I just don't bother to record the daily credit because on most of the platforms I aim at having 0 balance and I reinvest the proceeds as soon as they kick in. Therefore, I only record the withdrawals out of the platform or the existing platform value. I update this tracker once a month and see the evolution of every platform, to help me re-allocate resources based on each platform performance. So it seems a good tradeoff to me in terms of IRR calculation obtained and effort put on updating the file, but you can feel free to record it more accurately.

    Step-by-step guide to using the excel sheet:

    1. After each investment, select “paid” and input the date & amount invested
    2. Upon repayment, select “received” and input the date & amount repaid to you (after any platform/service fees paid are subtracted)
    3. XIRR real gross would be calculated in cell D4, where XIRR = internal rate of return for a schedule of cash flows that is not necessarily periodic (in this case it’s tied to the dates you have input) (more details on the Excel function)
    4. XIRR ptfm gross in Row 3 refers to the rate of return figure provided by the platform
    5. XIRR nett in Row 5 would allow you to also input returns after income/withholding taxes payable, or any other factors you’d like to consider

    With that, here's the tracking sheet! Enjoy!

    0_1550648426817_IRR template v2.xlsx

  • Community Manager

    Thank you @antoniomc27, this is really helpful to fellow P2P investors! :-D

  • Community Manager

    Should anyone wish to upload their excel document as well / add any enhancements to @antoniomc27's sheet, do drop me an email at weina@crowdfundtalks.com with your file attached so we can upload it on your behalf (unfortunately some of us have run into errors while uploading excel sheets, but do hang tight, we're looking into it currently!).

    Feel free to suggest any other dimensions we could take into account when calculating IRR as well / better track returns in general 💭

    Happy investing and analysing your returns!

  • Wow, big thank you @antoniomc27 for sharing it out of goodwill! :)

  • Contributor

    @QM here is a cleaner version of the template v1

  • @antoniomc27 Gracias!

  • Hey @antoniomc27, we'll input 'Paid' whenever we successfully invest? Or when we make a deposit?

  • @antoniomc27 I see some platforms which generate low returns compared to the rest in the document. Are you still investing with all of them? Or have you exited a platform because of its low XIRR? What's a suitable criteria for platform exit?

  • Contributor

    @popter i always invest within days of deposit (these platforms are generally highly liquid, I tend to avoid platforms with drag due to no availability of investments). But to get the IRR accurately, it is when you invest.

  • @antoniomc27 said in Excel template for IRR calculation:

    @popter i always invest within days of deposit (these platforms are generally highly liquid, I tend to avoid platforms with drag due to no availability of investments). But to get the IRR accurately, it is when you invest.

    Awesome. Thanks man! How regularly do you review your returns?

  • Contributor

    @popter I do it monthly. I update that template and then i have my Net Worth dasboard with a summary of the IRR evolution per investment.

    Normally I update on the 1st of every month, but in this case in the template the latest update is 22-Jan because I received a bonus and had to think fast where to allocate ;)

  • Contributor

    @wekwek in the file you can see an overview of the usual annual returns of each platform.

  • Contributor

    Hi @webnomad, sorry I did not notice your post before.
    Indeed, the return varies per platform. there are other platforms I have exited such as Robo.cash (too many late payments) and Swaper (fishy platform according to many reviews).

    The reason to keep myself invested in October is that the return is low because the risk is low too.
    The reason to keep Crowdestate is the wide variety of projects offered, so I don't mind it offers me lower returns.

    But more generally, these are my criteria for :

    From most to least important factor:

    A. Do due diligence: What do people say in the forums? I am especially attracted to those platforms that:
    - have a competent management team (e.g: proven track record, proactive on recovering loans).
    - are financially stable (offer moneyback guarantee and they are able to stick to it).

    B. What is the risk/return profile? Note that I do not rule out a platform just for not meeting a certain % net return, although 5% in Europe and 7% in Asia would be my absolute minimum.

    C. How does this platform fit in my current portfolio in terms of:
    - (Type of investment: SME, personal, Real Estate)
    - Geography and currency exposure

    D. Transparency: Is the customer support reachable? Do they have a blog/forum, do they publish regular updates of their notes/projects?

    E. Is there cash drag?

    F. Investments are automatic/easy, or you need to camp in the platform to fight to get in?

    G. Minimum funding amount: does it allow me to be diversified with the amount of money I intend to deposit?

    If it meets A and B, it's difficult to resist for me :D


    If my initial due diligence and estimation of platform risk/return turns sour (factors A and B again), then I consider exiting the platform.

    This is the case for both platforms exited so far: Robo.cash and Swaper.

  • No worries @antoniomc27 ! Thanks for your detailed response. I have some questions:

    @antoniomc27 said in Excel template for IRR calculation:

    have a competent management team (e.g: proven track record, proactive on recovering loans).

    Any favourites so far out of all the global platforms you're on?

    are financially stable (offer moneyback guarantee and they are able to stick to it).

    Moneyback guarantee, which platform has that?

    You mentioned an interesting point on minimum funding amount being important factor in ensuring diversification. Not just the initial amount, but also the minimum required for each round should be acceptable compared to the other investments I already have!

  • @antoniomc27 said in Excel template for IRR calculation for P2P investing:

    But to get the IRR accurately, it is when you invest.

    Thanks! Btw, to follow up on your previous point:

    You mentioned it would be more accurate to consider a negative outflow of cash when we actually manage to invest. e.g., today we've deposited $100 but managed to invest $50, so we'll input $50 as 'Paid'. Hence the IRR figure calculated based on this would refer to the rate of return on our actual investments.

    However, it just occurred to me that by indicating $100 instead, apart from the fact that it's less tedious, it also results in an IRR figure that has taken cash drag into account? This IRR figure would refer to the rate of return on investing with said platform? And to improve on this IRR figure, perhaps we should deposit in smaller amounts?

    Is my logic on track?

  • Contributor

    Hi @webnomad,

    i certainly do.

    Management-wise, I particularly like:

    • Funding Societies: their actions speak by themselves.
    • Grupeer: proactive management, workaholics and very approachable and transparent.
    • Envestio: a great team with good track record, and very responsive. I find the projects they publish very attractive in terms of risk-reward: they are high-yielding opportunities (avg 19%) that are actually worth investing in, and I attribute it to the due diligence of the team behind.
    • October: has a huge team behind, and are backed by several institutional investors. Their Achiles' heel is their low yield (avg 6%)

    Other management teams perhaps are not necessarily bad, just didnt have the opportunity to interact too much with them.

    In terms of buy-back, Mintos buy-back is world-class. For the loans stated, they guarantee they'll refund you, and they certainly do. Grupeer also does moneyback guarantee, it's just that I cannot judge because never activated so far. Robocash, on the other hand, a platform that i am exiting, takes ages to refund you and has high level of late loans.

  • @antoniomc27

    FS should hire you. Haha you’re good.

    Please “introduce yourself” hahaha

  • @antoniomc27 That's awesome! What's the level of interaction you get with Grupeer, Envestio and October?

    For Mintos, cool stuff. Haven't heard it before but sharing it with everyone here:

    Mintos' Buyback Guarantee: In general, loans with a buyback guarantee come with a lower interest rate than loans without it - the difference is the approximate estimated annualized bad debt rate. Loan originators charge borrowers higher interest rates than the rate they offer to investors on the Mintos marketplace. The difference covers administrative and marketing cost, as well as constitutes the profit margin of the loan originator.

    By offering a buyback guarantee, the loan originator keeps the borrower's default risk on its side. To compensate for this risk, the loan originator takes a higher share of the interest paid by the borrower. (https://www.mintos.com/en/invest/investor-protection/)

    Seems like buyback guarantee is a rather common offering of European P2P firms? Do most of the loans on Mintos come with that!

  • Contributor

    @popter that is absolutely right. By putting $100 you are accounting for the cash drag, which I consider a good practice.

    All in all, it also depends on how much you trust the platform. E.g: if I have $1000 in a platform with weak track record and which is not financially stable, I am taking an unnecessary risk. If those same $1000 are in a good one, I will not necessarily panic.

    Finally, as you say, to try to maximize IRR you have to try to top-up-and-invest kind of thing. But in my case, if this implies currency conversion fees or transfer fees, I do transfer in big batches. Which, by the way, can also be tracked with this template.

  • Contributor

    @googleging said in Excel template for IRR calculation for P2P investing:


    FS should hire you. Haha you’re good.

    Please “introduce yourself” hahaha

    haha @kaeley-wn @vamsi7 @shaun did you hear that? I ain't cheap though!

  • Contributor

    @webnomad some Spanish friends and I regularly email platforms for feedback / complaints. When they first come up we do some due diligence to see if they are worth investing in. Many of them (Grupeer, Mintos are active in p2p forums similar to this one, so interaction is quite easy. In some cases like October or Funding Societies they have a clear and public track record available to everyone if you google a bit.

  • @antoniomc27 THANKS SIFU!

  • Contributor

    @chekmeng glad you like it!


Looks like your connection to Crowdfund Talks | A Community for P2P Lending & Alternative Investments was lost, please wait while we try to reconnect.