Financial & Accounting
Dollar-Cost Averaging (DCA) Formula: Harmonic Average Cost Basis & Unrealized Gain
Calculate exact share-weighted Dollar-Cost Averaging cost basis SUM(Invested) / SUMPRODUCT(Invested / Share_Price)—showing why fixed-dollar contributions beat the arithmetic average share price—plus unrealized P&L.
Why fixed-dollar DCA buys more shares at lows and achieves the HARMONIC mean price ($25.81 < $27.50)!
When an investor contributes a fixed $500 across 4 months at share prices {$25, $20, $25, $40}, the naive arithmetic average share price is AVERAGE(25, 20, 25, 40) = $27.50—which OVERSTATES true cost per share! Because $500 buys 25.0 shares at $20 but only 12.5 shares at $40, total shares accumulated equal SUMPRODUCT(B2:B5 / C2:C5) = 20 + 25 + 20 + 12.5 = 77.50 shares!
Dividing total invested capital SUM(B2:B5) = $2,000 by 77.50 shares yields the true harmonic/share-weighted cost basis of $25.8065/share ($1.69 below the arithmetic mean!), generating an unrealized gain of 77.50 * $32 - $2,000 = $480.00 at current market price E2 = $32!
| Portfolio Valuation Metric ($500 × 4 Months at $25, $20, $25, $40) | Formula Output | Comparison / Portfolio Insight |
|---|---|---|
| True DCA Cost Basis per Share | $25.8065 | $1.6935 lower than $27.50 arithmetic mean! |
| Total Shares Accumulated | 77.50 shares | 20 + 25 + 20 + 12.50 fractional shares |
| Unrealized Gain at Market Price E2 = $32 | +$480.00 | Market value $2,480 - $2,000 invested |
| Unrealized P&L at Market Drop E2 = $22 | -$295.00 | Market value $1,705 - $2,000 invested |
Exact DCA Share-Weighted Average Cost Basis = SUM(Invested) / SUMPRODUCT(Invested / Price)
Computes total dollars invested divided by total fractional shares purchased across B2:C5.
=IF(OR(MIN(B2:B5) < 0, MIN(C2:C5) <= 0, E2 <= 0), "Invalid Investment or Share Price", ROUND(SUM(B2:B5) / SUMPRODUCT(B2:B5 / C2:C5), 4))Unrealized Portfolio Dollar Gain / Loss at Current Market Price E2
Evaluates Total_Shares * Current_Price - Total_Invested.
=IF(OR(MIN(B2:B5) < 0, MIN(C2:C5) <= 0, E2 <= 0), "Invalid Investment or Share Price", ROUND(SUMPRODUCT(B2:B5 / C2:C5) * E2 - SUM(B2:B5), 2))Try the example
Use the original values or change them to test your understanding.