When trading in stocks, we rarely buy all shares at the same price. You may accumulate shares at different price levels over time, or sell in multiple lots. To know your true average cost or average selling price, you need to calculate a weighted average , not just a simple average.
Excel’s SUMPRODUCT function makes this calculation simple and accurate.
Why Not Simple Average?
Suppose you buy:
-
10 shares of Infosys @ ₹1,500
-
20 shares of Infosys @ ₹1,600
A simple average would be:
But this is wrong, because you didn’t buy equal quantities.
Correct method is weighted average, which considers both price and quantity.
Manual Calculation of Weighted Average Buy Price
Formula:
Using the example:
-
Buy 10 shares × ₹1,500 = ₹15,000
-
Buy 20 shares × ₹1,600 = ₹32,000
Total Investment = ₹15,000 + ₹32,000 = ₹47,000
Total Quantity = 10 + 20 = 30
Using SUMPRODUCT in Excel
Suppose you record your trades like this:
| Quantity | Price |
|---|---|
| 10 | 1500 |
| 20 | 1600 |
| 15 | 1550 |
Formula for average buy price:
=SUMPRODUCT(A2:A4, B2:B4) / SUM(A2:A4)
Explanation:
-
SUMPRODUCT(A2:A4, B2:B4)→ multiplies each quantity × price and adds them. -
SUM(A2:A4)→ adds up total quantity. Division gives weighted average price.
📈 Example: Average Selling Price
If later you sell shares at different prices, you can apply the same logic.
| Quantity Sold | Price |
|---|---|
| 5 | 1700 |
| 10 | 1750 |
| 5 | 1600 |
Formula in Excel:
=SUMPRODUCT(A2:A4, B2:B4) / SUM(A2:A4)
Result → Weighted average selling price.
Why This Matters
-
Helps you know your exact cost per share for profit calculation.
-
Useful when filing taxes, as capital gains depend on accurate cost basis.
Eliminates mistakes of using simple averages.