How to Calculate Your Expected Return in Excel (Easy)

The expected return on an investment is a very crucial measure for the people of finances.

If you’re about to make up a portfolio of investments for yourself, make sure you’ve critically evaluated the expected return it shall yield to you will all the possible caveats.

Don’t know how to calculate the expected return for a portfolio? 🤔

Download the free practice workbook for this tutorial here and continue reading this tutorial until the end to learn everything about calculating expected returns in Excel.

Table of Contents

What is Expected Return

The expected return on a security/investment/portfolio is the return that you anticipate making out of it over a given time.

For example, you may expect a Company from the tech industry to boom in the coming years and generate a higher return since technology is a rapidly growing industry and other reassuring economic factors 🌎

No one can forecast it with certainty; however, a calculated guess makes it work. Financial analysts and investment managers would normally compute the minimum expected return from a company by using CAPM.

Why is it used

Expected return is super important for investors to know as it helps them make better and more informed financial decisions 💡

Not all investments are meant for all investors. Some investments are high-risk investments that bear the chance of making high returns over a short period – these investments will attract risk-taker investors who are willing to accept volatility for higher rewards.

Whereas other investments offer a slow and steady but constant return over a long period. These investments are more of a center of attraction for risk-averse investors.

Investment decisions are rooted in two main factors; risk appetite and the target period. This makes it essential to know the expected return a portfolio offers you to make the right decision about investing in it.

It is calculated by accounting for the probability of each possible return scenario 🎯

How to calculate it is explained in the next section.

Calculating Expected Return on a Portfolio in Excel

You’d need some information about your pool of securities to be able to calculate the expected rate of return for it.

For example, here I have a portfolio of three different stocks of Company A, Company B, and Company C.

The total portfolio value is $50,000 with varying investments in each company’s stocks.

Pro Tip!

The purpose of making a portfolio of investments (portfolio management) is to diversify and hedge your risk. A well-constructed portfolio will have a balanced mix of investments from high-risk and high-reward investments to stable, low-risk, and low-reward investments.

By spreading their investments across multiple classes of assets and companies, investors can hedge their risk and maximize their returns. Since some stocks in a portfolio come from a different industry, it enables investors to capture the positive side of the market even if some industries underperform ⚖

Similarly, investing in the stocks of companies that are competitors to each other neutralizes your risk. Either of the competitors will always be performing better than the other, so even if one performs badly, you’ll be covered by the winning performance of the other competitor.

Here I have some insights from the economic performance of the relevant state.

This shows the probability of different states of the economy. There’s a 60% probability that the economy will continue operating normally.

Whereas there is a 20% probability that it might go into a boom or recession 🚀

For each state of the economy, we have an expected stock return. For example, if the economy goes into recession, Company A’s stocks will only perform by 3% each year (value appreciation and dividends).

However, if the Economy sees a boom, Company A’s stocks will yield an annual return of 15%.

So, what is the overall expected return for Company A. To find it:

Step 1) Use Excel to write the SUMPRODUCT function as follows.

The first array is the one that contains probability percentages.

Step 2) Specify the expected return for Company A under each economic state as the second argument.

Step 3) Hit enter to see the expected probable return for Company A.

That’s 9.6%. But what did the SUMPRODUCT function do to return 9.6%?

If you are interested to know the background math, here’s what happened.

Step 4) Multiply each economic state probability with the expected return under that state.

Step 5) Add all these probable returns up.

This is how the SUMPRODUCT function returned a 9.6% expected return. It multiplied each return with the probability of its occurrence and summed them up 💪

Let’s come back to calculating the expected return for Company B and Company C.

Step 6) Write the SUMPRODUCT function to find the expected return for Company B as below.

Step 7) Press enter to get the expected probable return for Company B.

Step 8) Find the same for Company C by using the function below.

By now, we have already found the expected return on three different investments given the economic conditions.

Next, let’s compute the expected return on our portfolio.

Step 9) Compute the weight of each Company’s stock in your portfolio by dividing each investment value by the total portfolio value.

Tells that Investment in Company A makes a 50% part of the portfolio 😎

Step 10) Drag the formula down to the whole lit (make sure to use an absolute reference for portfolio value).

Step 11) Multiply the weight of each investment to its expected return.

This gives you a weighted return on each investment.

Step 12) Sum these weighted returns to find the weighted average expected return for the underlying portfolio.

And there it is. The average expected return for this portfolio is 9.64%.

This is a comprehensive example of how can you calculate the expected return for different investments and a whole portfolio of investments 📝

Conclusion

After you’ve learned how to calculate the expected rate of return on your portfolio, do not stop here. It is an important corporate finance metric to evaluate your investments but not the only one.

To take your investing game to the next level with the power of Microsoft Excel, make sure to read my following Excel tutorials which explain different ways of evaluating investments in Excel 📚

How to Calculate Growth Rate in Excel (Formula)

How to Use the ROI Formula in Excel (Step-by-Step)

How to Calculate Net Present Value (NPV) in Excel (NPV Formula Explained)