Excel Tips for Private Equity: How to Calculate Total Weighted Average

What is Total Weighted Average?

The total weighted average of a series of numbers is the average resulting from multiplying each number by a factor that typically represents its importance or frequency. Before calculating a weighted average, you must decide on the values to assign each number. In school, for example, a student’s overall grade depends more on test scores than homework because tests are weighted more heavily. To calculate weighted average, each value is multiplied by its assigned weight which is then summed and divided by the total number of data points.

Total Weighted Average Function

Unfortunately, Excel does not offer a specific Weighted Average function to use to calculate the weighted average. Instead, we must use two different function in combination with each other: SUMPRODUCT and SUM. When using these two functions together, we use SUMPRODUCT to find the sum of the product of multiplying each value by its corresponding weight, and then dividing this sum by the sum of the weights. For more information on the SUMPRODUCT formula, please check out our blog.

 

                                                          =SUMPRODUCT(array1,array2,array3,…)

 

Array1: the first array of numbers you want to multiply and then sum together. This is typically the values that you will be multiplying by the weights. This is a required parameter.

 

Array2,Array3,…: Arrays 2 through 255 are the other arrays that you want to multiply and then sum together with the first array. Think of these arrays as Array 1 multiplied by Array 2 multiplied by Array 3, and so on. This is an optional parameter, but you will need to enter in at least a second array in order to calculate a weighted average.

 

Operating tip: When inputting arrays into the SUMPRODUCT function, the arrays must have the same dimensions to be multiplied together. For example, SUMPRODUCT(A2:A10, B2:B5) will return an error. 

 

                                                            =SUM(number1,number2,number3,…)

 

Number1: the first number you want to add. This can be a single value (i.e. B5), or a range of numbers (i.e. B5:B10). This is a required parameter.

 

Number 2,Number3,…: The second number or range of numbers you would like to add. This parameter is optional.

Total Weighted Average vs. Average

Using the weighted average vs the traditional average is best when you have values that occur more frequently, or are more important. Using the traditional average in this situation would result in a calculation that might not be illustrative of your reality. When calculating a weighted average, the final calculated average reflects the importance/frequency of each value, making the final number a better representation.

Example

 To calculate the weighted average using the SUMPRODUCT and SUM functions in Excel, we can think of the syntax as SUMPRODUCT(Value Array, Weight Array)/SUM(Weight Array). Let’s take a look at a quick example where we calculate the total weighted average for a stock portfolio.

Here we have the stocks in the portfolio, the price of each stock, and the target percentage of each stock in the total portfolio. First, were going to use the SUMPRODUCT function to sum the product of the prices (C4:C9) and the target percentages (D4:D9). 

Next, we are going to sum our percentages and divide our summed product by the total sum of our percentages. This will result in our total weighted average. 

Weighted Average in Real Estate

At Top Shelf Models, we use Total Weighted Average to calculate items in our models such as weighted-average lease terms and weighted-average rent. With weighted average lease terms, we can use the total weighted average to determine the average lease expiry. This provides us with not only a better understanding of the overall tenancy risks of a property with multiple tenants, but also a better idea of when the property could become vacant. Additionally, we can calculate the weighted average rental rate for a property for a more accurate idea of rental rates per square footage without larger spaces affecting the average disproportionately. 

---

About the Author

Eric Bergin is the founder of TSM. He realized that there was a need for real estate financial models that were more than just generic templates. He wanted to create a personalized product for his customers that would ensure success for them and their company. Please reach out to him if you have any questions regarding discounted cashflows or if he can help you with your modeling needs.