Colminey

Get email notifications on new posts:

Loading
How to calculate portfolio returns using XIRR
31/08/2019
How to calculate portfolio 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
2
3
4
5

Add todays’ date to cell A2 using the formula =today(). Then type the current value of your portfolio into cell B2. I also like to add a background colour to row 2 to distinguish it from the rest of the document.

ABC
1DateCash FlowAnnualised Return
2=today()
05/03/19
1396
3
4
5

Add the dates and amounts of your deposits and withdrawals. Use negative values for deposits, since you “lost” your money by investing it, and positive values for withdrawals, since you “gained back” the money by withdrawing it.

ABC
1DateCash FlowAnnualised Return
205/03/191396
301/01/19-1000
415/02/19100
504/03/19-300

Adding the XIRR Function

Add the XIRR function like this:

ABC
1DateCash FlowAnnualised Return
205/03/191396=xirr(B2:B, A2:A)
0.09080592707
301/01/19-1000
415/02/19100
504/03/19-300

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
205/03/1913969.01%
301/01/19(€1000)
415/02/19100
504/03/19(€300)

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.

Facebook Comments:

Leave a Reply

Your email address will not be published.