Midterm 1

FI 4080

June 21, 2017

Save your file as YourlastnameMidterm1.xlsx and put in the dropbox. Points will be deducted for not following instructions and/or for turning it in late. The questions are equally weighted.

1. Complete the “Taxes’ tab. The user should be able to input their filing status, gross income, and number of exemptions. Filing status should be a dropdown menu. There should never be a #DIV/0! Or #N/A error. The user should be able to input a start value and increment for the gross income in the data table, which will show various effective tax rates.

2. Build the DDM spreadsheet exactly as we did in class. Put in a tab named ‘DDM’.

3. Complete the ‘Mortgage’ tab. Assume a 30-year mortgage with the first payment made August 1, 2017. Show the exact cash flows for each month until the mortgage is paid off.

4. Use the data from the ‘Monthly’ tab to find the five-year rolling β for each of the following stocks: ADBE, AMD, HAS, MU, and NANO. Use the equally-weighted returns to proxy for the market. Graph the βs.

5. Use the data from the ‘Daily’ tab to find the average daily return for each stock for each month. Show in a table like the following.

Then create an indicator table with a 1 indicating the best month for return for each stock.

Repeat for risk and Sharpe ratio. (Use a formula for the indicator tables, don’t just typo 1s and 0s.)