How to Calculate XIRR for Annualized Returns
What You Will Learn
- How to use XIRR to easily calculate annualized returns
- How to use the free spreadsheet to calculate annualized returns
Calculate XIRR Fast and Easy
The current portfolio tracking spreadsheet has its shortcomings as it doesn’t include non invested cash when calculating returns.
The true returns of any portfolio will include all cash flows and I have found the XIRR function in excel to be the best to calculate annualized returns.
If calculating returns was as simple as taking the beginning balance and ending balance and then calculating the absolute return, tracking investment returns would be so much easier.
But there is time value in money and once you start depositing or withdrawing cash and receiving dividends, it makes calculating annualized returns that much more difficult.
Investing $1,000 in January certainly is much different to investing $1,000 in December, before the year end.
That’s where the XIRR feature in Excel comes in.
Using XIRR to Calculate Annualized Returns
With XIRR you can calculate annualized returns even when cash flow for your account is irregular.
As an example, the starting balance is $10,000 with regular deposits and some gains totaling a portfolio balance of $15,000 on Jun 27 2010.
At first glance, without taking into account the cash deposits, you could be fooled into thinking the return is 50%. However, if you use the XIRR function, the calculated annualized return is much less at 28.8%.
As you can see, if the cash flows become longer and irregular with different cash flows, the calculation by hand becomes virtually impossible.
Before we get deep into the topic, just click on the image below to get my Piotroski Score spreadsheet. You’ll also get exclusive content and resources we don’t share anywhere else.
Free Spreadsheet to Calculate Annualized and Cumulative Returns
Now you can calculate your returns quickly and easily with this free spreadsheet to calculate your portfolio returns.
Enter your beginning balance at the very top with deposits as positive values and withdrawals as negative values.
Enter the date next to each corresponding cash flow and if you need to calculate the return for any particular date, enter it into the third column.
Just make sure the ending balance is negative with a date to prevent errors.
The results will then look like the image below.
Full credit goes to a site called Gummy Stuff that is unfortunately no longer online for giving me this idea.
To Calculate XIRR easily, Download the Portfolio Return XIRR Spreadsheet
- No special plug ins required.
- Just enable macros and run.
