Calculating Cash Flow Before and After Taxes

This tutorial presents the steps for calculating a commercial real estate property’s cash flow before and after taxes. These problems utilize a worksheet structure inspired by the CCIM Institute’s Cash Flow Analysis Worksheet.


About this Guide

The content, format, and problems in this guide derive from coursework designed by Professor Robert J. Nahigian for REALTOR® University’s Master of Real Estate CRIA course series. For more information, inquiries may be directed to the REALTOR® University Library.

The figures in this guide are calculated using Microsoft Excel. Many commercial real estate practitioners use the HP-12C or HP-2B calculator when calculating these formulas. Financial calculators sometimes round numbers in a different manner than Excel, so it is always good to compare numbers in Excel to those calculated using a financial calculator as a means of double checking work. If you do not have either calculator on-hand, check out your smartphone or tablet’s app store, as some app stores offer HP-12C or HP-2B apps.


Disclaimer

The information contained in this guide is intended for informational and educational purposes only, and does not constitute legal, tax, investment or other professional advice. Those using this guide should independently verify all information provided to ensure its accuracy and compliance with applicable law.


Definitions:

  • Net Operating Income (NOI) → “the actual or anticipated net income remaining after all operating expenses are deducted from effective gross income” (The Appraisal Institute, 2008, p. 457). Net Operating Income = Gross Operating Income (aka “effective rental income”) – Operating Expenses
  • Cash flow worksheet → a worksheet design used by commercial real estate practitioners to conduct a cash analysis for a property. This tutorial utilizes CCIM’s Cash Flow Analysis Worksheet (CCIM, 2012).
  • Overall Rate of Return “Cap Rate à “the overall return on a real estate investment […] calculated as a mathematical relationship between NOI and an asset’s value” (Cassidy, 1986, p. 30). (LTV x Loan Constant) + (Equity % x cash-on-cash)
  • Loan-to-value (LTV) ratio → “The ratio between a mortgage loan and the value of the property pledged as security, usually expressed as a percentage” (The Appraisal Institute, 2008, p. 506).
  • Value → Commercial property value is typically determined by dividing NOI by cap rate. “CAP rates are commonly used by investors to estimate value. All you need to come up with the value of the property using this method is the net operating income (usually referred to as NOI), and the prevailing CAP rate. Divide the NOI into the CAP rate and you get an estimated value” (Scanlon, 2006, p. 46).
  • Real Estate Taxable Income → For the purposes of this example, we will take real estate taxable income to mean NOI minus interest, depreciation, and leasing commissions.*
  • Annual Debt Service (ADS) → “The cash that is required for a particular time period to cover the repayment of interest and principal on a debt” (“Debt Service,” n.d.). For this exercise, we calculate the total amount paid toward loan service (principal + interest) in Year 1 of the investment opportunity.
  • Cash Flow Before Taxes → For the purposes of this example, we will take cash flow before taxes to mean NOI minus ADS, fees, and reserves.

 *Please note this example is for learning purposes only. Consult the guidance of a Certified Public Accountant (CPA) when assessing the true taxable income for an income property. Federal, state, and local tax deductions for leasing commissions and real property improvement depreciation change frequently and CPAs provide a reliable source to ensure compliance with the tax code.


Question 1: Develop a cash flow analysis for the scenario below and answer the following questions:

  1.  What is the taxable income?
  2. What is the tax liability?
  3. What is the cash flow before taxes?
  4. What is the cash flow after taxes?

 Details:

  • NOI: $175,800
  • Cap Rate: 5%
  • LTV: 70%
  • Interest Rate: 5%
  • Period:                         25 years
  • Leasing Fees: $ 12,000
  • Depreciation: 80% to improvement over 31 years
  • Funded Reserves: $ 2,200
  • Client’s Tax Bracket: 30%

Process:

  1. Set up a cash flow analysis worksheet in Excel
  2. We suggest using CCIM’s worksheet as the template
  3. Enter the data above into the worksheet (e.g. Interest rate in B12, Loan Term in B14, NOI in B26 and B36, Interest B27, Depreciation B29, Leasing Commissions B32 and B39, Reserves B40, Cap Rate E8
  4. Next we need to determine the property’s value, based off the cap rate and NOI; value is NOI divided by cap rate
    1. In B8 enter =B26/E8
  5. Next we need to calculate the loan amount; find a blank cell (perhaps C11)
    1. Enter in C11 =B8*B11
  6. Next we need to create a loan amortization schedule to determine Annual Debt Service and the total first year’s interest payments
  7. This schedule will be in a different worksheet than our cash analysis worksheet
    1. We suggest using a free online loan amortization calculator such as this one offered by com or use Excel’s Amortization Schedule templates
      1. To create a loan amortization schedule in Excel, go to the “File” menu and select “New”
      2. In the Office.com Templates search box search for amortization (see Appendix A Figure 1 for a visual)
      3. Select one of the amortization schedule templates (see Appendix A Figure 2 for a visual)
      4. In the amortization schedule template, enter the loan amount (number in C11); interest rate, loan period, number of payments per year (usually 12), and loan start date (we’ll use 1/2/2013 for this example)
  8. Next we need to determine the Annual Debt Service (total payments of principal and interest in one year); We see that the total monthly payment for this example is $9,437.26 so in a black Excel cell enter 9,4357.26*12 which is $113,247.14
    1. Enter this number on our cash analysis worksheet B37
  9. Next we need to determine the total interest paid in the first year; interest varies with every loan payment, so total the interest paid in the first year; for this example in the amortization schedule we enter =sum(H13:H24) in F10
    1. Enter this number on our cash analysis worksheet B27
  10. Next we need to calculate the annual amount to be depreciated* during the first year of the investment.
    1. In B29 enter =(B8*0.8)/31
  11. Next we need to calculate Real Estate Taxable Income
    1. In B33 enter =B26-(B27+B29+B32)
  12. Next we need to calculate the tax liability
    1. In B34 type =B33*0.3
  13. Next we need to calculate cash flow before taxes
    1. In B41 enter =B36-SUM(B37:B40)
  14. Next we need to calculate cash flow after taxes
    1. Since the cash flows are negative, we add the tax liability to the cash flow after taxes; but if the cash flow is positive, one would subtract the taxes from the cash flow
    2. In B43 enter =B34+B41

Cash Flow 1

Answers Question 1:

  1. The taxable income is $21,517
  2. The tax liability is $6,455.10
  3. The cash flow before taxes is $48,353
  4. The cash flow after taxes is $41,898

Question 2: Develop a cash flow analysis for the scenario below and answer the following questions:

  1.  What is the taxable income?
  2. What is the cash flow after taxes?

 

Details:

  • Cap Rate: 5%
  • NOI: $222,800
  • LTV: 70%
  • Interest Rate: 8%
  • Loan period: 25 years
  • Depreciation: 80% to improvement over 31 years
  • Funded Reserves: $ 3,500
  • Client’s Tax Bracket: 35%

 

Process:

  1. Set up a cash flow analysis worksheet in Excel
  2. We suggest using CCIM’s worksheet as the template
  3. Enter the data above into the worksheet (e.g. Interest rate in B12, Loan Term in B14, NOI in B26 and B36, Interest B27, Depreciation B29, Leasing Commissions B32 and B39, Reserves B40, Cap Rate E8
  4. Next we need to determine the property’s value, based off the cap rate and NOI; value is NOI divided by cap rate
    1. In B8 enter =B26/E8
  5. Next we need to calculate the loan amount; find a blank cell (perhaps C11)
    1. Enter in C11 =B8*B11
  6. Next we need to create a loan amortization schedule to determine Annual Debt Service and the total first year’s interest payments
  7. This schedule will be in a different worksheet than our cash analysis worksheet
    1. We suggest using a free online loan amortization calculator such as this one offered by com or use Excel’s Amortization Schedule templates
      1. To create a loan amortization schedule in Excel, go to the “File” menu and select “New”
      2. In the Office.com Templates search box search for amortization (see Appendix A Figure 1 for a visual)
      3. Select one of the amortization schedule templates (see Appendix A Figure 2 for a visual)
      4. In the amortization schedule template, enter the loan amount (number in C11); interest rate, loan period, number of payments per year (usually 12), and loan start date (we’ll use 1/2/2013 for this example)
  8. Next we need to determine the Annual Debt Service (total payments of principal and interest in one year); We see that the total monthly payment for this example is $14,433 so in a black Excel cell enter 14433*12 which is $173,196
    1. Enter this number on our cash analysis worksheet B37
  9. Next we need to determine the total interest paid in the first year; interest varies with every loan payment, so total the interest paid in the first year; for this example in the amortization schedule we enter =sum(H13:H24) in F10
    1. Enter this number on our cash analysis worksheet B27
  10. Next we need to calculate the annual amount to be depreciated* during the first year of the investment.
    1. In B29 enter =(B8*0.8)/31
  11. Next we need to calculate Real Estate Taxable Income
    1. In B33 enter =B26-(B27+B29+B32)
  12. Next we need to calculate the tax liability
    1. In B34 type =B33*0.33
  13. Next we need to calculate cash flow before taxes
    1. In B41 enter =B36-SUM(B37:B40)
  14. Next we need to calculate cash flow after taxes
    1. Since the cash flows are negative, we add the tax liability to the cash flow after taxes; but if the cash flow is positive, one would subtract the taxes from the cash flow
    2. In B43 enter =B34+B41

Cash Flow 2

Answer 2:

  1.  The taxable income is $5,743.63
  2. The cash flow after taxes is $44,094

 

Appendix A

 

Cash Flow 3

Figure 1. Locating an amortization template

Cash Flow 4

Figure 2. Selecting an amoritization template


 References

The Appraisal Institute. (2008). The appraisal of real estate (13th ed.). Chicago, IL: The Appraisal Institute.

Cassidy, T. C. (1986). Breaking down the IRR. Commercial Investment Real Estate Journal, 5(2), 26-30.

CCIM. (2012). Cash flow analysis worksheet. Retrieved from http://www.ccim.com/sites/default/files/cash-flow-analysis-worksheet.pdf

Debt service. (n.d.) Investopedia. Retrieved from http://www.investopedia.com/terms/d/debtservice.asp

Nahigian, R. (2013). Commercial real estate investment an analysis (CRIA) 540: Advanced analytics for real estate investments. Chicago, IL: REALTOR® University.

Scanlon, D. (2006). Putting a value on investment real estate. New Hampshire Business Review, 28(27), 46. Retrieved from http://search.proquest.com/docview/208135853?accountid=41020


 © 2014 REALTOR® University. All rights reserved. These documents are intended for the sole use of REALTOR® University faculty and students. No part of these materials may be reproduced, in any form or by any means, without the permission in writing by REALTOR® University.  Contact the REALTOR® University Library to request permission.