FINS 5535 Computer Assignment For this assignment, you may work in groups of up to four. The due date for the assignment is Friday, 1 June, 2012 by 6:00pm. You may hand in the assignment at the Banking and Finance assignment boxes on the ground floor of the Australian School of Business building. To find the assignment boxes, go to the west elevator (further from the bookstore, closer to the Roundhouse), and go straight out the back through the glass doors (left of the elevator). On the left-hand side you’ll see the Banking and Finance assignment boxes. Let’s use Assignment box 2. I’ll put up a sign closer to the due date. Or, you can hand them directly to me, or bring them to my office during my consultation hours. Please do not disturb our administrative staff. Introduction The purpose of this assignment is to have you solve a number of problems using a variety of spreadsheets that will be provided to you. For the most part, the spreadsheets are designed so that you need merely enter the input variables, which are indicated by the red font. You should only have to change the variables with the red font. Some other colours are used as well, for example, in some cases the solution is given in blue font. NOTE: any input variables that happen to be in the spreadsheet when you receive them should be considered to be irrelevant. You will provide all the relevant input variables. For each spreadsheet, answer the questions provided. The questions will be indicated by letters, (a), (b), etc. Generally, each question is worth 1 mark. Please do not hand in a spreadsheet unless explicitly asked to do so. Introduction to the Spreadsheets Puts&Dvd.xls and Call&Dvd.xls NOTE: before opening the spreadsheets Call&Dvd.xls and Puts&Dvd.xls, you need to go to Tools, Add-Ins, and make sure there’s a check mark beside Analysis ToolPak-VBA. Also, make sure that there’s a check mark beside Solver. (To do this with Office 2007, click on the Microsoft Office button on the upper left corner. When the window comes up, look for Excel Options at the bottom of the window. Then click “Add-Ins”. Then, in the Manage box at the bottom of the window, select Excel Add-ins, and then hit Go. Make sure there are ticks beside Analysis ToolPak-VBA and Solver.) Many of the bond functions require the Analysis ToolPak-VBA add-in. In the past it has been difficult to send files to students because of these bond functions. Therefore, I have removed the “equal” sign from all the bond functions. So, you have to go to each of the cells with the light blue background and insert an equal sign in front of the formula. (A useful trick is to hit the F2 key, then the Home key, then type the = key.) First, go to cell Q2, and insert the equal sign. Then go to cell R2, insert the equal sign, and copy and paste that formula into the remaining cells in that row (down to cell BE2). The files Puts&Dvd.xls and Call&Dvd.xls calculate option prices using the binomial model we learned in class. One thing that each of the spreadsheets has in common, is that the binomial tree
Su S Sd
is represented in the spreadsheets as
So, as prices move horizontally, they increase. So, for example, when the price of the underlying asset is given in row 21, if the price moves up, it stays in row 21, if the price drops, it moves down to row 23 (in the next column). (In general, once the price is in row 21 it can’t move back up to row 19, despite appearances; the best it can do is move horizontally.) Also, the call (or put, depending on the spreadsheet) prices are given right below the stock prices. For example, if the stock price is given in row 21, then the corresponding American call price appears in row 22 (in blue font), directly below the stock price (given in black bold). So, in a sense, one “node” in this case, is two cells high and contains information about the price of the underlying asset, and the American call (or put). The price of the European call (or put)...