Real Estate Essentials – Financial Modeling

The Basics

There are four tabs that cover the basics of (1) loan sizing, (2) cap rates, (3) pro forma analysis (calculating levered and unlevered IRR), and a (4) scenario builder.

DOWNLOAD

1. Loan Sizing
   
   The user tests for debt service coverage ratio (DSCR), debt yield (DY), loan-to-value (LTV), and loan-to-cost (LTC).
   
   
2. Cap Rate Assumptions
   
   A capitalization rate is the overall or non-financed return on a real estate investment, akin to the return on total assets in accounting terms. A cap rate is calculated as a mathematical relationship between net operating income and an asset’s value. Most commonly cap rates are extracted from transactions of buyers and sellers competing in a marketplace; but they are related to the current state of capital markets as well as the future growth outlook.
   
   Generally, cap rates are derived from real property sales via the formula:
   Cap Rate = Net Operating Income (NOI) ÷ Property Value.
   
   
3. Pro Forma
   
   Levered IRR uses the cash flows when a property is financed, while unlevered or unleveraged IRR is based on an all cash purchase.
   
   Unlevered IRR is often used for calculating the IRR of a project, because an IRR that is unlevered is only affected by the operating risks of the investment.
   
   On the other hand, levered IRR is influenced by both the operating risks and potential financing risks such as interest rate changes or the lender requirement for an additional down payment if the property is underperforming.
   
   The IRR and MIRR functions in Excel can be used to calculate potential IRRs for investments that are unleveraged and leveraged.
   
   
4. Scenario Builder
   
   It’s essential to understand the relationship between NOI, Capitalization Rate and IRR.
   
   A great way to analyze this relationship is via the use of a data table.