How to Calculate XIRR – Easy Step-By-Step Guide

As explained in P2P Glossary, XIRR is a formula to calculate your actual return on investment. Most P2P platforms use other, less correct formulas, like IRR or NAR, which show a higher percentage return on your investment than there actually is. It is therefore almost essential for every investor, to know how to calculate XIRR for their benefit.

To explain in a bit more scientifically, XIRR stands for Extended Internal Rate of Return. It measures daily change in your portfolio based on deposits and withdrawals and calculated annualized rate of return.

How to Calculate XIRR Overview

I divided How to Calculate XIRR guide into logical chunks:

• Preparing spreadsheet,
• Adding formatting,
• Adding deposits,
• Calculating monthly XIRR,
• Calculating overall XIRR.

Prepare Spreadsheet

I use Google Sheets so that I can access it from anywhere, but you can also use other programs, like Excel or OpenOffice. The formula is the same in all of them.

To help you better understand, I use the italic style for text that has been newly added to the table. Bold text (mostly just =xirr(…)) is not actually shown at the end, but I wrote it to show you where to put it.

Let’s start with creating 4 columns – Date, Cash Flow, XIRR, and Profit.

Add Formatting

Formatting makes data look easier to read. Let’s see what’s the best way to do it.

In Excel:

• Select row A -> right click -> format cells -> date
• Select row B -> right click -> format cells -> currency or accounting
• Select row C -> right click -> format cells -> percentage

In Google Sheets:

• Select row A -> right click -> format cells -> date
• Select row B -> menu format -> number -> currency or accounting or financial
• Select row C -> menu format -> number -> percent

Add Deposits

We start by adding deposits. I deposited my first amount of 2500 € on 21. 2. 2020, my second amount of 10 € on 25. 2. 2020 and my third amount of 90 € on 26. 2. 2020. So let’s add that.

Calculate Monthly XIRR

Then came the first of March, and I wanted to calculate monthly XIRR for February. So in cell B5, I wrote down the amount of money I had on 1. 3. 2020 but added a minus before it (so -2605,08). In cell C5, I wrote =xirr(B2:B5;A2:A5) and pressed enter. This calculated XIRR for the time from the start of investing (A2) do current date (A5). It calculated it to be 8,42%. In cell D5 I manually calculated and wrote down the difference between the current amount of cash (2605,08) and all the deposits (2600), which turned out to be 5,08 €.

So my return for February was 8,42% or 5,08 €.

After that, we have to add additional (6th) row – same date and the same amount of cash flow (but without the minus).

Then, one full month came around and it was 1. 4. 2020. I wanted to calculate monthly XIRR for March, so I added 1. 4. 2020 as the date and amount of money I had on 1. 4. 2020, but added a minus before it (so -2631,52). In cell C7, I wrote =xirr(B6:B7;A6:A7). This calculated my monthly XIRR (so just from A6 to A7) to be 12,63%.

Let’s do not forget to add an additional row, similar to the 6th row.

This is how you calculate monthly XIRR.

Calculate Overall XIRR

If you want to calculate XIRR for the whole time (since you started investing to today), you add a new row with the current date (=TODAY()), amount of money on the current date (2633,03) and XIRR formula for the whole time (=xirr(B2:B;A2:A)). Let me show you by adding that to the 9th row.

If you now want to know how to calculate XIRR for May, you just insert 2 additional rows between the current 8th and 9th row and fill them up with the date, amount of money, and XIRR formula.

For reference, my current table for calculating XIRR for my Iuvo Group investments:

And for Mintos:

That’s it! You can now track your actual monthly and overall returns.

If you are stuck and need help, write a comment below (or shoot me email to [email protected]) and I will gladly share my Google Sheet with you and help you with your numbers.