I need help completing a real estate homework assignment, wherein you are building out a full Free Cash Flow statement for the property and analyzing the profitability of your investment based on the assumptions/inputs given.

Attached is the Excel template you are to use for the assignment. In addition to working with this template, you are to create a word document detailing: 1) What methods you used to complete this assignment 2) Why you used these methods to complete the assignment 3) What challenges you encountered with the assignment

INSTRUCTIONS FOR THE ASSIGNMENT ARE BELOW. A COPY OF THESE INSTRUCTIONS IS ALSO ATTACHED IN A WORD DOCUMENT AS WELL.

Three tenants occupy an office building with square footage occupied and current rents per square foot as shown in template. Expenses are provided for the previous year as a baseline in Column G, starting row 25 in yellow. Your overall task is to build out a full Free Cash Flow statement for the property and analyze the profitability of your investment based on the assumptions/inputs given. Ignore taxes for this exercise.

Revenues:

Current rents should be your Year 1 Rent revenues which then increase by year for each tenant by the increases provided in column H – note that the annual increases are applied to the previous year’s rent, so rents compound over time.

Other income – current parking and cell tower revenue given which should also be your Year 1 revenues, these both increase by 3% per year as shown also on a compounding basis.

Expense recovery – tenants collectively share a portion of operating expenses each year as shown (25%), not including the Capex Budget. However, the amount they pay is based on the prior year’s expenses and each tenant pays pro rata based on their space occupied as a percent of total space (column E). Ex., Year 2 Expense recovery revenue is 25% of Year 1 total operating expenses incurred excluding Capex.

Expenses:

Prior year expenses shown in Column G in yellow, these all (including Capex budget) increase at the same rate by the given growth rate in the assumptions. Year 1 expenses will be Prior Year expenses increased by the annual increase given. To be clear on Expense recovery above, in Year 1 tenants will collectively pay 25% of the $3.275 million shown (Exclude Capex) – you don’t have to calculate each expense for each tenant, just use the total expenses.

Financing:

Using the cap rate given, Year 1 NOI will determine the initial property value. The debt coverage ratio (DCR) shown should be used to determine how much debt service can be supported – this in turn determines how much of a loan may be taken.

The loan will be interest only, so as reminder the maximum debt service you compute above will be for just the interest on the loan with no principal paydown. HINT: By using the maximum debt service figure and the the loan interest rate shown, you will be able to determine the mortgage amount. The different between the property value and this loan amount is your equity investment.

Complete the spreadsheet for the five years shown for all the line items given to solve for items highlighted in blue:

Terminal Value – using Year 5 NOI and the perpetuity growth method. Long term growth rate and discount rate provided.

NPV – using discount rate provided

IRR – using cash flows you determined and initial investment

HINTS: remember to factor in paying off the mortgage balance (no amortization) in year 5 after determining your Terminal Value. Your Year 5 flow from the sale should be just the net after paying off mortgage. For NPV and IRR’s your Year 0 flow is your equity investment.

Extra Credit: Based on the above OPEX sharing described, how much would Tenant 3 have paid in year 4 for their share of operating expenses.