RES 3400 Excel Project #1: Pro-Forma Analysis of Single Privately Owned Asset Using the templates provided, input the following assumptions for each of the investme
RES 3400 Excel Project #1:
Pro-Forma Analysis of Single Privately Owned Asset
Using the templates provided, input the following assumptions for each of the investment opportunities in the yellow spaces only. The rest of the information has been inputted for you, including formulas, and that information does not change. Note for expenses, only input those that are relevant to the type of lease shown, since there is no “expense reimbursement” available in these proformas.
Unless otherwise indicated keep the annual rent/inflation/variable based increases as stated in the proforma – increases are consistent with inflation and market conditions
(Note that you do not have to calculate either “proportionate share” or take into account “base year” for any of these examples.)
All results are “pre-tax.” Tax consequences of any results are not being evaluated.
===================================================================
Compare each opportunity by Internal Rate of Return (IRR) and Discounted Cash Flow (NPV), putting them in a table of your own creation and answer these questions:
What is the capital stack for each – dollar value and % of purchase price for each — Noting that the percentages are of the purchase price?
· Mortgage
· Borrowed Equity
· Cash Equity
· Total Purchase Price
Which investment has the most in overall returns (NPV) and why?
Which investment has the best return on cash equity (IRR) and why?
Which investment would you choose and why?
Provide 2 articles regarding the relative health and risks involved with the markets of each of these asset types: big box retail, office, industrial, multifamily. They do not have to be geography specific.
Differentiate between the qualitative (i.e. market conditions and their role in long lasting value/performance of the investment) and quantitative (i.e. cash returns from the investment) risks for your investment choice as well as each of the other investment candidates. Explain why you made your choice and why you did not choose each of the others.
Investment Opportunity #1: RETAIL
· Fully occupied single tenant 50,000 SF “Junior Big Box” Retail Building on 5 acres of land. “B+” Credit Tenant (i.e. TJ Maxx/Marshalls/Burlington Coat/Barnes & Noble)
· 0% Vacancy
· Rent $20/SF NNN
· No additional income
· Lease Term = 20 years, start 1/1/2020
· Expenses are as follows (all as a % of EGI)
o Property Taxes 15%
o Insurance 3%
o Maintenance 20%
o Management Fee 8%
o Reserves for Replacement 5%
o Other expenses 5%
· Price = $11,500,000
· Capital Stack:
o 80% LTV @7.5% Interest Rate, 25 Year Term, Fully Amortized
o Constrained by 1.2 DSCR
o 10% Borrowed Equity@ 15% Preferred Return (Interest)
o 10% Interest Rate on Shortfalls
o Remainder Cash Equity
· Terminal Cap Rate = 9%
Investment Opportunity #2: OFFICE
· Multiple Occupancy 100,000 SF Multistory Office Building with consistent 10% vacancy. In addition to that 10% vacancy, 2 tenants totaling 40,000 SF leave at the end of year 2 and are replaced after 1 year (additional vacancy) with new tenants.
· Full service 5-year leases with 5-year options
· Initial Rent $80/SF, Replacement Tenant Rent $40/SF.
· No additional income
· Expenses are as follows (all as a % of EGI)
o Property Taxes 15%
o Insurance 3%
o Maintenance 20%
o Management Fee 5%
o Reserves for Replacement 7%
o Other expenses 7%
· Price = $11,500,000
· Capital Stack:
o 65% LTV @9% Interest Rate, 25 Year Term, Fully Amortized
o Constrained by 1.3 DSCR
o 15% Borrowed Equity@ 12% Preferred Return (Interest)
o 10% Interest Rate on Shortfalls
o Remainder Cash Equity
· Terminal Cap Rate 10.5%
Investment Opportunity #3: Industrial
· Multiple Occupancy 300,000 SF Multistory Industrial Building with consistent 5% vacancy. Class “B” Buildings with 24’ Clear Ceiling Heights
o In addition, 150,000 SF of tenants leave at end of year 3, and are replaced immediately with tenants paying $15/SF NNN
· Lease Term = 5-year leases with 5-year options
· Initial Rent $12/SF NNN, Replacement Tenant Rent $15/SF NNN
· Additional income consists of 40 truck parking spaces rented for $400/month each. Spaces are fully rented all the time with no vacancy.
· Expenses are as follows (all as a % of EGI)
o Property Taxes 15%
o Insurance 3%
o Maintenance 20%
o Management Fee 8%
o Reserves for Replacement 5%
o Other expenses 5%
· Price = $65,000,000
· Capital Stack:
o 80% LTV @6.5% Interest Rate, 25 Year Term, Fully Amortized
o Constrained by 1.2 DSCR, requiring
o 20% Borrowed Equity@ 12% Preferred Return (Interest)
o 10% Interest Rate on Shortfalls
o Remainder Cash Equity
· Terminal Cap Rate = 5.5%
Investment Opportunity #4: Multifamily Residential
· Multiple Occupancy 60,000 SF Multistory Apartment Building with consistent 2% vacancy
· Full service annual leases, No base year.
· Rent 30 Studios (400 sq. ft. each) @ $2000/month; 50 1 Bedrooms (600 SF each) @ $3,000/month, 20 2 Bedrooms (900 SF each) @ $4500/month
· Additional Income from Parking (60 spaces @ $300/month) + Laundry/Gym/Storage/Amenity Fees ($400/month/Apartment for 60 apartments)
· Expenses are as follows (all as a % of EGI)
o Property Taxes 8%
o Insurance 3%
o Maintenance 12%
o Management Fee 5%
o Reserves for Replacement 10%
o Other expenses 5%
· Price = $30,000,000
· Capital Stack:
o 90% LTV @6.5% Interest Rate, 25 Year Term, Fully Amortized
o 1.1 DSCR
o 10% Borrowed Equity@ 12% Preferred Return (Interest)
o 10% Interest Rate on Shortfalls
o Remainder Cash Equity
· Terminal Cap Rate 6.5%
