Nikhil Gupta07 / 08 · Business Analytics
Portfolio Optimisation
Decision modelling · LP / ILP / NLP
Built three Excel Solver optimisation models (linear, integer, and nonlinear) to construct an ASX equity portfolio under real sector, risk, and allocation constraints.
10 ASX stocks · 3 optimisation models
Excel SolverOptimisation01 — The brief
Problem & outcome.
The problem
An investor holding ten ASX-listed stocks across five sectors needed a defensible way to allocate capital, maximising return under real constraints on sector minimums, single-stock exposure, and risk-category weighting, rather than picking stocks by feel.
The outcome
Built and solved three Excel Solver models: a Simplex LP for proportional allocation (1.37% monthly expected return under 13 constraints), a binary ILP for discrete stock selection (7 of 10 stocks), and a GRG Nonlinear model minimising portfolio variance via the full covariance matrix.
The binary ILP consistently excluded the same three weakest performers the LP had already zeroed out, cross-validating the recommendation across two approaches. The NLP flipped the objective, minimising variance while guaranteeing at least 1.50% monthly return. Sensitivity analysis showed the risk cap on the highest-risk assets was the most expensive constraint, worth roughly 0.027% of return per 1% it was relaxed.
02 — Key numbers
By the numbers.
03 — How it works
The model.
The Excel Solver LP model: objective function, decision variables, and all 13 constraints.
04 — Highlights
At a glance.
- 10 ASX stocks, 5 sectors
- LP: 1.37% monthly return
- ILP: best 7-stock portfolio
- NLP: minimum variance
- 13 constraints
- Sensitivity analysis