A quantitative portfolio construction project applying Modern Portfolio Theory (MPT) and CAPM to a universe of 7 NSE-listed Indian equities, extended with international assets (Apple Inc. and GBP/INR) to evaluate the impact of global diversification on risk-adjusted returns.
- Overview
- Dataset
- Methodology
- Key Results
- Project Structure
- Tools & Techniques
- Concepts Applied
- How to Use
- Limitations
This project constructs and compares optimal equity portfolios under two frameworks:
- Domestic-only — 7 NSE-listed stocks benchmarked against the NIFTY 50
- International — Same 7 stocks augmented with Apple Inc. (AAPL) and the GBP/INR exchange rate
For each framework, four portfolio configurations are derived:
- Tangency Portfolio (with short selling)
- Minimum Variance Portfolio (with short selling)
- Tangency Portfolio (long-only)
- Minimum Variance Portfolio (long-only)
Efficient frontiers are plotted for both with and without short-selling constraints, and CAPM analysis is used to assess whether each stock is overweight or underweight relative to its market-capitalization benchmark.
| Asset | Type | Exchange |
|---|---|---|
| Apollo Hospitals | Equity | NSE |
| Axis Bank | Equity | NSE |
| ITC | Equity | NSE |
| Infosys | Equity | NSE |
| MRF | Equity | NSE |
| DLF | Equity | NSE |
| Reliance Industries | Equity | NSE |
| NIFTY 50 | Index (benchmark) | NSE |
| Apple Inc. (AAPL) | Equity (INR-converted) | NASDAQ |
| GBP/INR | Currency Pair | FX Market |
- Frequency: Daily adjusted closing prices
- Period: ~600+ trading days (approx. late 2022 – early 2025)
- Risk-Free Rate: Daily Indian T-bill/repo rate (annualized ~6.7%–7.2%)
Daily log/percentage returns computed for each asset from adjusted closing prices.
- Daily variance-covariance matrices computed for both the 7×7 domestic universe and the 9×9 international universe.
- Annualized by multiplying by 252 (trading days per year).
Excel Solver used to minimize portfolio variance subject to:
- Target return constraint
- Weights summing to 1
- Non-negativity constraints (for long-only portfolios)
Four portfolios solved per framework:
- Tangency Portfolio: Maximizes the Sharpe Ratio
- Minimum Variance Portfolio: Minimizes total portfolio risk
8 points generated per scenario by stepping through target return levels and solving for the minimum variance at each, tracing the full risk-return frontier.
Beta-implied CAPM weights (market-cap proportional) compared against tangency portfolio weights to classify each stock as overweight or underweight relative to the market portfolio.
| Stock | Annualized Return | Annualized Std. Dev. |
|---|---|---|
| DLF | 55.9% | ~20.4% |
| Axis Bank | 47.7% | ~26.1% |
| MRF | 40.6% | ~18.0% |
| ITC | 38.3% | ~20.1% |
| Apollo Hospitals | 22.6% | ~21.7% |
| Infosys | 10.1% | ~23.8% |
| Reliance Industries | 2.85% | ~20.3% |
| Stock | Tangency Weight | Market-Cap Weight | Signal |
|---|---|---|---|
| Axis Bank | +42.77% | 9.66% | Overweight |
| ITC | +42.06% | 16.76% | Overweight |
| MRF | +31.05% | 1.38% | Overweight |
| DLF | +21.30% | 3.97% | Overweight |
| Apollo Hospitals | +5.09% | 2.23% | Overweight |
| Infosys | −4.60% | 18.02% | Underweight |
| Reliance Industries | −37.67% | 47.98% | Underweight |
Reliance and Infosys — together representing ~66% of the market-cap benchmark — are underweight or shorted in the optimal portfolio, reflecting poor risk-adjusted returns in this period.
| Portfolio | Framework | Expected Return | Std. Dev. | Sharpe Ratio |
|---|---|---|---|---|
| Tangency (short selling) | Domestic | 60.6% | 16.1% | 3.33 |
| Min. Variance (short selling) | Domestic | 31.4% | 10.9% | 2.25 |
| Tangency (long-only) | Domestic | 44.1% | 12.5% | 2.97 |
| Min. Variance (long-only) | Domestic | 32.0% | 10.9% | 2.30 |
| Tangency (short selling) | International | 44.9% | 10.6% | 3.56 |
| Min. Variance (short selling) | International | 24.5% | 7.26% | 2.43 |
| Tangency (long-only) | International | 35.4% | 8.94% | 3.18 |
| Min. Variance (long-only) | International | 25.1% | 7.27% | 2.50 |
Adding AAPL and GBP/INR to the portfolio reduced minimum-variance risk from 10.9% → 7.26% standard deviation — a ~33% reduction — driven primarily by GBP/INR's near-zero correlation with Indian equities.
├── RiskLens.xlsx
│ ├── Historical Data # Raw daily adjusted closing prices + risk-free rate
│ ├── DailyReturn # Computed daily returns for all assets
│ ├── Covariance # 7×7 domestic variance-covariance matrix (daily + annualized)
│ ├── CAPM # CAPM weights vs. tangency weights + over/underweight classification
│ ├── International Covariance # 9×9 international variance-covariance matrix
│ ├── Domestic # Domestic portfolio optimization results + efficient frontier
│ └── International # International portfolio optimization results + efficient frontier
└── README.md
| Tool / Method | Purpose |
|---|---|
| Microsoft Excel | Primary modelling environment |
| Excel Solver (GRG Nonlinear) | Constrained portfolio optimization |
| Variance-Covariance Matrix | Risk quantification |
| Modern Portfolio Theory | Portfolio construction framework |
| Capital Asset Pricing Model | Benchmark weight comparison |
| Efficient Frontier | Risk-return tradeoff visualization |
- Modern Portfolio Theory (Markowitz, 1952) — Mean-variance optimization to identify efficient portfolios
- Sharpe Ratio — Risk-adjusted return metric used to rank portfolios
- CAPM — Deriving market-implied weights from market capitalizations and comparing against optimized weights
- International Diversification — Testing whether adding foreign assets (equity + currency) improves the efficient frontier
- Short-Selling Constraints — Comparing unconstrained vs. long-only portfolio outcomes to assess practical implementability
- Open
RiskLens.xlsxin Microsoft Excel (with Solver add-in enabled). - Navigate to the Historical Data sheet to review raw price inputs.
- The DailyReturn sheet auto-computes returns from prices.
- Covariance and International Covariance sheets contain the risk matrices.
- Domestic and International sheets contain the four optimized portfolios each, along with efficient frontier data points.
- To re-run optimization, open Data → Solver on the relevant sheet and execute with the existing constraints.
Note: Solver must be enabled via File → Options → Add-ins → Solver Add-in if not already active.
- Backward-looking inputs: Return and covariance estimates are based purely on historical data and may not reflect future distributions.
- No transaction costs: The model assumes frictionless trading; real implementation would erode theoretical Sharpe Ratios.
- Short-selling costs ignored: The unconstrained portfolios assume costless shorting, which is unrealistic in practice.
- Static weights: Portfolio weights are fixed point-in-time; no rebalancing or dynamic strategy is modelled.
- Currency risk: AAPL returns are converted to INR, embedding USD/INR risk that is not separately hedged or accounted for beyond observed covariance.
- Normal return assumption: MPT assumes normally distributed returns; tail risk and skewness are not explicitly modelled.