XIRR is a very useful function to calculate rate of returns on an investment or on a portfolio. You can read my previous post to get some background information about What is XIRR? How to incorporate this into your portfolio.

It is very important to understand how your portfolio is performing to help you make future decisions. Unfortunately many individual investors do not understand how to calculate returns of their portfolio when it involves a lot of transactions across many years.

Here, I’ll walk you through to create an excel sheet to calculate annualized returns for your portfolio, you can use a similar logic for calculating returns for individual stocks.

Before we proceed make sure that XIRR function is installed (you will see a “#NAME?” when you type =XIRR() in any cell). To install it, have your Office CD ready, launch Excel, and go to Tools – Add-Ins and select “Analysis ToolPak”. It will prompt you for the Office CD and you can select to install this.

Step 1: Collate all your statement of accounts from your brokerage for the time period you are calculating the returns for. Determine the capital gains taxes you have paid for (if any) and note down all your dividend payments that you have received in your bank account or in your brokerage amount. Write down the dates of amounts of all these transactions.

Step 2: Open an excel spreadsheet, and create 3 columns with headings “Transaction Date”, “Transaction Details” and “Transaction Amount”. In the first row of the table, enter the portfolio value as 0 if you are just starting out otherwise enter the value of the portfolio as of that date.

Step 3: Enter your transaction details in chronological order with your deposits, taxes or any other cash outflow with a negative sign and the withdrawals, dividends or any inflow of cash with a positive sign. Your opening balance is treated as a cash outflow and should be marked with a negative sign.

Step 4: Your last transaction should be the market value of your portfolio as of current date. This should be entered with a positive sign as this is the cash that you will receive if you liquidate your holdings today.

Step 5: Now to compute the return, type in “=XIRR()” into an empty cell and select the range for the 2 arguments. XIRR takes 2 arguments. The first argument is the list of cash flows and the second argument is the list of dates. If you are new to investing and do not have an opening balance then you can ignore the first row while calculating the returns.

Now, this cell will hold the returns your portfolio has generated. You can click on the “%” sign in the toolbar to convert this into a percentage value.

That’s it. A very simple way to calculate your portfolio returns. Now that you know how your portfolio has performed you can decide the changes that you would like to implement or just leave it alone. You are in a better position to take an informed decision than before you had started this process.

You can download the Using XIRR to calculate annualized returns of your portfolio (.xls) (499) and try using different values or use this as part of your portfolio calculations.