Portfolio-Risk-Analysis

Portfolio Risk Analysis and Optimization Project

Sheets-native portfolio analytics: returns, risk, factor exposure, and attribution

_{10-asset universe + S&P 500 • Fully reproducible • Formula-driven (LET / FILTER / XLOOKUP)}

Disclaimer: Cell references and ranges (e.g., B3, B6, AP22–AP24, and 3-column blocks like D:E:F) may differ in your sheet. Adjust anchors and ranges to match your layout—the formula patterns remain the same.

Quick Start
Problem Statement & Why It Matters
System Architecture (Sheets-Native)
Modeling & Math
Implementation Details
Portfolio Construction & Portfolio-Level Metrics
Example Findings
Validation & QA
Limitations & Responsible Use
Extensions on the Roadmap
Troubleshooting
Appendix — Key Formula Patterns

Quick Start

Create the control cells
- B3 = Start Date (date)
- B6 = End Date (date)
- AP22 = S&P Annual Variance (auto)
- AP23 = Risk-Free Rate (e.g., 0.04)
- AP24 = Market Risk Premium (e.g., 0.05)

Benchmark block (A–C)

A10: =GOOGLEFINANCE("^GSPC","price",$B$3,$B$6) (spills A=Date, B=Close)
C11 (daily %Δ, copy down):
```
=IF(OR(B11="",B10=""),"",B11/B10-1)
```

AP22 (annual variance, safe):

=LET(m, FILTER($C$11:$C, ($A$11:$A>=$B$3)*($A$11:$A<=$B$6)*ISNUMBER($C$11:$C)), IF(COUNT(m)<2,"", VAR.P(m)*252))

Stocks in 3-column blocks (each: Date|Close|%Δ starting at row 10):
- Stock 1 → D:E:F, Stock 2 → G:H:I, …, Stock 10 → AE:AF:AG
- Daily %Δ formula in the third column of each block (start at row 11):
```
=IF(OR(E11="",E10=""),"",E11/E10-1)
```

Per-stock analytics (copy across to each block)

Covariance w/ S&P (annualized), Stock 1 (D/E/F):

=IFERROR(
  COVARIANCE.P(
    FILTER( XLOOKUP(D$11:D, A$11:A, C$11:C, "", -1), (D$11:D>=$B$3)*(D$11:D<=$B$6) ),
    FILTER( F$11:F, (D$11:D>=$B$3)*(D$11:D<=$B$6) )
  )*252,
"")

Variance (annualized), Stock 1 (D/E/F):

=LET(rf, FILTER(F$11:F,(D$11:D>=$B$3)*(D$11:D<=$B$6)*ISNUMBER(F$11:F)), IF(COUNT(rf)<2,"", VAR.P(rf)*252))

Volatility (annualized), Stock 1:

=LET(rf, FILTER(F$11:F,(D$11:D>=$B$3)*(D$11:D<=$B$6)*ISNUMBER(F$11:F)), IF(COUNT(rf)<2,"", STDEV.P(rf)*SQRT(252)))

Beta, Stock 1:

=LET(
  covA, IFERROR(
    COVARIANCE.P(
      FILTER( XLOOKUP(D$11:D, A$11:A, C$11:C, "", -1), (D$11:D>=$B$3)*(D$11:D<=$B$6) ),
      FILTER( F$11:F, (D$11:D>=$B$3)*(D$11:D<=$B$6) )
    )*252, ""),
  IF(OR(covA="", $AP$22<=0),"", covA/$AP$22))

Expected Return (CAPM), given Beta in e.g. F6:

=IF(OR(F6="", $AP$23="", $AP$24=""), "", $AP$23 + F6*$AP$24)

Actual Return (B3–B6), Stock 1:

=IFERROR(
  XLOOKUP($B$6, D$10:D, E$10:E, "", -1) / XLOOKUP($B$3, D$10:D, E$10:E, "", 1) - 1,
"")

Alpha = Actual − Expected:

=IF(OR(<ActualCell>="", <ExpectedCell>=""), "", <ActualCell> - <ExpectedCell>)

1) Problem Statement & Why It Matters

High-quality analytics are paywalled. This project demonstrates that with careful formula design you can match institutional risk/return workflows: variance, volatility, covariance, beta, CAPM expected return, actual return, alpha, plus portfolio rollups—directly in Google Sheets, with reproducibility and no scripts.

2) System Architecture (Sheets-Native)

Inputs

Universe: JNJ, NVDA, AAPL, XOM, NEE, JPM, XLP, VDC, TGT, COST (extensible)
Benchmark: S&P 500 (close-only)
Controls: B3 Start, B6 End, optional Manual End
Assumptions: AP23 Rf, AP24 MRP, AP22 Var(S&P) annual

Data layer

Three-column blocks: Date | Close | %Δ. Benchmark in A–C; assets in D–F, G–I, …, AE–AG.
Returns start at row 11 to avoid the first missing observation.

Computation layer

Filter returns to B3–B6 and align to benchmark via XLOOKUP(..., -1).
Annualize: ×252 for variance / covariance, ×√252 for st.dev.

Presentation layer

Per-name cards with the seven core metrics; portfolio section with rollups.

3) Modeling & Math (Ground Truth)

3.1 Daily Return Model

For any price series P_t: r_t = P_t / P_{t-1} - 1. In Sheets, guard for blanks to avoid spurious values.

3.2 Variance & Volatility

Variance (daily): VAR.P(r). Annualize: ×252.
Volatility (daily): STDEV.P(r). Annualize: ×√252.

3.3 Covariance & Alignment

We pair each stock’s daily return with the market return from the same or nearest previous trading day to handle holidays & gaps:

=IFERROR(
  COVARIANCE.P(
    FILTER( XLOOKUP(D$11:D, A$11:A, C$11:C, "", -1), (D$11:D>=$B$3)*(D$11:D<=$B$6) ),
    FILTER( F$11:F, (D$11:D>=$B$3)*(D$11:D<=$B$6) )
  )*252,
"")

3.4 Beta

β = Cov(stock, market) / Var(market), with both terms annualized. Denominator is AP22.

3.5 CAPM Expected Return

E[r] = R_f + β × MRP. Inputs in AP23 and AP24.

3.6 Actual Return

Use last close on/before B6 divided by first close on/after B3, minus 1.

3.7 Alpha

α = Actual − Expected. Positive alpha indicates outperformance beyond risk exposure.

4) Implementation Details

No circular references. Inputs are decoupled from outputs.
Null-safe returns. Return cells are blank if either price is blank (no −100% artefacts).
Alignment. Market returns are aligned via XLOOKUP(...,-1) to prevent FILTER size mismatches.
Dynamic windows. Every metric is filtered to B3–B6. No 252-row truncation.
Performance. LET scoping reduces recomputation; only TODAY and GOOGLEFINANCE are volatile.

5) Portfolio Construction & Portfolio-Level Metrics

Weights

Create a 10-cell weight row (e.g., above each %Δ column: F8, I8, L8, O8, R8, U8, X8, AA8, AD8, AG8). Ensure weights sum to 1.00.

Daily portfolio returns (aligned to benchmark dates)

Construct a matrix of aligned returns, then weight them

=LET(
  md, $A$11:$A,   /* market dates, row 11 down */
  r1, XLOOKUP(md, D$11:D, F$11:F, "", -1),
  r2, XLOOKUP(md, G$11:G, I$11:I, "", -1),
  r3, XLOOKUP(md, J$11:J, L$11:L, "", -1),
  r4, XLOOKUP(md, M$11:M, O$11:O, "", -1),
  r5, XLOOKUP(md, P$11:P, R$11:R, "", -1),
  r6, XLOOKUP(md, S$11:S, U$11:U, "", -1),
  r7, XLOOKUP(md, V$11:V, X$11:X, "", -1),
  r8, XLOOKUP(md, Y$11:Y, AA$11:AA, "", -1),
  r9, XLOOKUP(md, AB$11:AB, AD$11:AD, "", -1),
  r10,XLOOKUP(md, AE$11:AE, AG$11:AG, "", -1),
  R, HSTACK(r1,r2,r3,r4,r5,r6,r7,r8,r9,r10),
  w, HSTACK(F8,I8,L8,O8,R8,U8,X8,AA8,AD8,AG8),
  pr, MMULT(R, TRANSPOSE(w)),
  pr)

Portfolio annualized volatility

=LET(pr, <reference_to_portfolio_return_series_above>, 
     rf, FILTER(pr, ISNUMBER(pr)),
     IF(COUNT(rf)<2,"", STDEV.P(rf)*SQRT(252)))

Portfolio actual return over window

=LET(pr, <portfolio_return_series>, rf, FILTER(pr, (md>=$B$3)*(md<=$B$6)*ISNUMBER(pr)),
     IF(COUNT(rf)<2,"", PRODUCT(1+rf) - 1))

Sharpe Ratio

=( <PortfolioActual> - $AP$23 ) / <PortfolioVolAnnual>

Information Ratio (proper)

Use tracking error (st.dev of daily excess returns × √252)

=LET(
  md, $A$11:$A,
  rm, $C$11:$C,                               /* market return series */
  pr, <portfolio_return_series>,
  ex, FILTER(pr - rm, ISNUMBER(pr)*ISNUMBER(rm)),
  IF(COUNT(ex)<2,"", (<PortfolioActual> - <BenchmarkActual>) / (STDEV.P(ex)*SQRT(252)))
)

6) Example Findings (from the showcased window)

NVDA, AAPL: high beta & realized returns; strong positive alpha (risk-on leadership).
JNJ: defensive beta; moderate vol; positive alpha (quality ballast).
XOM: negative alpha during tech-led rally periods.
XLP, VDC: stabilizers; alpha can lag in risk-on periods.
Portfolio Sharpe ~0.4: could be improved by concentrating alpha, trimming redundant exposures, or lowering cross-name covariance.

7) Validation & QA

Alpha cross-check: Actual − (Rf + β × MRP) matches the card calculation.
S&P 1Y Return: provide both calendar-year and 252-trading-day variants; they reconcile for liquid periods.
Edge windows: short ranges (<5 obs) intentionally return blanks to avoid spurious stats.
Unit tests: swapping MRP, Rf, or the window updates Expected/Alpha consistently.

8) Limitations & Responsible Use

Close-only (no dividends) → for dividend-heavy names/ETFs, prefer total-return proxies.
Betas are window-specific; correlations are non-stationary.
IR shown with proper tracking error; avoid simplified proxies for decision-grade work.

9) Extensions on the Roadmap

Full 10×10 covariance matrix (annualized) and portfolio σ_p = √(wᵀ Σ w) from daily series.
Drawdown analytics: MaxDD, time-to-recover, underwater charts.
Sortino, Calmar, Omega ratios; downside-only dispersion.
Log-return mode, CAGR reporting, roll-window beta/vol charts.
Dividend-adjusted prices, multi-asset benchmarks, currency handling.

10) Troubleshooting

“Array result was not expanded because it would overwrite…”

Clear the spill range below/next to the formula (Delete, not Backspace). Arrays need empty target ranges.

“FILTER has mismatched range sizes”

Ensure both arrays filtered by the same mask have identical shapes. In this model, align dates first with XLOOKUP(...,-1), then apply a single mask to both arrays.

“An array value could not be found”

Occurs when exact date matches fail. Use XLOOKUP(date, dates, values, "", -1) to return the previous trading day when an exact match doesn’t exist.

“DIVIDE parameter 2 cannot be zero”

Guard denominators: check AP22 > 0 for Beta, and ensure start price > 0 in Actual Return. Use IF() gates or IFERROR().

GOOGLEFINANCE quirks

Data can be delayed or missing on holidays & far history. The exact-or-previous lookup pattern prevents gaps from breaking covariance.

Appendix — Key Formula Patterns

Daily return (safe)

=IF(OR(B11="",B10=""),"",B11/B10-1)

Covariance with S&P (annualized; Stock 1 D/E/F)

=IFERROR(
  COVARIANCE.P(
    FILTER( XLOOKUP(D$11:D, A$11:A, C$11:C, "", -1), (D$11:D>=$B$3)*(D$11:D<=$B$6) ),
    FILTER( F$11:F, (D$11:D>=$B$3)*(D$11:D<=$B$6) )
  )*252,
""
)

Variance / Volatility (annualized)

=LET(rf, FILTER(F$11:F,(D$11:D>=$B$3)*(D$11:D<=$B$6)*ISNUMBER(F$11:F)), IF(COUNT(rf)<2,"", VAR.P(rf)*252))
=LET(rf, FILTER(F$11:F,(D$11:D>=$B$3)*(D$11:D<=$B$6)*ISNUMBER(F$11:F)), IF(COUNT(rf)<2,"", STDEV.P(rf)*SQRT(252)))

Beta

=LET(
  covA, IFERROR(
    COVARIANCE.P(
      FILTER( XLOOKUP(D$11:D, A$11:A, C$11:C, "", -1), (D$11:D>=$B$3)*(D$11:D<=$B$6) ),
      FILTER( F$11:F, (D$11:D>=$B$3)*(D$11:D<=$B$6) )
    )*252, ""),
  IF(OR(covA="", $AP$22<=0),"", covA/$AP$22))

Actual return (window; Stock 1 D/E/F)

=IFERROR(
  XLOOKUP($B$6, D$10:D, E$10:E, "", -1) / XLOOKUP($B$3, D$10:D, E$10:E, "", 1) - 1,
""
)

Expected (CAPM) & Alpha

=$AP$23 + <BetaCell> * $AP$24
=<ActualCell> - <ExpectedCell>

Statistical rigor • Defensive spreadsheet engineering • Product thinking