Reference no: EM133055835

An investment group is considering the purchase of an income-property for $30 million. First year expected unlevered cash flow (PBTCF) is $1.8 million and this is expected to grow by 2% per year over the next 11 years and beyond. In valuing the property using a 10-year pro forma DCF analysis, a reversionary or terminal cap rate of 6.45% was applied to year 11 PBTCF. Don't worry about selling costs for this exercise. The acquisition is being financed in part with a 60% LTV, interest-only mortgage with a 4% annual mortgage rate and annual debt service payments. 

Setup the 10-year pro forma in Excel, showing both property level and equity level numbers. Do this in such a way that the calculation of the annual debt service (DS), and therefore projected equity cash flows, is flexible enough to allow you to change the LTV, and hence initial loan and equity values (i.e. as a simple financial model with key inputs that can change and a DCF model that evaluates the inputs. That is, make the DS a formula make this equal to LOAN*MR where you have the LOAN and the mortgage interest rate (MR) entered into cells above the pro forma. You also need to make the "sale proceeds" part of the year 10 Equity CF handle a changing loan amount (i.e. the loan balance that is repaid at time of sale). Then answer the following questions: 

(a) Use the =IRR( ) function to calculate expected total returns at both the property and equity investor levels.

(b) Assume that the equity investor is a private equity fund that has "promised" to provide investors an expected total return on equity invested of 17%. By changing the loan amount in your spreadsheet (i.e. using trial and error or solver, etc.), and assuming the mortgage rate does not change, determine the size of the loan and LTV that is required to produce an expected IRR of 17% on equity cash flows.

(c) Suppose the investor was able to obtain a 90% LTV ratio loan. What is the expected IRR on equity cash flows? (again, assuming the mortgage rate is unchanged; an unrealistic assumption we will fix in 2. below).

(d) Create two scatterplot charts that plot the equity IRRs versus (i) the L/E and (ii) the LTV for the 3 different leverage situations in parts (a) through (c) - and feel free to add additional data points. Create two separate charts. Think about what you see and why I might have asked you to do this.