How to calculate XIRR for Annualized Returns?

The understanding of usefulness of XIRR function in Excel has different reasons. There are some that would find it useful for one thing, while it might be useful for other things. It is practically useful for anything that has to do with calculating your returns. There are actually differences in time value when it comes to investing money. When depositing and withdrawing money, and then receive dividends, it becomes much more difficult to make the calculation of the annualized returns. This is because there is certainly much differences in making an investment of $1,000 in January, to the one thousand dollars invested in December, right before the year ends. This make the XIRR feature Microsoft Excel something that simplify the calculation.

When all data has been gathered, then it would be easy to calculate the annualized return. This is because there are true returns of any portfolio will be including all cash flows, and the XIRR function in Excel is best suitable for calculating the returns. If it happens that return calculation would be as simple as taking beginning balance and ending balance, and then finally calculate absolute return, and this would make tracking investment returns so much easier.

We are going to do the XIRR calculation together, and I will be using Microsoft Excel 2013.

Necessary Data

This is the first step in using the XIRR feature in Microsoft Excel. The data has to be directly relevant for the calculation. The relevance are flexible, and dependent on the timing of each investment. I want to know my XIRR annualized returns for the last year, which makes it possible for the calculation to be quite different. It is possible for your calculation to bring a whole new result, because it has to do with the date. This is because it is natural for you to set the date between January 1st and December 31st.

xirr data

The first thing we need to focus on is the dates. The dates are when the investments are made. This is important because, even the date matters as there is differences to an investment made in the beginning of the year and the one made in the end of the year. You should click on the column, as it is marked in the number 1, and then type in =date(year; month; date) in that same column, as it is showing in the area labeled as number 2. You should follow this steps to compose all the dates that the investments are made.

amount

Click on the column that is beside a specific date, as it is labeled as number 1, and then surrounded in green. You should type in the amount, as it is showing in number 2. It is important to know the amount invested on that very same day. As you could see, we have placed a subtraction in front of the column. This is to make it possible for you to familiarize yourself with a scenario, where there is subtraction prior to the investments.

Annualized XIRR Calculation

This is where we would now calculate your annual returns with the XIRR feature.

xirr formula

Click on any empty column, as the case was, in the place labeled as number 1, and then type in =XIRR(value; dates) in that column, as it is displaying in the area labeled as 2, and then press enter. It is important to first choose all the values, after placing them in the same row.

format cells

Click on the column beside the calculation you made in previous step, as it is in the area marked as number 1, and type in XIRR. This allows you to know which column has the calculation. On the column you’d calculated, right click with the mouse on it, as labeled as number 2, and then choose Format Cells, which is number 3.

percentage

The format cells would be showing, and the number tab should be displaying, which ultimately allow you to choose percentage, as labeled as number 1, and then choose two decimal, as it is done in the place labeled as number 2, and finally press ok.

That’s the XIRR calculation for the annualized returns on investment.

How to calculate XIRR for Annualized Returns?