# Excel Lab

Knowledge base to support Excel Lab, a quantitative modeling library for asset allocation and factor analysis - a Windham Labs technology solution.

{% embed url="<https://windhamlabs.wistia.com/medias/gxhqhsfora>" %}
Watch the introduction to Excel Lab
{% endembed %}

Leverage linear optimization, numerical methods, matrix mathematics, and regression models within your Microsoft Excel environment. Quickly prototype quantitative models without leaving Microsoft Excel!&#x20;

Access the power of the Windham Labs' quant models via Microsoft Excel.

## What is Excel Lab?

Windham's Excel Lab is a Microsoft Excel add-in. It is a library of functions available for direct use within Microsoft Excel's workbooks to calculate and analyze complex mathematics and models used in asset allocation and risk management.

These sets of functions are not natively available in Microsoft Excel. Some examples include:

* Mean-variance optimization (and various other objective functions)
* First-passage time probability models
* Partial sample regression model
* Mahalanobis distance
* Multi-variate simulations

## Why use Excel Lab?

Asset owners and buy-side analysts may find Excel Lab a valuable as part of their daily workflow.

* Centralize your workflow
* Save time and mitigate errors
  * Focus on sourcing data and building analytics in one place
  * Track your models in one workbook instead of relying on a string of tools that risks disconnecting your data states and models.
* Communicate your work in a clear and transparent workbook
* Extend your workbooks to natively include responsive and dynamic analytics

> #### "With Excel Lab, I am able to have my data and models displayed in one place, prototype my research ideas within the same workbook, and communicate all of this in a format accessible to decision makers. This has greatly improved my research process and productivity."
>
> Anonymous buy-side analyst


# Release Notes

Change log for communicating new features, fixes, and revisions of Excel Lab.

## 1.1.0.11887 (2025-08-14)

### Updated

* [**LOSSPR**](/functions/exposure-to-loss/losspr) revised for vectorization support.

## 1.1.0.11885 (2025-05-02)

Spring cleaning season!

### Updated

Prerequisites for Matlab runtime updated for version 24.2

## 1.0.0.11884 (2023-12-26)

Happy holidays! Santa has been busy, new API connection this holiday season.

### Added

New licensing server API connection.

* New licensing API connection.

## 1.0.0.11875 (2023-12-01)

Preparations for licensing server upgrade support for unlock code feature.

## 1.0.0.11863 (2023-10-09)

Fixed a gremlin that is fussy on double-precision machine errors.

### Updated

* [**ISMATRIXPSD** ](/functions/general/ismatrixpsd)now checks and accounts for double-precision differences in the upper and lower triangular corrected matrix, i.e. symmetry is verified.

## 1.0.0.11845 (2023-06-27)

Refinements to several existing functions and new runtime framework.

### Updated

* [**IMPLIEDRETURNS** ](/functions/return-models/impliedreturns)added specification for covariance matrix as an optional parameter.
* [**PSR** ](/functions/regression-analysis/psr)improved handling for machine epsilon.
* [**ISMATRIXPSD** ](/functions/general/ismatrixpsd)now uses Newton's method by minimizing the Frobenius distance.
* New runtime (9.13).

## 1.0.0.11555 (2022-08-05)

Added desmoothing returns functionality to Excel Lab.

### Added

* [**DESMOOTHRETURNS**](/functions/return-models/desmoothreturns) using a first-order autoregressive model.

### Updated

* Improvements to authentication process.

## 1.0.0.11271 (2021-11-18)

Introducing scenario modification functionality to Excel Lab based on our innovations in [scenario analysis](https://doi.org/10.3905/jpm.2020.1.125).

### Added

* [**IMPLIEDSCENARIO** ](/functions/scenario-analysis/impliedscenario)solve for the implied scenario estimates across economic variables to reconcile with your target probabilities.

## 1.0.0.11166 (2021-09-07)

Small improvements to calculator logic and verification.

### Updated

* Default marshalling between Microsoft Excel and Excel Lab's engine for missing values. This will impact [**MLERISK**](/functions/risk-models/mlerisk) and [**MLERETURNS**](/functions/return-models/mlereturns) where users will have to explicitly specify missing values in their workbook's cells as $$=\text{NA()}$$ in the formula bar.

## 1.0.0.10882 (2021-01-15)

It has been an exciting week working with early adopters, we have had very constructive feedback.

### Added

* [**BOOTSTRAP** ](/functions/simulation/bootstrap)sample from a dataset with replacement with equal or custom probability weights across sample periods. Draw from a univariate or multivariate sample with the option to apply a set of portfolio weights to the multivariate sample.

### Updated

* Wrapper fix for **VERIFYLICENSE**.

## 1.0.0.10874 (2021-01-11)

It has been an exciting week working with early adopters, we have had very constructive feedback.

### Updated

* [**XLABINFO**](/functions/general/xlabinfo) now also returns general information in cell in addition to a UI dialog.
* [**MAHALANOBIS** ](/functions/scenario-analysis/mahalanobis#syntax)new optional input arguments, giving you more control.
* [**QUIETRISK**](/functions/risk-models/quietrisk) the threshold parameter is now based from an inliers perspective.
* [**PSR** ](/functions/regression-analysis/psr)minor refinements to code elegance and speed.

## 1.0.0.10855 (2021-01-01)

Happy new year :tada:&#x20;

### Added

* [**MATRIX** ](/functions/general/matrix)concatenate disjointed vectors (or matrices) quickly and intuitively within Excel.

### Updated

* Security enhancements in offline license manager.

## 1.0.0.10853 prerelease (2020-12-30)

Ready for prime time!

### Added

* [**SCENARIOPR** ](/functions/scenario-analysis/scenariopr#syntax)added an optional free parameter to model fatter tails for calculating scenario probabilities.

### Updated

* Improved licensing verification.
* Revised matrix orientation and dimensional fixes for iso-curve functions.

## 1.0.0.10752 prerelease (2020-11-13)

Feature-creep is getting intense!&#x20;

### Added

* [**MVFRONTIER** ](/functions/optimization/mvfrontier)solve for multiple optimal portfolios on the efficient frontier.
* [**MTFRONTIER** ](/functions/optimization/mtfrontier)solve for multiple optimal portfolios to evaluate relative risk.

## 1.0.0.10746 prerelease (2020-11-11)

Sometimes, we have to regress in order to progress :sweat\_smile:&#x20;

### Added

* [**MAXDD** ](/functions/exposure-to-loss/maxdd)maximum drawdown in Excel - forget your VBA code upkeep!
* **COVMATRIX** an much easier and direct way to compute covariance matrix, with the ability to treat missing data as well.
* **CORRMATRIX** you can't have the covariance matrix without some correlation coefficients.
* [**PORTFOLIORISK** ](/functions/risk-models/portfoliorisk)forget nested MMULT and SUMPRODUCT formulas! Vector math simplified!

### Updated

* [**PSR** ](/functions/regression-analysis/psr#syntax)added argument to allow the specification for the predictor values for the partial sample regression model. This allows the analyst to specify any set of predictor values to evaluate a response for the dependent variable forecast based. If not specified, as previously implicitly assumed, the most recent observations of the dependent variables are used with the model parameters for forecasting.

## 1.0.0.10720 prerelease (2020-11-03)

Hello world! Lots of coffee and rock music in the background at [Windham Labs](https://www.windhamlabs.com/). The Excel Lab quant library is being tested and worked on extensively by the team. We are getting things ready to bring this exciting tool to your desk.&#x20;

### Added

* [**CAPM** ](/functions/return-models/capm)calculate equilibrium returns
* [**IMPLIEDRETURNS** ](/functions/return-models/impliedreturns)run a reverse optimization
* [**MLERETURNS** ](/functions/return-models/mlereturns)maximum-likelihood return estimates
* [**ANNUALIZERISK** ](/functions/risk-models/annualizerisk)annualize risk to account for compounding and log-normality
* [**EWMA** ](/functions/risk-models/ewma)exponential moving average risk model
* [**HISTORICALRISK** ](/functions/risk-models/historicalrisk)estimate expected risk from historical data
* [**MAHALANOBIS** ](/functions/scenario-analysis/mahalanobis)compute the Mahalanobis distance statistic
* [**MLERISK** ](/functions/risk-models/mlerisk)maximum-likelihood risk estimates
* [**TURBULENTRISK** ](/functions/risk-models/turbulentrisk)estimate risk based on statistical outliers
* [**QUIETRISK** ](/functions/risk-models/quietrisk)estimate risk based on statistical inliers
* [**MVT** ](/functions/optimization/mvt)multi-goal optimization (mean-variance tracking error)
* [**MVO** ](/functions/optimization/mvo)mean-variance optimization
* [**MTO** ](/functions/optimization/mto)mean-tracking error optimization
* [**ISORETURN** ](/functions/optimization/isoreturn)solve for an iso-return efficient frontier (risk vs. tracking error)
* [**FACTORANALYSIS** ](/functions/regression-analysis/factoranalysis)run multi-factor OLS and stepwise regression analysis
* [**LOSSPR** ](/functions/exposure-to-loss/losspr)estimate probability of loss continuously and conventionally
* [**OMEGARATIO** ](/functions/exposure-to-loss/omegaratio)calculate the omega ratio
* [**PSR** ](/functions/regression-analysis/psr)run partial-sample regressions, a new novel approach to regression analysis
* [**SORTINORATIO** ](/functions/exposure-to-loss/sortinoratio)calculate the Sortino ratio
* [**TAILRATIO** ](/functions/exposure-to-loss/tailratio)estimate tail ratio from empirical data
* [**VALUEATRISK** ](/functions/exposure-to-loss/valueatrisk)estimate value at risk measures continuously and conventionally
* [**ISMATRIXPSD** ](/functions/general/ismatrixpsd)check matrix for positive semi-definite (PSD) properties / solve to nearest PSD values
* [**MCNORM** ](/functions/simulation/mcnorm)simulate multi-variate normal random values using Monte-Carlo
* [**SCENARIOPR** ](/functions/scenario-analysis/scenariopr)calculate scenario probabilities implied from empirical data


# Installing Excel Lab

Excel Lab is a powerful add-in that provides access to Windham's quant library spanning modern portfolio theory concepts including optimization, linear algebra, statistics, and probability theory.

## System Requirements <a href="#system-requirements" id="system-requirements"></a>

| Minimum                             | Recommended                                                                                                                              |
| ----------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| Microsoft Windows 7                 | Microsoft Windows 10                                                                                                                     |
| Any Intel or AMD x86 - 64 processor | Intel x64 processor with more than two logical cores                                                                                     |
| 8GB of HDD space                    | 8GB SSD space                                                                                                                            |
| 1GB RAM                             | 8GB or more                                                                                                                              |
| Microsoft Excel 2019 or later       | Microsoft Excel 365 (with [**dynamic arrays**](https://insider.office.com/en-us/blog/dynamic-arrays-and-new-functions-in-excel) enabled) |

{% hint style="info" %}
Older versions of Microsoft Excel are not officially supported by the Windham Labs team. We highly recommend Microsoft Excel 365 with dynamic arrays.
{% endhint %}

## Setting Up Excel Lab

Windham's Excel Lab is powered by a combination of complex technologies to enable powerful calculators and analytics. It will be helpful to enlist your IT team/administrator for help when installing the tool.

There are **4 steps** that are important to follow for a successful installation.

{% content-ref url="/pages/-MQE6LVVl8CFno7AFDAC" %}
[Step 1: Download Files](/getting-started/installing-excel-lab/setup-files)
{% endcontent-ref %}

{% content-ref url="/pages/-MQE6uo8wNWMV8JUELsq" %}
[Step 2: Register Libraries](/getting-started/installing-excel-lab/register-files)
{% endcontent-ref %}

{% content-ref url="/pages/-MQE79-fwQh\_PfCiDATB" %}
[Step 3: Activate Add-in](/getting-started/installing-excel-lab/activate-add-in)
{% endcontent-ref %}

{% content-ref url="/pages/-MQE7ODSg02oYI30k1Px" %}
[Step 4: Verify Installation](/getting-started/installing-excel-lab/verify-installation)
{% endcontent-ref %}


# Step 1: Download Files

The following page contains all the necessary files required for installing Excel Lab.

You will need administrative privileges (UAC) on your Windows machine in order to successfully install the prerequisite and Excel Lab. Work with your IT administrator for assistance.

## (1) Prerequisite Runtime

Download and install the MATLAB Runtime. This is a prerequisite for using Excel Lab.

#### :arrow\_down: [MATLAB Runtime 24.2 for Windows (64-bit)](https://ssd.mathworks.com/supportfiles/downloads/R2024b/Release/6/deployment_files/installer/complete/win64/MATLAB_Runtime_R2024b_Update_6_win64.zip)

If you already have this runtime version installed, you will only need to install Excel Lab in the following section below.

## (2) Excel Lab Install Kit

Please read the License Agreement before downloading or using Excel Lab.

{% hint style="danger" %}
By downloading or using Excel Lab, you are agreeing to be bound by the terms and conditions of the License Agreement.
{% endhint %}

{% file src="/files/-MNWDXPv3EOgpIAGd3lQ" %}
End User License Agreement
{% endfile %}

Download and run the install the Excel Lab installation kit. You will need administrator privileges on your machine (consult your IT team). You will also need a valid user account with Windham Labs to use Excel Lab.

#### :arrow\_down: [Excel Lab Installer (1.1.0.11887)](https://files.windhamlabs.com/xlab/WindhamExcelLabInstaller\(11887\).exe)

The CRC64 checksum for the installer file is `A2710F30ECAA12BF`.


# Step 2: Register Libraries

## Required Instructions

Register the necessary library (DLL) files to your system.&#x20;

{% hint style="warning" %}
The following should be executed using the user profile account that will be using Excel Lab. This registers the user profile with UAC protections to be able use this add-in.
{% endhint %}

(1) Open Microsoft Windows' command console

(2) Execute the following in your command console to start the registration script

```powershell
cd "C:\Program Files\Windham Labs\WindhamXlsLab\application\" & _install.bat
```

Answer **yes** if prompted to register only to your user.

## Why register?

Registering a DLL is a typical technical process for using advanced third-party libraries in a system. This process ensures that the instance of the component (Excel Lab) can be created or launched.&#x20;

The process adds information to a central directory for use by Microsoft. The registered information typically includes a friendly name of the component, which makes it easier to use from within other programs (i.e. Microsoft Excel), the full path to the library of functions, and any other relevant resources.&#x20;

{% hint style="info" %}
Typically, an installer package should take care of this for you with no guarantee of success.&#x20;

We recommend explicitly executing this step as a prudent measure for ensuring a successful installation.
{% endhint %}


# Step 3: Activate Add-in

Once Excel Lab has been installed, you will need to activate the add-in. To do this in Microsoft Excel

(1) Open Excel

(2) In Excel's menu, select **File -> Options**

(3) In Excel's options menu, select **Add-ins**

(4) At the bottom of the dialog, click on **Go... (Excel Add-ins)**

(5) An Add-ins window will pop up, click on **Browse...**

(6) Select the Excel Lab add-in file at the following location

&#x20;    `C:\Program Files\Windham Labs\WindhamXlsLab\application\WindhamXlsLab.xla`

(7) Click OK. The add-in should be activated.


# Step 4: Verify Installation

Finally, verify whether you have successfully installed the add-in library.&#x20;

(1) Start Microsoft Excel

(2) Open or create a blank workbook

(3) Type `=XLABINFO()` in any cell

```excel-formula
=XLABINFO()
```

{% hint style="info" %}
Excel Lab will take a minute to initialize its computation engine.
{% endhint %}


# General


# ISMATRIXPSD

Verify if a matrix is positive semidefinite. Solve for the nearest positive semidefinite matrix if it is not.

## Description

Positive semidefinite matrices are useful in portfolio theory because they are symmetric and easy to decompose. This is particularly useful for optimization (e.g. [Cholsky decomposition](https://en.wikipedia.org/wiki/Cholesky_decomposition)). When a matrix is symmetric and positive semidefinite, we can decompose it in a lower triangular matrix and find its inverse - which is computationally more efficient than inverting a matrix. Positive semidefinite matrices are a special case of symmetric matrices and is a good check for covariance and correlation matrices.

This function allows you to check if your matrix is positive semidefinite, and if it isn't it will perturb its elements to solve for the nearest positive semidefinite matrix.

We consider a matrix positive semidefinite if its characteristic roots are greater or equal to zero.

## Syntax

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

```excel-formula
=ISMATRIXPSD(x, isSolveNearest)
```

### Input(s)

| Argument           | Description                                                                                                                                                                                                                                                                   |
| ------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **x**              | Matrix to evaluate or solve.                                                                                                                                                                                                                                                  |
| **isSolveNearest** | Optional logical (TRUE or FALSE) flag. If true, the function will solve and return the nearest positive semidefinite matrix, otherwise it will only evaluate whether **x** is positive semidefinite or not. If the argument is not specified, the function defaults to false. |

### Output(s)

The function's output will vary depending on the specification of the `isSolveNearest` argument. The following table will describe the corresponding output result.&#x20;

Logical whether x is positive semidefinite or otherwise. Nearest positive semidefinite matrix

| isSolveNearest  | Output                                                                       |
| --------------- | ---------------------------------------------------------------------------- |
| true            | Square matrix of the nearest positive semidefinite matrix relative to `x`.   |
| false (default) | Logical indicating whether input square matrix `x` is positive semidefinite. |

## Example

![Check or solve to the closest positive semidefinite matrix!](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MQVGmPeWLqPvVOBdmIv%2F-MQVGnbHXgC_w0ppSnfM%2Fimage.png?alt=media\&token=65e917e0-03c8-4b38-b9ec-f86f0c614cc5)

{% file src="/files/-MQVGwQEnasDc2otquaJ" %}
Example Workbook: ISMATRIXPSD
{% endfile %}


# MATRIX

Concatenate disjointed arrays in Excel column (or row)-wise into a matrix

## Description

Microsoft Excel does not provide a natural way to work with concatenating numerical arrays / vectors. The only natural way to do this in Microsoft Excel is to input or display vectors adjacent to another within a worksheet.&#x20;

This restriction can force copying and replicating vectors into new or existing sheets when constructing more complex workbooks that require references to matrix's constituents in different columns (or rows).

The MATRIX function in Excel Lab allows you to concatenate arrays intuitively and can be used nested within other Microsoft Excel or Excel Lab functions.

## Syntax

```
output = MATRIX(v1, v2, ...., vN, isRow)
```

{% tabs %}
{% tab title="Input(s)" %}

| Argument         | Description                                                                                                                                                                   |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **v1,v2,...,vN** | Column-vectors, row-vectors, or matrices.                                                                                                                                     |
| **isRow**        | Optional. Logical flag (TRUE or FALSE) to indicated whether you would like to concatenate the input vectors/matrices row-wise. Defaults to false (column-wise concatenation). |
| {% endtab %}     |                                                                                                                                                                               |

{% tab title="Output(s)" %}
Input vectors (or matrices) are concatenated column-wise by default or when isRow is FALSE.

$$
\text{output}=  \begin{bmatrix}v\_1 & v\_2 & \dots & v\_N \end{bmatrix}
$$

When isRow is specified and set to FALSE, the function concatenates the input vectors (matrices) row-wise

$$
\text{output}=  \begin{bmatrix}v\_1 \ v\_2 \ \vdots \ v\_N \end{bmatrix}
$$
{% endtab %}
{% endtabs %}

## Example


# XLABHELP

Getting help within Excel for the Excel Lab quant library.

## Description

If you need help or require documentation, invoke the library's help resource. This function open our documentation repository for function definitions and examples.

## Syntax

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

```excel-formula
=XLABHELP(openHelpURL)
```

### Input(s)

| Argument        | Description                                                                                                                                                                                   |
| --------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **openHelpURL** | Optional. Logical, opens our documentation repository is set to TRUE. Otherwise, it will return the URL address to the Excel cell. If the argument is not specified, it will default to TRUE. |

### Output(s)

The function will return a URL address or open Excel Lab's documentation repository for function definitions and examples.

## Example

![Excel Lab launches this website for your technical reference](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MM2khFqx4n_d-r0mnQl%2F-MM2lu8O9v07y3qTrxbP%2Fxlabhelp2.gif?alt=media\&token=b5c667a4-d339-4fd4-ac0e-4d88a743e009)


# XLABINFO

Retrieve information about the Excel Lab quant library installed on your workstation.

## Description

Review information about Windham's quant library installed on your workstation. You can use this to obtain version information and general information for support.

{% hint style="info" %}
Invoking this function initializes the computation engine just like any other Excel Lab feature.
{% endhint %}

This function can also be used to test whether the add-in is initializing successfully.&#x20;

## Syntax

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

```excel-formula
=XLABINFO(showDialog)
```

### Input(s)

| Argument       | Description                                                                                                                                                                                                                                                                  |
| -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **showDialog** | Optional. Logical to specify the display of a graphical dialog presenting information on the Excel Lab quant library installed, if FALSE the function will return text information into the corresponding Excel cell. If the argument is not specified, it defaults to TRUE. |

### Output(s)

Information on the product, version number, resource addresses, etc.

## Example

![=XLABINFO() in the formula bar](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MM2g2q3bXomiz4owJt9%2F-MM2htC9OtruzlFqrqCI%2Fxlabinfo.gif?alt=media\&token=b908211a-bc3c-480e-9470-da2540e9eb3e)


# XLABLICENSE

Invoke authentication with licensing servers to enable or re-enable your use of Excel Lab.

## Description

Excel Lab authenticates with Windham's licensing server for convenience and security. If you encounter issues with re-authentication, you will be able to manually invoke authentication my following the syntax as documented below in Excel's formula bar.

![A workbook using Excel Lab requiring re-authentication](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MPeB-1lci2OMH5xxX5p%2F-MPeBxSIobRpVYOGf9W5%2Fimage.png?alt=media\&token=9e34a98c-76be-444e-8b94-7d7cee614100)

{% hint style="info" %}
You can invoke Excel Lab's license authentication process anywhere in a workbook. You can even do this in a new blank workbook.&#x20;

You do not need to save the workbook once authentication is successful.
{% endhint %}

## Syntax

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

```excel-formula
=XLABLICENSE()
```

### Input(s)

Follow the on-screen dialog prompt to enter your username and password.

| Argument     | Description                                                                                                                           |
| ------------ | ------------------------------------------------------------------------------------------------------------------------------------- |
| **Username** | Your e-mail address registered with Windham Labs.                                                                                     |
| **Password** | <p>Your password, or unlock key code for offline use. <br>The offline unlock key code is only available for yearly billing plans.</p> |

### Output(s)

| Message                 | Description                                                                               |
| ----------------------- | ----------------------------------------------------------------------------------------- |
| **Valid**               | Credentials (username / password) and account successfully authenticated. Enjoy ExcelLab! |
| **Invalid Credentials** | Invalid username and/or password. Check for typos, try again.                             |
| **Inactive**            | Your account is no longer active.                                                         |
| **Invalid Permissions** | Your account is not permissioned for the product / feature.                               |
| **Expired**             | Your account has expired.                                                                 |
| **Invalid Host**        | Unauthorized device. Each Excel Lab license is locked to a device.                        |
| **Missing Credentials** | No input for either username or password. Please try again.                               |

## Example

![Invoke licensing via the Formula Bar](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MPeFnywmU0c_F2Kco_z%2F-MPeGnpShvNvmbGtRFuG%2Fimage.png?alt=media\&token=cc99c0c0-5cf2-4f11-b0a6-30e5930db917)

To manually authenticated

1. Open any workbook, or create a new (temporary) workbook.
2. In any cell, type `=XLABLICENSE()` in the formula bar.
3. Follow the instructions as described in the Syntax section above.

{% hint style="info" %}
When successful, Excel Lab will store your license information within user-specific system files. You do not need to save the temporary workbook.
{% endhint %}


# RESETPASSWORD

Update and set a new password for your user account

## Description

Excel Lab authenticates with Windham's licensing server for convenience and security. To set a new password, you will require your current password and a reset code. The reset code will be emailed to you during this process (MFA).

{% hint style="info" %}
You can invoke Excel Lab's license authentication process anywhere in a workbook. You can even do this in a new blank workbook.&#x20;

You do not need to save the workbook once authentication is successful.
{% endhint %}

## Syntax

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

```excel-formula
=RESETPASSWORD()
```

### Input(s)

Follow the on-screen dialog prompt to enter your username and password.

| Argument       | Description                                                                                 |
| -------------- | ------------------------------------------------------------------------------------------- |
| **Username**   | Your e-mail address registered with Windham Labs.                                           |
| **Password**   | Your current password.                                                                      |
| **Reset Code** | Our licensing server will send you a reset code to confirm your identity, check your email. |
| New Password   | Enter a new password.                                                                       |

### Output(s)

| Message   | Description                                                                             |
| --------- | --------------------------------------------------------------------------------------- |
| **TRUE**  | Password successfully set.                                                              |
| **FALSE** | Invalid username and/or current password and/or reset code. Check for typos, try again. |

## Example

<figure><img src="https://258561627-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MK66-MGuoULhqCDXLwy%2Fuploads%2FI5G65graMBgCQdkiWE5j%2Fimage.png?alt=media&amp;token=ba10af24-65ca-45f8-b95e-4f082e395229" alt=""><figcaption></figcaption></figure>

To set a new password:

1. Open any workbook, or create a new (temporary) workbook.
2. In any cell, type `=RESETPASSWORD()` in the formula bar.
3. Follow the instructions as described in the Syntax section above.

{% hint style="info" %}
When successful, Excel Lab will store your license information within user-specific system files. You do not need to save the temporary workbook.
{% endhint %}


# Return Models


# CAPM

Estimate expected returns using the Capital Asset Pricing Model. This is also known as the Equilibrium Returns.

## Description

The capital asset pricing model (CAPM) is an asset valuation model describing the relationship between assets (or portfolios) and market prices. CAPM posits that the intercept of a regression equation between an instrument's returns and the returns of systematic factors is zero in an efficient market. This is a widely used classic model for asset pricing.

## Syntax

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

```excel-formula
=CAPM(marketReturns, assetReturns, rf, rm, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                    |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **marketReturns**   | Required. Time series of market portfolio returns.                                                                                                                                             |
| **assetReturns**    | Required. Time series or matrix of asset (or portfolio) returns.                                                                                                                               |
| **rf**              | Optional. Risk-free rate. If you do not enter the argument, it defaults to zero.                                                                                                               |
| **rm**              | Optional. Expected market return. If you do not enter the argument it, defaults to the annualized historical average of the **marketReturns** time series.                                     |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p> |

### Output(s)

Row vector of CAPM expected return estimates.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MKuIS1nbFw1KMGIwGz2%2F-MKuIhKqT302WpCyqbYy%2Fimage.png?alt=media\&token=69d93296-58df-4216-8e06-e65705d936bc)

{% file src="/files/-MKuFBvILcLpShxM7FbS" %}
Example Workbook: CAPM
{% endfile %}


# DESMOOTHRETURNS

Desmooth time series returns using a first-order autoregressive model.

## Description

For certain time series, analysts may need to employ an autoregressive model to adjust or desmooth its returns. The first-order autoregressive model assumes that the return in a given period is a linear function of the return in the previous period plus an intercept and an error term.

## Syntax

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

```excel-formula
=DESMOOTHRETURNS(assetReturns, solveEndValueOnly)
```

### Input(s)

| Argument              |                                                                                                                                                                                        |
| --------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **assetReturns**      | Required. Time series or matrix of asset returns.                                                                                                                                      |
| **solveEndValueOnly** | Optional. Logical to indicate whether to only solve for an end of time series value, or to adjust and return all observations. If you do not enter the argument, it defaults to FALSE. |

### Output(s)

Time series or matrix of adjusted (desmoothed) returns.


# IMPLIEDRETURNS

Calculate implied expected returns of your assets / instruments.

## Description

Implied returns is a reverse mean-variance optimization methodology. It solves for a set of expected returns for the portfolio's assets under the assumption that the portfolio is on the efficient frontier (optimal).

This can help investors understand the implicit assumptions of maintaining their current portfolio allocations. An investor may choose to rebalance their portfolio if the implied returns are substantially different from the plausible range of expected asset returns.

Analysts may also use this method to contrast against other models of expected returns.

## Syntax

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

<pre class="language-excel-formula"><code class="lang-excel-formula">=IMPLIEDRETURNS(assetReturns, weights, riskAversion, indexCash, rf, dataPeriodicity)
=IMPLIEDRETURNS(<a data-footnote-ref href="#user-content-fn-1">___</a>, <a data-footnote-ref href="#user-content-fn-2">"Name1", value1, ..., "NameN", valueN</a>)
</code></pre>

### Input(s)

| Argument            | Description                                                                                                                                                                                                                                                                        |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                                                                                                                  |
| **weights**         | Required. Vector of portfolio weights.                                                                                                                                                                                                                                             |
| **riskAversion**    | Optional. Risk aversion constant. If not specified, it will default to 1.                                                                                                                                                                                                          |
| **indexCash**       | Optional. Integer to indicate the column index position of a cash asset in *assetReturns*. This is used to fix a variable in the system of equations in order to solve the reverse optimization. If not specified, it will default to the column index of the lowest return asset. |
| **rf**              | Optional. Risk-free rate that would correspond with *indexCash*. If not specified, it will default to the annualized historical average of the lowest return asset.                                                                                                                |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p>                                                                                     |

### Name-Value Optional Arguments

Specify optional pairs of arguments where Name is the option argument name and Value is the corresponding input object. Name-value arguments must appear after other input argument(s) above, but the order of these pairs does not matter.

Example:

```excel-formula
=IMPLIEDRETURNS(___, "Name1", value1, "Name2", value2, ..., "NameN", valueN)
```

| Name           | Value                                                                                                                                                |
| -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| **covariance** | Custom covariance matrix of asset returns. This may differ from historical covariance in order to solve implied returns under different risk models. |

### Output(s)

Row or column vector of implied expected returns. The output vector orientation follows the same orientation as the input vector *weights*.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MKuPFr45gONMOPNTVOq%2F-MKuXf0KrERwE1tepS0r%2Fimage.png?alt=media\&token=43661919-4c22-4f9f-b22f-4f639ef27fbe)

{% file src="/files/-MKuYPZNoYjFrCehvXH4" %}
Example Workbook: IMPLIEDRETURNS
{% endfile %}

[^1]: Name-value pair arguments come after strong-typed arguments above.

[^2]: See Name-Value table below for available Name-value pair arguments.


# MLERETURNS

Estimate expected returns for assets with incomplete historical data (unequal lengths) with a maximum likelihood estimator.

## Description

In an ideal world, analysts would have access to complete and clean data. In practice, the amount of historical time series data across asset classes differs. A common workaround is to discard observations that are incomplete across the panel, or to restrict the analysis with assets that have common histories. This forgoes the potential information embedded within asset classes with shorter or incomplete histories.

We can employ a Maximum Likelihood Estimation (MLE) method to use all available data to solve for parameters (means or covariances) that is most likely to have produced the observations.

## Syntax

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

```excel-formula
=MLERETURNS(assetReturns, dataPeriodicity)
```

### Input(s)

{% hint style="info" %}
Microsoft Excel defaults to marshalling empty cells as zeros in the background. Therefore, please explicitly specify missing values within your worksheet using $$=\text{NA()}$$in your formula bar for the relevant cells.
{% endhint %}

| Argument            | Description                                                                                                                                                                                    |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                              |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p> |

### Output(s)

Row vector of MLE expected returns.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MKubO_BlYJq2Q8B28hc%2F-MKueOdhcPQ9ZyNbFbZg%2Fimage.png?alt=media\&token=0ccc7675-d4bc-439b-b0b0-f6a5e75ef9cb)

{% file src="/files/-MKueefokh9yxUBpCKOq" %}
Example Workbook: MLERETURNS
{% endfile %}


# Risk Models


# ANNUALIZERISK

Annualize discrete estimates of standard deviation to account for the compounding effects on assets.

## Description

If we assume that assets' time series instantaneous rates of returns are normally distributed, then its discrete returns are lognormally distributed. To account for this, we annualize measures of risk by converting the discrete means and standard deviations into its continuous counterparts prior to annualization.

{% hint style="info" %}
To read more about the mathematics behind this, please see <https://wpahelp.windhamlabs.com/expected-risk/annualizing-volatility-and-return>
{% endhint %}

## Syntax

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

```excel-formula
=ANNUALIZERISK(mu, sigma, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                    |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **mu**              | Required. Vector or time series matrix of discrete asset returns.                                                                                                                              |
| **sigma**           | Required. Vector of risk estimates to be annualized.                                                                                                                                           |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p> |

### Output(s)

Vector of annualized risk estimates.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9GsEK2JXm2MqRSh7k%2F-ML9Zx6YLLYyevd_8MfG%2Fimage.png?alt=media\&token=e1eb8a54-4c8f-476a-9ad3-e85532f9b063)

{% file src="/files/-ML9\_1hApx1QMCOd7sQQ" %}
Example Workbook: ANNUALIZERISK
{% endfile %}


# EWMA

Estimate expected risk, correlation matrix, or covariance matrix for assets using the exponential-weighted moving average risk model (EWMA).

## Description

Practitioners may want to consider the relevance of more recent events relative to observations further in the past. The exponentially-weighted moving average (EWMA) model calculates covariances by placing more emphasis on recent observations via a decay factor,$$\lambda$$.

{% hint style="info" %}
For a detailed description on the decay factor and its relationship to half-life, please see <https://wpahelp.windhamlabs.com/expected-risk/exponential-risk#half-life>
{% endhint %}

## Syntax

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

```excel-formula
=EWMA(type, assetReturns, lambda, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                    |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **type**            | <p>Required. Enumeration string to specify calculation type:<br>   "risk", "sigma", or "stdev"<br>   "correlation", "corr", or "rho", <br>   "covariance", "covar", or "cov"</p>               |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                              |
| **lambda**          | Required. Decay factor, scalar.                                                                                                                                                                |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p> |

### Output(s)

Depending on the specified output *type*, the function will return the respective vector of risk estimates (annualized standard deviations), correlation matrix, or a covariance matrix.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9A0ubdYUndPL82Yl6%2F-ML9BP0uZzvVdMJdbhNJ%2Fewma.gif?alt=media\&token=9eded642-d0ee-4c49-ab0a-ce0a7cf82f6b)

{% file src="/files/-ML9Brk9J\_GZRVZ1mTsw" %}
Example Workbook: EWMA
{% endfile %}


# HISTORICALRISK

Estimate expected risk, correlation matrix, or covariance matrix for assets using historical data.

## Description

The following risk model estimates covariances based on equally-weighted historical observations.

## Syntax

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

```excel-formula
=HISTORICALRISK(type, assetReturns, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                    |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **type**            | <p>Required. Enumeration string to specify calculation type:<br>   "risk", "sigma", or "stdev"<br>   "correlation", "corr", or "rho", <br>   "covariance", "covar", or "cov"</p>               |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                              |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p> |

### Output(s)

Depending on the specified output *type*, the function will return the respective vector of risk estimates (annualized standard deviations), correlation matrix, or a covariance matrix.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9C9GqZaK37QkljV1E%2F-ML9Ghswu8MmVLXqLHjY%2Fimage.png?alt=media\&token=206fa8d4-f69a-4b56-a3e9-69bc98a771c8)

{% file src="/files/-ML9GlYdPtrnjCBL4Fos" %}
Example Workbook: HISTORICALRISK
{% endfile %}


# MLERISK

Estimate expected risk, correlation matrix, or covariance matrix for assets with incomplete historical data (unequal lengths) with a maximum likelihood estimator.

## Description

In an ideal world, analysts would have access to complete and clean data. In practice, the amount of historical time series data across asset classes differs. A common workaround is to discard observations that are incomplete across the panel, or to restrict the analysis with assets that have common histories. This forgoes the potential information embedded within asset classes with shorter or incomplete histories.

We can employ a Maximum Likelihood Estimation (MLE) method to use all available data to solve for parameters (means or covariances) that is most likely to have produced the observations.

## Syntax

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

```excel-formula
=MLERISK(type, assetReturns, dataPeriodicity)
```

### Input(s)

{% hint style="info" %}
Microsoft Excel defaults to marshalling empty cells as zeros in the background. Therefore, please explicitly specify missing values within your worksheet using $$=\text{NA()}$$in your formula bar for the relevant cells.
{% endhint %}

| Argument            | Description                                                                                                                                                                                    |
| ------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **type**            | <p>Required. Enumeration string to specify calculation type:<br>   "risk", "sigma", or "stdev"<br>   "correlation", "corr", or "rho", <br>   "covariance", "covar", or "cov"</p>               |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                              |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p> |

### Output(s)

Depending on the specified output *type*, the function will return the respective vector of risk estimates (annualized standard deviations), correlation matrix, or a covariance matrix.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MKuw8PRXhZuMJak4Bdd%2F-MKv81yAYOvt3TcDgyVz%2Fmlerisk.gif?alt=media\&token=9b90dc48-d29f-44b0-b4db-6fdbe5cd4223)

{% file src="/files/-MKv8a1VI64y-nykFRCW" %}
Example Workbook: MLERISK
{% endfile %}


# PORTFOLIORISK

Calculate portfolio risk from a vector of weights, risk, and correlation matrix.

## Description

Portfolio risk is simply a vector product of asset weights and the covariance matrix. Compute portfolio risk conviniently without having to nest complex array formulas in Microsoft Excel.

## Syntax

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

```excel-formula
=PORTFOLIORISK(weights, sigma, rho)
```

### Input(s)

| Argument    | Description                                                                                 |
| ----------- | ------------------------------------------------------------------------------------------- |
| **weights** | Vector of weights                                                                           |
| **sigma**   | Vector of risk estimates (or covariance matrix)                                             |
| **rho**     | Correlation matrix. You do not need to specify **rho** if **sigma** is a covariance matrix. |

### Output(s)

Portfolio risk estimate.

## Example


# TURBULENTRISK

A robust statistical risk model using the Mahalanobis distance.

## Description

Using the Mahalanobis distance, this risk model characterizes the degree of unusualness in a cross section of asset returns and partitions the historical data into periods of financial turbulence and quiecense. These periods are usually marked by large asset movements (volatility) and/or unusual changes in correlations (e.g. when non-correlated assets become correlated) and considered statistical outliers.

The risk model is useful for stress testing and constructing resilient portfolios.

{% hint style="info" %}
This risk model is also available in the Windham Portfolio Advisor. For a deeper dive into the model, please see <https://wpahelp.windhamlabs.com/expected-risk/quiet-and-turbulent-risk>
{% endhint %}

## Syntax

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

```excel-formula
=TURBULENTRISK(type, assetReturns, threshold, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                                                                                                                                                                                                                                       |
| ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **type**            | <p>Required. Enumeration string to specify calculation type:<br>   "risk", "sigma", or "stdev"<br>   "correlation", "corr", or "rho", <br>   "covariance", "covar", or "cov"</p>                                                                                                                                                                                                                                  |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                                                                                                                                                                                                                                                 |
| **threshold**       | Required. Probability threshold of turbulent periods (0.00 - 1.00). This threshold is the converted into the equivalent chi-squared,$$\chi\_{N}^2$$, score. Under a multivariate normal assumption, the cutoff can be interpreted as an approximation of the percentage of the sub-sample of outliers (turbulent). This approximation may vary depending on the underlying characteristics of the empirical data. |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p>                                                                                                                                                                                                                    |

### Output(s)

Depending on the specified output *type*, the function will return the respective vector of risk estimates (annualized standard deviations), correlation matrix, or a covariance matrix.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9dRbWbwW2SVWdnYpe%2F-ML9nbPdbg6SDVajlh8J%2Fimage.png?alt=media\&token=9c7c896d-11ee-42ac-b238-ffce8f8fa2bb)

{% file src="/files/-ML9nw7N3O1nehODTWjB" %}
Example Workbook: TURBULENTRISK
{% endfile %}

The following video describes the conceptual application of this methodology.

{% embed url="<https://windhamcapital.wistia.com/medias/ti6fqxaqxf>" %}
Windham's Risk Model
{% endembed %}


# QUIETRISK

A robust statistical risk model using the Mahalanobis distance.

## Description

Using the Mahalanobis distance, this risk model characterizes the degree of unusualness in a cross section of asset returns and partitions the historical data into periods of financial turbulence and quiescence. Turbulent periods are usually marked by large asset movements (volatility) and/or unusual changes in correlations (e.g. when non-correlated assets become correlated) and considered statistical outliers. Conversely, quiescent periods are market by subdued asset movements (lower volatility) and/or lower correlation surprises and are considered statistical inliers.

The risk model is useful for stress testing and constructing robust portfolios.

{% hint style="info" %}
This risk model is also available in the Windham Portfolio Advisor. For a deeper dive into the model, please see <https://wpahelp.windhamlabs.com/expected-risk/quiet-and-turbulent-risk>
{% endhint %}

## Syntax

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

```excel-formula
=QUIETRISK(type, assetReturns, threshold, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                                                                                                                                                                                                                              |
| ------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **type**            | <p>Required. Enumeration string to specify calculation type:</p><p>   "risk", "sigma", or "stdev"</p><p>   "correlation", "corr", or "rho"</p><p>   "covariance", "covar", or "cov"</p>                                                                                                                                                                                                                  |
| **assetReturns**    | Required. Time series or matrix of asset returns.                                                                                                                                                                                                                                                                                                                                                        |
| **threshold**       | Required. Probability threshold of quiet periods (0.00 - 1.00). This threshold is the converted into the equivalent chi-squared,$$\chi\_{N}^2$$, score. Under a multivariate normal assumption, the cutoff can be interpreted as an approximation of the percentage of the sub-sample of inliers (quiet). This approximation may vary depending on the underlying characteristics of the empirical data. |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1.</p><p>e.g. Daily = 255, Monthly = 12, Yearly = 1, Quarterly = 4.</p>                                                                                                                                                                                                                   |

### Output(s)

Depending on the specified output *type*, the function will return the respective vector of risk estimates (annualized standard deviations), correlation matrix, or a covariance matrix.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9dRbWbwW2SVWdnYpe%2F-ML9nbPdbg6SDVajlh8J%2Fimage.png?alt=media\&token=9c7c896d-11ee-42ac-b238-ffce8f8fa2bb)

{% file src="/files/-ML9nw7N3O1nehODTWjB" %}
Example Workbook
{% endfile %}

The following video describes the conceptual application of the methodology.

{% embed url="<https://windhamcapital.wistia.com/medias/ti6fqxaqxf>" %}


# 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.

```excel-formula
=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** | <p>Optional. Matrix of constraints, operator enumeration, and values: <span class="math">\begin{bmatrix}A & op \&b\end{bmatrix}</span> </p><p></p><p>The operator enumeration is represented by   <span class="math">op \in \begin{cases} 0: & \leq  \1: & = \ 2: & \geq \end{cases}</span> </p><p></p><p>If the argument is not specified, it will default to a fully-funded constraint. <br>i.e. <span class="math">\[1, 1, \ldots , 1\_{N}, 1, 1]</span> </p> |
| **nonlincons**  | <p>Optional. Matrix to specify nonlinear constraint enumeration, operator enumeration, and values: <span class="math">\begin{bmatrix}nonlinType & op \&value\end{bmatrix}</span> <br></p><p>The nonlinType enumeration is <span class="math">nonlinType \in \begin{cases} 0: & \text{off}  \1: & \text{same risk} \ 2: & \text{same tracking-error} \end{cases}</span> </p>                                                                                      |

### Output(s)

The function returns a vector of optimal weights $$w$$ across $$N$$assets and appends the corresponding optimization's exit flag.&#x20;

&#x20;  $$\text{output}=\begin{bmatrix}w\_1 & w\_2 & \ldots & w\_N & \text{exitFlag}\end{bmatrix}$$&#x20;

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

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLsoAw1A3BeoKDkwJKU%2F-MLt-kXczwcWZmVXkobt%2Fimage.png?alt=media\&token=bb7a760f-ced2-4f54-9051-a58a806e4212)

{% file src="/files/-MLt-qotLTK9oF8WC\_X9" %}
Example Workbook: MVO
{% endfile %}


# MTO

Mean-tracking-error optimization, maximize your return in excess of a benchmark while minimizing tracking-error.

## Description

Maximize expected returns relative to a benchmark and minimize expected tracking-error. Solve for the mean-tracking-error optimal portfolio (MTO). This is a quadratic programming optimizer in active space. The function allow you to specify both linear and non-linear constraints and is able to account for friction penalties such as transaction costs across assets.

## Syntax

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

```excel-formula
=MTO(mu, sigma, rho, aversion, wBenchmark, 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 tracking aversion. If the argument is not specified, it defaults to 1.                                                                                                                                                                                                                                                                                                                                                                          |
| **wBenchmark**  | Optional. Vector of benchmark weights. This is used to evaluate the utility function in excess return space. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                     |
| **wInitial**    | Optional. Vector of initial weights (or your current weights). This is used to measure the friction penalties when solving for an optimal transition. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                            |
| **tc**          | Optional. Vector of transaction costs. If the argument is not specified, it defaults to a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                 |
| **lb**          | Optional. Vector of lower bound limits. If the argument is not specified, it defaults to a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                |
| **ub**          | Optional. Vector of upper bound limits. If the argument is not specified, it defaults to a vector of ones.                                                                                                                                                                                                                                                                                                                                                                 |
| **constraints** | <p>Optional. Matrix of constraints, operator enumeration, and values: <span class="math">\begin{bmatrix}A & op \&b\end{bmatrix}</span> </p><p><br>The operator enumeration is represented by <span class="math">op \in \begin{cases} 0: & \leq  \1: & = \ 2: & \geq \end{cases}</span></p><p><br>If the argument is not specified, it will default to a fully-funded constraint.<br>i.e. <span class="math">\begin{bmatrix}1,1,…,1\_N​,1,1\end{bmatrix}</span> </p><p></p> |
| **nonlincons**  | <p>Optional. Matrix to specify nonlinear constraint enumeration, operator enumeration, and values: <span class="math">\begin{bmatrix}nonlinType​ & op ​& value\end{bmatrix}</span> </p><p></p><p>The nonlinType enumeration is <span class="math">nonlinType \in \begin{cases} 0: & \text{off}  \1: & \text{same risk} \ 2: & \text{same tracking-error} \end{cases}</span> </p>                                                                                           |

### Output(s)

The function returns a vector of optimal weights $$w$$ across $$N$$assets and appends the corresponding optimization's exit flag.&#x20;

&#x20;  $$\text{output}=\begin{bmatrix}w\_1 & w\_2 & \ldots & w\_N & \text{exitFlag}\end{bmatrix}$$&#x20;

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

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLt-yTN95wIJHjsiJKQ%2F-MLt1qzFdKkapx3MnruJ%2Fimage.png?alt=media\&token=a25ded44-7b31-4005-b1f2-07f8778421f1)

{% file src="/files/-MLt22VKqehJRVW1xzrF" %}
Example Workbook: MTO
{% endfile %}


# MVT

Optimize portfolio allocations for absolute and relative performance with this multi-goal optimization function. Maximize return while minimizing risk and tracking-error (Mean-variance-tracking-error)

## Description

For investors concerned with both absolute and relative performance, the mean-variance tracking-error (MVT) objective function maximizes a portfolio’s expected return while minimizing both risk and tracking error. This function also allows for the robust specifications of linear and nonlinear constraints.&#x20;

## Syntax

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

```excel-formula
=MVT(mu, sigma, rho, λ1, λ2, wBenchmark, 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.                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| **λ1**          | Optional. Scalar value for risk aversion. If not specified, it defaults to 1.                                                                                                                                                                                                                                                                                                                                                                                              |
| **λ2**          | Optional. Scalar value for tracking aversion. If not specified, it defaults to 1.                                                                                                                                                                                                                                                                                                                                                                                          |
| **wBenchmark**  | Optional. Vector of benchmark weights. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                           |
| **wInitial**    | Optional. Vector of initial weights (or your current weights). If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                                                                   |
| **tc**          | Optional. Vector of transaction costs. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                           |
| **lb**          | Optional. Vector of lower bound limits. If not specified, it defaults to a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                                |
| **ub**          | Optional. Vector of upper bound limits. If the argument is not specified, it defaults to a vector of ones.                                                                                                                                                                                                                                                                                                                                                                 |
| **constraints** | <p>Optional. Matrix of constraints, operator enumeration, and values: <span class="math">\begin{bmatrix}A & op \&b\end{bmatrix}</span> </p><p></p><p>The operator enumeration is represented by <span class="math">op \in \begin{cases} 0: & \leq  \1: & = \ 2: & \geq \end{cases}</span> </p><p></p><p>If the argument is not specified, it will default to a fully-funded constraint.<br>i.e. <span class="math">\begin{bmatrix}1,1,…,1\_N​,1,1\end{bmatrix}</span> </p> |
| **nonlincons**  | <p>Optional. Matrix to specify nonlinear constraint enumeration, operator enumeration, and values: <span class="math">\begin{bmatrix}nonlinType​ & op ​& value\end{bmatrix}</span> <br></p><p>The nonlinType enumeration is <br><span class="math">nonlinType \in \begin{cases} 0: & \text{off}  \1: & \text{same risk} \ 2: & \text{same tracking-error} \end{cases}</span> </p>                                                                                          |

### Output(s)

The function returns a vector of optimal weights $$w$$ across $$N$$assets and appends the corresponding optimization's exit flag.&#x20;

&#x20;  $$\text{output}=\begin{bmatrix}w\_1 & w\_2 & \ldots & w\_N & \text{exitFlag}\end{bmatrix}$$&#x20;

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

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLt27r8erls27hgv_6g%2F-MLt3uvSCCrX_JEHQwJA%2Fimage.png?alt=media\&token=c0624002-8b26-4072-97cb-a2c3898a02f2)

{% file src="/files/-MLt5L-DChWwQ0a3TrWY" %}
Example Workbook: MVT
{% endfile %}

## Further Reading

{% embed url="<https://insights.windhamlabs.com/insights/multi-goal-optimization>" %}
Insights into Windham's Multi-goal Optimization approach
{% endembed %}


# MVFRONTIER

Solve for the Mean-Variance efficient frontier. Optimize for multiple portfolios to evaluate trade-offs.

## Description

Solve for multiple mean-variance optimal portfolios on the efficient frontier. Evaluate the absolute return and risk tradeoffs. The function allows you to specify both linear and non-linear constraints and is able to account for friction penalties (transaction costs).

{% hint style="warning" %}
The convexity of the efficient frontier may not necessarily hold when transaction costs are present.
{% endhint %}

## Syntax

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

```excel-formula
=MVFRONTIER(P, mu, sigma, rho, wInitial, tc, lb, ub, constraints)
```

### Input(s)

| Argument        | Description                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **P**           | Required. Number of portfolios to solve for, $$P \geq 5$$.                                                                                                                                                                                                                                                                                                                                                                                               |
| **mu**          | Required. Vector of expected returns.                                                                                                                                                                                                                                                                                                                                                                                                                    |
| **sigma**       | Required. Vector of expected risk.                                                                                                                                                                                                                                                                                                                                                                                                                       |
| **rho**         | Required. Correlation matrix.                                                                                                                                                                                                                                                                                                                                                                                                                            |
| **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** | <p>Optional. Matrix of constraints, operator enumeration, and values: <span class="math">\begin{bmatrix}A & op \&b\end{bmatrix}</span> </p><p><br>The operator enumeration is represented by <span class="math">op \in \begin{cases} 0: & \leq  \1: & = \ 2: & \geq \end{cases}</span> </p><p><br>If the argument is not specified, it will default to a fully-funded constraint. <br>i.e. <span class="math">\[1, 1, \ldots , 1\_{N}, 1, 1]</span> </p> |

### Output(s)

The function returns optimal weights $$w$$ across $$N$$assets for $$P$$portfolios. The portfolios' expected return, risk, and corresponding optimization's exit flag is appended at the end of the matrix.

&#x20;  $$\text{output}=  \begin{bmatrix}  w\_{1,1} & w\_{1,2} & \ldots & w\_{1,N}  & \mu\_1 & \sigma\_1 &  \text{exitFlag}*1    \  w*{2,1} & w\_{2,2} & \ldots & w\_{2,N}  & \mu\_2 & \sigma\_2 &  \text{exitFlag}*2 \ \  \vdots & \vdots & \ddots & \vdots  & \vdots & \vdots &  \vdots \   \w*{P,1} & w\_{P,2} & \ldots & w\_{P,N}  & \mu\_P & \sigma\_P &  \text{exitFlag}\_P \       \end{bmatrix}$$&#x20;

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

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MM2UQQL-If-fHvfKXhc%2F-MM2_D199BiOE2hfuC4U%2Fimage.png?alt=media\&token=0ffd05d4-46c4-41a5-a93e-2fb0cf1a0998)

{% file src="/files/-MQVCL9vVCHmPZsIEcdu" %}
Example Workbook: MVFRONTIER
{% endfile %}


# MTFRONTIER

Solve for the Mean-Tracking-Error efficient frontier. Optimize for multiple portfolios to evaluate return and risk trade-offs in relative return space (active management).

## Description

Solve for multiple mean-tracking-error optimal portfolios on the efficient frontier. Evaluate the return and relative risk tradeoff. The function allows you to specify both linear and non-linear constraints and is able to account for friction penalties (transaction costs).

{% hint style="warning" %}
The convexity of the efficient frontier may not necessarily hold when transaction costs are present.
{% endhint %}

## Syntax

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

```excel-formula
=MTFRONTIER(P, mu, sigma, rho, wBenchmark, wInitial, tc, lb, ub, constraints)
```

### Input(s)

| Argument        | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **P**           | Required. Number of portfolios to solve for $$P \geq 5$$.                                                                                                                                                                                                                                                                                                                                                                                                                  |
| **mu**          | Required. Vector of expected returns.                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| **sigma**       | Required. Vector of expected risk.                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| **rho**         | Required. Correlation matrix.                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| **wBenchmark**  | Optional. Vector of benchmark weights. This is used to evaluate the utility function in excess return space. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                     |
| **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 of zeros.                                                                                                                                                                                                                                                                                                                                                                 |
| **lb**          | Optional. Vector of lower bound limits. If the argument is not specified, it defaults to a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                |
| **ub**          | Optional. Vector of upper bound limits. If the argument is not specified, it defaults to a vector of ones.                                                                                                                                                                                                                                                                                                                                                                 |
| **constraints** | <p>Optional. Matrix of constraints, operator enumeration, and values: <span class="math">\begin{bmatrix}A & op \&b\end{bmatrix}</span> </p><p></p><p>The operator enumeration is represented by <span class="math">op \in \begin{cases} 0: & \leq  \1: & = \ 2: & \geq \end{cases}</span> </p><p></p><p>If the argument is not specified, it will default to a fully-funded constraint.<br>i.e. <span class="math">\begin{bmatrix}1,1,…,1\_N​,1,1\end{bmatrix}</span> </p> |

### Output(s)

The function returns optimal weights $$w$$ across $$N$$assets for $$P$$portfolios. The portfolios' expected return, tracking-error, and corresponding optimization's exit flag is appended at the end of the matrix.

$$\text{output} = \begin{bmatrix}   w\_{1,1} & w\_{1,2} & \ldots & w\_{1,K}  & \mu\_1 & \sigma\_{te\_1} &  \text{exitFlag}*1 \   w*{2,1} & w\_{2,2} & \ldots & w\_{2,K}  & \mu\_2 & \sigma\_{te\_2} &  \text{exitFlag}*2 \ \   \vdots & \vdots & \ddots & \vdots  & \vdots & \vdots &  \vdots \ \ w*{N,1} & w\_{N,2} & \ldots & w\_{N,K}  & \mu\_N & \sigma\_{te\_N} &  \text{exitFlag}\_N  \end{bmatrix}$$&#x20;

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

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MM2_OiVdvxhDR7O6OPf%2F-MM2aa2bKxUH-yC0sRYL%2Fimage.png?alt=media\&token=042acd7a-200e-4220-b42d-8eb41a82f15d)

{% file src="/files/-MQVCYIIyc9b86Jw85z9" %}
Example Workbook: MTFRONTIER
{% endfile %}


# ISORETURN

Solve for an iso-return efficient frontier. Construct an efficient frontier to evaluate the risk and tracking-error tradeoffs.

## Description

Solve for multiple optimal portfolios on the efficient surface for a specified target return. Evaluate the tradeoff between absolute risk and relative risk (tracking-error).

{% hint style="warning" %}
The convexity of the efficient frontier may not necessarily hold when transaction costs are present.
{% endhint %}

## Syntax

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

```excel-formula
=ISORETURN(P, target, mu, sigma, rho, wBenchmark, wInitial, tc, lb, ub, constraints)
```

### Input(s)

| Argument        | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **P**           | Required. Number of portfolios to solve for, $$P \geq 5$$.                                                                                                                                                                                                                                                                                                                                                                                                                 |
| **target**      | Required. Target expected returns, the target level must be between the return of minimum risk portfolio and the maximum return portfolio.                                                                                                                                                                                                                                                                                                                                 |
| **mu**          | Required. Vector of expected returns.                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| **sigma**       | Required. Vector of expected risk.                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| **rho**         | Required. Correlation matrix.                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| **wBenchmark**  | Optional. Vector of benchmark weights. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                           |
| **wInitial**    | Optional. Vector of initial weights (or your current weights). If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                                                                   |
| **tc**          | Optional. Vector of transaction costs. If not specified, the function assumes a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                           |
| **lb**          | Optional. Vector of lower bound limits. If not specified, it defaults to a vector of zeros.                                                                                                                                                                                                                                                                                                                                                                                |
| **ub**          | Optional. Vector of upper bound limits. If the argument is not specified, it defaults to a vector of ones.                                                                                                                                                                                                                                                                                                                                                                 |
| **constraints** | <p>Optional. Matrix of constraints, operator enumeration, and values: <span class="math">\begin{bmatrix}A & op \&b\end{bmatrix}</span> </p><p></p><p>The operator enumeration is represented by <span class="math">op \in \begin{cases} 0: & \leq  \1: & = \ 2: & \geq \end{cases}</span> </p><p></p><p>If the argument is not specified, it will default to a fully-funded constraint.<br>i.e. <span class="math">\begin{bmatrix}1,1,…,1\_N​,1,1\end{bmatrix}</span> </p> |

### Output(s)

The function returns optimal weights $$w$$ across $$N$$assets for $$P$$portfolios. The portfolios' expected risk, tracking-error, and corresponding optimization's exit flag is appended at the end of the matrix.

$$\text{output} = \begin{bmatrix}   w\_{1,1} & w\_{1,2} & \ldots & w\_{1,K}  & \sigma\_1 & \sigma\_{te\_1} &  \text{exitFlag}*1 \   w*{2,1} & w\_{2,2} & \ldots & w\_{2,K}  & \sigma\_2 & \sigma\_{te\_2} &  \text{exitFlag}*2 \ \   \vdots & \vdots & \ddots & \vdots  & \vdots & \vdots &  \vdots \ \ w*{N,1} & w\_{N,2} & \ldots & w\_{N,K}  & \sigma\_N & \sigma\_{te\_N} &  \text{exitFlag}\_N  \end{bmatrix}$$&#x20;

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

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MM2alUWNI_bv6nYM6y3%2F-MM2frO21cqGqwd5k2k1%2Fimage.png?alt=media\&token=d88b901b-9359-4662-b12a-368f240a7db3)

{% file src="/files/-MM2fxW1xZ-EIJ\_6WAEJ" %}
Example Workbook: ISORETURN
{% endfile %}


# Simulation


# MCNORM

Simulate a matrix of multivariate normal returns using Monte-Carlo with the option to preserve an expected correlation structure.

## Description

Simulate a matrix of returns using multi-variate Monte-Carlo.&#x20;

## Syntax

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

```excel-formula
=MCNORM(mu, sigma, rho, isRhoExact, T, dataPeriodicity)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                                                                                                                     |
| ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **mu**              | Required. Vector of expected returns.                                                                                                                                                                                                                                                           |
| **sigma**           | Required. Vector of expected risk.                                                                                                                                                                                                                                                              |
| **rho**             | Optional. Correlation matrix. If not specified, the function assumes zero correlation coefficients (identity matrix).                                                                                                                                                                           |
| **isRhoExact**      | Optional. Logical (TRUE or FALSE). If TRUE, the sample mean and covariances will match mu and sigma. The resulting compounded (terminal) prices will be in close agreement, set to FALSE for typical expected value behavior. If the argument is not specified, the function defaults to FALSE. |
| **T**               | Optional. Time horizon (number of observations, in years) to generate. If not specified, defaults to 1.                                                                                                                                                                                         |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p>                                                                                                  |

### Output(s)

Matrix of multi-variate returns simulated using Monte-Carlo.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MMutBNt7rJtGr6-VRYm%2F-MMuuPPAr_hk3scD2zGh%2Fimage.png?alt=media\&token=d09b761a-059b-4a0a-918f-22824b85f06f)

{% file src="/files/-MMut5j8QBbI4Mk-30vC" %}
Example Workbook: MCNORM
{% endfile %}


# BOOTSTRAP

Simulate values by bootstrap sampling from a dateset. The bootstrap method allows for a uniform random sampling, or a biased (probability-weighted) sampling method, useful for scenario analysis.

## Description

Bootstrap simulation method samples from an empirical dataset typically drawing independent observations with equal probability to reconstruct a new hyptothetical distribution. with BOOTSTRAP, can sample with equal probability or with specified biases in the empirical dataset. This is useful for stress testing and scenario analysis. This function draws from sample with replacement.

## Syntax

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

```excel-formula
=BOOTSTRAP(sample, nPaths, numPeriods, weights, prVector, rndSeed)
```

### Input(s)

| Argument       | Description                                                                                                                                                                                                                              |
| -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **sample**     | Vector or matrix of the sample population data.                                                                                                                                                                                          |
| **nPaths**     | Number of paths to simulate.                                                                                                                                                                                                             |
| **numPeriods** | Number of periods to simulate.                                                                                                                                                                                                           |
| **weights**    | Optional argument. Vector of asset/variable weights to apply to sample. If weights are not specified and sample is multivariate, then **nPaths** will be forced to 1.                                                                    |
| **prVector**   | Optional argument. Probability vector to specifiy the likelihood of sampling from a given period in the sample population. If not specified, this argument defaults to an equal probabilities across all periods (uniform distribution). |
| **rndSeed**    | Optional argument, numeric value to specify random generator seed. If not specified, the random seed is a function of CPU clock.                                                                                                         |

### Output(s)

Bootstrap simulated sample with repetitions. The simulated sample will be `numPeriods` by `nPaths` in size.&#x20;

{% hint style="info" %}
Because Microsoft Excel is in some ways a 2-dimensional application, it is challenging to work with persisting N-dimensional arrays in a presentable form. To accommodate this, we can bootstrap multivariate samples and aggregate the results to a portfolio solution via **weights** and return a 2-dimensional matrix of bootstrapped portfolio values.
{% endhint %}

## Example

{% hint style="warning" %}
Coming soon.
{% endhint %}


# Exposure to Loss


# LOSSPR

Measure the probability of loss at throughout an investment horizon (first-passage time) or at the end of horizon.

## Description

Probability of loss is a measure of the likelihood that a portfolio will incur a particular percentage of loss at the end of an investment horizon. The function is also able to calculate the within-horizon probability of loss which is estimated as a first-passage time probability.

{% hint style="info" %}
See <https://insights.windhamlabs.com/insights/rethinking-exposure-to-loss> for an introduction to stress testing your portfolios and assessing a more realistic measurement of exposure to loss.
{% endhint %}

## Syntax

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

```excel-formula
=LOSSPR(mu, sigma, horizon, lossThreshold, estWithinHorizon)
```

### Input(s)

| Argument             | Description                                                                                                                                                                          |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **mu**               | Required. Vector / scalar of portfolio return estimate(s).                                                                                                                           |
| **sigma**            | Required. Vector / scalar of portoflio risk estimate(s).                                                                                                                             |
| **horizon**          | Required. Investment time horizon in the same time units of mu and sigma.                                                                                                            |
| **threshold**        | Required. Loss threshold to assess likelihoods.                                                                                                                                      |
| **estWithinHorizon** | Optional. Logical flag (TRUE or FALSE) to indicate whether you would like to calculate the within-horizon risk measurement. If this argument is not specified, it defaults to false. |

### Output(s)

Probability of loss estimate(s).

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLDa6RQVeSCEx8InW_G%2F-MLDbLMsWQNpZ1zY5cCR%2Fimage.png?alt=media\&token=f3ad9798-0ab4-4d06-bb33-847eb7b21e99)

{% file src="/files/-MLDbQpbZVBrMJlEQfnG" %}
Example Workbook: LOSSPR
{% endfile %}


# OMEGARATIO

Measure the probability weighted ratio of gains to losses for a given target return.

## Description

The Omega ratio can be considered a complement or an alternative to the widely used Sharpe ratio (or return-to-risk statistics). The Omega ratio uses all statistical moments within the data and does discard information from higher moments, it measures the probability weighted ratio of gains to losses as partitioned by a specified level of return target.

## Syntax

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

```excel-formula
=OMEGARATIO(returns, threshold)
```

### Input(s)

| Argument      | Description                                                                   |
| ------------- | ----------------------------------------------------------------------------- |
| **returns**   | Required. Time series or matrix of instrument returns.                        |
| **threshold** | Optional. Return threshold. If the argument is not defined, it will use zero. |

### Output(s)

Vector of Omega ratio(s).

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLE1xWgwOoPHs_u-okt%2F-MLEMdI8FuGd3wDtvDzD%2Fratios.gif?alt=media\&token=bb1ea8e8-046e-4281-b922-c6b2e92d8c0e)

{% file src="/files/-MLEMhFMH7RCi9SNZIt\_" %}
Example Workbook: RATIOS
{% endfile %}


# SORTINORATIO

Measure risk-adjusted returns relative to downside deviations.

## Description

The Sortino ratio measures the risk-adjusted return of an instrument (or portfolio) normalized by its downside deviation. This ratio is often considered a variant of the Sharpe ratio as it only considers downside risk. This is useful for investors and analysts to assess an investment's performance under undesirable volatility environments.

## Syntax

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

```excel-formula
=SORTINORATIO(returns, threshold)
```

### Input(s)

| Argument      | Description                                                                   |
| ------------- | ----------------------------------------------------------------------------- |
| **returns**   | Required. Time series or matrix of instrument returns.                        |
| **threshold** | Optional. Return threshold. If the argument is not defined, it will use zero. |

### Output(s)

Vector of Sortino ratio estimate(s).

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLE1xWgwOoPHs_u-okt%2F-MLEMdI8FuGd3wDtvDzD%2Fratios.gif?alt=media\&token=bb1ea8e8-046e-4281-b922-c6b2e92d8c0e)

{% file src="/files/-MLEMhFMH7RCi9SNZIt\_" %}
Example Workbook: RATIOS
{% endfile %}


# TAILRATIO

Determine the relative magnitude of losses to gains within an empirical distribution.

## Description

Calculate the empirical distribution's tail ratio. The tail ratio is defined as the ratio between the 95th and 5th percentile of returns. This statistic is useful to assess the relative magnitude of losses to gains. For example, a tail ratio of 0.25 would mean that losses are four times as bad as gains.

## Syntax

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

```excel-formula
=TAILRATIO(x)
```

### Input(s)

| Argument | Description                                 |
| -------- | ------------------------------------------- |
| **x**    | Required. Time series or matrix of returns. |

### Output(s)

Vector of tail ratio estimate(s).

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLE1xWgwOoPHs_u-okt%2F-MLEMdI8FuGd3wDtvDzD%2Fratios.gif?alt=media\&token=bb1ea8e8-046e-4281-b922-c6b2e92d8c0e)

{% file src="/files/-MLEMhFMH7RCi9SNZIt\_" %}
Example Workbook: RATIOS
{% endfile %}


# VALUEATRISK

Estimate exposure to loss of your portfolio with the Value at Risk framework.

## Description

Value at risk is a currency-denominated measurement of the loss a portfolio (or financial instrument) would experience over a specified investment horizon. This is a common method for assessing risk. The Excel Lab function allows you to estimate value at risk both at the end of an investment horizon as well as throughout the investment horizon (risk measured continuously, within-horizon).

{% hint style="info" %}
See <https://insights.windhamlabs.com/insights/rethinking-exposure-to-loss> for an introduction to stress testing your portfolios and assessing a more realistic measurement of exposure to loss.
{% endhint %}

## Syntax

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

```excel-formula
=VALUEATRISK(value, mu, sigma, horizon, threshold, estWithinHorizon)
```

### Input(s)

| Argument             | Description                                                                                                                                                                          |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **value**            | Required. Vector / scalar value of portfolio wealth.                                                                                                                                 |
| **mu**               | Required. Vector / scalar value of portfolio return estimate(s).                                                                                                                     |
| **sigma**            | Required. Vector / scalar value of portfolio risk estimate(s).                                                                                                                       |
| **horizon**          | Required. Investment time horizon in the same time units of return and risk.                                                                                                         |
| **threshold**        | Required. Threshold for tail risk, this determines the area of the probability distribution to evaluate. You can interprete this as one minus your confidence probability.           |
| **estWithinHorizon** | Optional. Logical flag (TRUE or FALSE) to indicate whether you would like to calculate the within-horizon risk measurement. If this argument is not specified, it defaults to false. |

### Output(s)

Value at Risk estimate(s).

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9xJBq4fvGOwU8AX_1%2F-MLABSH5_UwvqNK0hMgE%2Fimage.png?alt=media\&token=5cc6ec6f-6160-4e25-9117-3f804c1d573d)

{% file src="/files/-MLAF87MfxBuzSO-Yjwz" %}
Example Workbook: VALUEATRISK
{% endfile %}


# MAXDD

Calculate the maximum drawdown for time series returns.

## Description

Calculate the maximum drawdown for one or more time series of asset (or portfolio) returns. Maximum drawdown is the measure of peak to through and is typically used as an indicator of downside risk for historical data.

## Syntax

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

```excel-formula
=MAXDD(returns, dataPeriodicity, isAppendIndex)
```

### Input(s)

| Argument            | Description                                                                                                                                                                                                             |
| ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **returns**         | Required. Vector or matrix of time series returns.                                                                                                                                                                      |
| **dataPeriodicity** | <p>Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. <br><em>e.g. Daily = 255, Monthly = 12,  Yearly = 1, Quarterly = 4.</em></p>                          |
| **isAppendIndex**   | Optional. Logical (TRUE or FALSE), when set to TRUE, the function will append the start and end index positions of the respective max drawdown periods. If not specified, the function defaults this argument to FALSE. |

### Output(s)

The function returns a vector of maximum drawdown estimates, $$mdd$$&#x20;

&#x20;$$\text{output}=\begin{bmatrix}mdd\_1 & mdd\_2 & \ldots & mdd\_N\end{bmatrix}$$&#x20;

When `isAppendIndex` is set to `TRUE`, the function appends the start index, $$p$$ , and end index, $$q$$ , of the corresponding maximum drawdown periods.

$$\text{output}=\begin{bmatrix}mdd\_1 & mdd\_2 & \ldots & mdd\_N \ p\_1 & p\_2 & \ldots & p\_N \ q\_1 & q\_2 & \ldots & q\_N  \end{bmatrix}$$&#x20;

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MMudx1pLzAPHwndGjQH%2F-MMunU659UZpi7CYcujK%2Fimage.png?alt=media\&token=d5809c97-5ee4-44a0-b66b-c9c533f69359)

{% file src="/files/-MMunjwTgLENy8PgClAW" %}
Example Workbook: MAXDD
{% endfile %}


# Regression Analysis


# FACTORANALYSIS

Evaluate your assets, managers, and portfolios against factors (economic variables, signals, risk models). Decompose risk and return of your instruments using multi-factor or stepwise regressions.

## Description

Multi-factor and stepwise regression model to identify the sensitivity of an asset, manager, or portfolio's return against a set of factors.

## Syntax

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

```excel-formula
=FACTORANALYSIS(whichStat, y, x, isStepwise)
```

### Input(s)

| Argument       | Description                                                                                                                                                                                                                                                                                             |
| -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **whichStat**  | <p>Required. String to specify statistic to return, use one of the following options:<br>   "beta"</p><p>   "intercept"</p><p>   "rsquared"</p><p>   "tstat"</p><p>   "tintercept"</p><p>   "residualvariance"</p><p>   "totalvariance"</p><p>   "riskdecomposition"</p><p>   "returndecomposition"</p> |
| **y**          | Required. Time series or matrix of dependent variables. This is typically the time series of your portfolios, managers, or asset class returns.                                                                                                                                                         |
| **x**          | Required. Time series or matrix of independent variables. This is typically a set of economic variables or factors.                                                                                                                                                                                     |
| **isStepwise** | Optional. Logical (TRUE or FALSE). If TRUE then a stepwise regression algorithm is used, if the argument is not specified it defaults to FALSE.                                                                                                                                                         |

### Output(s)

The function's output will vary depending on the specification of the `whichStat` argument. The following table will describe the corresponding output result. For M-dependent variables (y) and N-independent variables (x)

| whichStat               | Output                                                           |
| ----------------------- | ---------------------------------------------------------------- |
| **beta**                | NxM regression coefficients.                                     |
| **intercept**           | 1xN intercept values.                                            |
| **rsquared**            | 1xN coefficient of determination, or$$R^2$$ values.              |
| **tstat**               | MxN t-statistics.                                                |
| **tintercept**          | 1xN t-statistic of the intercepts.                               |
| **residualvariance**    | 1xN residual variance.                                           |
| **totalvariance**       | Total variance of the regression.                                |
| **riskdecomposition**   | MxN risk decomposition of y as a function of x and residuals.    |
| **returndecomposition** | MxN return decomposition of y as a function of x and intercepts. |

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MLEfc3X88qo0AJvS7LI%2F-MLEfv67tUTz3h9E5Fx2%2Fimage.png?alt=media\&token=c10169b0-b41f-46bb-a5ab-56768374c374)

{% file src="/files/-MLEg525gUpFwqubyLYm" %}
Example Workbook: FACTORANALYSIS
{% endfile %}


# PSR

Partial-sample regression function to estimate the similarity, informativeness, and relevance of dependent variables.

## Description

Estimate results from the partial-sample regression model as described by Czasonis, Kritzman, and Turkington in their 2020 research paper (Journal of Portfolio Management, see reference link below).

{% embed url="<https://doi.org/10.3905/jpm.2020.1.167>" %}
Journal of Portfolio Management research paper: Addition by Subtraction (Partial Sample Regression)
{% endembed %}

One of our principals, Mark Kritzman, introduces this powerful model in a lecture at State Street's research retreat in 2020. View a recording on the lecture below.

{% embed url="<https://www.statestreet.com/events/statestreetlive/researchretreat2020/forecasting-technique-with-applications-kriztman.html>" %}
Partial Sample Regression Model in Practice (Recorded Lecture)
{% endembed %}

## Syntax

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

<pre class="language-excel-formula"><code class="lang-excel-formula">=PSR(whichStat, y, x, theta)
=PSR(whichStat, y, x, theta, <a data-footnote-ref href="#user-content-fn-1">"Name1", value1, ..., "NameN", valueN</a>)
</code></pre>

### Input(s)

| Argument      | Description                                                                                                                                                                                                                                                                                                                                                                                            |
| ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **whichStat** | <p>Required. String to specify statistic to return, use one of the following options:</p><p>   "similarity"</p><p>   "informativeness"</p><p>   "relevance"</p><p>   "scaledrelevance"</p><p>   "rank"</p><p>   "filter" = dummy vector to indicate relevant cross-sectional observations</p><p>   "weighted", "relevanceweighted"</p><p>   "yhat" = forecast value(s) for the dependent variable.</p> |
| **y**         | Required. Time series or matrix of dependent variables. This is typically the time series of your portfolios, managers, or asset class returns.                                                                                                                                                                                                                                                        |
| **x**         | Required. Time series or matrix of independent variables. This is typically a set of economic variables or factors.                                                                                                                                                                                                                                                                                    |
| **theta**     | Vector of predictor values, $$\hat{x}$$ , to use with the model parameters (coefficients) to forecast the response variable $$\hat{y}$$ . If this argument is empty, the function will assume the most recent cross-sectional values of the independent variables.                                                                                                                                     |
| **threshold** | Optional. Relevance threshold, numerical value to specify the minimum percentage of or relevant periods. If the argument is not specified, it defaults to 0.50 (at least 50% relevant periods will be included in the forecast of the partial-sample regression).                                                                                                                                      |

### Name-Value Optional Arguments

Specify optional pairs of arguments where Name is the option argument name and Value is the corresponding input object. Name-value arguments must appear after other input arguments above, but the order of these pairs does not matter.

Example:

```excel-formula
=PSR(whichStat, y, x, theta, "Name1", value1, "Name2", value2, ..., "NameN", valueN) 
```

| Name                   | Value                                                                                                                                                                                                                                                                              |
| ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **threshold**          | Threshold value to determine relevance cutoff. If the argument is not specified, it defaults to 0.50 (at least 50% relevant periods will be included in the forecast of the partial-sample regression). See also isPercentile option.                                              |
| **isPercentile**       | Logical, to indicate whether the threshold value is in percentile units or a level value, default = true.                                                                                                                                                                          |
| **thresholdDirection** | <p>Value to indicate the criteria to evaluate relevance against the threshold value set<br></p><p>   <span class="math">value \in\begin{cases} -1: & <  \\+1: & \geq  \end{cases}</span> </p><p></p><p>The default threshold direction is <span class="math">\geq</span> .<br></p> |
| **solveMaxFit**        | Logical (TRUE or FALSE) flag. If true, the regression model will solve for the maximum fit.                                                                                                                                                                                        |
| **selectVariables**    | Logical (TRUE or FALSE) flag. If true, the regression model will solve for maximum fit with the optimal selection of variables. If false, then the model will use all variables when solving for maximum fit.                                                                      |
| **covariance**         | Covariance matrix of the independent variables.                                                                                                                                                                                                                                    |

### Output(s)

The function's output will vary depending on the specification of the `whichStat` argument. The following table will describe the corresponding output result. For M-dependent variables (y) and N-independent variables (x) across T-observations:

| whichStat            | Output                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **yHat, prediction** | Forecast of dependent variable(s) from the partial sample regression model.                                                                                                                                                                                                                                                                                                                                                                 |
| **relevance**        | Tx1 vector of relevance scores. Relevance is the sum of statistical similarity and informativeness. I.e. Relevance is a measure of the importance of an observation to prediction. Its components are the informativeness of past circumstances, the informativeness of current circumstances, and the similarity of past circumstances to current circumstances.                                                                           |
| **similarity**       | Tx1 vector of statistical similarity, measured as the negative of the Mahalanobis distance of the past observations for the independent variables to the current values for the independent variables. Or put simply, past observations that are like the current observations are more relevant.                                                                                                                                           |
| **informativeness**  | Tx1 vector of informativeness as measured by the Mahalanobis distance of the historical observations of the independent variables from its average values.                                                                                                                                                                                                                                                                                  |
| **infoTheta**        | Tx1 vector of informativeness as measured by the Mahalanobis distance of the historical observations of the independent variables from the circumstances specified (theta).                                                                                                                                                                                                                                                                 |
| **weights**          | Tx1 Vector of partial sample regression weights.                                                                                                                                                                                                                                                                                                                                                                                            |
| **fit**              | 1xM Fit values. Fit is the average alignment between relevance and outcomes across all observation pairs for a single prediction. A large value indicates that the observations that are similarly relevant have similar outcomes, in which case on should have more confidence in the prediction. A small value indicates that relevance does not line up with the outcomes, in which case one should view the prediction more cautiously. |
| **filter, included** | Tx1 Dummy vector to indicate sub-sample periods that meet the threshold criteria.                                                                                                                                                                                                                                                                                                                                                           |

## Example

![Identify similarity, informativeness, and relevance - deepen your regression models](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MQnsFcD4tEWFmlkjkIY%2F-MQnsIzpVS8whGPfNPMk%2Fimage.png?alt=media\&token=8cb41dd6-268d-4c90-bffc-580041408dec)

{% file src="/files/-MQnseRt-2ltZy24msIf" %}
Example Workbook: PSR
{% endfile %}

[^1]: **Name-Value** pair input argument, variable length, see [Name-Value Optional Argument table](#name-value-optional-arguments) below.


# Scenario Analysis


# MAHALANOBIS

Compute the Mahalanobis Distance on your empirical data set.

## Description

The Mahalanobis function measures the distances of the cross-sectional point estimates from its empirical distribution. This is a multi-dimensional generalization tool in statistics. It is unitless, scale-invariant, and accounts for the correlation relationships within the data set.

For a given dataset,$$X$$, the Mahalanobis distance,$$D$$, is given by&#x20;

$$
D\_t=(X\_t-\mu) \Sigma^{-1}(X\_t-\mu)'
$$

$$
D\_t=\text{Mahalanobis distance for cross-section } t\newline
X\_t=\text{vector of asset returns for period } t\newline
\mu=\text{sample average of historical asset returns}\newline
\Sigma=\text{sample covariance matrix of asset returns}
$$

## Syntax

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

```excel-formula
=MAHALANOBIS(X, mu, covariance)
```

### Input(s)

<table><thead><tr><th width="150">Argument</th><th width="485.65217391304344">Description</th></tr></thead><tbody><tr><td><strong>X</strong></td><td>Matrix of time series returns (or values).</td></tr><tr><td><strong>mu</strong></td><td>Optional. Vector of means to measure the distance from. If the argument is not specified, it defaults to the sample average of the <strong>X</strong>.</td></tr><tr><td><strong>covariance</strong></td><td>Optional. Covariance matrix, specify a covariance matrix to normalize the distances. If the argument is not specified, it defaults to the sample covariance matrix of <strong>X</strong>.</td></tr></tbody></table>

### Output(s)

Vector of Mahalanobis distance values for the corresponding data, $$X$$. Vector will have the same length as $$X$$.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-ML9dRbWbwW2SVWdnYpe%2F-ML9nbPdbg6SDVajlh8J%2Fimage.png?alt=media\&token=9c7c896d-11ee-42ac-b238-ffce8f8fa2bb)

{% file src="/files/-ML9nw7N3O1nehODTWjB" %}
Example Workbook
{% endfile %}


# SCENARIOPR

Calculate relative scenario probabilities across a set of economic outcomes.

## Description

Calculate the relative probabilities of prospective economic scenarios. Employ this powerful statistical framework in your scenario analysis excercise. The function follows the research published in the Journal of Portfolio Management, March 2020.

{% embed url="<https://doi.org/10.3905/jpm.2020.1.125>" %}
Journal of Portfolio Management research paper: Enhanced Scenario Analysis
{% endembed %}

## Syntax

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

```excel-formula
=SCENARIOPR(estimates, anchor, covariance, j, isReturnDistance)
```

### Input(s)

<table><thead><tr><th width="247.80525499768095">Argument</th><th>Description</th></tr></thead><tbody><tr><td><strong>estimates</strong></td><td>Required. Matrix of scenario estimates where each column corresponds to a scenario and each row corresponds to an economic variable.</td></tr><tr><td><strong>anchor</strong></td><td>Required. Column vector of anchor estimates across economic variables.</td></tr><tr><td><strong>covariance</strong></td><td>Required. Covariance matrix of economic variables. We recommend that the covariance specification be aligned with deviations from the anchor selection.</td></tr><tr><td><strong>j</strong></td><td>Optional. Scalar value to model fatter tails. <span class="math">j \leq 1</span>. If not specified, the function assumes that j is one and that multivariate normality across economic variables hold. </td></tr><tr><td><strong>isReturnDistance</strong></td><td>Optional. Logical (TRUE or FALSE). If TRUE, then the function will return Mahalanobis distance values across all scenarios, otherwise the function returns a vector of relative probabilities that sum to one. If not specified, the function defaults to FALSE.</td></tr></tbody></table>

### Output(s)

The function returns a vector of scenario probabilities. When `isReturnDistance` is set to TRUE, the function returns the Mahalanobis distance of the scenario estimates relative to the specified anchor.

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MMuWc5gZbfkzC9TzOxf%2F-MMuaEBhDAFkVUj6P6A-%2Fimage.png?alt=media\&token=f5cbe579-df2b-4c2a-a977-6520f3789cca)

{% file src="/files/-MMuaLq4Bw5sa8K4zR-t" %}
Example Workbook: SCENARIOPR
{% endfile %}


# IMPLIEDSCENARIO

Calculate the implied scenario estimates that reconciles with your target scenario probabilities.

## Description

Solve for a modified set of implied scenario estimates given target probabilities. Reconcile your views on target scenario probabilities with implied scenario estimates across economic variables. The function follows the research published in the Journal of Portfolio Management, March 2020.

{% embed url="<https://doi.org/10.3905/jpm.2020.1.125>" %}
Journal of Portfolio Management research paper: Enhanced Scenario Analysis
{% endembed %}

## Syntax

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

```excel-formula
=IMPLIEDSCENARIO(prTarget, initialScenariosEstimates, anchor, covariance)
```

### Input(s)

| Argument                     | Description                                                                                                                                  |
| ---------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- |
| **prTarget**                 | Required. Row vector of target probabilities.                                                                                                |
| **initialScenarioEstimates** | Required. Matrix of initial scenario estimates where each column corresponds to a scenario and each row corresponds to an economic variable. |
| **anchor**                   | Required. Column vector of anchor estimates across economic variables.                                                                       |
| **covariance**               | Required. Covariance matrix of economic variables.                                                                                           |

### Output(s)

The function returns a matrix of implied estimates, $$s\_{m,n}$$, across $$M$$ economic variables and $$N$$ scenarios. The reconciled probabilities, $$p$$, of the implied scenario estimates are included in the final row of output.

$$
\text{output} = \quad \begin{matrix}
s\_{1,1} & s\_{1,2} & \cdots & s\_{1,N} \\
s\_{2,1} & s\_{2,2} & \cdots & s\_{2,N} \\
\vdots & \vdots & \ddots & \vdots \\
s\_{M,1} & s\_{M,2} & \cdots & s\_{M,N} \\
\hline
p\_1 & p\_2 & \cdots & p\_N
\end{matrix}
$$

## Example

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MK66-MGuoULhqCDXLwy%2Fuploads%2Fs8O97ax5fRPc4AOg3579%2Fimage.png?alt=media\&token=402ea787-a99c-412a-ab87-b40a49ae89ff)

{% file src="/files/R5hvutqATceuawCYzz4B" %}
Example Workbook: IMPLIEDSCENARIO
{% endfile %}


# Common Issues

With great power comes great responsibility!

Excel Lab relies on a combination of complex technologies to provide you access to powerful calculators and analytics. The following are common installation issues that may occur and how you can solve them.

## Error in VBAProject: ActiveX component can't create object

The required libraries (DLLs) were not successfully registered with the system during the installation process.

### Solution

Follow the steps outlined for registering the library files.

{% content-ref url="/pages/-MQE6uo8wNWMV8JUELsq" %}
[Step 2: Register Libraries](/getting-started/installing-excel-lab/register-files)
{% endcontent-ref %}

***

## Error in VBAProject: Automation errorClassFactory cannot supply requested class

<div align="left"><img src="https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MQTfQBZB_oNAka3QBye%2F-MQTfWn-8uHyacfVW3eF%2Fimage.png?alt=media&amp;token=fdbf7d09-98a7-43c9-8358-8172d16c0ad8" alt=""></div>

The required libraries (DLLs) were not successfully registered with the system during the installation process.

### Solution

Follow the steps outlined for registering the library files.

{% content-ref url="/pages/-MQE6uo8wNWMV8JUELsq" %}
[Step 2: Register Libraries](/getting-started/installing-excel-lab/register-files)
{% endcontent-ref %}

***

## Error in VBAProject: the specified module could not be found

The environment path to the prerequisite component runtime is either incorrect or not initialized correctly. Typically, this can occur when trying to use Excel Lab immediately after installing the prerequisite runtime.&#x20;

### Solution

Reboot your machine. If the problem continues to persist, verify that your system path includes **C:\Program Files\MATLAB\MATLAB Runtime\R2024b\runtime\win64**

(1) Open the Windows command console and list your system path variable

```bash
echo %PATH:;=&echo.%
```

(2) Verify that **C:\Program Files\MATLAB\MATLAB Runtime\R2024b\runtime\win64** is included in the list

(3) Reboot your machine.

***

## (LoadLibrary)Error: 0x0000007e Message: The specified module could no be found.

This error message appears when you are trying to run the registration batch file (**\_install.bat**) from a different folder of its location.

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MQV0wFt7ottrgc4TJGn%2F-MQV1BlZk6mLyAzAttYT%2Fimage.png?alt=media\&token=c3a83e02-5129-4bcc-8ef7-0c6723543383)

### Solution

Change your working folder in the command console to the location of **\_install.bat**; only then run the batch file. Or you can use the following multi-command syntax in a single line

```bash
cd "C:\Program Files\Windham Labs\WindhamXlsLab\application\" & _install.bat
```

***

## File Already Exist

When adding the add-in in Microsoft Excel, if prompted to replace the file location, select **NO**.

![](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MQ-0HIgCHQXoIwKXAd4%2F-MQ-0LvIXT4evgUBc-VB%2Fimage.png?alt=media\&token=e6356e34-e0b3-4029-a2e1-e3a56b8a5da4)

***

## Access Denied Registering DLL

Access denied appears in the command console when following [Step 2: Register Libraries](/getting-started/installing-excel-lab/register-files) for setting up Excel Lab.

![Access denied while registering the DLL library.](https://258561627-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MK66-MGuoULhqCDXLwy%2F-MZwmaDImbqY0rF0LAgf%2F-MZwpaVgS1ZyWL0eUe6K%2Fimage.png?alt=media\&token=837e1023-a292-438f-8a9f-4c9e2fd61d45)

### Solution

The setup step requires elevated administrative privileges when following the instructions. Please work with your IT professional to start the command console with administrative privileges to execute the installation steps.

***

## MWComUtil9.13 could not be found in the registry

Error message describing that the MWComUtil 9.13 dependency could not be found in the system registry.

<div data-full-width="false"><figure><img src="https://258561627-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MK66-MGuoULhqCDXLwy%2Fuploads%2F1dqc1BYHqWhE5px51Fcy%2Fimage.png?alt=media&amp;token=0f3fd8d6-b101-4603-a8c6-4454834b3bc2" alt="MWComUtil 9.13 could not be found in the registry. Please refer to MATLAB Compiler SDK documentation for instructions on how ot install and register MWComUtil."><figcaption><p>MWComUtil 9.13 could not be found in the registry. <br>Please refer to MATLAB Compiler SDK documentation for instructions on how ot install and register MWComUtil.</p></figcaption></figure></div>

### Solution

The mwcomutil.dll library needs to be registered with the OS. Execute the following command in the system console to resolve the issue.

```bash
cd "C:\Program Files\MATLAB\MATLAB Runtime\R2024b\bin\win64\"
"C:\Program Files\MATLAB\MATLAB Runtime\R2024b\runtime\win64\mwregsvr.exe" mwcomutil.dll /useronly
```


# FAQ

You have questions, we have answers!

## I don't have dynamic arrays, now what?

If you have an older version of Excel that does not have dynamic arrays, you will have to use Ctrl+Shift+Enter (CSE) when working with array formulas / results as you already do in Excel.

Excel Lab works best with dynamic arrays, we recommend upgrading Microsoft Excel to utilize this enhancement.

To learn more about dynamic arrays, read on Microsoft's support site.

{% embed url="<https://support.microsoft.com/en-us/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2>" %}
Working with dynamic arrays in non-dynamic aware Microsoft Excel versions
{% endembed %}