Weighted Average Calculations in Microsoft Excel

There are many analyses you will undertake that will require the need to calculate a weighted average instead of a simple average, and there are a few different methods by which to do so. This article will first define the concept and then illustrate the methods used to calculate the correct answer. Once you understand the shortcut that Excel provides in determining the result, you will likely not return to the longer, more cumbersome approach.

First, what is a weighted average ("WAVG")? The WAVG uses a parameter, like market capitalization or shares traded, to modify a simple average calculation. As an example, let's assume that you have five data points that are stock prices: $3.00, $4.00, $4.50, $5.00 and $5.50. If these are the closing prices over the last five trading days, the average price over this period would be $4.40. But, if we assume that these prices were intraday prices, we could say that the average price for the day was $4.40. This is misleading, however, as it does not consider the volume of shares at each trade. The volume at the time of each trade provides a clearer picture of what the market thinks of valuation. The calculation used to weight these trades is the volume weighted average price ("VWAP") and is a common practice in private capital transactions involving equity or equity-linked securities, like convertible debt (note that VWAP and WAVG are calculated in the same fashion, and I use VWAP to illustrate a real life application).

In keeping with the prior example, let us assume that we have volume information associated with the prices as follows: 1,000 shares, 1,500 shares, 1,000 shares, 10,000 shares and 500 shares. You can see from the data that many more shares traded at $5.00, or 71.4% of the total daily volume to be exact. How does one incorporate that information to determine the VWAP? There are two approaches for doing the calculation: the step-by-step method or the SUMPRODUCT method.

Assume that in the first column of your spreadsheet contains the stock prices and the next column contains the shares traded at each price. The step-by-step approach would dictate that you create two new columns: weighting and contribution. The weighting column would contain the result of that day's shares divided by the total shares traded for the day for each of the data points. For example, the weight for the $4.00 price would be 10.7% (1,500 divided by the total shares traded that day of 14,000 shares). Once you have the weightings completed, you can do the contribution column, which would be the weighting value multiplied by the actual stock price. In the $4.00 example, you would take the 10.7% and multiply by $4.00, yielding $0.43, which is the contribution to the VWAP by the $4.00 share price. Summing the contribution column yields the VWAP, which, in this case, is $4.73, higher than the simple average of $4.40. The result would indicate that the actual value of this stock is closer to $4.73 than $4.40.

To avoid the need for two additional columns, Excel provides the SUMPRODUCT function. The form of this function is =SUMPRODUCT(array1,[array2],[array3],etc). This formula takes an array (row or column) and multiplies it by one or more other arrays of the same size. To calculate the VWAP from our prior example, all you would need is =SUMPRODUCT(prices,volume)/total volume. This would result in the same answer as above, $4.73. The answer is calculated without the additional columns. When you look at the formula and break it down, you can see that it takes the form of (A x B)/C, which can be rewritten as A x (B/C). The B/C component is the same as the "weighting" column described in the step-by-step approach, with A being the stock price used in determining the "contribution" column in the former method.

Finally, you can also utilize an array method to calculate the VWAP using the form of ={SUM((prices)*(volume))/total volume}, which is entered using ctrl+shift+enter. You can clearly see that 1) this is not much of a time saver and 2) SUMPRODUCT is similar to this form. I only show this to illustrate that there are several ways to accomplish the WAVG calculation, and depending upon space, modeling skill or other personal factors, you can use whichever works for you. I recommend minimization of superfluous data and err towards using the functionality contained in the Excel formulas, and would use SUMPRODUCT over the lengthier method.

Hot Game !!!