We cover how to use the Excel Solver tool to achieve an optimal portfolio based on objectives and constraints. In our example, we have a portfolio comprising six fictitious securities or assets and will use Solver to either maximise expected return without any volatility constraint, maximise expected return for a given level of volatility, maximise risk-adjusted return or minimise volatility. We have the prices of the securities in the price sheet. We have calculated their daily returns in the returns sheet and based on this, have calculated the annual expected return and volatility figures for each security. We have also produced a variance-covariance matrix. This variance, covariance matrix helps us to calculate the overall portfolio volatility figure. The overall portfolio expected return is a weighted average of the individual securities returns and their weights. Meanwhile, the Sharpe Ratio is the expected return minus the risk free rate which we assume to be 2% divided by the volatility.
DISCLAIMER: Although I have taken great care to check all of the calculations, I am not a qualified accountant or financial advisor and you should not use this video to substitute financial advice. Make sure you talk to your own accountant or advisor before you use this information to make any financial decisions.
Access the templates featured in The Excel Hub's video tutorials here:
https://theexcelhub.myshopify.com/
Subscribe for weekly Excel tutorials, techniques and examples:
https://www.youtube.com/channel/UCgdRm6hepAn6Y0FqMZPLiAw?sub_confirmation1
Please share this video with others that may find it useful:
https://youtu.be/dXcvtrZI1uo
Please comment below if you have any feedback or any suggestions for videos you would like us to upload next!
The Excel Hub website: https://theexcelhub.com/
Instagram: https://www.instagram.com/theexcelhub/
ABOUT US:
The Excel Hub seeks to enhance your Excel and VBA knowledge by providing value-adding tutorials, techniques and examples in order to help you take your spreadsheet and data analysis skills to the next level.
For any enquiries, please contact:
[email protected]Overview: (0:00)
Set up Solver: (1:55)
Maximise expected return: (2:12)
Achieve a target level of risk: (4:09)
Maximise risk-adjusted return: (5:08)
Minimise volatility: (6:05)