Theoretical Portfolio Optimisation Calculator

Description
PURPOSE OF MODEL

User-friendly Excel tool for the calculation of the theoretical optimal portfolio weights for up to 25 securities using Modern Portfolio Theory. The tool calculates the optimal portfolio weights using weekly historic pricing data for the past 3 years. The tool makes use of the MS Excel Solver function to determine the portfolio composition which produces the highest sharpe ratio.


KEY OUTPUTS

The key outputs include:
- The average return, standard deviation and sharpe ratio for:
o Each security;
o Overall portfolio based on current weights;
o Overall portfolio for an equally weighted portfolio;
- Weights for an optimal portfolio (highest sharp ratio portfolio);
- Risk vs Return chart;
- Pie chart with optimal portfolio composition.


KEY INPUTS

Inputs include:
- Latest year and week;
- Risk free rate;
- Security tickers;
- Current portfolio composition;
- Weekly pricing data for past years


MODEL STRUCTURE

The model contains, 4 tabs:
- A 'Front Sheet' containing a disclaimer and instructions;
- An Input tab for all the user-defined data inputs
- A calculation tab to calculate variance-covariance martrix and correlation matrix
- A results tab for presentation of all the outputs


KEY FEATURES

Other key features of this tool include the following:
- The tool follows good practice financial modelling guidelines and includes instructions;
- The tool calculates the optimal portfolio using historic weekly pricing data for the past 3 years;
- The tool generates the links to download the pricing data automatically from Yahoo Finance;
- Securities and latest period are fully customisable;
- Tool requires the use of the MS Excel solver function to determine the optimal weights. The tool includes preset parameters for the solver function.


MODIFICATIONS

If you require any be-spoke modifications to the tool, we are more than happy to assist with this. Please send us a message through the Eloquens site or contact us on: [email protected]


ABOUT PROJECTIFY

We are a small team of financial modelling professionals with experience working in Big 4 Business Modelling teams and strong experience supporting businesses with their financial planning and decision support needs. Our aim is to provide robust and easy-to-use tools that follow best practice financial modelling guidelines and assist individuals and businesses with common financial planning and analysis processes.

We are keen to make sure our customers are fully satisfied with the tools / models they purchase and will be more than happy to assist with any questions or support required following or in advance of purchase.

We are also always keen to receive feedback so please do let us know what you think of our products/offering by sending us a message or submitting a review.