Excel Formulas for Private Equity: SUMPRODUCT
What is the SUMPRODUCT function in Excel?
The SUMPRODUCT function is a versatile function that multiplies ranges or arrays together and outputs values that are the sum of the products. It is important to note that this function treats non-numeric items in arrays as zeros and all array arguments must be the same size.
= SUMPRODUCT (array1, [array2],…)
Array 1 – the first array or range to multiply, then add.
Array 2 – the second array or range to multiply, then add.
Why is the SUMPRODUCT important?
The SUMPRODUCT function is an important function in Excel as it has many uses and saves significant amounts of time. They can be used to count and sum COUNTIFS or SUMIFS, and in combination with other functions inside the SUMPRODUCT to extend functionality. It is the primary way to calculate a weighted average of a data set.
How is it used in real estate?
We will use this Hotel Acquisition Template from Top Shelf Models to demonstrate how we have used the SUMPRODUCT function in real estate financial modeling.
In this example, the formula in cell D69 in the Comp – Hotel tab is doing the following:
The formula is saying that Excel should add C58:C68 and multiply the result with the result of the sum of D58:D68. It should then divide that result by C69.
In this example, we have used this function in the Hotel Acquisition Model to calculate the weighted average for the year opened of the primary competitive by taking the number of keys and the year the hotel was opened in.
