Can you write the VBA code for this excel model
Financial Modeling II: Assignment #1 Note: Submit the assignment through D2L, just like last term. Please only submit one Excel file with each problem in an appropriately labeled worksheet. VBA code for each problem should be placed in a separate appropriately labeled module. Documentation still counts. Both spreadsheets and VBA code should contain at a minimum 1) Objective, 2) Name and 3) Date. The Problem Set will be marked out of a total of 10. Note: There are four problems (marks are as indicated – note the one-mark bonus) Problem #1: Black-Scholes Option Function and Chart (6 marks) Create a chart showing (i) the payoff at maturity and (ii) the value of a call or put before expiration (calculated using Black-Scholes) as a function of the underlying stock price ranging from zero to two times the exercise price. I have set up a spreadsheet in the associated Excel file: “Assignment #1 – Black-Scholes Chart Start.xlsm”. There you will find the equation for the Black-Scholes value of a European call and put that pays a continuous dividend yield as well as the payoff functions. The inputs to the model are: Parameter Name Parameter Value Description Data Type Security Call or Put Option Type String S $20 Stock Price Double X $20 Exercise Price Double T 3 Time to Maturity (years) Double r 2% Risk Free Rate * Double q 0% Dividend Yield * Double sigma 30% Volatility of Asset Return * Double * % per year (continuously compounded) Excel Setup: Refer to the Assignment #1 – BlackScholes Chart – Start.xlsm file. 1. You should format the inputs and title etc. 2. You should also name the inputs as well. 3. Set up Data Validation for the Security (i.e. drop-down list of either “Call” or “Put”). You should also restrict what a user can enter for the other inputs to something reasonable. Restrict them to be decimal type numbers, and don’t, for example, let a user enter 5000% for the interest rate. If you have an economy with a 5000% risk free rate – you have bigger problems to worry about than the Black-Scholes price of a call. Pick some reasonable restrictions for all the parameters. However, we are going to deal with various parameters being equal to zero in the code. VBA Functions: You will need to set up 5 functions for this assignment. 1. Create a private function that will find the Max of two numbers. Unfortunately, VBA does not have a built-in max function. We could use Excel’s MAX function – but transferring the work to Excel is relatively slow so we are going to create our own max function right in VBA. It Should start as follows: 2. Create a public function that will calculate the payoff of a European Call or Put at the maturity of the option. The payoffs are given by: You should implement the error handling code (see item 4 below), but there is nothing else to guard against. You will need to call the function MaxOfTwo in this function. Here is the start for this function: 3. Create two private functions; one to calculate d1 and another to calculate d2. Here is the start of d1: Note: I used K for the strike price instead of X. It does not matter what you call this parameter, it has procedure level scope so is completely independent of any other X or K in any other subroutine. Use the Excel function NormSDist(.) to find the cumulative normal distribution value for the parameters d1 and d2. This is an Excel function so you will need to implement it as follows: Worksheetfunction.NormSDist(d1) 4. Create a Public function to calculate the Black-Scholes value of a European call or put. This function should be able to handle both calls and puts (i.e. do not create two separate functions). I have given you the basic set up of this function in the Excel file – to help get you started. Note: space under score (i.e. “ _”) is the line continuation character sequence. It means that you continuing this instruction on the next line of the editor. The computer will interpret it as one continuous instruction. Also, the basic error handling that I set up in this function should be incorporated in all 5 functions. Writing a basic Black-Scholes function should be relatively straight forward, until you start to think of all the things that could go wrong. We are using Data validation to avoid the user from entering stupid inputs (i.e. entering “Mike” for the exercise price). However, we still have some other valid issues to deal with. If you look at the Black-Scholes equations we have 4 potential issues: a. What happens when T = 0? The Black-Scholes equations will just blow up if we enter T=0 (i.e. d1 and d2 would be infinite – which VBA can’t deal with so will just return an error). In this case the option has matured so we could just call our payoff function to get the correct values. b. What happens when sigma = 0? Same problem. In this case, we basically have a risk-free security (i.e. no volatility or uncertainty about the future return). In the risk-neutral world all securities earn the risk-free rate. So, the expected stock price at the maturity of the option is . The payoff on calls and puts is: This is the payoff at maturity. The value of the option at some point prior to maturity, t, is the present value of the payoff discounted at the risk-free rate: c. What happens if S = 0? In this case the options are worth: d. What if X = 0? I will let you think about this one. Hint: d1 and d2 both equal to infinity. You should check all of these cases (just use an IF, ElseIf … Else, End IF construction) before you actually implement the actual Black-Scholes formulas. You should test each of your functions as you go along and make sure each is working correctly and returning the correct values. Just call it from the Excel spreadsheet. In the end you will be calling the private functions d1 and d2 from within the Black-Scholes function. Back to Excel: 1. Once you have your Black-Scholes function working, you will need to set up a 3-column table. The first column will be stock price ranging from 0 to 40 (in this case). Automate, this so that the stock price ranges from 0 to 2 times the exercise price. (ROWS; IF statements?). For example, you can pick some reasonable number of stock price rows (say 201 or so). You will have to scale the stock prices so they will all fit within 200 rows. For example, if X = $1000 (think Apple) then your stock prices would have to range from 0 to $2000. If you are going to fit this range in 201 rows of stock prices then you will need a sequence such as: {0, 10, 20 …2000}. You could accomplish this with: “=(2X/200)*(rows($A$1:A1)-1)” and then dragging it down 200 rows. This assumes that you type the formula into cell A1. 2. The second column will have the payoff (i.e. T=0) for the chosen option as a function of stock price. The third column will have the Black-Scholes price of the option for each stock price. 3. Create a scatter chart for the data in this table. Make sure this table is appropriately formatted (i.e. title, axis labels etc.). Also include: i) dynamic chart title, ii) dynamic x and y labels and iii) a dynamic legend. Get rid of all other chart junk. Here is a screenshot of my final model: Problem #2: (1 mark) Chapter 4, Exercise #6 of SCA. Note: For purposes of handing in the assignment save your macro in the file you are submitting, not your personal macro workbook. Good documentation and formatting are always important. Problem #3: (2 marks) Chapter 5, Exercise #4 of SCA. Use an InputBox to ask the user for a string and a Msg Box to display the result. If you are in the VBA editor the shortcut for opening the object browser is F2. In the top left corner of the box that pops up you should see “All Libraries”. From the drop-down menu select “VBA”. In the Classes column click on the “Strings” class. In the “Members of” column you will see all the properties and methods of the string class. For example, check out “StrReverse”. If you click on this method, you will get a very short explanation of what it does. Problem #4: (2 marks) Chapter 5, Exercise #8 of SCA. See the Count Large.xlsm file, within the Assignment #1 – Start.xlsm file. 3