{"id":36689,"date":"2013-01-28T14:07:09","date_gmt":"2013-01-28T21:07:09","guid":{"rendered":"https:\/\/www.biggerpockets.com\/renewsblog\/?p=36689"},"modified":"2021-03-16T09:59:12","modified_gmt":"2021-03-16T15:59:12","slug":"2013-01-28-discounted-cash-flow-analysis","status":"publish","type":"post","link":"https:\/\/www.biggerpockets.com\/blog\/2013-01-28-discounted-cash-flow-analysis","title":{"rendered":"Discounted Cash Flow Analysis 101"},"content":{"rendered":"

As Ali Boone’s article titled Rental Property Numbers so Easy that You Can Calculate Them on Napkin<\/a> pointed out, “In this industry, you must love the numbers. Love them like they are part of you. For good or for bad, \u2018til death do you part, never leave the numbers.”<\/p>\n

Knowing how to quickly analyze deals to ascertain their cash flow is the key skill set needed by an income oriented real estate investor. My goal with this article is to build on top of the great foundation laid by Ali to show you to how build out a Discount Cash Flow Analysis Model.<\/p>\n

What is a Discounted Cash Flow Model?<\/h2>\n

A discounted cash flow\u00a0(DCF) analysis is a method of valuing an asset\u00a0using the concepts of the\u00a0time value of money. All future\u00a0cash flows\u00a0are estimated and discounted to give their present value \u00a0 (PVs)\u00a0\u2014 the sum of all future cash flows, both incoming and outgoing, is the\u00a0net present value\u00a0(NPV), which is taken as the value or price of the cash flows in question.\u00a0Simplifying\u00a0that cumbersome definition is that\u00a0DCF analysis helps determine the value of an item\/asset\/company based on discounting back to today the cash flow that this asset can produce during your target holding period.<\/p>\n

What You Need to Build a DCF Model?<\/h2>\n

You will need two things to build your very own DCF model:<\/p>\n

    \n
  1. You will need access to a spreadsheet program. I utilize Microsoft Excel but if you do not want to pay for that program then you get a great proxy of that program at Open Office<\/a> or through Google Docs<\/a>.<\/li>\n
  2. You are going to need data on the items that Ali wrote about in her past article<\/a> relating to the income and the expense categories. So have those number handy and close by.<\/li>\n<\/ol>\n

    How to Build your Own DCF Model?<\/span><\/h3>\n

    Now that you have the two items needed to build your model. Lets start building this model from scratch:<\/span><\/p>\n

    Step I- Open a New Spreadsheet Document<\/p>\n

    Tip: Name and Save the spreadsheet upfront as the last thing you want to do is loose your work incase your close out or power goes from your laptop. It has happened to me so learn from my lessons.<\/p>\n

    Step II- There are two components to a DCF calculation: The Cash Flows and the Discount Rate.<\/p>\n

    Cash Flows: In our case will be net cash remaining after all expenses associated with the property are paid off so basically any cash flow that you as the equity investor will receive.<\/p>\n

    Discount Rate: The required rate of return that you demand on your money. Remember: we advised you to include this rate of return in your investment plan as well. It will all come together. So time to take that number of the out of the plan and input into your DCF model! <\/i><\/p>\n

    These two components when used together will help determine the Present Value (PV) of your next investment asset.<\/p>\n

    Open the blank spreadsheet and enter \u201cyears\u201d in Cell A2 and the numbers 1-5 in cells\u00a0B2:F2<\/b>\u00a0to represent the year numbers for our worksheet as per the example below:<\/p>\n

    \"Image<\/p>\n

    Next we are going to enter the first year Net Operating Income (NOI)<\/p>\n

    Assumption:<\/span> The first year net cash flow is $10,000; Enter that amount in cell B3.<\/p>\n

    The next part of a DCF Analysis is to project the growth of income and expense over the horizon of your holding period. For the sake of simplicity we will be using just the NOI t is normal to use 3% for income growth rate year over year but make sure that the growth rate is feasible within your rental market. Enter 3% in cell B5 and lets grow our cash flow by 3% each year. Enter the following formula<\/p>\n

    =B3*(1+$B$5) into Cell C3 to grow the cash flow by 3% from the prior year and then click the outside right part of the cell and drag it across the columns until F3.<\/p>\n

    \"Image<\/p>\n

    Next we will put the labels Cash Flow and Discount Factor in cells\u00a0A3:A4<\/strong>\u00a0respectively as per the example below:<\/p>\n

    \"Image<\/p>\n

    Next, in cell\u00a0B5<\/strong>\u00a0input the rate at which you wish to discount the future cash flow back to today\u2019s dollars.<\/p>\n

    Discount Rate percentage can simply be what you want to make per annum or you can use the following formula as guide post in helping you ascertain the target risk-free rate:<\/p>\n

    Risk Free Rate (10 Year Treasury Rate)<\/p>\n

    + Opportunity Cost (5 Year CD Rate or S&P500 Dividend Yield)<\/p>\n

    + Liquidity Premium (Real Estate is a illiquid asset so charge a premium for the risk)<\/p>\n

    + Investor Return Percentage (What is the annual return that you wish to make)<\/p>\n

    \n

    = Your Personalized Discount Rate Percentage<\/p>\n

    **My discount rate analysis is indicated in the excel snapshot below:<\/p>\n<\/div>\n

    \"Image<\/p>\n

    Next we need to calculate what is the Net Value of the asset’s future discounted cash flows. For that you use a formula called Net Present Value (NPV).<\/p>\n

    Net Present Value compares the value of a dollar today to the value of that same dollar in the future, taking inflation and returns into account. If the NPV of a prospective project is positive, it should be accepted.<\/p>\n

    You can use the formula below to both discount the cash flow and calculate the NPV value of your asset:<\/span><\/p>\n

    =NPV(B4, B3;F3)<\/p>\n

    Lastly the enter the heading NPV into cell A6, then go to cell\u00a0B6<\/strong>\u00a0and enter the formula shown above.<\/p>\n

    So was your NPV positive or negative?<\/p>\n

    \"Image<\/a>
    \nIn our example above the NPV was positive which means that the project should be accepted as it will produce positive cash flows based on your personalized discount rate. \u00a0In addition, this\u00a0analysis indicates that the value of $53,092 of future cash flow is $41,542 today. That is time value of money folks as a dollar tomorrow is worth less than a dollar today!<\/p>\n

    Disclaimer: This is a very basic DCF model that ignores capital repairs, income changes, expense changes. I wanted to put this post together to just help new investors get a good grab on what a DCF model is all about and how they can use the tool to make income investment decision.<\/p>\n

    Build your very own DCF model and analyze your future deals with ease.<\/p>\n

    Happy Investing \ud83d\ude42
    \n    Lauren Manning<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

    As Ali Boone’s article titled Rental Property Numbers so Easy that You Can Calculate Them on Napkin pointed out, “In this industry, you must love the numbers. Love them like […]<\/p>\n","protected":false},"author":787,"featured_media":36777,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[5528],"tags":[],"class_list":["post-36689","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-real-estate-news"],"acf":[],"comment_count":0,"_links":{"self":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/posts\/36689","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/users\/787"}],"replies":[{"embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/comments?post=36689"}],"version-history":[{"count":0,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/posts\/36689\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/media\/36777"}],"wp:attachment":[{"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/media?parent=36689"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/categories?post=36689"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.biggerpockets.com\/blog\/wp-json\/wp\/v2\/tags?post=36689"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}