The following data pertains to a commercial investment property that you are considering as an investment. Create two spreadsheets (SHEET 1 before tax; SHEET 2 after tax). Include an executive summary (or two) that reports the information you choose to report (key inputs and outputs) and not all of the numbers. I suggest starting with the BTCF model template, master it, then copy it to sheet 2 and add the tax analysis.

I. Financing Information: I suggest you create an annual amortization schedule.

Interest Rate............ 8.0% Monthly payments

Term (in years)....... 20 Percent Financed.... 60%

II. Income and Expense Information

Square Footage 300,000 Current (going-in) Cap Rate 9.0%

Operating Expense Ratio (% of EGI) 35 % Year 10 (going out) Cap Rate 9.5%

Rent: YR1, $15 per square foot; Growth rate 2.0% per year; last year of rent growth: 5

Vacancy Rate: 10%.

Land Value as Percentage of Purchase Price 25% Marginal Income Tax Rate 30%

Depreciation Recapture Tax Rate 25% Capital Gains Tax Rate 15%

The initial value/purchase price is calculated as NOI₁ / R₀. This will feed into the loan. If you have a circular logic issue, convert the initial value to a number (hard code).

Assume a 10-year holding period with the selling price assumed as the 11^th year NOI capitalized at the going out cap rate. Selling expenses will be 7% of the projected selling price. All of the space will earn the market rent. Ignore all other transactions costs.

1) SHEET 1: Calculate the projected BTCFs, BTER, BTIRR and BTNPV (choose your own discount rate as an input). SHEET 2: Calculate the projected ATCFs, ATER, ATIRR and ATNPV. Summarize each in an executive summary.

2) For year 1 only: Calculate the Debt Coverage Ratio, Break-even Vacancy Rate and Cash-on-cash Return (BTCF/Equity). Include in the executive summary.

*** Use an IF statement to reduce rent growth to zero after a specified month

** Use an IF statement in the depreciation formula to enter the correct denominator. Do not worry with the half-month convention on the first and last year. But, you may use it if you want.

** Use conditional formatting so that a negative NPV appears as RED and a positive NPV appears as GREEN. Do the same for your BTCFs [negative RED, positive GREEN] and so that an IRRk is green.

Have an input section, a working section and an output section/executive summary. There should be no (zero) hard-coded numbers beyond the input section except the number 12. That is, if this sheet works correctly, any changes to the inputs will be accurately reflected in the working area and the executive summary without having to edit a formula.

PLEASE SHOW ALL THE FORMULAS YOU USED!