Portfolio Optimisation Made Simple: A Step-by-Step Guide Using Excel

 

This article will help us- how to build a mean-variance portfolio of four funds with nothing more than Excel formulas and Solver (These sample calculations are based on dummy data)

Introduction

Most investors pick funds one at a time: a fund that did well last year, a fund a friend recommended, a fund that sounds safe. The problem is that a portfolio is not just a collection of good funds. How the funds behave together matters as much as how each one behaves on its own.

Portfolio optimisation is a structured way of answering one practical question: given a set of funds, how much money should go into each one so that I get the best possible return for the risk I am taking? This article walks through a ready-made Excel workbook, Portfolio Optimizer – Mean Variance, that answers exactly that question for four mutual funds. Every step is explained in plain language, with screenshots of the sheets so you can follow along and later repeat the process with your own funds.

What you will learn

·         The basic idea behind mean-variance optimisation, without heavy maths

·         How to turn daily fund prices (NAVs) into returns and risk numbers

·         What a covariance matrix is and why it is the secret ingredient

·         How to set sensible minimum and maximum limits for each fund

·         How to use Excel Solver to find the best mix automatically

·         How to read and sense-check the result

The Big Idea in Plain Language

Mean-variance optimisation was introduced by economist Harry Markowitz in 1952 and later earned him a Nobel Prize. The name sounds technical, but it rests on three simple ideas.

1. Return is the reward

The “mean” in mean-variance is the average return you expect from a fund. A fund that has grown 17% a year on average is more rewarding than one that has grown 7% a year.

2. Volatility is the risk

The “variance” is a measure of how much a fund’s value jumps around. We usually quote its square root, the standard deviation. A fund with a standard deviation of 3% moves gently; a fund with 16% can swing sharply up or down in a bad year. Higher return usually comes with higher volatility.

3. Mixing funds reduces risk

This is the key insight. When two funds do not move in step – one rises while the other falls or stays flat – their ups and downs partly cancel out. The combined portfolio is then less volatile than the simple average of the two. Statisticians measure how funds move together with covariance (or its friendlier cousin, correlation). Because of this, the right mix can give you nearly the same return as your best fund with noticeably less risk.

Putting it together: the Sharpe ratio

To compare portfolios fairly we need one number that balances reward against risk. The workbook uses the Sharpe ratio:

Sharpe ratio = (Portfolio return − Risk-free rate) ÷ Portfolio standard deviation

The risk-free rate is what you could earn with almost no risk, such as a government bond or bank deposit (the workbook assumes 7%). The Sharpe ratio tells you how much extra return you earn for every unit of risk you accept. The higher the Sharpe ratio, the better. The whole job of the optimiser is to find the mix of funds with the highest Sharpe ratio, within the limits you set.

What Is Inside the Workbook

The workbook has two sheets that work as a simple pipeline:

·         Raw data: daily adjusted NAVs (closing prices) for four funds from 5 September 2022 to 28 August 2026 – 1,040 trading days – and the daily returns calculated from them.

·         Final: the summary statistics for each fund, the weight limits, the optimal weights found by Solver, the portfolio summary, a covariance matrix and a pie chart of the final allocation.

The four funds used are:

·         Liquid Fund: a liquid fund that invests in very short-term money-market instruments such as treasury bills and commercial paper; the lowest-volatility fund in the set.

·         ESF: a moderate-risk fund.

·         Aggr Hybrid Fund: an aggressive hybrid fund (mostly equity with some debt).

·         Multicap Fund: a pure equity fund that invests across large, mid and small companies.

Step-by-Step: How the Optimiser Works

Step 1 – Collect daily prices

Everything starts with history. On the Raw data sheet, column A holds the date and columns B to E hold the adjusted NAV of each fund on that date. Adjusted NAVs already account for dividends, so the price movement reflects the full return an investor actually received. Nearly four years of daily data gives the model enough observations to estimate return and risk reliably.

	A	B	C	D	E	F	G	H	I	J
1		Adjusted close prices					Lognormal return
2	Date	Liquid Fund	ESF	Aggr Hybrid Fund	Multicap Fund		Liquid Fund	ESF	Aggr Hybrid Fund	Multicap Fund
3	05-09-2022	2,049.9009	18.4370	315.6730	93.7379
4	06-09-2022	2,049.9634	18.4943	316.5250	94.1012		0.000030	0.003103	0.002695	0.003868
5	07-09-2022	2,049.8188	18.5705	317.9194	94.4806		-0.000071	0.004112	0.004396	0.004024
6	08-09-2022	2,070.9662	18.6864	320.0379	95.6588		0.010264	0.006222	0.006642	0.012393
7	…	…	…	…	…		…	…	…	…
8	28-08-2026	2,691.5304	27.6096	602.6706	184.8970		…	…	…	…

Screenshot 1: The Raw data sheet – daily NAVs on the left, daily log returns on the right

Step 2 – Convert prices into daily returns

Prices on their own cannot be compared – the Liquid Fund’s NAV is above 2,000 while ESF’s is around 18. What matters is the percentage change from one day to the next. Columns G to J calculate this as a log return using Excel’s LN function:

	A	B	G
3	05-09-2022	2,049.9009	(first day – no return)
4	06-09-2022	2,049.9634	=LN(B4/B3)
5	07-09-2022	2,049.8188	=LN(B5/B4)
6	08-09-2022	2,070.9662	=LN(B6/B5)

Screenshot 2: Formula view – each daily return is the natural log of today’s NAV divided by yesterday’s

 

Why a log return instead of a simple percentage? Log returns add up neatly over time: the sum of daily log returns equals the total log return for the period. That makes it easy to scale a daily average up to an annual figure, which is exactly what the next step does. For small daily moves, a log return and a simple percentage are almost identical (0.0103 versus 1.03% on 8 September 2022, for example).

Step 3 – Turn daily returns into annual return and risk

Daily numbers are tiny and hard to interpret, so the Final sheet converts them into annual figures. Row 3 calculates the annual return and row 4 the annual standard deviation for each fund.

	A	B	C	D	E	F
1		Use EXP function for continuous compounding
2	Security	Liquid Fund	ESF	Aggr Hybrid Fund	Multicap Fund
3	Annual return	6.83%	10.29%	16.98%	17.91%
4	Std Dev	2.95%	10.52%	13.01%	15.96%	Use VAR.P for std dev of complete population
5	Minimum Weight	10.00%	10.00%	10.00%	10.00%
6	Maximum Weight	45.00%	45.00%	45.00%	45.00%
7	Optimal Weight	17.21%	13.88%	45.00%	23.90%

Screenshot 3: The Final sheet – fund statistics (rows 3–4), weight limits (yellow) and Solver’s optimal weights (green)

 

The two formulas behind these numbers are:

·         Annual return: =EXP(AVERAGE('Raw data'!G4:G1042)*252)-1. Take the average daily log return, multiply by 252 (the approximate number of trading days in a year), then use EXP to convert the log figure back into an ordinary percentage. This is the “continuous compounding” note you see in cell B1.

·         Annual standard deviation: =SQRT(VAR.P('Raw data'!G4:G1042)*252). Take the variance of daily returns, scale it up by 252, then take the square root. Variance grows in line with time, which is why we multiply the variance (not the standard deviation) by 252. VAR.P is used because the model treats the full history as the complete data set.

Reading row 3 and row 4 together already tells a story. The Liquid Fund is the calmest (2.95% risk) but also the lowest earner (6.83%). The Multicap Fund earns the most (17.91%) but is the bumpiest (15.96%). The Aggressive Hybrid Fund sits in a sweet spot – almost the same return as Multicap with noticeably less risk.

Step 4 – Measure how the funds move together

This is where optimisation earns its keep. Rows 18 to 23 of the Final sheet contain a covariance matrix. Each cell shows how two funds move together day by day. The diagonal cells (shaded orange) are simply each fund’s own variance, calculated with =VARP('Raw data'!$G$4:$G$1042) and so on. The off-diagonal cells show the shared movement between each pair. The matrix is symmetric: the Liquid Fund–ESF cell is the same as the ESF–Liquid Fund cell.

	A	B	C	D	E
18	Covariance Matrix
19		Liquid Fund	ESF	Aggr Hybrid Fund	Multicap Fund
20	Liquid Fund	0.00000345	0.00000032	-0.00000081	-0.00000059
21	ESF	0.00000032	0.00004390	0.00001102	0.00001969
22	Aggr Hybrid Fund	-0.00000081	0.00001102	0.00006713	0.00005235
23	Multicap Fund	-0.00000059	0.00001969	0.00005235	0.00010108

Screenshot 4: The daily covariance matrix – the diagonal holds each fund’s own variance

 

Covariance numbers are hard to read because they are so small, so it helps to translate them into correlations, which always sit between −1 and +1:

·         Liquid Fund with the equity funds: about −0.05 and −0.03. Practically zero, even slightly negative. When equity markets fall, the Liquid Fund does not fall with them. This makes it an excellent shock absorber.

·         ESF with the equity funds: about 0.20 to 0.30. Some shared movement, but still a useful diversifier.

·         Aggr Hybrid Fund with Multicap Fund: about 0.64. These two often move together because both hold a lot of Indian equity, so holding both gives less diversification benefit.

The optimiser uses these relationships to decide how much of each fund can be combined before the benefits of mixing run out.

Step 5 – Set the rules: minimum and maximum weights

Left alone, an optimiser can produce extreme answers, such as putting 90% in one fund because it happened to do well in the past. Rows 5 and 6 of the Final sheet (yellow cells in Screenshot 3) stop that from happening:

·         Minimum weight of 10%: every fund must get at least a tenth of the money, so no fund is dropped entirely.

·         Maximum weight of 45%: no fund can take more than 45%, so the portfolio is never dominated by one fund.

These limits are your personal judgement and they are the easiest part of the model to change. A cautious investor might cap equity funds at 30%; a younger investor with a long horizon might allow 60%. Change the numbers, re-run Solver and see how the answer shifts.

Step 6 – Calculate the portfolio’s return, risk and Sharpe ratio

Rows 9 to 14 of the Final sheet combine the individual fund numbers with the weights in row 7 to describe the whole portfolio.

	A	B
9	Portfolio Summary
10	Expected Return	14.53%
11	Std Dev	9.27%
12	Risk free rate	7.00%
13	Total Weight	100.00%
14	Sharpe Ratio	0.81

Screenshot 5: Portfolio summary – the Sharpe ratio in B14 is the number Solver maximises

 

Here is what each line does:

·         Expected Return (B10): =SUMPRODUCT(B3:E3,B7:E7). Each fund’s annual return multiplied by its weight, then added up. Return is a simple weighted average.

·         Std Dev (B11): =SQRT(MMULT(MMULT(B7:E7,B20:E23),TRANSPOSE(B7:E7)*252)). This is the heart of the model. It multiplies the weights by the covariance matrix and by the weights again, scales the result to a year and takes the square root. Unlike return, risk is not a simple weighted average – it is lowered by the way funds offset each other. This is an array formula; in older versions of Excel confirm it with Ctrl + Shift + Enter.

·         Risk free rate (B12): an input you set. 7% is roughly what a safe government instrument might pay.

·         Total Weight (B13): =B7+C7+D7+E7. A check that the weights add up to 100%.

·         Sharpe Ratio (B14): =(B10-B12)/B11. Excess return over the risk-free rate, per unit of risk.

Notice the diversification effect: the weighted average of the four funds’ individual risks at the optimal weights is about 11.6%, yet the portfolio’s actual risk is only 9.27%. That gap is the free lunch of diversification, and it exists only because the funds do not move perfectly together.

Step 7 – Let Solver find the best weights

You could try different weights by hand and watch the Sharpe ratio change, but Excel’s Solver add-in does this automatically, testing thousands of combinations in seconds. If you cannot see Solver on the Data tab, enable it first: File → Options → Add-ins → Manage Excel Add-ins → Go → tick Solver Add-in → OK.

Then open Data → Solver and enter the settings below (these are the exact settings saved in the workbook):

	Solver field	Setting	What it means
1	Set Objective	$B$14	The Sharpe ratio cell
2	To	Max	Make the Sharpe ratio as high as possible
3	By Changing Variable Cells	$B$7:$E$7	Solver may change only the four weights
4	Constraint 1	$B$13 = 1	Weights must add up to 100%
5	Constraint 2	$B$7:$E$7 <= $B$6:$E$6	No fund above its maximum weight
6	Constraint 3	$B$7:$E$7 >= $B$5:$E$5	No fund below its minimum weight
7	Make Unconstrained Variables Non-Negative	Ticked	No short selling
8	Solving Method	GRG Nonlinear	Correct engine for a curved (non-linear) problem like this

Screenshot 6: Solver Parameters as saved in the workbook

 

Click Solve, choose Keep Solver Solution, and row 7 fills with the optimal weights. Start from any reasonable guess, such as 25% in each fund; Solver will move from there. It will provide the optimal portfolio weights for the highest Sharpe Ratio.

Step 8 – Read the result

Once Solver finishes, row 7 shows the recommended mix and the pie chart on the Final sheet updates automatically.

Screenshot 7: The optimal allocation pie chart from the Final sheet

The optimal portfolio is:

·         Aggr Hybrid Fund – 45.0%: the largest holding, and it hits the 45% cap. Solver would have liked even more of it because it offers the best return for its level of risk.

·         Multicap Fund – 23.9%: the return engine. Solver keeps a meaningful slice for its high return but does not go further, because it moves closely with the Hybrid fund and is the most volatile.

·         Liquid Fund – 17.2%: the stabiliser. Its return is the lowest, but because it barely moves with the equity funds it pulls overall risk down sharply.

·         ESF – 13.9%: close to its 10% minimum. It adds a little diversification but does not offer enough return for its risk to earn a bigger share.

Together this mix is expected to earn about 14.5% a year with a standard deviation of about 9.3%, giving a Sharpe ratio of 0.81.

Is it actually better? A quick comparison

A useful sanity check is to compare the optimised portfolio with the simplest alternative: 25% in each fund.

Portfolio	Expected return	Risk (std dev)	Sharpe ratio
Equal weights (25% each)	13.00%	7.73%	0.78
Optimised portfolio	14.53%	9.27%	0.81

Table 1: Optimised versus equal-weight portfolio, using the same historical data

 

The optimised portfolio takes a little more risk, but it is paid well for it: each extra unit of risk earns more extra return than in the equal-weight mix. That is exactly what a higher Sharpe ratio means. If you would rather keep risk lower, you would tighten the maximum weights on the equity funds or raise the Liquid Fund’s minimum and re-run Solver.

Using the Workbook with Your Own Funds

Once you understand the steps, re-using the model takes only a few minutes:

1.      Download daily NAV history for your chosen funds (for example from AMFI or the fund house website) and paste it into columns A to E of the Raw data sheet. Use the same date range for every fund.

2.      Rename the fund headings in row 2 of both sheets so the labels match your funds.

3.      Make sure the return formulas in columns G to J, and the ranges in rows 3, 4 and 20 to 23 of the Final sheet, cover the full length of your new data.

4.      Update the risk-free rate in cell B12 to today’s level, for example the current yield on a government bond.

5.      Set minimum and maximum weights in rows 5 and 6 that suit your goals and risk appetite.

6.      Run Solver again with the same settings and read the new weights from row 7.

Limitations to Keep in Mind

Mean-variance optimisation is a powerful tool, but it is only as good as the data and assumptions you feed it:

·         The past is not the future: the model assumes the next few years will look like the last four. Returns from a strong equity run may not repeat.

·         Sensitive to inputs: small changes in expected returns can shift the weights a lot. Treat the output as a guide, not an exact prescription.

·         Standard deviation is not the only risk: it treats gains and losses the same and does not capture credit events, liquidity issues or sudden market crashes well.

·         Correlations change: funds that move independently in calm markets often fall together in a crisis.

·         Costs and taxes are ignored: exit loads, expense ratios and capital gains tax can change which mix is truly best for you.

A sensible approach is to use the optimiser to understand the trade-offs, apply your own judgement through the weight limits, and re-run it once or twice a year rather than chasing every change.

Key Takeaways

·         A good portfolio is about how funds work together, not just how each fund performs on its own.

·         Excel can do the full job: LN for daily returns, EXP and VAR.P for annual figures, MMULT for portfolio risk and Solver for the best weights.

·         The Sharpe ratio is the scorecard; Solver’s job is to make it as high as possible within your limits.

·         In this example, a mix of 45% Aggr Hybrid Fund, 23.9% Multicap Fund, 17.2% Liquid Fund and 13.9% ESF gives an expected return of about 14.5% with risk of about 9.3% and a Sharpe ratio of 0.81.

·         The Liquid Fund earns the least on its own, yet it earns a meaningful place because it barely moves with the equity funds and steadies the whole portfolio.