Valentin Kragelj

Valentin Kragelj

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.

ABCD
1DateCash FlowXIRRProfit

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.

ABCD
1DateCash FlowXIRRProfit
221. 2. 20202500
325. 2. 202010
426. 2. 202090

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

ABCD
1DateCash FlowXIRRProfit
221. 2. 20202500
325. 2. 202010
426. 2. 202090
51. 3. 2020-2605,08=xirr(B2:B5;A2:A5)
8,42%
februar: 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).

ABCD
1DateCash FlowXIRRProfit
221. 2. 20202500
325. 2. 202010
426. 2. 202090
51. 3. 2020-2605,08=xirr(B2:B5;A2:A5)
8,42%
februar: 5,08 €.
61. 3. 20202605,08

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

ABCD
1DateCash FlowXIRRProfit
221. 2. 20202500
325. 2. 202010
426. 2. 202090
51. 3. 2020-2605,08=xirr(B2:B5;A2:A5)
8,42%
februar: 5,08 €.
61. 3. 20202605,08
71. 4. 20202631,52=xirr(B6:B7;A6:A7)
12,63%
march: 26,44 €

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

ABCD
1DateCash FlowXIRRProfit
221. 2. 20202500
325. 2. 202010
426. 2. 202090
51. 3. 2020-2605,08=xirr(B2:B5;A2:A5)
8,42%
februar: 5,08 €.
61. 3. 20202605,08
71. 4. 2020-2631,52=xirr(B6:B7;A6:A7)
12,63%
march: 26,44 €
81. 4. 20202631,52

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.

ABCD
1DateCash FlowXIRRProfit
221. 2. 20202500
325. 2. 202010
426. 2. 202090
51. 3. 2020-2605,08=xirr(B2:B5;A2:A5)
8,42%
februar: 5,08 €.
61. 3. 20202605,08
71. 4. 2020-2631,52=xirr(B6:B7;A6:A7)
12,63%
march: 26,44 €
81. 4. 20202631,52
9=TODAY()
23. 4. 2020
2633,03=xirr(B2:B;A2:A)
7,74%

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:

How To Calculate XIRR table

And for Mintos:

How To Calculate XIRR table 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.

Leave a Comment

Your email address will not be published.

Content
profile_picture

Valentin Kragelj

Heyo, my name is Valentin and I am a 29 year old investor from Slovenia.  I started investing in ETFs and P2P lending recently, and decided to share my path with you. Because sharing is caring. :)

P2P portfolio: 4500 €

0%

Emergency fund: 2000 €

0%

Passive income: 31 €

0%
P2P platform XIRR
IUVO Group
12,07 %
Mintos
21,46 %
Lendermarket
(TBU on 1. 9. 2020)

Disclaimer

All posts represent the opinion of the individual authors. P2pguarantee.eu takes no responsibility for any claims or statements made in these posts.