Your objective is to build an application to perform the analysis of a two-asset portfolio.
The input data consists in two tables containing the dates and adjusted close prices of two different assets. There is a third table with the same data for a risk-free asset: you will use it to calculate the Sharpe ratio.
The most commonly used risk-adjusted performance measure in finance is the Sharpe Ratio. It relies on volatility as risk measure and considering historical returns. The Sharpe Ratio is usually computed as:
Where denotes the sample average of realised returns, the constant return over the risk-free asset and the sample volatility of portfolio returns.
Minimum requirements of your application:
x Fill a table with weight of asset #1 in the portfolio (ranging from 0-100%), expected portfolio returns, portfolio standard deviations and portfolio variances;
x Calculate the Sharpe Ratio risk-adjusted performance measures for each portfolio; x Fill a pre-created graph with data in order to show the efficient frontier; x Calculate the weight of each asset in the minimum variance portfolio.
portfolio_mean_return(asset1_prices As Range, asset2_prices As Range, proportionAsset1 As Double) As Variant portfolio_variance(asset1_prices As Range, asset2_prices As Range, proportionAsset1 As Double) As Variant sharpe_ratio(asset1_prices As Range, asset2_prices As Range, proportionAsset1 As Double, risk_free_prices As Range) As Variant minimum_variance_portfolio(rangeA As Range, rangeB As Range) As Variant
Read these carefully as they impact your final grade.
x Implementation of alternative risk-adjusted performance measure (implying the creation of another UDF). For instance, Jensen’s alpha is given by the outperformance (or underperformance) of the portfolio with respect to the CAPM-implied expected return. Once a CAPM regression is run for the portfolio under analysis, the Jensen’s alpha is just represented by the estimate of the constant :
The signature of this function could be:
Jensen_alpha(asset1 As Range, asset2 As Range, proportionAsset1 As Double, risk_free As Range, benchmark As Range) As Variant
x Creative userform design;
x Users can navigate through several userforms easily, one userform per task (efficiency frontier, building the portfolios table, etc.), and only one userform is visible at the same time;
x Create a new chart from the GUI for the efficiency frontier (instead of filling a pre-created one); x Allow stock prices to be automatically imported from a text file;
x Harder: Allow users to give ticker symbol of stocks, a start date and an end date (and possibly the choice between weekly, monthly, annual data…) and then use a macro to import data from a website
(only try this if all the basics work well); x Let your creative juices flow for further enhancements… but make enhancements pertinent to the tasks at hand!
x You will see 2 assignments on Blackboard – one for each teacher: use the right one, depending on the teacher you had in class!
x Upload a single Excel file containing all the work, with the names and groups of both partners in the file name. Example: “Teller-Pierre-A1_Lespagnol-Vivien-B2.xlsm”.
x You must also upload the consent form with both names and signatures indicating that you are aware of penalties for non-adherence to the Academic Honesty policy. Any pieces of work judged to be too similar will be investigated and heavily sanctioned.
Assignment Writing Help
Engineering Assignment Services
Do My Assignment Help
Write My Essay Services