FIN 5130 Mini-case 1
Learning Objectives: Calculate initial outlay and market betas
Directions:
Due: Sunday 3/28/2021 (end of week 4). Late assignments will not be accepted.
Submit 1 Word file and 1 Excel file together at Canvas>Module>Module 3>Mini-case 1. Include the cover page in the Word file.
(Optional) You can work in groups (upto 4 students) and submit the mini-case as a group project. List all the student names in the cover page. However, only 1 student needs to submit to Canvas. You can do group chat in Teams to facilitate your group online communication (see FAQ). Try to work together on each question instead of splitting questions up.
For %, increase decimal points to 2 decimal % points such as 12.35%.
If you need help or need clarification, ask in Teams. Ill help you solve but will not confirm whether your answer is correct or not. For example, you can ask how do I calculate beta? and Ill give you hints and point you to the right direction. But if you ask Heres my excel. Is my answer correct?, I wont confirm it because its unfair to other students.
Problems (5 questions, 1 point for each):
Suppose you are interested in starting a new rental property business. Use https://www.realtor.com (or zillow.com) to identify a house or condo (preferably in Florida, suggested price range between $250k – $1 million). Include the screenshot and URL of your choice.
1. Estimate the annual rent, annual operating expenses (maintenance, property tax, insurance, realtor fee, etc), annual depreciation expense (see Hints a below). Use reasonable and realistic estimates.
2. Estimate the initial outlay of this new rental business. Assume you will need 2-month rents as cash on hand to run this business (= net working capital).
(Mini-case 2 will ask the rest of capital budgeting problems.)
Next, instead of rental property, you are now interested in investing in stocks. Specifically, you are deciding between SPDR S&P 500 ETF (ticker = SPY) and AT&T (ticker = T).
From https://finance.yahoo.com, download the monthly data for SPY and from 1/1/2011 to 1/1/2021. (See screenshot below for SPY.)
If you open the downloaded CSV file in Excel, you should have 121 rows with first date = 1/1/2011 and last date = 12/1/2020. See Hints b for more information.
3. Calculate monthly returns (= monthly percentage change in Adj Close) for SPY and T. You should have 119 monthly returns each for SPY and T.
As a comparison, this is what market returns look like for the same sample period.
https://drive.google.com/file/d/1pF4WP0g4VlaJpz-QxWHkXVMw5W2fsJrp/view?usp=sharing (Download this MKT.xlsx file, as you will need for Q5.)
4. Calculate the average monthly return and the standard deviation of returns for SPY and T. Between these 2 securities, does the one with the higher standard deviation also have the higher average return?
5. Calculate the market beta for SPY and T. For this exercise, you will need the MKT return from MKT.xlsx file above. (See Hints c.) Does the security with the higher beta also have the higher average return?
Hints:
Per IRS rule, residential rental property is depreciated at a rate of 3.636% each year for 27.5 years.
To obtain the monthly data for each security, on Yahoo! Finance website, enter the ticker symbol in the companies search bar. Then, click on Historical Data. Enter Time Period as given above. For Frequency, make sure Monthly is selected and then click on Apply. Click Download Data to download the CSV file to your computer. But make sure you save as XLSX because Excel wont save the formulas in CSV format.
Investopedia explains how to calculate beta in Excel using 3 different methods: https://www.investopedia.com/articles/investing/102115/what-beta-and-how-calculate-beta-excel.asp However, you only need to present 1 method.
