FIN2014 Excel and VBA Course – Individual Project
(Friday 12 noon 19th November 2021)
YOU MUST UPLOAD THE EXCEL AND WORD FILE TO CANVAS BY
Time: 23:59, Date: FRIDAY 17th December 2021
Assignment
On Canvas, in the VBA assignment page, you will be able to download the dataset titled “Green Investments.xlsx”. The dataset contains the monthly closing prices of five different exchange-traded funds (ETFs) which have a different investment theme, in the worksheet entitled “Indices”:
A fossil fuel free index fund (S&P 500 Fossil Free)
A clean technology index fund (iShares Global Clean Energy)
A fossil fuel index fund (iShares Oil & Gas)
A global equity fund (iShares MSCI World)
An index fund which avoids controversial industries (iShares MSCI World ESG)
In the workbook, you will also find more details about these index funds (in the “Index Overview” worksheet), as well as two worksheets with the Open, High, Low, Close prices and (trading) Volume for the S&P Fossil Free index fund and the iShares Global Clean Energy Fund.
You have also been given 4 papers to read about the financial performance of green investments. Based on the in-depth Excel & VBA skills gained throughout the course and the topical knowledge you acquire by reading the 4 papers provided, the assignment is the following:
Write a short introduction on the importance of sustainable investing for society and a literature review (based on the 4 papers provided) summarising what research so far says about the comparative financial performance between green and sustainable funds, fossil fuel funds, fossil free funds and conventional funds.
(Introduction – no more than 100 words; literature review summary – no more than 500 words – please include in text references from the 4 papers, Harvard style)
25%
Using the information in the “S&P Fossil Free” and “iShares Global Clean Energy” worksheets, please create a stock chart for each index including information about opening, closing, high & low index pricing as well as trading volume over the period 2nd of February 2019 and 29th October 2020. Briefly compare and contrast the two charts and the evolution of the two indices in no more than 200 words.
10%
Using the information in columns A-F from the “Indices” worksheet, create an index chart that comprises the relative monthly performance of the five indices (columns B to F). Compare and contrast these five indices in no more than 300 words by commenting on their evolution between 2nd of February 2019 and 29th October 2020. You are welcome to create a new sheet and copy the information from columns A-F in order to make the index chart.
15%
Next, calculate the monthly percentage return for each index in columns H to L and the Excess return (%) in columns M to Q in the “Indices” worksheet. Based on these calculations, compute the Sharpe Ratio for each index in the cells M26-Q26.
Sharpe Ratio = Average Excess Return / Standard Deviation
10%
Using your knowledge of VBA, create a VBA function called SharpeRatio which takes as inputs the range of monthly returns and the risk free rate for a given index and outputs its sharpe ratio. Once you have created this function, use it to calculate the sharpe ratio for each index fund in cells M27 – Q27. Save your workbook in the appropriate format to support the VBA code.
10%
Using the sharp ratio results for each index fund in 5), comment on the relative performance of these index funds over the 2nd of February 2019 and 29th October 2020 period. In addition, link these results back to your readings of the provided papers (you are welcome to look for additional references) to support the explanation of the results. Explain the likely drivers of this relative performance from your readings in no more than 500 words.
30%
Once you are done, please upload both your answers to each question and the workbook on Canvas by 23:59 (UK time), Date: FRIDAY 17th December 2021.
Good luck!
Page 1 of 1