Calculate Investment Portfolio Returns with Excel: A Step-by-Step Guide

How to Use Excel To Calculate Investment Portfolio Returns

Calculating a rate of return is easy to do by hand if you have a starting value and an ending value one year apart. However, when you have multiple years of data, as well as contributions and withdrawals to the portfolio during that time, using Excel to figure your returns can save you a lot of time. Another perk to setting up a spreadsheet is you can easily change one of the variables and immediately see how it impacts your returns. For example, you could see what would happen to your return if your account were worth $5,000 more (or less).

Step 1

Enter the date of all of the contributions you have made to and the distributions you have taken from your portfolio in column A. For example, say you contributed $5,000 on January 1, 2014; $6,000 on March 3, 2015; and $4,000 on April 15, 2016. Enter "1/1/14" in cell A1, 3/3/15 in cell A2 and 4/15/16 in cell A3.

Tip

If you want to calculate your return for a specific time period rather than over the entire life of the portfolio, enter the value of the account on the starting date as the first contribution. For example, if you wanted to figure the return from January 1, 2015 to December 31, 2015, your first entry would be the value of the account on January 1, 2015.

Step 2

Enter all of the contributions you have made to and the distributions you have taken from your portfolio in column B. In this example, enter $5,000 in cell B1, $6,000 in cell B2 and $4,000 in cell B3.

Step 3

Enter the date you want the calculation to end at the end of column A. For this example, if you want your calculation to end on December 31, 2016, enter "12/31/16" in cell A4.

Step 4

Enter the value of your portfolio on the end date at the end of column B as a negative number, as if you were taking it all out. In this example, if the portfolio is worth $16,000, enter "-16,000" in cell B4.

Step 5

Enter the internal rate of return formula in cell C1 using the formula "=XIRR([the cells containing the values],[the cells containing the dates])". In this example, all your values are in cells B1 to B4 and your dates are in cells A1 through A4, so you would enter "=XIRR(B1:B4,A1:A4)" and you will see 0.033896, meaning your portfolio return is 3.3896 percent per year.