Colminey
Subscribe
for updates
How to Calculate P2P Returns using XIRR
31/08/2019

How to Calculate P2P Returns using XIRR

Starters' guide to measuring the returns of your investment portfolio

XIRR is a simple and powerful function built into Google Sheets and Microsoft Office Excel. Short for “eXtended Internal Rate of Return”, it measures the daily change in your portfolio value based on a series of deposits and withdrawals, and calculates the annualised rate of return.

It can be used for calculating the return of any type of investment, including loan financing and crowdfunding, stocks and bonds, real estate and crypto. It also works for any investment duration, from 1 day to 1 lifetime. Let’s begin!

Preparing your spreadsheet

Start with a new sheet and create these three columns:

A B C
1 Date Cash FlowAnnualised Return

Add the date and amount of your first deposit. Some people use positive numbers to represent deposits, some prefer negatives*. I’ll use the first method, starting with a deposit of 1000 Euros:

ABC
1DateCash FlowAnnualised Return
201/01/191000

Now add today’s date using the =today() function, and enter the current value of your portfolio. If you used positive numbers for deposits, then your current portfolio value should be a negative number – imagine that you withdrew your entire portfolio today in order to calculate the return:

ABC
1DateCash FlowAnnualised Return
201/01/191000
3 =today()
05/03/19
-1013.01

Adding the XIRR Function

Add the XIRR function like this:

ABC
1DateCash FlowAnnualised Return
201/01/191000=xirr(B2:B, A2:A)
0.077703188
3=today()
05/03/19
-1013.01

Adding formatting

Formatting makes the data look nicer without actually changing the content of the table cells.

In Google Sheets: Select cell C2. In the top menu click Format > Number > Percent. Then select column B:B, click Format > Number and choose either Currency, Accounting or Financial. Also select the appropriate currency.

In Excel: Select cell C2, right-click it and choose Format Cells > Percentage. Then select column B:B, right-click it, and choose Format Cells > Currency or Accounting. Also select the appropriate currency.

The result should be something like this (in financials, parenthesis are sometimes used instead of a minus sign):

ABC
1DateCash FlowAnnualised Return
201/01/191000=xirr(B2:B, A2:A)
7.77%
3=today()
05/03/19
(€1013.01)

Adding rows

As you withdraw or deposit more money into the investment, you will need to add rows. The easiest way to do this is to keep the “today” row last, and add rows above it:

In Google Sheets: Right-click the “today” row and choose Insert 1 above.

In Excel: Right-click the “today” row and choose Insert.

In a few months, your table should look something like this:

ABC
1DateCash FlowAnnualised Return
201/01/19€1000.00=xirr(B2:B, A2:A)
11.21%
310/03/18(€500.00)
417/05/19€200.00
5=today()
20/06/19
(€1349.67)

I like to add a coloured background to the bottom row, to distinguish today’s value from the list of deposits and withdrawals.

Fees, deductions and losses

Depending on platform and investment success, you might also lose money on certain things:

  • Platform fees
  • Currency conversion fees
  • Tax deduction at source
  • Deductions that go towards a provision fund
  • Loss from secondary market transactions
  • Loss from bad debts

In all of these circumstances, the loss will be reflected in your account value. That’s the beauty of XIRR: It shows your actual returns after fees, deductions, late payments and loss (but usually before taxes).

Special income

Personally, I view cashback bonuses, secondary market profits, and bonuses earned from being invited to a platform – as part of my returns. Again, as long as the bonus is reflected in your account value, it will already be included in the XIRR calculation.

However, I prefer to exclude affiliate commissions earned by inviting other people to the platform. That requires listing those bonuses in your table as deposits. Imagine that the bonus was paid into your personal bank account, and you deposited it manually into the platform for investment.

At this stage it’s also a good idea to add a column for comments:

ABCD
1DateCash FlowCommentsAnnualised Return
201/01/19€1000.00=xirr(B2:B, A2:A)
11.14%
310/03/18(€500.00)
417/05/19€200.00
525/06/19 €7.12Affiliate bonus
6=today()
04/07/19
(€1362.01)
Remember: The things you deduct when calculating returns should not be deducted when reporting your taxable income. In fact, this entire post is irrelevant for tax purposes. For that you need to use the annual income report provided by the investment platform.

Adding checkpoints

Many of us like to track our portfolios on a monthly basis. You can add table rows to indicate the account value at the beginning/end of each month, using the Comments column or a dedicated column. As long as the Cash Flow column is empty, the XIRR function will not be affected.

ABCD
1DateCash FlowCommentsAnnualised Return
201/01/19€1000.00=xirr(B2:B, A2:A)
12.75%
301/02/19€1009.86
418/02/19€150
5=today()
25/02/19
(€1168.59)

You can now add a separate column for calculating the monthly change using basic calculus. That’s good enough for me. But if you insist on calculating XIRR for each month, you can try the following trick.

List each month’s portfolio value as both a withdrawal and a deposit, and create a separate XIRR function for each month:

ABCD
1DateCash FlowMonthly Annualised ReturnTotal Annualised Return
201/01/19€1000.00=xirr(B2:B, A2:A)
10.81%
301/02/19(€1009.86)=xirr(B2:B3, A2:A3)
12.25%
401/02/19€1009.86
501/03/19(€1016.73)=xirr(B4:B5, A4:A5)
9.24%
601/03/19€1016.73

You can insert these special rows in-between the actual deposits and withdrawals. The pairs of positive and negative values balance each other out, so the overall XIRR calculation is not affected. However, your table might appear cluttered.

That’s it, you can now track your own portfolio and verify the returns reported by investment platforms! Let me know if this guide was helpful, or if you have other recommendations.


XIRR was originally intended for calculating the return of a specific investment, where money leaves your account and you no longer see it until it is repaid. In that scenario, it makes sense to list outgoing cash flows in negative numbers, and incoming cash flows in positive numbers.

The same logic is commonly applied for calculating portfolio returns: Deposits into the investment account are listed as negative numbers, and withdrawals as positive numbers. But here I find the convention confusing. When depositing money into your account, you don’t feel like it’s flowing out, but in. Of course, the money does leave your account to finance loans, but you keep seeing it on your account balance, so the psychology is different.

Also, it’s not a finite investment, where you wait for the money to flow back into your bank on the final payment date, and only then run the XIRR formula. Rather, the money is continuously cycling between loans, and you are tracking the ongoing performance based on a hypothetical assumption that the money is always at your disposal.

Since we are already “not using XIRR as we’re supposed to”, I feel it’s OK to bend the rules a step further and use positive numbers for deposits, if that makes more sense to you (as it does to me). If you prefer the more conventional method, you can always flip the positives and negatives and get the exact same end result.

Facebook Comments:

Leave a Reply

Your email address will not be published.