Excel Lab
  • Excel Lab
  • Release Notes
  • Getting Started
    • Installing Excel Lab
      • Step 1: Download Files
      • Step 2: Register Libraries
      • Step 3: Activate Add-in
      • Step 4: Verify Installation
  • Functions
    • General
      • ISMATRIXPSD
      • MATRIX
      • XLABHELP
      • XLABINFO
      • XLABLICENSE
      • RESETPASSWORD
    • Return Models
      • CAPM
      • DESMOOTHRETURNS
      • IMPLIEDRETURNS
      • MLERETURNS
    • Risk Models
      • ANNUALIZERISK
      • EWMA
      • HISTORICALRISK
      • MLERISK
      • PORTFOLIORISK
      • TURBULENTRISK
      • QUIETRISK
    • Optimization
      • MVO
      • MTO
      • MVT
      • MVFRONTIER
      • MTFRONTIER
      • ISORETURN
    • Simulation
      • MCNORM
      • BOOTSTRAP
    • Exposure to Loss
      • LOSSPR
      • OMEGARATIO
      • SORTINORATIO
      • TAILRATIO
      • VALUEATRISK
      • MAXDD
    • Regression Analysis
      • FACTORANALYSIS
      • PSR
    • Scenario Analysis
      • MAHALANOBIS
      • SCENARIOPR
      • IMPLIEDSCENARIO
  • Frequently Asked Questions
    • Common Issues
    • FAQ
  • Further Reading
  • Windham's Research Insights
  • Watch Our Educational Videos
Powered by GitBook
On this page
  • Description
  • Syntax
  • Input(s)
  • Output(s)
  • Example
  1. Functions
  2. Optimization

MVO

Mean-variance optimization, a quadratic programming optimizer.

Description

Maximize expected returns and minimize expected risk. Solve for a mean-variance optimal portfolio. The function allows you to specify both linear and non-linear constraints and is able to account for friction penalties (transaction costs).

Syntax

The following describes the function signature for use in Microsoft Excel's formula bar.

=MVO(mu, sigma, rho, aversion, wInitial, tc, lb, ub, constraints, nonlincons)

Input(s)

Argument
Description

mu

Required. Vector of expected returns.

sigma

Required. Vector of expected risk.

rho

Required. Correlation matrix.

aversion

Optional. Scalar value for risk aversion. If the argument is not specified, it defaults to 1.

wInitial

Optional. Vector of initial weights (or your current weights). This is used to measure the friction penalties or as a starting point should a numerical approach be necessary. If not specified, it defaults to a vector of zeros.

tc

Optional. Vector of transaction costs. If the argument is not specified, it defaults to a vector zeros.

lb

Optional. Vector of lower bound limits. If the argument is not specified, it defaults to a vector zeros.

ub

Optional. Vector of upper bound limits. If the argument is not specified, it defaults to a vector ones.

constraints

nonlincons

Output(s)

The output matrix follows the vector orientation of mu (column / row). If you have specified your inputs as column-vectors, the corresponding output matrix will be transpose of the above.

exitFlag
Description

-2

No feasible solution found. Check your constraints and problem definition.

-1

Unexpected interruption.

0

Number of iterations exceeded.

1

First-order optimality measure is less than tolerance threshold and the constraints were satisfied.

2

Delta in optimal weights is less than the configured numerical step size.

3

Change in the expected utility value is less than the tolerance threshold.

4

Magnitude of search direction was less than the configured threshold.

5

Magnitude of directional derivative in the search direction was less than the configured threshold.

Example

PreviousOptimizationNextMTO

Last updated 3 years ago

Optional. Matrix of constraints, operator enumeration, and values:

The operator enumeration is represented by

If the argument is not specified, it will default to a fully-funded constraint. i.e.

Optional. Matrix to specify nonlinear constraint enumeration, operator enumeration, and values:

The nonlinType enumeration is

The function returns a vector of optimal weights www across NNNassets and appends the corresponding optimization's exit flag.

output=[w1w2…wNexitFlag]\text{output}=\begin{bmatrix}w_1 & w_2 & \ldots & w_N & \text{exitFlag}\end{bmatrix}output=[w1​​w2​​…​wN​​exitFlag​]

[Aopb]\begin{bmatrix}A & op &b\end{bmatrix}[A​op​b​]
op∈{0:≤1:=2:≥op \in \begin{cases} 0: & \leq \\1: & = \\ 2: & \geq \end{cases}op∈⎩⎨⎧​0:1:2:​≤=≥​
[1,1,…,1N,1,1][1, 1, \ldots , 1_{N}, 1, 1][1,1,…,1N​,1,1]
[nonlinTypeopvalue]\begin{bmatrix}nonlinType & op &value\end{bmatrix}[nonlinType​op​value​]
nonlinType∈{0:off1:same risk2:same tracking-errornonlinType \in \begin{cases} 0: & \text{off} \\1: & \text{same risk} \\ 2: & \text{same tracking-error} \end{cases}nonlinType∈⎩⎨⎧​0:1:2:​offsame risksame tracking-error​
88KB
MVO.xlsx
Example Workbook: MVO