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.
- 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.
- Build the DDM spreadsheet exactly as we did in class. Put in a tab named ‘DDM’.
- 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.
- 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.
- 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.)