The Forecasts Tab is where the magic happens. Most of the spreadsheet calculations are made in this tab, which will source all the other tabs. This tab is split into 5 sections:
Operating Cash Flow
This section calculates your annual unlevered cash flow based on the operating assumptions filed in the Dashboard tab previously. Starting with total revenue, the spreadsheet deduces all operating costs to obtain Net Operating Income (NOI), then deducing capex and purchase / sale items to obtain cash flow. Note the distinction between unlevered cash flow and levered cash flow:
- Unlevered cash flow is the cash flow your property would produce if you were to purchase it with cash only. In this situation, the cash outflow at purchase is the total property value (100% down payment, no debt financing), and the cash flow is calculated before payment of interest or loan insurance
- Levered cash flow is the cash flow your property actually produces based on the financing structure you selected (i.e. post payment of interest, etc.). See section below about Financing Cash Flow
Financing Cash Flow
This section calculates levered cash flow be deducing principal repayment, interest expense and loan insurance (if any). This annual figure gives you the real cash flow produced by the property each year. Note that the information presented in this section is sourced from the Debt tab.
Summary Return
This section calculates key financial ratios, both on a unlevered and levered basis, including:
- Internal Rate of Return (IRR): probably one of the most useful and most widely looked at ratio in the financial industry
- Modified Internal Rate of Return (MIRR): an alternative to IRR, allowing you to adjust the reinvestment rate over the project life
- Equity Multiple (EMx): metric calculated simply by dividing total profit plus equity invested by equity invested
Property Value
This section tracks your property value over the years, using either the appreciation method or cap rate method (as selected in the Dashboard article). It also calculates debt ratios such as:
- Loan to Value (LTV): used by lenders to express the ratio of a loan to the value of an asset purchased. The term is commonly used by banks and building societies to represent the ratio of the first mortgage line as a percentage of the total appraised value of real property. Generally, lenders will accept to lend up to an LTV of 80%. Below that, loan insurance will usually be required.
- Debt Service Coverage Ratio (DSCR): defined as the recurring unlevered cash flow divided by the debt service (i.e. Principal repayment, Interest expense, Loan insurance payment). It is a popular benchmark used in the measurement of an entity’s ability to produce enough cash to cover its debt payments. A ratio below 1x means your property can’t cover the cost of debt associated with it. Lenders will usually require DSCR to be at least 1.2x in order to build in additional cushion
- Break-Even Ratio: Used in evaluating the financial performance of an income property to determine what rate of occupancy or level of rent is required to meet both operating expense and mortgage payments. As a general rule of thumb, lenders will look for a break even ratio of 85% or less.
For more information about financial ratios, click here.
Return Analysis
This section tracks certain financial ratios over time, including:
- Purchase Capitalization Rate (PCR): Calculated as NOI divided by After-Repair Value at closing. This ratio will change in line with your property’s NOI. A growing NOI will lead to a higher PCR; a declining NOI will translate into a lower PCR
- Adjusted Purchase Capitalization Rate (APCR): Calculated as (NOI minus CAPEX) divided by After-Repair Value at closing. Certain people prefer looking at the APCR in lieu of the PCR to have a more complete picture. Indeed, if the property you purchase is already quite old, you may face higher capex in the future. Those capex would not be taken into account in the PCR calculation, biasing the analysis. This is not the case with the APCR
- Resale Capitalization Rate (RCR): Calculated as NOI divided by Current Property Value. If you selected Cap Rate as your valuation method, this ratio should remain stable over the period. However, if you selected the appreciation method, this ratio may fluctuate depending on the changes of your NOI in relation to your property value.
- Adjusted Resale Capitalization Rate (ARCR): Calculated as (NOI minus CAPEX) dividend by Current Property Value.
- Gross Rent Multiplier: Calculated as Property Value divided by Gross Scheduled Income
- Unlevered Cash on Cash Return (UCOC): Calculated as Unlevered Cash Flow divided by Total Project Cost (i.e. excluding any debt funding)
- Levered Cash on Cash Return (LCOC): Calculated as Levered Cash Flow divided by Cash Needed (i.e. actual cash put into the property
The spreadsheet also calculates what is called “Total Return” for illustrative purpose. Total Return takes into account all sources of return generated each year, including cash flow, debt principal repayment and implied property appreciation. Although this ratio usually results in high return on cash invested, it is based on simplistic calculations that don’t consider the time value of money and therefore overestimate real return, so use it with caution and do not base your investment strategy upon this ratio only.
- Why you should not rely on Total Return only: Total Return is composed of three sources of return, although only one of them, cash flow, is being captured each year. Indeed, both principal repayment and property appreciation are non-cash items that will be captured only when you sell your property. Because one dollar made today is worth more than a dollar made tomorrow, imagine a dollar made in 30 years! Overall, this calculation overestimates the actual return by not taking into account the time value of money. So don’t be surprise next time you compare this figure with your actual project’s IRR.
