Make an investment report for 1 property picked from the Excel file (sheet 1). Use approximately 1,000 words only! (This is about 2 pages.) The 1,000 words do not include any figures and/or tables you might want to add.
Add an Excel file with a 10-year pro-forma. This must include discounted cash flows (PBTCF, EBTCF, PATCF and EATCF), value/purchase price and IRR for all cash flows (again PBTCF, EBTCF, PATCF and EATCF). The investment report must correspond to this Excel file.
I want to see that the students use the PowerPoints, Excel files, and videos used in class. You will find an Excel file attached to this assignment with some relevant trends (sheet 2) and a few properties that you can use as “comparables” (sheet 3). I want to see you use that data in your investment report / pro-forma. You are obviously allowed to use other data sources as well, see slides for examples.
Next, you will find more details of what needs to go in the investment report and pro-forma.
Client
Shortly describe your client (which you can make up yourself) first in the investment report. Is it a pension fund or a wealthy individual, etc? More importantly, you need to;
Give a brief description of what levels of risk are desirable (in your opinion) to your client.
Whether the client has a specialty or is marginal.
The former allows you to set debt levels (corresponding to the level of risk the client wants) in the pro-forma, and the latter will affect the (investment) value of the property (see below). If the client is a marginal investor, that’s fine too. Find the prevalent mortgage rate that would go with the chosen LTV levels. Will you make principal payments? Or is it interest-only. Try to make it as realistic as can be. (But don’t worry if you cannot find very exact numbers.)
Potential Gross Income (PGI)
The current (year 0) Gross Potential Income is given in the Excel file (sheet 1), except for the development project. How do you think PGI will grow in the coming years? As always, there are no wrong answers, as long as any claim is substantiated with data/clear opinions. If the client has a specialty, it will be able to grow PGI more than the market average. However, you will still need to give a market projection in this case. Use trend extrapolation and market analysis to find a growth expectation. But as explained in class, do not hide behind the numbers, and also give good argumentation on what you think will happen.
Capital Expenditures (CapEx)
How large do you think the CapEx is going to be? Is it a flat rate, or do you expect some spikes in CapEx? There is also no wrong answer here. Make sure you use the rules of thumb from the lectures as a starting point. If you think CapEx is higher/lower for the target property, explain why. (Taking an average of 30% of NOI is a good start.)
Return Requirement (r)
In class we discussed two ways of computing the return requirement. Use both methods and use it appropriately to determine the correct r parameter in you proforma. Remember from class that the discount rate is not affected by the investment value!
Purchase Price / Value of property
Using the inputs, make a 10-year pro-forma in Excel and upload this Excel file separately to HuskyCT as well. The pro-forma should give a purchase price where the NPV is zero (or sum of discounted cash flows is zero). If you think your investor can grow PGI more than the market average, you need to give the investment value, and the size of the NPV as well. (I.e. Investment value – Purchase price.)
Investment Metrics
Using the pro-forma calculate the (1) Property Before Tax Cash Flow (PBTCF), (2) Property After Tax Cash Flow (PATCF), (3) Equity Before Tax Cash Flow (EBTCF), and (4) the Equity After Tax Cash Flow (EATCF). Also compute the Internal Rate of Return (IRR) for all four sets of cash flows and put this in the report as well. As noted before, you can pick levels of debt you feel is realistic/desirable for your client.
Please use the following inputs to complete the pro-forma.
Use a 20% income tax on the cash flow.
The recapture tax rate on the accrued depreciation is 25%.
The difference between the sales price and gross book value is taxed at 15%.
Use an 80% depreciation cost basis.
Cost of selling the property is 5% of the sales price.
Vacancy is 5% of PGI in all years.
Operating Expenses are 30% of Effective Gross Income in every year.
For the development only specifically.
You can only – via zoning – build either (1) office, or (2) apartment.
First, find comparables (so offices and apartments build nearby).
Compute the average FAR (Floor-to-Area-Ratio) of the comparables, which is square footage of building divided by size of land.
Calculate the square footage that you think is possible on this plot of land using the found FARs and multiplying it with the size of the land of the development. Note that it can be different between office and apartment. You can also manually change it, if you think that is possible / necessary.
Find a reasonable Net-Operating-Income separately for both apartments and offices per square foot on that location, using the same comparables.
Multiply the NOI per square foot, with the square footage that is possible on that piece of land.
Do a pro-forma for both the apartment and the office. This will give you the value of the property.
Assume that you sell the property in month 12 of the development.
Assume that the costs are evenly spread out over the 12 months. (Costs are given in Excel file.)
Calculate the land value using the DCF for developments, we discussed in class.
Pick the property which results in the highest land value.
