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.

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | 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.

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | Profit |

2 | 21. 2. 2020 | 2500 | ||

3 | 25. 2. 2020 | 10 | ||

4 | 26. 2. 2020 | 90 |

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

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | Profit |

2 | 21. 2. 2020 | 2500 | ||

3 | 25. 2. 2020 | 10 | ||

4 | 26. 2. 2020 | 90 | ||

5 | 1. 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 th**e same amount of cash flow** (but without the minus).

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | Profit |

2 | 21. 2. 2020 | 2500 | ||

3 | 25. 2. 2020 | 10 | ||

4 | 26. 2. 2020 | 90 | ||

5 | 1. 3. 2020 | -2605,08 | =xirr(B2:B5;A2:A5)8,42% | februar: 5,08 €. |

6 | 1. 3. 2020 | 2605,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%**.

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | Profit |

2 | 21. 2. 2020 | 2500 | ||

3 | 25. 2. 2020 | 10 | ||

4 | 26. 2. 2020 | 90 | ||

5 | 1. 3. 2020 | -2605,08 | =xirr(B2:B5;A2:A5)8,42% | februar: 5,08 €. |

6 | 1. 3. 2020 | 2605,08 | ||

7 | 1. 4. 2020 | 2631,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.

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | Profit |

2 | 21. 2. 2020 | 2500 | ||

3 | 25. 2. 2020 | 10 | ||

4 | 26. 2. 2020 | 90 | ||

5 | 1. 3. 2020 | -2605,08 | =xirr(B2:B5;A2:A5)8,42% | februar: 5,08 €. |

6 | 1. 3. 2020 | 2605,08 | ||

7 | 1. 4. 2020 | -2631,52 | =xirr(B6:B7;A6:A7)12,63% | march: 26,44 € |

8 | 1. 4. 2020 | 2631,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.

A | B | C | D | |
---|---|---|---|---|

1 | Date | Cash Flow | XIRR | Profit |

2 | 21. 2. 2020 | 2500 | ||

3 | 25. 2. 2020 | 10 | ||

4 | 26. 2. 2020 | 90 | ||

5 | 1. 3. 2020 | -2605,08 | =xirr(B2:B5;A2:A5)8,42% | februar: 5,08 €. |

6 | 1. 3. 2020 | 2605,08 | ||

7 | 1. 4. 2020 | -2631,52 | =xirr(B6:B7;A6:A7)12,63% | march: 26,44 € |

8 | 1. 4. 2020 | 2631,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:

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.