How to calculate XIRR for Annualized Returns in Excel
How to calculate XIRR for Annualized Returns?
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.
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.
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.
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.
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.
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.
Template
Further reading: Basic concepts Getting started with Excel Cell References
