All in One questions – MF

[Tucker Wells]

7 years, 9 months ago #2626

Hi Guys – great model! I’m working on a mf dev deal in it and have a couple of questions:

1. Summary Tab – in valuation details i’m getting a message saying “rates are not accurate, set up rate matrix”. i selected my region and then added 100 bps to it. not sure what else i need to do.

2.Permanent Debt tab – cell e12 is in blue font yet has a formula in it. can i manually enter an amt or will it make the model explode? if i leave that formula there, it has the effect of lowering my loan amt as the construction period ends. i have a constr/mini perm loan and i’m trying to have the model reflect that the IO burns off and then we leave that loan in place until we sell. or we may refi, in which case, we’d expect to increase the loan amt and recover some of our equity.

3. Equity CF tab – i know we’ve traded emails abt this so i think i know the answer, but just want to double chk. is there a way here or in one of your waterfall models to show a promote on operating cf in addition to sale proceeds?

4. Sources & uses tab – i am getting add %’s in the loan amts after i hit the set construction loan amt. The %’s shown do not match what i calculate manually.

5. MF – OpStmt tab- i understood that j35 is an optional input tab since it’s orange. but when i enter an amt manually, it still carries out some different values to subsequent years.
Also, on columns j & l i’m getting double dipped on vacancy in that it’s inserting a large figure in j14 and then another vac figure in j25. i can’t figure out how to modify.

Thanks Very much!

[Spencer Burton]

7 years, 9 months ago #3120

I’ll be answering your questions sequentially and in separate responses.

1. Summary Tab – in valuation details i’m getting a message saying “rates are not accurate, set up rate matrix”. i selected my region and then added 100 bps to it. not sure what else i need to do.

The alert: “Rates are not accurate, setup Rate Matrix” that you see on the Summary tab when you open the template file for the first time is meant to alert the user if/when the Rate Matrix has not yet been setup. When the model was built, a dummy rate matrix was created with base cap rates and discount rates that are not correct for each market. It is up to the user to set locations with cap rates and discount rates appropriate for their investment platform. You can learn more about the Rate Matrix and how to set it up here.

With all of that said, it is not required to use the Rate Matrix feature. But if you plan to use the model as a template to assess multiple deals, the Rate Matrix is a great tool to add consistency to your underwriting and speed up the valuation process.

[Spencer Burton]

7 years, 9 months ago #3122

2.Permanent Debt tab – cell e12 is in blue font yet has a formula in it. can i manually enter an amt or will it make the model explode? if i leave that formula there, it has the effect of lowering my loan amt as the construction period ends. i have a constr/mini perm loan and i’m trying to have the model reflect that the IO burns off and then we leave that loan in place until we sell. or we may refi, in which case, we’d expect to increase the loan amt and recover some of our equity.

Blue font cells require user input. That means, the default value in every cell that is blue needs to be assessed by the user and changed accordingly. Values entered into blue font cells generally are hard coded numbers, but not always. Sometimes you may prefer to use a formula in a blue font cell, as with cell E12 of the Perm. Debt tab, to make an input value dynamic to changes to the model. So using the example of the default value used in cell E12 on the Perm. Debt tab of the template file, rather than hard-coding in a loan amount, I wrote a formula such that the loan amount would always be equal to the lesser of 70% of value or 75% of cost. It’s up to you to change that cell, either via a formula or by hard-coding in a loan amount, to match your situation.

[Spencer Burton]

7 years, 9 months ago #3124

3. Equity CF tab – Is there a way here or in one of your waterfall models to show a promote on operating CF in addition to sale proceeds?

The model does not currently differentiate between operation cash flows and residual cash flows when modeling the partnership waterfall. I’ve added this concept to the feature request list for inclusion in a future release.

[Spencer Burton]

7 years, 9 months ago #3126

4. Sources & uses tab – i am getting add %’s in the loan amts after i hit the set construction loan amt. The %’s shown do not match what i calculate manually.

I don’t entirely follow. Could you email me your model and I’ll take a look?

[Spencer Burton]

7 years, 9 months ago #3128

5. MF – OpStmt tab- i understood that j35 is an optional input tab since it’s orange. but when i enter an amt manually, it still carries out some different values to subsequent years. Also, on columns j & l i’m getting double dipped on vacancy in that it’s inserting a large figure in j14 and then another vac figure in j25. i can’t figure out how to modify.

These are very good questions, and get at some important nuances of the model. Let me break them down by explaining what’s happening with 1) Downtime Vacancy / Loss-to-Market (Row 14), 2) General Vacancy (Row 24), and 3) Make Ready Costs (Row 35). Then, I’ll explain why the values in cells K35:T35 don’t change when you optionally change the Pro Forma Make Ready cost in cell J35.

1) Gross Potential Rent is the gross market rent as if all available units were leased at market rates. Row 14 on the MF-OpSt tab then adjusts that Gross Potential rent to account for Downtime Vacancy (based on the Downtime assumption in days set in column AR of the MF-RR tab) and Loss-to-Lease (the difference between market rent and actual rent as modeled in columns M:AE on the MF-RR tab ). Downtime Vacancy / Loss-to-Lease are common adjustments used in multifamily underwriting to help understand the difference between actual rent collection (Total Rental Revenue Row 15) and potential rent collection (Gross Potential Rent Row 12).

2) Total Rental Revenue and Total Other Income are gross income values (Row 23: Total Potential Gross Income). They assume that the property is 100% occupied – even if certain adjustments for downtime, loss-to-market, and concessions have been made. They also don’t take into account credit loss. Thus, General Vacancy is what you assume on average vacancy plus credit loss will be at the property over the hold period.

3) When a tenant’s lease expires, one of two things can happen. Either the tenant renews, or the tenant vacates. If a tenant renews, the tenant is less likely to demand concessions and the unit will require less investment to make the unit ready for the next lease. In contrast, if the tenant vacates, a new tenant may require concessions and an investment will need to be made to get the unit physically ready for a new tenant. ‘Make Ready’ is the cost to bring a unit up to physical condition for a new lease. The Make Ready assumption for each scenario is made in columns Al and AM of the MF-RR together with the renewal probability assumption in column AQ. The model then calculates the number of units expiring each year, the blended cost to ‘Make those Units Ready to Lease’, grows that value by the annual expense growth rate, and reports that amount in rows 82 and 35 of the MF-OpSt tab. Note: a similar thing occurs with Concessions in Row 13 of the MF-OpSt tab, driven by assumptions made in columns AN and AO of the MF-RR tab).

So finally, why do the values in columns K:T of the MF-OpSt tab not change when you change the optional/orange inputs in column J (such as Make Ready J35)? To understand this, you must understand the Stabilized Pro Forma (column J) and the Residual Pro Forma (Column U) concept.

For valuation purposes, the model uses a direct cap method whereby some stabilized or residual net operating income (or CFO) is divided by an assumed market cap rate to arrive at a value. At analysis start, this value is calculated by taking the stabilized net operating income found in cell J40 of the MF-OpSt tab and dividing it by the Adjusted Cap Rate in cell L25 of the Summary tab. Likewise for residual (i,e, sale) valu, the residual net operating income found in cell U40 is divided by the adjusted cap rate grown by the annual change in cap rate as input in cell L26 of the Summary tab.

The stabilized and residual pro formas are NOT part of the net cash flow of the property – that’s what is occurring in columns K:T. They are purely used for valuation purposes. Thus, you’re given the option to adjust certain values in the pro formas (such as Concessions and Make Ready) that don’t impact the operating cash flow, but insures that the pro formas are representative of a typical year. This is more important with the office/retail/industrial tenants, as leasing costs can be quite sporadic requiring an adjustment to the pro forma to smooth out the noise.