Project
This project is an individual assignment is due no later than Saturday, March 2 at 11:59pm CST. Please provide the results and answers to each question in an Excel file that you will upload to Canvas. Format your spreadsheet professionally, as presentation will count 20% towards your grade in this assignment.
All results should be presented in the first sheet of your workbook called Answers. Computations for each ticker (TSLA, SP500 and EURUSD) should be performed on different sheets. You can put the data on a different sheet or together with the computations of the corresponding ticker. All sheets should be formatted professionally. Use titles when necessary, format numbers to an appropriate number of decimals, use borders to delimitate blocks of computations like a binomial tree for the stock or the call.
The data for the assignment can be found here. The file contains daily values for the price of TSLA, the level of the S&P 500 and the EUR/USD exchange rate for the 3 months prior to 9/26/2023. Note that the dates for which we have data for the EUR/USD might differ from the ones for the S&P 500 or TSLA.
Problem 1
Compute daily percentage price changes for TSLA, the S&P 500 and the EUR/USD as follows: \[\begin{equation*} r_{t+\Delta t} = \frac{P_{t + \Delta t} - P_{t}}{P_{t}}, \end{equation*}\] where \(r_{t}\) denotes a daily rate of return for each asset, and \(P_{t}\) denotes the closing price for the same.
Using the Excel function
STDEV.S, compute the standard deviation of daily returns for each asset. This number represents an estimate of the volatility per day. In order to obtain an annualized figure, you need to multiply it by \(\sqrt{252}\), that is: \[\begin{equation*} \sigma_{\text{annualized}} = \sqrt{252} \times \sigma_{\text{daily}}. \end{equation*}\]Discuss the differences in magnitude of the annualized volatility among the different assets. Are these estimates consistent with your intuition? Make sure that you answer appears nicely formatted on your final answer sheet.
Problem 2
On 9/27/2023, you collect the closing price of each asset:
| Asset | Closing Price |
|---|---|
| TSLA | 244.52 |
| S&P 500 | 4,287.83 |
| EUR/USD | 1.0513 |
You also know that the 3-month SOFR is 5.39% per year whereas the 3-month Euribor reference rate is 3.94% per year. All rates are expressed with continuous compounding and you can assume that they stay constant at all periods in your tree.
Build an \(n\)-period binomial tree for TSLA, the S&P 500 and the EUR/USD exchange rate as of 9/27/2023. On Canvas, you will find a grade for an assignment called Number of Periods. Use this number as your value for \(n.\) To compute up and down movements for each period, use \(u = e^{\sigma \sqrt{\Delta t}}\) and \(d = 1 / u,\) where \(\Delta t = T / n,\) \(T = 3 / 12,\) and \(\sigma\) is the annualized volatility that you computed in Problem 1. That is, your tree will span a period of 3 months. You will use the binomial trees to compute the values of the options described below.
Compute the price of European call and put options as of 9/27/2023 written on TSLA and expiring in 3-months with strike price \(K = \$245\). Express the price of each option per share.
Compute the price of European call and put options as of 9/27/2023 written on the S&P 500 and expiring in 3-months with strike price \(K = 4{,}280\). SPX options are cash settled and their payoffs are equal to \(\$100 \times \max(S - K, 0)\) for calls, and \(\$100 \times \max(K - S, 0)\) for puts, where \(S\) is the value of the index at expiration. In your computations use a dividend yield of 1.45% per year expressed with continuous compounding.
Compute the price of 3-month European call and put options as of 9/27/2023 written on the EUR/USD over a notional of EUR 1,000,000 with strike price \(K = 1.10\).