Hi there. I have a mini case study due tomorrow and I really need some assistance/guidance in making sure I am getting it completed on time.
FIN 5130 Mini-case 1
Learning Objectives: Calculate initial outlay and market betas
A. Due: Sunday 3/28/2021 (end of week 4). Late assignments will not be accepted.
B. 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.
C. (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.
D. For %, increase decimal points to 2 decimal % points such as Ã¢â‚¬Å“12.35%Ã¢â‚¬Â.
E. If you need help or need clarification, ask in Teams. IÃ¢â‚¬â„¢ll 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 IÃ¢â‚¬â„¢ll give you hints and point you to the right direction. But if you ask
Ã¢â‚¬Å“HereÃ¢â‚¬â„¢s my excel. Is my answer correct?Ã¢â‚¬Â, I wonÃ¢â‚¬â„¢t confirm it because itÃ¢â‚¬â„¢s unfair to other
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
Hollywood, FL Real
Estate – Hollywood Homes for Sale _ realtor.comÃ‚Â®.pdf
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.
(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?
a. Per IRS rule, residential rental property is depreciated at a rate of 3.636% each year for
b. 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 wonÃ¢â‚¬â„¢t save the formulas in CSV format.
c. Investopedia explains how to calculate beta in Excel using 3 different methods:
https://www.investopedia.com/articles/investing/102115/what-beta-and-how-calculatebeta-excel.asp However, you only need to present 1 method.
Purchase answer to see full