Introduction to the rDCF Underwriting Workbook

rDCF Underwriting Workbook

Article Contents:

• Introduction
• Excel Model Design Assumptions
• How to Generate the Underwriting Workbook from rDCF
• Valuation Settings in rDCF

Note: A PDF version of this guide is attached at the bottom of this article for download. 

Introduction

The rDCF Underwriting Workbook (“UW”) is a suite of reports generated from rDCF into a single Microsoft Excel file. Unlike individually generated rDCF reports these reports work in concert with each other and additional tabs in the Underwriting Workbook to provide the user with a fully functional underwriting tool that can be used to model property and portfolio valuations, debt financing and waterfall scenarios. The Underwriting Workbook enhances productivity for buyers, lenders and asset managers, while evaluating deals faster and producing company-wide valuation and underwriting standards, and high-level metrics at the push of a button.

Powerful, innovative and versatile, the underwriting workbook enables rDCF users to:

• Conduct advanced, sophisticated analyses of their data to uncover value and opportunities
• Generate and analyze essential property or portfolio-level data, including in-place revenue and occupancy costs, contractual and market rents and key market assumptions
• Quickly and easily produce professional, presentation-ready reports

rDCF’s Underwriting Workbook can also be used to:

• Build and review multiple debt tranches for either leveraged or unleveraged returns
• Model sophisticated equity structures, including multi-tiered waterfalls and fees

In conjunction with rDCF, you can rely on the rDCF Underwriting Workbook to leverage internal resources, increase overall efficiency and manage production volume to meet tight deadlines.

Excel Model Design Assumptions

The Underwriting Workbook has no hidden fields or formulas to provide absolute transparency into the calculations. There are no locked cells or fields. The model uses complex Excel functions and formulas. Because of this, it is important, as a user of the model, to be aware of your actions within the workbook. Below are recommendations for using the workbook features.

1. Do not change data in any cells except for Blue cells on Blue tabs.
2. If you click into a cell to trace the formula, always exit the cell via Esc button.
3. Formulas that contain the “Offset” function are not traceable using Excel trace features.
4. Braces {} (“mustaches”) in the formula indicate an array formula – if you click into the cell, do not exit the cell using the “Enter” key, it will break the formula. If you accidentally exit using “Enter”, re-enter the cell and click Ctrl-Shift-Enter to resolve the error.
   Note: You will see this in the Valuation Matrix on the Unleveraged Cash Flow tab.
5. You cannot import the UW into rDCF.
6. Without the "Source Workbook" macro, you cannot automatically transfer assumptions from one workbook into another.

How to Generate the Underwriting Workbook from rDCF

The Underwriting Workbook can be generated from the Header Menu, Folder Section (Underwriting Workbook tab), and the Property Section (Property Settings tab) after calculating the properties. When run from the Folder section, the user can select to include all properties in the Folder or select properties. The selected properties will be treated as a consolidated portfolio within the one Folder Underwriting Workbook.

Look for the reporting icon to identify where you can run the Underwriting Workbook. Note that on the Navigation Panel this icon represents the Reports Section.

Folder Section > Underwriting Workbook tab: generate one underwriting workbook for multiple properties

Note: all selected properties must be calculated in order to run the Underwriting Workbook.

Property Section > Property Settings tab: generate individual property underwriting workbook

When you initiate the workbook, the file will appear on your rDCF Dashboard and be available for download. The Dashboard will contain your recently generated Underwriting Workbooks, available for download. You will also receive an email to your registered email address with an attachment.

For more information about generating a Folder Underwriting Workbook, click here.

For more information about generating an Underwriting Workbook via the Property Settings tab, click here. 

Valuation Settings in rDCF

When you generate the Underwriting Workbook from rDCF, the workbook default settings are based on user-defined inputs in the Property Section on the Valuation Settings tab.

The Valuation Settings tab allows the user to define the property’s valuation parameters for up to five (5) scenarios simultaneously. If more than one (1) valuation scenario exists, the user selects one scenario to be the “default” scenario. The default scenario is used in the Underwriting Workbook when run from the Header Menu or the Folder Underwriting Workbook tab. When running the Underwriting Workbook from the Property Settings tab using the  icon, you have the option to select any scenario.

By default, the Valuation Settings tab has one valuation scenario (“Scenario #1”). Scenario #1 is also the Default Scenario when there are two or more scenarios. These two fields function as follows:

# of Valuation Scenarios defines the number of scenarios for which unique valuation parameters may be entered and property valuations calculated. You can select between one (1) and five (5) scenarios.

When you add a scenario, the new scenario(s) will duplicate the one immediately before it. For example, if the # of Valuation Scenarios is initially set to “2” and then subsequently changed to “4”, the initial settings for Scenario #3 and Scenario #4 will match Scenario #2. You may edit any of the scenarios as desired.

Default Scenario # reflects which of the available scenarios will be the “default” scenario. The default scenario defines the valuation parameters that will be used when a Property Underwriting Workbook is generated.

Valuation Settings Scenario Fields

Valuation Scenario Fields	Default Value	Description
Scenario Description	Primary Valuation	Descriptive name for the Valuation Scenario (i.e., “As-Is”, “As-Built”, “As-Stabilized”). The default scenario description will appear in the Folder Section Underwriting Workbook tab in the Scenario column. It will also appear as the Scenario Name in the Underwriting Workbook when generated.
Valuation Type	DCF	Three Valuation Types: DCF – discounted cash flow Direct Cap – direct cap of Year 1 NOI – Note: you cannot generate an Underwriting Workbook for a Direct Cap Valuation Scenario. Specified Price – enter Year 1 Purchase Price. Used in conjunction with the Underwriting Workbook For DCF and Specified Price, the parameters entered on the Valuation Settings tab will be passed to the Underwriting Workbook for the Default Scenario. If the Default Scenario is a Direct Cap, no Underwriting workbook will be generated.
Valuation Date Type	Report Begin	Defines the date of valuation (i.e., time period “0” for IRR calculation purposes). Analysis Begin Date = defined on the Property Settings tab Report Begin Date = defined on the Property Settings tab Specified Date = Allows user to enter a date in the Valuation Date field.
Valuation Date	N/A	User-defined Valuation begin date when “Specified Date” is selected as the Valuation Date Type. The date entered must be equal to or after the Analysis Begin Date. Note: this field is only editable when the Valuation Date Type = “Specified Date”
Specified Price	Blank	User-defined value, typically an agreed-upon purchase price. Note: this field is only editable when the Valuation Type = “Specified Price”
Going-In Capitalization Rate	10	Also known as the direct capitalization rate, this is the cap rate that is applied to the year 1 Net Operating Income (after the defined valuation date) to determine a Direct Cap value. Note: this field is only editable when the Valuation Type=“Direct Cap”
Valuation Term (Years)	10	Defines the hold period for which property cash flows will be discounted in the calculation of the property’s value. The Valuation Term cannot be longer than the rDCF term entered on the Property Settings tab in the Property section. Note: Not available when Valuation Type="Direct Cap"
Discount Rate	10	Rate of return used to determine the present value of future cash flows Note: Not available when Valuation Type="Direct Cap" or "Specified Price"
Discount Rate Increment	0.5	Establishes the percentage increments used in the Underwriting Workbook’s Valuation Matrix for the selected Discount Rate. Note: Not available when Valuation Type="Direct Cap" or "Specified Price"
Terminal Capitalization Rate	10	Known also as the Resale, Reversionary or Residual cap rate, this rate is used to estimate the resale value (or Sale Price) at the end of the valuation term/hold period. Formula:  Sale Price = Reversion Cap Year NOI / Terminal Cap Rate Note: Not available when Valuation Type="Direct Cap"
Capitalization Rate Increment	0.5	Establishes the increments used in the Underwriting Workbook’s Valuation Matrix for the selected Terminal Cap Rate. Note: Not available when Valuation Type="Direct Cap"
Cost of Sale %	0	Defines the percentage by which the sale price of the property at the end of the Valuation Term will be reduced to reflect sale costs (i.e., broker fees, closing costs, etc.) Note: Not available when Valuation Type="Direct Cap"
IRR/NPV Discounting	Annual	Indicates calculation method to be used for IRR / present value calculation. Choose from Monthly or Annual Note: Monthly discounting will typically generate a higher property value as cash flows are assumed to be received throughout each year rather than at the end of each year. Note: Not available when Valuation Type="Direct Cap"
Reversion Cap Year	Sale Year+1	Sale Year+1 caps NOI for the year immediately following the Sale year to determine sale price. Helpful Hint: “Sale Year + 1” is the default because when a property is sold, the price paid by the next investor is based upon an assessment of income for his/her expected period of ownership. Therefore, for the next investor, or potential buyer, the net operating income for his/her first year of ownership will be the year after we sell the property. This will be the first year of his/her investment. Sale Year caps NOI for the Sale year to determine the sale price. Helpful Hint: “Sale Year” should only be used if the net operating income for “Sale Year + 1” does not reflect stabilized occupancy and you either (i) don’t want to use the “Stabilize Reversion Vacancy Loss” option (see below) in the Valuation Settings; or (ii) don’t want to change your Valuation Term such that “Sale Year + 1” will reflect a stabilized net operating income. Note: Not available when Valuation Type="Direct Cap"
Abatement Gross-up	No	Provides an option to add abatements (i.e., free rent) back into net operating income for the Reversion Cap Year. No: No adjustment is made to net operating income for the Reversion Cap Year. Yes: Abatements are added back to net operating income for the Reversion Cap Year. After capitalizing the adjusted net operating income, the resulting sale price is then reduced by the abatements that were added back to net operating income. The theory is that the seller is offering the buyer a sale price reduction (i.e., credit) for abatements during the buyer’s first year of ownership.
Stabilize Sale Vacancy Loss	No	Provides an option to add back a portion of the vacancy allowance for a property (reflecting a combination of both General Vacancy Loss and Absorption & Downtime Vacancy) in order to reflect a stabilized vacancy loss in net operating income for the Reversion Cap Year. No: No adjustment is made to net operating income for the Reversion Cap Year. Yes: If necessary, an adjustment is made to net operating income for the Reversion Cap Year to reflect a stabilized vacancy loss. The difference between (i) General Vacancy Loss plus Absorption and Downtime Vacancy; and (ii) Stabilized Vacancy Loss is added back to net operating income for the Reversion Cap Year. If the General Vacancy Loss plus Absorption & Downtime Vacancy is equal to the Stabilized Vacancy Loss, then no adjustment is made. Helpful Hint: When running the Valuation report, there is a supporting tab entitled “Vacancy Loss Schedule” that provides a detailed calculation of the amount of vacancy loss added back to Reversion Cap Year net operating income in order to reflect a stabilized vacancy loss.
Capital Items Adjustment	No	Yes: Reversion sale price at the end of the Hold Period is reduced by Capital Expenses for the year following the Sale Date. No: No Capital Adjustment Example: In a 10-year Cash Flow with a sale in Year 10, including capital items reduces the Sale Price by Year 11 Capital. This reflects costs that the next buyer will pay if they purchase the building.
$ Adjustment Amount	0	Provides an option to adjust the calculated direct cap value or the reversion sale price upward or downward in an amount specified by the user. For example, if a parcel of land is to be valued or sold as part of the valuation of an income producing property, enter the value of the land parcel in this field and it will be added to the direct cap value or the reversion sale price of the entire property.
Valuation Amt	N/A	Non-editable field that displays the last calculated value for each Valuation Scenario
Valuation Per Area	N/A	Non-editable field that displays the last calculated value per area for each Valuation Scenario

As noted, the default scenario settings will be used to generate the Underwriting Workbook and subsequent calculations; however, once in the Underwriting Workbook, these fields can be modified.

Note: in a Single Property Underwriting Workbook, the valuation calculation uses the default scenario from the Property Section>Valuation Settings tab and, therefore, will yield the same Valuation Amount as is displayed on that tab and in the rDCF Valuation Report.

In a Folder Underwriting Workbook, the valuation amounts could differ from the Property Section valuations due to the standardization of certain settings required by a Folder Underwriting Workbook (such as Begin Date, Hold Period, Adjustment for Capital Items, etc.).