• For this 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. Keep in mind that to create a 5 year hold plus reversion you need to generate 6 years of income.
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.
Analysis begins 1/1/2022
Market rent $45/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 market rent, expenses and capital
Rent Roll
Tenant SF Rent/yr /sf Stop Lease Start Mo. Term Steps
Tenant A 37,000 $45.00 BY $15.00/sf 1/1/2022 10 yrs 3%/year
Tenant B 28,000 $47.00 Share of tax over $6.00 1/1/2022 8 yrs $1.50/year.
Tenant C 40,000 $30.00 Net 1/1/2022 3 yrs N/A
Tenant D 45,000 $35.00 BY $15.50/sf 6/1/2022 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 only reimburses the landlord for increase in RE Tax.
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 $ 6.25/sf
R&M $ 3.00/sf
Insurance $ 0.75/sf
Utilities $ 2.10/sf
Payroll $ 1.10/sf
Marketing $ 1.50/sf
G&A $ 0.30/sf
Total Reimbursable $15.00/sf
Non-Reimbursable
Management 3% of EGI
Assume zero rollover possibility. Downtime (vacancy at end of lease) 6 months
Capital Expenditures
Total tenant leasing costs at rollover 55/sf growing at inflation.
Structural reserve = $0.15 per square foot each year.
From the above information, you should be able to create a proforma. Use following parameters.
Reversion (exit cap rate) 7.5%
Discount Rate 10.5%
Term 5 years
Loan Info
Amortization 30 years
Loan Term 5 years
Rate 3.0% rate
LTV 65%
Create a proforma for a five-year hold aggregating the cash flows from each lease.
Calculate
• Value = npv of the cash flows including the reversion.
• Loan = value x ltv
• IRR = irr of equity investment given the cash flow before debt service
• Cash flow after debt Service
• Cash on Cash Return = calculate the cash return after debt on the equity investment each year
• Debt service coverage Ratio = noi / debt service for each year
• Leveraged = IRR irr of equity investment given the cash flow after debt service
• Debt Yield = typically NOI / mortgage
• Break-even ratio = expense + debt service / pgi
• Equity multiple = sum of returns from investment / equity investment
The developer will invest 20% of the equity and the money partner will invest 80%. The investment partner has been promised an 8% preferred return. They will split cash flows after the preferred return 50/50. Please calculated each investors IRR and Equity Multiple.
How to begin
• Start by creating a column for each month of the hold period and additional 12 months for the reversion year. In these columns enter in the first line 1 increasing each month by 1.
• The next line put in the dates. Starting with 1/1/22. The next row calculate the year of each cell so you can aggregate on that year later on.
• At this point you may want to create a row with an inflation modifier.
• Then for each lease put in the monthly rent that is due for each month. Rent is shown as rent / sf so total rent per year is rent / sf times sf for the tenant. Then remember that your cash flow in each cell is monthly. You are given terms.
• Add a line for the sum. (note: This is one building so the cash flows need to be summed and the value is for all four tenants.)
• Then put in the pass through that is due in each month for each tenant. Pass throughs are calculated typically as the (current expense amount above the base expense) multiplied by the tenants sf for modified gross / base year. Net expense is typically where the base is zero.
Column B in the sample proforma is the “stop” or base expense per sf and is given to you in the problem in the Rent Roll. Column C is the tenants pro rata share which is their SF / total building sf. Tenant sf and building sf are both given to you in the problem.
The expense pass through is the amount of the actual current expense less expense base multiplied by the tenants share. Typically, their share is their SF / Building SF. The base is a negotiated number and I have indicated the base in the assignment. (Expense – Base) * (tenant sf / total sf) or (expense per sf – base per sf) * tenant sf. Lets say the building expenses are 15 / sf and the tenants base was 10 / sf and their sf was 10,000 sf their expense would be ($15-$10) * 10,000 or $50,000. If you are calculating a monthly number, then you have to divide by 12.
• Add a line for the sum of the pass throughs.
• Sum of pass throughs and rent equals PGI
• Apply the vacancy for the property
• Calculate EGI.
• Enter in each column the appropriate expense for each line for each column. The expense is expense per foot multiplied by the total building sf. Expense per foot is expressed as an annual number so it needs to be adjusted for monthly inputs. Also, the expenses should be modified by the inflation factor. Although your expenses show after the pass throughs on the proforma, they really need to be calculated before the pass throughs.
• Sum the expenses.
• Subtract the expenses from the EGI to get NOI.
• Capital reserves are shown as a dollar per foot per year and also have to be adjusted for monthly inputs and for inflation. Tenant leasing expenses hit the proforma in the period that the lease begins.
• After cash flows you can calculate value. Value is equal to the discount of all cash flows for the holding period and the reversion. The reversion is achieved in the last month of the hold period and is calculated by summing the NOI for the year after the hold period and dividing that by the exit cap rate.
• After you have the value you can calculate the loan and using your amortization tool, you can calculate the debt service.
• Finally calculate the various metrics that are required.