A wealth-manager tool that builds a client portfolio from a list of stocks. Instead of chasing the highest expected return, it weights the holdings so that their risks offset each other as much as possible, the "maximum diversification" approach. Then it turns the weights into an order list for a real dollar amount and tests the result two ways: against history and against 1,000 simulated futures.
How it works
- Live data. Pulls 36 months of prices with Excel's STOCKHISTORY function and builds the correlation matrix, annualized returns, and volatility for every holding.
- Optimizer in VBA. Solves the long-only maximum-diversification portfolio in native VBA, with no Solver add-in needed. It maximizes the diversification ratio, which is the weighted-average volatility of the holdings divided by the volatility of the portfolio. A second sheet runs a mean-variance (max-Sharpe) optimization for comparison.
- Order sheet. Converts the weights into share counts for any investment amount at today's prices, and tracks cost basis, drift versus target, 5-year beta, and a CAPM expected return.
- One-click update. After adding or removing a stock, an "Update Model" button rebuilds the data and optimizer sheets.
- Backtest. Buys today's weights on a chosen start date and compares the result with the S&P 500 (SPY).
- Monte Carlo. Runs 1,000 five-year simulations and reports percentile outcomes, Sharpe and Sortino ratios, drawdowns, and the probability of reaching each return level.
Example: $1,000,000 across eight stocks
| Ticker | Company | Weight | 5-yr beta |
| LMT | Lockheed Martin | 27.2% | 0.09 |
| GOOGL | Alphabet | 19.1% | 1.22 |
| LLY | Eli Lilly | 17.4% | 0.39 |
| NFLX | Netflix | 15.1% | 1.61 |
| META | Meta Platforms | 11.0% | 1.28 |
| TSLA | Tesla | 6.6% | 1.75 |
| AMZN | Amazon | 1.8% | 1.49 |
| AAPL | Apple | 1.7% | 1.09 |
| — | Cash | 0.1% | — |
The optimizer gives the largest weights to Lockheed Martin and Eli Lilly. Their returns had low or negative correlation with the big tech names, so they cancel out more of the portfolio's risk than adding another tech stock would.
+131%Backtest total return, Nov 2021 – Nov 2026
(S&P 500: +61%)
−17.9%Backtest max drawdown
(S&P 500: −24.8%)
$1.55MMedian 5-year value in 1,000 simulations
(10th–90th: $1.04M–$2.32M)
92%Share of simulations with a positive
5-year annualized return
Educational project, not investment advice. The backtest is hypothetical: it applies weights chosen with today's data to past prices and counts price returns only, so it flatters the result. The simulation assumes returns follow the historical mean and volatility.