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**.

**Table of Contents**hide

## Download Practice Workbook

You can download and practice this workbook.

## 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 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/Average 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**.

`=C5*G5+D5*G5+E5*G5+F5*G5`

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 **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**.

`=C5*$G$5+C6*$G$6+C7*$G$7+C8*$G$8`

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**.

`=(C9+D9+E9+F9)/4`

As 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**.

`=PRODUCT(SUM(C5:F5), G5)`

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**.

`=SUMPRODUCT(C5:C8,$G$5:$G$8)`

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**.

`=AVERAGE(H5:H8)`

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 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 required fields.

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

## Frequently Asked Questions

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

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

`=SUMPRODUCT(returns, probability) `

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

`=AVERAGE(returns)`

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.

`=RATE(nper, pmt, pv, fv)*nper`

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.

## 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. You can find similar articles like this on our website, **ExcelDemy.com**.