For the final project you will need you to create a spreadsheet /proforma of the cash flows from a property. There is no magic. All you have to do is layout the cash flows on a monthly basis. Then you need to (on a second tab) aggregate the cash flows by year. When you have the second tab showing annual cash flows for the 5 years of holding period plus the reversion, you can calculate value, mortgage and all the other metrics. I am not looking for elegant coding. I am just looking for an understanding of how the cash flows work and how to calculate the various metrics.

This assignment is to create an analysis for purchasing a property, projecting rents, expense pass throughs, expenses, capital over a five-year holding period and the sale. Below is all the information that you need to do the calculations and determine the metrics behind the investment.

Market rent $50/sf for gross leases with a new base year based on expenses at time of new lease

$30/sf for net leases.

Growth rate 2.5% annually for everything

Rent Roll

Tenant SF Rent/yr /sf Stop Lease Start Mo. Term Steps

Tenant A 30,000 $49.00 BY $20.00/sf 1/1/2021 10 yrs 3%/year

Tenant B 20,000 $45.00 percentage rent 1/1/2021 8 y see belowrs

Tenant C 15,000 $30.00 Net 1/1/2021 3 yrs N/A

Tenant D 35,000 $50.00 BY $21.50/sf 6/1/2021 10 yrs $56/sf in month 57

Total

Base year (BY) The tenant will pay for expenses that have risen to a level above their expense stop. Net leases have a stop of zero, so they pay all reimbursable expense. (Expense – stop) x sf

Tenant B Percentage rent – the tenants will pay 10% on sales per year exceeding $9,800,000. The sales are Yr1 450/sf, yr2=550/sf, yr3=510/sf, yr4=465/sf and yr5=600/sf.

Tenants A and D are modified gross with expenses passed through over a base year while tenant C is Net.

Vacancy/Credit Loss 10% of PGI

Expenses

Expense Category Expense per foot

Taxes $10.00/sf

R&M $ 3.00/sf

Insurance $ 0.50/sf

Utilities $ 4.00/sf

Payroll $ 1.75/sf

Security $ 1.00/sf

G&A $ 0.80/sf

Total Reimbursable $21.05/sf

Non Reimbursable

Management 3% of EGI

Assume zero rollover possibility. Downtime (vacancy at end of lease) 6 months

Capital Expenditures

Total Capital at rollover 25/sf growing at inflation

Structurural reserve = $0.15 per square foot each year .

From the above information, you should be able to create a proforma. Use following parameters.

Reversion 7.0%

Discount Rate 10.0%

Term 5 years

Loan Info

Amortization 30 years

Term 5 years

Rate 4% rate

LTV 70%

Create a proforma for a five-year hold aggregating the cash flows from each lease.

Calculate

• Value
• Loan
• IRR
• Cash flow after debt Service
• Cash on Cash Return
• Debt service coverage Ratio
• Leveraged IRR
• Debt Yield
• Break-even ratio

Answer rating: 100% (QA)

Proforma Year 1 Year 2 Year 3 Year 4 Year 5 Revenue 600000 615000 630875 647219 664141 Expenses 588750 603438 618859 635055 652032 Net Operating Income 11250 11562 11816 12165 12109 Capital Expenditur... View the full answer