How to Calculate Expected Return in Excel (3 Suitable Ways)

You can calculate the expected return on your investments or business in an Excel worksheet. With this tool, you can predict the returns. Also, you can decide which investment is more viable for you. This article will show you how to calculate expected return in Excel.

How to Calculate Expected Return in Excel: 3 Suitable Ways

We will show you three suitable ways to calculate the expected return in Excel. First, we will calculate the expected return manually. Then we will use relevant functions. And at last, we will show you how you can calculate the expected return using the proper VBA code.

This is the preliminary dataset that we will use to find the expected return. The Profit Percentage is given in C5:G8. The investment proportions are given in the Weight column. We will find the expected return for each investment in Column H. Also, we will find expected returns for each year in Row 9. In the end, we shall find Total Expected Return in F12.

1. Calculate Expected Return Manually

To find the return on each investment, we need to multiply the Profit Percentage by the corresponding Weight value. Then we have to add them.

So, in the H5 cell, write the following formula and press Enter.

This formula multiplies Profit percentages with Weight values and adds them together.

Using the formula, we got the expected return for Investment1 which is 6.25%.

Now, hold and drag the H5 cell downward to copy the formula for all other investments.

By doing so, we got the expected return for all investments. We can use these values to find the average/total expected return.

Now we will find the expected return for each year.

We will follow the same rules. We need to multiply the Profit Percentage with the corresponding Weight values, and we need to add the results.

For that, write the following formula in C9 and press Enter.

The Weight values need absolute referencing.

Using the formula will give you the expected return for the year 2023.

Now, hold and drag the C9 cell rightwards to copy the formula for all other years.

You can see that we got the expected returns for all years.

Now, for the average/total expected return, we need to calculate the average of these individual returns.

Write the following formula in F12 and press Enter.

We have to find the average of 4 individual returns. So, this formula divides the summation of those cells by 4.

Using this formula, we have calculated the expected return, which is 6.44%.

We can also find the Expected Return from the average of Each Year Return.

2. Use Relevant Excel Functions to Calculate Expected Return

In this method, we will carry out the same task. But this time we will use some dedicated functions like PRODUCT, SUM, SUMPRODUCT, and AVERAGE.

To find the expected return for each investment, use the following formula in H5 and press Enter.

This formula finds the sum of all Profit percentages and multiplies it by the corresponding Weight value.

Using this formula, we get the expected return for Investment1.

Now, hold and drag the H5 cell downwards to copy the formula for all other investments.

By doing so, we get returns on all other investments.

After that, let’s find out the expected return for each year.

Write the following formula in C9 and press Enter.

This formula takes two arrays. Then it multiplies those two arrays and adds them together.

Doing so, we get the expected return for the year 2023.

Now, hold and drag the C9 cell rightwards to copy the formula.

And thereby, we get the expected return for all years.

Now find the average of any of these two returns.

Write the following formula in F12 and press Enter.

By doing so, you will get the expected return.

3. Apply VBA Code to Calculate the Expected Return

We can use the previously shown formula in the VBA code to find the expected return.

This time we have taken different Weight values, as shown in the below image.

To launch the VBA Editor in Excel, follow these steps:

• Open Microsoft Excel.
• Then, click on the Developer tab >> Visual Basic icon in the Code group.

• This will open the VBA Editor window, in which the Project Explorer will be on the left-hand side and the Code Editor in the main area. Now, select Module from the Insert tab.

• A new Module will be created where you can Write or Edit your VBA code in the Code Editor window.

Note: By default, the Developer tab remains hidden. In that case, you have to enable the Developer tab. To do this, go to File > Options > Customize Ribbon, and then check the box next to Developer in the right-hand pane.

Sub Calculate_Expected_Return()
Range("H5").Formula = "=PRODUCT(SUM(C5:F5), G5)"
Range("H5").AutoFill Destination:=Range("H5:H8")
Range("C9").Formula = "=SUMPRODUCT(C5:C8,$G$5:$G$8)"
Range("C9").AutoFill Destination:=Range("C9:F9")
Range("F12").Formula = "=AVERAGE(C9:F9)"
End Sub

This VBA code writes all the formulas to the relevant cells. Also, it auto fills the required fields.

If you run the code, all the blank cells will be filled with expected returns.

Read More: How to Calculate ROI Percentage in Excel

Frequently Asked Questions

• Can you calculate expected return in Excel using a probability distribution?

Ans: Yes, you can calculate the expected return in Excel. For that, you can use a probability distribution by using the formula.

where “returns” is the range of possible returns and “probability” is the range of probabilities associated with each return.

• How do you calculate expected return in Excel for a mutual fund?

Ans: You can use the formula to calculate the expected return for a mutual fund in Excel

where “returns” is the range of historical returns for the mutual fund.

• How do you calculate the expected return for a bond in Excel?

Ans: You can calculate the expected return for a bond in Excel. For that, you can use the following formula.

where “nper” is the number of periods, “pmt” is the periodic payment, “pv” is the present value, and “fv” is the future value. This will give you the expected return in percentage.

Things to Remember

• Make sure you have converted the data range into percentage format.
• You can use different weight percentages to find the expected return.
• Make sure your worksheet is working properly before you use it for practical purposes.
• The VBA code generally works on the active worksheet.

Download Practice Workbook

You can download and practice this workbook.

Conclusion

So, we have shown you how to calculate expected returns in Excel. We have shown 3 suitable methods for solving the problem. We hope you find the content of this article useful. If there are further queries or suggestions, make sure to mention them in the comment section.