How to Calculate the Average Return for the Share of Stock in Excel

Calculating returns from your stock portfolio can be a tricky matter, especially if some of your holdings pay dividends, or you make frequent deposits and withdrawals from your account. With Excel and data from your broker, however, calculating the average return is fairly easy using the XIRR function.

How to calculate average stock returns in ExcelIn the following example, I'll calculate the returns for Microsoft stock using historical prices and dividends.

The first step is to get organized. You'll need only two column headings, titled "Dates" and "Cash flows." In the "Dates" column, we'll input the date you purchased the stock, dates you received dividends, and the date that you sold the stock. If you haven't yet sold the shares, you'll just use the most recent date for which a stock quote is available.

In the "cash flows" column, we'll input the purchase price, dividends received, and the price at which you sold the stock (or the price at which it traded most recently, if you haven't yet sold your shares.)

I'll assume that I bought shares of Microsoft stock on June 10, 2011 for $23.70 per share. In the first row under the column headings, I've typed in the date of the purchase, and -$23.70 to show that I paid $23.70 for the shares.

Next, I need to fill in all the other cash flows I've received from my Microsoft investment. In this case, I've received several dividends from the stock, which must be included to accurately calculate the return.

Your broker should be able to provide you with an easy-to-read table of dividends received from your holdings. (I don't actually own shares of Microsoft, so I'll use dividend data from NASDAQ.) The dividends should be entered in as positive numbers, because they represent a cash inflow to you.

Finally, I'll enter the share price as of the most recent trading day. On November 9, 2015, Microsoft shares traded for $54.10 per share. I'll type this into the last column as a positive number, as it is the most recent available price at which Microsoft shares could be sold.

Calculating the returnOne of the best methods for calculating an average return for a stock investment is the XIRR function in Excel. The XIRR function calculates an annual return that would make the net present value of the cash flows equal to zero. You can think of it as an average annual return for your investment.

To calculate the return for this Microsoft investment, we simply need to go to an empty cell and type in the formula.

In cell A24 I typed in "XIRR," to remind me later what I've calculated. One cell to the right (B24), I'll type in "=XIRR(B4:B22,A4:A:22)."

Inside the parentheses are the two parts that make this function work. The "B4:B22" part (blue in the image above) tells Excel that all the cash flows are in this selection. The "A4:A:22" tells Excel that these are the corresponding dates (green in the image above) for each cash flow.

Now I just hit enter, and Excel calculates that my internal rate of return on my Microsoft stock was 0.2373. This result is actually a percentage, an IRR of 23.73%.

As easy or complex as you want to make itYou can use XIRR to calculate an internal rate of return for just about anything. Perhaps you want to find the rate of return on just one stock, but you can also use it to calculate the return on your whole portfolio by typing in the deposits, withdrawals, and ending balance of your portfolio to calculate an IRR.

You could also add some extra complexity. If, for example, you wanted to calculate your returns after taxes, you could input the amount of taxes due in the table as a negative number to recalculate an after-tax return. Alternatively, you might want to subtract trading costs like commissions from the final price, so that you calculate a return after expenses.

If you make routine investments, perhaps once per month, you can also enter all your deposits into your account as negative numbers, then enter the ending value of your account as a positive number, to give you the IRR on your whole account. It's all up to you.

As one final reminder, remember that IRRs are annual. Thus, if you hold an investment for only a few days or weeks, the return will likely seem very high. A 10% gain in a month, for example, works out to an IRR of 207%, which tells you that a 10% return in a month will more than triple your investment in one year's time if you continue to earn that return.

But despite that small weakness, calculating an IRR for your investments is an easy way to get a number to quantify your investment performance.

This article is part of The Motley Fool's Knowledge Center, which was created based on the collected wisdom of a fantastic community of investors based in theFoolsaurus. Pop on over there to learn more about our Wiki andhow you can be involvedin helping the world invest, better! If you see any issues with this page, please email us atknowledgecenter@fool.com. Thanks -- and Fool on!

The article How to Calculate the Average Return for the Share of Stock in Excel originally appeared on Fool.com.

the_motley_fool has no position in any stocks mentioned. The Motley Fool owns shares of Microsoft. Try any of our Foolish newsletter services free for 30 days. We Fools may not all hold the same opinions, but we all believe that considering a diverse range of insights makes us better investors. The Motley Fool has a disclosure policy.