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 clarify the notion and then illustrate the methods old to calculate the honest reply. Once you understand the shortcut that Excel provides in determining the result, you will likely not return to the longer, more cumbersome arrive.
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 prefer 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 heed over this period would be $4.40. But, if we steal that these prices were intraday prices, we could say that the average trace for the day was $4.40. This is misleading, however, as it does not assume the volume of shares at each trade. The volume at the time of each trade provides a clearer record of what the market thinks of valuation. The calculation ragged to weight these trades is the volume weighted average heed (“VWAP”) and is a well-liked practice in private capital transactions challenging equity or equity-linked securities, like convertible debt (impress that VWAP and WAVG are calculated in the same fashion, and I employ VWAP to illustrate a loyal life application) .
In keeping with the prior example, let us seize 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 peruse from the data that many more shares traded at $5.00, or 71.4% of the total daily volume to be right. How does one incorporate that information to settle the VWAP? There are two approaches for doing the calculation: the step-by-step contrivance or the SUMPRODUCT diagram.
grasp that in the first column of your spreadsheet contains the stock prices and the next column contains the shares traded at each impress. The step-by-step near would dictate that you do two current columns: weighting and contribution. The weighting column would possess 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 label 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 steady stock notice. In the $4.00 example, you would acquire the 10.7% and multiply by $4.00, yielding $0.43, which is the contribution to the VWAP by the $4.00 allotment impress. 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 display that the trusty 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 perform 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 respond as above, $4.73. The reply is calculated without the additional columns. When you leer at the formula and crash it down, you can notice that it takes the accomplish 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 arrive, with A being the stock stamp former in determining the “contribution” column in the broken-down map.
Finally, you can also exercise an array diagram to calculate the VWAP using the get of ={SUM((prices) *(volume) ) /total volume}, which is entered using ctrl+shift+enter. You can clearly peruse that 1) this is not powerful of a time saver and 2) SUMPRODUCT is similar to this perform. I only demonstrate this to illustrate that there are several ways to carry out the WAVG calculation, and depending upon residence, modeling skill or other personal factors, you can exercise whichever works for you. I recommend minimization of superfluous data and err towards using the functionality contained in the Excel formulas, and would spend SUMPRODUCT over the lengthier plan.
0 Responses to “Weighted Average Calculations in Microsoft Excel”