How to calculate weighted average returns using MS Excel
A combination of MS Excel functions proves effective when diverse amounts are invested across options.
However, the average return may be ineffective when various investments have different levels of relevance. In the area of personal finance and investments, the relevance refers to the varied amounts of investments made, which is technically termed weightages. In the example given above, the arithmetic average of 2.3% will turn out to be wrong and misleading, if weightages are unequal.
Let us take an example. If Rs 10,000 is invested in stock 1, Rs 15,000 in stock 2 and Rs 30,000 in stock 3, the weightage of 18%, 27% and 55% are created for stock 1, stock 2 and stock 3 respectively. The weightage of each stock is calculated by dividing the respective investment amount by the total amount of investments. Therefore, in case of stock 1, the weightage is calculated by dividing Rs 10,000 by the total investments of Rs 55,000, which is 18%. Other stock weightages are worked out in a similar manner. Compared to this, simple average assumes equal weightage of 33% in each of the three stocks.
For estimating returns when amounts vary across investments, a concept called weighted average return is used. The weighted average return is the sum total of the product (or multiplication) of weights that are associated with different investment options and their respective returns. The sum of such weights equals 100%. In the above example, the weighted average return works out to -1.2% [18% X 10% + 27% X 5% + 55% X (-8%)], compared to a positive 2.3% arithmetic return. The reason for the negative weighted return is due to the substantial amount of money which was invested in a negative yielding investment option (stock 3). The investor made Rs 1,000 in stock 1, Rs 750 in stock 2 and suffered a loss of Rs 2,400 in stock 3, which created a total market value of Rs 54,350. With a total principal investment amount of Rs 55,000, it led to a loss of Rs 650.
Weighted returns have several applications in stock markets, mutual funds, personal finance investments and company analysis. The values of benchmark indices like BSE Sensex and NSE Nifty are calculated by assigning weights to the constituent stocks according to their market capitalisations. Moreover, weighted average has applications in stock market averaging, where one can reduce the cost of acquisition of a stock by buying additional shares, when the prices are declining. In the company analysis, the concept helps to determine the weighted average cost of capital (WACC), which is used in equity discounting valuation models.
Although the concept is simple, it involves calculations of weightages as the same are not readily available. Alternatively, one can use a combination of two MS Excel functions to compute weighted average return. The functions are SUMPRODUCT and SUM. While the SUM function is well known as it helps to add numbers, SUMPRODUCT helps in both cross multiplication and addition across multiple sets of data.
Divide SUMPRODUCT by SUM to get weighted average return
Let us look at an example to understand the computation process. The example will assess the performance of an investment portfolio with investments spread across different asset classes—gold, silver, stocks, mutual funds and FDs. A total of Rs 1.5 lakh is invested across asset classes (see screenshot). The SUMPRODUCT function gives a value of Rs 4,770, which is the net amount earned from the portfolio, given the amount invested and returns. The sum function calculates the total amount of investments, which is Rs 1.5 lakh. Dividing SUMPRODUCT by SUM calculates the weighted average returns—3.18%.
As one can observe, the maximum amount is invested in stocks which yielded negative returnd in the past one year. If the amount invested is interchanged between gold and stocks— Rs 50,000 is invested in gold and Rs 25,000 in stocks, the net amount earned and the weighted average return jumps to Rs 14,050 and 9.37% respectively. This demonstrates the significance of the portfolio re-balancing as the same can help in improving the overall returns. One can play with the numbers by changing the amount invested across asset classes and observe its effect on the weighted return.
The simple average, if used will work out to 8.86% which is completely misleading because each asset class has a different weight according to the amount invested. Simple average will be effective if the weightages are equal, or in other words, the equal amount of Rs 30,000 is invested in each of the given five asset classes.