Portfolio Dashboard - TWR vs MWR, Sharpe, Sortino, Calmar

DISCLAIMER: Educational content, not investment, tax or legal advice. Consult licensed advisor.

Intro: Why Most Traders Lie (Even to Themselves) About Results?

Ask trader result: “I do 20% monthly”. Show statement: turns out 20% was one month, average yearly -15% because doesn’t count deposits and withdrawals.

Problem is lack of measure. Without measure no management.

In this lesson build dashboard that shows truth. Bitter but true. And teach metrics hedge funds use for millions dollars that you can do in Google Sheets free.

Table of Contents

1. TWR vs MWR - why broker tricks you
2. CAGR, Vol, Max Drawdown - base
3. Sharpe, Sortino, Calmar - quality metrics
4. Math formulas
5. Interpretation table
6. Why important for trader
7. How to build dashboard in Google Sheets step by step
8. Case Study: Marek dashboard 34
9. Beginner mistakes
10. Exercises and Checklist

1. TWR vs MWR - Two Ways of Lying

MWR (Money Weighted Return) - IRR: Return dependent on WHEN you deposited and withdrew. Broker shows MWR often as “total profit”. If you deposited 100k, made +10% (10k), then added 100k at top and fell -10% (-21k), MWR shows -5.2% because weights by capital. It’s measure of timing deposits, not skill.

TWR (Time Weighted Return): Return independent of deposits/withdrawals. Splits history into subperiods between cash flows and multiplies. It’s measure of pure management skill.

TWR = [ _(i=1^(n (1 + R_i) ] - 1

Where R_i return in subperiod i (between deposits).

Example: Day 1: Deposit 10k PLN (~$2.5k) Month 1: Value 11k (+10%) R1=10% Day 31: Additional 20k, value before 11k, after 31k Month 2: Value 28k. Return subperiod 2: 28k/31k -1 = -9.68% R2=-9.68%

TWR = (1+0.10)(1-0.0968)-1 = 1.100.9032-1 = -0.648% -> -0.65%

MWR (IRR) calculated in Excel = -6.5% because second deposit big at top.

Which use?

• To evaluate SKILL trader / fund: TWR
• To evaluate how much REAL earned PLN including deposits: MWR

Pro funds report TWR. You should too.

2. CAGR, Vol, Max DD - Foundation

CAGR (Compound Annual Growth Rate):

CAGR = ((End_Value/Start_Value)^((1/n - 1

Where n = years.

Example: 100k -> 180k in 5y

CAGR = (180/100)^{0.2} -1 = 1.8^{0.2}-1 = 0.1247 = 12.47\%

Volatility (σ annualized):

Vol_(annual = σ_(monthly * (12

Calculate std dev monthly returns and multiply sqrt(12).

Max Drawdown:

MDD = min((Peak - Trough/Peak)

Biggest drop from peak.

3. Sharpe, Sortino, Calmar - Quality of Profit

Sharpe Ratio:

Sharpe = (R_p - R_f/σ_p

Where R_p = portfolio return, R_f = risk-free rate (e.g. WIBOR 3M ~5.5% in PL or 4% USD), σ_p = vol.

Example: R_p=12%, R_f=5%, σ=15%

Sharpe = (12-5)/15 = 0.466

Interpretation:

• <0.5 weak
• 0.5-1.0 ok
• 1.0-2.0 very good

• 2.0 outstanding / suspicious (Madoff had 2.5 and cheated)

Sortino Ratio: Like Sharpe but penalizes only downside vol.

Sortino = (R_p - R_f/σ_(downside

Where downside vol calculated only from losing months.

For trader more important than Sharpe, because doesn’t penalize for big profits (upside volatility).

Calmar Ratio:

Calmar = (CAGR/|MaxDD|

Example: CAGR 15%, MaxDD -20%

Calmar = 15/20 = 0.75`

Higher better. >1.0 great. Shows return per unit worst pain.

4. Interpretation Table for Trader

5. Why Important for Trader?

Because without TWR you don’t know if good or just deposit more at bottoms.

Scenario: You do 30% TWR trading, but MWR 8% because you constantly add at tops and withdraw at bottoms for living. That’s cashflow management problem, not skill. Dashboard catches it.

Second: Comparison to benchmark. If your retirement portfolio does 8% CAGR with Sharpe 0.7 and VWCE does 9% with Sharpe 0.6, you are worse than buy&hold. Maybe better close trading and buy VWCE? Dashboard forces honesty.

6. How to Build Dashboard in Google Sheets - 12 Columns of Happiness

Sheet 1: Transactions Columns: Date | Type (Deposit/Withdrawal/Value) | Amount PLN/USD | Portfolio value after operation | Notes

Example: 2024-01-01 | Deposit | 10000 | 10000 | Start IKE 2024-01-31 | Value | 0 | 10500 | End month 2024-02-15 | Deposit | 5000 | 15500 | Additional

Sheet 2: Monthly Summary Formulas:

• Column A: Month (2024-01)
• Column B: End month value (from Sheet1)
• Column C: Cash flow month (sum deposits-withdrawals)
• Column D: Monthly return TWR: =(B2 - C2 - B1_prev)/(B1_prev + C_mid?) Simplified: use XIRR for MWR and chain for TWR.

Simplified TWR monthly: If no deposits month: R = B2/B1 -1 If were: Need split month into subperiods. Simplest: use Modified Dietz for MWR approximation and separately TWR counting R_i before and after deposit.

Ready formulas Google Sheets:

CAGR = (B_last / B_first)^(1 / YEARS) -1
Vol monthly = STDEV(R_monthly)
Vol annual = Vol monthly * SQRT(12)
Sharpe = (AVERAGE(R_monthly)*12 - Rf) / Vol annual
MaxDD: =MIN( (B_current / MAX(B$1:B_current) -1) )
Sortino: STDEV only negative R
Calmar = CAGR / ABS(MaxDD)

Sheet 3: Dashboard - Visualization

• Equity curve chart vs VWCE benchmark
• Metrics table vs benchmark
• Current allocation vs target table (from lesson 03)
• Cost summary: how much commissions and tax ate CAGR

Template Google Sheets Sharpe formula:

= (AVERAGE(C2:C100)*12 - 0.055) / (STDEV(C2:C100)*SQRT(12))

Where 0.055 Rf = WIBOR 5.5% or 0.04 for USD

7. Case Study: Marek Dashboard 34 - 2024

Marek has retirement Golden Butterfly 75k start (~$18.75k), deposit 36.5k yearly whole 2024 (monthly 3041 PLN $760), end value 125k ($31k).

Transactions simplified: Start 75k, after 12 months 125k, deposits 36.5k, profit 13.5k.

MWR = XIRR(…) ~11.2% (because deposits during year)

TWR: Calculate monthly R: avg 0.9% monthly

CAGR = (125k - weighted deposits?) Simplified TWR annual ~13.1% (because initial capital worked whole year)

Vol monthly 3.2% -> annual 11.08%

Rf 5.5%

Sharpe = (13.1-5.5)/11.08 = 0.686 - good

MaxDD during year: -6.2% July

Calmar = 13.1/6.2 = 2.11 - outstanding because short period

Comparison to VWCE benchmark: VWCE 2024 +18%, vol 14%, Sharpe 0.89, DD -8%. Marek worse than buy&hold VWCE 2024, but had 20% gold and bonds that lowered DD.

Conclusion: In 2024 100% stocks won but Marek slept better.

8. Beginner Mistakes

1. Measuring only PLN profit: “Earned 20k PLN” - but how much deposited? If deposited 200k to earn 20k it’s 10%, less than bonds.
2. Ignoring TWR and bragging MWR after deposit at bottom: Deposited 100k at bottom, bounced 30%, MWR 30% but TWR 10% - timing not skill. Not repeatable.
3. Not comparing to benchmark: You do 12% CAGR but VWCE does 15% with lower DD. Overpaying with time.
4. No Rf in Sharpe: Calculate Sharpe as R/σ without risk-free deduction. In PL Rf 5-6%, so Sharpe without Rf inflated.
5. Dashboard only in head: “I remember results”. You don’t. Tax reports show 80% traders don’t know CAGR.

9. Practical Exercises

Exercise 1: Export account history from XTB (CSV). Calculate MWR using XIRR in Excel/Google Sheets. Exercise 2: Calculate TWR last year splitting into months. Compare with MWR. Which bigger and why? Exercise 3: Calculate Sharpe, Sortino, Calmar last 12 months. Compare to VWCE (data stooq.pl or Yahoo). Exercise 4: Build Dashboard: Sheet1 transactions, Sheet2 monthly TWR, Sheet3 metrics and chart vs benchmark. Set auto VWCE price via =GOOGLEFINANCE(“VWCE”).

10. Checklist

• Have Google Sheets file “Portfolio Dashboard 28”
• Save EVERY deposit/withdrawal with date
• Save portfolio value last day month (21:00)
• Sheet auto-calculates: Monthly TWR, CAGR, Vol annual, Sharpe, Sortino, Calmar, MaxDD
• Have benchmark: VWCE and 60/40 (VWCE+AGGH) same equity chart
• Know TWR vs MWR and understand difference
• Calculate Sharpe with Rf = WIBOR 3M (5.5%) not 0%
• Quarterly review: is TWR > benchmark? If not for 2y -> strategy change
• Yearly tax summary: how much tax and commissions ate CAGR

Course 28 Summary and Next Steps

Congratulations! You passed whole Portfolio and Asset Allocation course.

What you have now?

1. Lesson 01: 3-bucket system and formula Trading_Capital = Total*(1-emergency-retirement)
2. Lesson 02: Chosen portfolio model (60/40, All Weather, Permanent, Golden Butterfly) and formula 110-age
3. Lesson 03: Rebalancing algorithm threshold 20% + knowledge correlations
4. Lesson 04: ETF choice Acc IE and formula Total_Cost = TER+TD+spread+tax
5. Lesson 05: Hedge ratio -Corr*(Vol_p/Vol_h) and mix gold/bonds/BTC
6. Lesson 06: Plan IKE/IKZE, limits, asset location, glidepath 90\%-(age-30)*1.6667\%
7. Lesson 07: Dashboard TWR, CAGR, Sharpe, Sortino, Calmar

Your final assignment:

Within 7 days:

1. Calculate 3 buckets and split capital
2. Open IKE and IKZE if not have and set automatic orders
3. Buy first ETFs per chosen model (e.g. 70% VWCE + 20% AGGH + 10% 4GLD)
4. Copy template Google Sheets dashboard and enter first data
5. Set rebalancing calendar Mar 15, Jun 15, Sep 15, Dec 15

In 10 years you’ll thank yourself. Your 34-year-old self builds freedom for 60-year-old you.

This is not end. This is beginning of being capital architect, not just trader.

Good luck!