Financial
Modified Dietz portfolio return with day-weighted external cash flows
Calculate investment performance across mid-month deposits and withdrawals without daily portfolio valuations via R = (EMV - BMV - SUM(CF)) / (BMV + SUM(w_i * CF_i)).
Estimate a period return while accounting for external flows
Modified Dietz uses the change in portfolio value net of external cash flows, divided by beginning value plus weighted flows. Deposits are positive and withdrawals negative; investment income retained inside the portfolio is not an external contribution. This example uses end-of-day cash flows: a flow on day d of a T-day period receives weight (T-d)/T.
The result is a period-return estimate, not an exact time-weighted return or a claim of GIPS compliance. Large flows combined with volatile performance can weaken the approximation. A different start-of-day convention changes weights and must be applied consistently.
| Portfolio Event | Day / Weight w_i | Amount | Capital & Gain Impact |
|---|---|---|---|
| Beginning Market Value (BMV) | Day 0 (100%) | $100,000 (B2) | Base capital = $100,000 |
| Deposit #1 (C2, D2) | Day 6 -> (30-6)/30 = 0.80 | +$10,000 | Weighted CF E2 = +$8,000 |
| Withdrawal #2 (C3, D3) | Day 21 -> (30-21)/30 = 0.30 | -$4,000 | Weighted CF E3 = -$1,200 |
| Ending Market Value (EMV) | Day 30 (B4 = 30) | $118,500 (B3) | Gain $12,500 / Cap $106,800 = 11.70%! |
Modified Dietz period return formula
With E2:E3 storing weighted cash flows =((($B$4 - C2) / $B$4) * D2), numerator subtracts raw SUM(D2:D3) while denominator adds time-weighted SUM(E2:E3).
=IF(OR(COUNT(B2:B4)<>3,COUNT(C2:D3)<>4),"Invalid inputs",IF(OR(B2<0,B3<0,B4<=0,B4<>INT(B4),C2<0,C3<0,C2>B4,C3>B4,C2<>INT(C2),C3<>INT(C3)),"Invalid inputs",IF((B2+SUM(E2:E3))<=0,"Undefined return",ROUND((B3 - B2 - SUM(D2:D3)) / (B2 + SUM(E2:E3)), 4))))Compare with midpoint weighting
The midpoint method gives every flow a weight of 0.5. In this example it yields about 12.14% versus 11.70% with actual day weights, a difference of about 44 basis points. The midpoint result is not necessarily higher for other flow patterns.
=IF(OR(COUNT(B2:B4)<>3,COUNT(C2:D3)<>4),"Invalid inputs",IF(OR(B2<0,B3<0,B4<=0,B4<>INT(B4),C2<0,C3<0,C2>B4,C3>B4,C2<>INT(C2),C3<>INT(C3)),"Invalid inputs",IF((B2+0.5*SUM(D2:D3))<=0,"Undefined return",ROUND((B3 - B2 - SUM(D2:D3)) / (B2 + 0.5 * SUM(D2:D3)), 4))))Try the example
Use the original values or change them to test your understanding.