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


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.


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

=MLERISK(type, assetReturns, dataPeriodicity)


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



Required. Enumeration string to specify calculation type: "risk", "sigma", or "stdev" "correlation", "corr", or "rho", "covariance", "covar", or "cov"


Required. Time series or matrix of asset returns.


Optional. Periodicity of the data, used for annualization. If you do not enter the argument, it defaults to 1. e.g. Daily = 255, Monthly = 12, Yearly = 1, Quarterly = 4.


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.


Last updated