For the final project you will need you to create a spreadsheet /proforma of the cash flows from a property. This is not something we did in class but we discussed each part. This will involve you doing something you haven't done before so ask questions and I will post questions and answers. This could take you maybe 4 hours, which I believe is less time than you would spend trying to study for a final. There is no magic. All you have to do is layout the cash flows on a monthly basis. Each day late will be a deduction of 3 points. No excuses, no exceptions. If you are late, I will give you an incomplete for the semester and mark you grade at my convenience with points deducted for tardiness. This assignment is to create an analysis for purchasing a property, projecting rents, expense pass throughs, expenses, capital over a holding period through the sale. Below are is all the information that you need to do the calculations and determine the metrics behind the investment. Market rent $32/sf for gross leases $22/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 25,000 $33.00 BY $9.40/sf 1 10 yrs 3%/year Tenant B 30,000 $29.00 BY $8.50/sf 1 8 yrs $34/sf in yr 3 Tenant C 35,000 $22.00 Net 1 3 yrs N/A Tenant 10,000 $30.00 BY $10.00/sf 13 10 yrs $36/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 Tenants A, B and D are modified gross with expenses passed through over a base year while tenant C is Net. Vacancy/Credit Loss 5% of PGI Expense Category Expense per foot Taxes $ 5.00/sf R&M $ 1.00/sf Insurance $ 0.25/sf Utilities $ 2.00/sf Payroll $ 0.85/sf Security $ 0.50/sf G&A $ 0.40/sf Total Reimbursable $10.00/sf Non Reimbursable Management 3% of EGI Assume zero rollover possibility. Downtime (vacancy at end of lease) 6 months Total Capital at rollover 25/sf growing at inflation From the above information, you should be able to create a proforma. Reversion 7.5% Discount Rate 10.0% Term 5 years Amortization 30 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 Start by creating a column for each month of the hold period and additional 12 months for the reveersion 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/19 seems reasonable. Then for each lease put in the monthly rent that is due for each month. Add a line for the sum. Then put in the pass through that is due in each month for each tenant. Add a line for the sum of the pass throughs. Calculate andy other income by month. Apply the vacancy for the property Calculate EGI. Enter in each column the appropriate expense for each line for each column. Sum the expenses Subtract the expenses from the EGI.