Cash Flow Excel Spreadsheet Template

photo src: handexcel.com

photo src: www.excel-templates.net

Maps, Directions, and Place Reviews

Would it be possible to add an example to this page? After reading it's still kind of unclear. Iflipti 04:29, 22 May 2005 (UTC)

• Response from User:Ertyqway, 21 April 2006: Done.

---

I would like to see a correlation of discounted cash flow to the fair value of a share of stock. I have often heard of discounted cash flow being used as a way to value a stock however I am having trouble equating this to stock price.--69.249.154.41 01:44, 30 October 2005 (UTC)

Resonse to the above comment: Equity analysts develop their own proprietary models of what they expect the dividends and future stock price to be. Then those are discounted to the present using the DCF model. When one compares their model to the market price of a share, this is a source of buy or sell recommendation because they may not be the same. Of course, the model is only as good as the forecasted dividends and predicted dtock price.

Cash Flow Excel Spreadsheet Template Video

Discount cash flow versus nominal cash flow

Which cash flow is best to use for evaluating real estate investment discount or nominal cash flow. Why?

Response: Expected future cash flows that do to the owner in nominal (not real) terms. In real estate, this is normally called operating income. These are the cash receipts to the owner from which he or she derrives value.

• Response from User:Ertyqway, 21 April 2006: Future cash flows for real estate transaction should only be considered in discounted, not nominal, terms. In fact, a real estate pro forma is not considered complete without a full DCF analysis. A complete DCF for a real estate transaction must include all expected cash flows, positive and negative. This not only includes net operating income, but also tax shelter value, built-up equity, and capital appreciation. For a real estate development project, further cash flows must be included in the analysis: construction costs, marketing costs, proceeds from sale, commissions, permit fees, and a whole host of other cash flows. When I do DCF for my own projects, the analysis matrix is typically many pages long, broken out quarterly or monthly depending on the detail required.

photo src: www.vinotique.com

Article substantially revised on 21 APR 2006

I have substantially re-written the article to include the entire DCF formula and a very simple example showing how DCF analysis is generally done and what it means. Also, the original article limited the calculation of the discount factor to opportunity cost only, while not really discussing where opportunity cost numbers come from or what they really mean. A discussion of risk factors (commonly used in real estate DCF) has been included. The article has also been linked to the "real estate" category, where it is often referenced.

For further discussion: there appear to be several finance articles that all cover similar ground. Among these are discounted cash flow, future value, present value, and net present value. These subjects are all intimately related to one another (especially DCF and NPV), and should perhaps be collected together somehow.

Ertyqway

Thanks for the awesome article, this so saved me mega time in my capital costs class for engineering.

Ditto. Article is bangin'. Financial markets exam in two weeks. Keep up the good work

---

I would disagree with merging DCF with NPV (although I note that the above suggestion does not specifically demand this, I just want to be clear). DCF is a much broader topic than NPV, and covers the basic concept of time value of money, which is relevant to a range of applications. One of these is NPV.

NPV is a specific application of DCF analysis that is used to evaluate projects based on a pre-determined 'hurdle rate'. IRR and even Stern Stewart's Economic Value Added methodologies also implicitly use the DCF concept, while being distinct from NPV.

I would suggest that instead of merging DCF and NPV, perhaps merging present value and future value into NPV and linking to NPV and IRR in the DCF article would make sense. I believe this will make clear that these articles (and the ideas and methods they relate to) are conceptually and practically distinct, but intimately related. --Preceding unsigned comment added by 139.149.31.232 (talk) 20:55, 15 September 2007 (UTC)

photo src: www.xltemplates.us

Large re-write March 2007 - opinions?

I have moved comments from user to bottom of this page, as traditional.--Gregalton 13:54, 1 April 2007 (UTC)

I have several problems with that article. It starts with an introduction that describes what is going on. Then we have a section Mathematics that contains some formulaes without giving the reader a clue qhat is going on (what has this to do with cash flows, values or cost of capital?). The exmaple (in my opinion) complicates the stoty even more - how do we proceed if we have different cash flows? Is that approach always allowed? Does it lead to the correct value? Then one citation and that's it. Since I am interested in that subject I take the freedom to erase the formulas. Examples are fine if you know what is going on, otherwise they can confuse people: This example is particular flawed since it calculated the net present value of an uncertain income stream with riskless interest rates. Where are all those numbers coming from? Can they be choosen arbitrarily? Or is there a rule that forces us to take particular interest rates? And I can add a lot more questions... User:al64 29 March 2007 (UTC)

OK, let me put it this way: Let us talk about what should be added to the text. We had an example, but the problem was that this example got us to believe we understood something although it raised in fact more questions than it gave answers (where were those numbers coming from, when can we in fact calculate like the authors suggested, is such a calculation always successfull...). What should be added to that text or the topic? For example, a long text about CAPM is in my opinion misleading, since CAPM is an equilibrium model about one period (although there are some extensions to multiperiod) and valuation is in fact a multiperiod problem etc. Some sentences about cost of capital should be moved to the topic cost of capital and the same applies to cost of equity and cost of debt. For me, Discounted Cash Flow is just like a headline, that now is to be distribbuted into several sections. Al64 2 April 2007 (UTC)

OK, I am commenting to the current version:

• It starts with time value of money. The first line has an interest rate i that is somehow changed to a rate d. Why? What is the relation between those two?

• The next line gives us a simple NPV formula. This formula makes only sense for certain cash flows, not for uncertain ones (than you need expectations). I do not see this assumption anywhere in that section.

• It is worthless having a formula for certain cash flows and no formula for uncertain cash flows. What changes with uncertainty?

• The last sentence says that this can be used as net present value. That is wrong: Net present value is the difference between market price minus present value (DPV).

• The internal rate of return is then mentioned. As anyone who hast studied business or finance should know, this is a very bad advice if cash flows change sign.

• The example of buing a house the comparison is not correct (complicating the example): you should compare renting and buying, hence you have to add fictious rent payments for the house. I suggest that a much easier example would be appropriate.

• Also the didactics is bad: The example starts with the wrong calculation. As people from teaching will tell you the better way to present a story is to start with the simplest example possible (one period I would suggest),present a correct calculation, then say something about why the wrong way to calculate is indeed wrong and then continue to the more complicated cases.

• Then a risk factor of 5% is added. Where is that risk factor comming from? Is there a theory for that or is it just taken from the air? If it is beyond the scope, why is no reference given?

• The main idea underlying the net present value is arbitrage. So why not saying it? The word arbitrage does not even appear.

• In the main text some links appear: Adjusted present value, Total cash flow, Flow to equity. Why are they mentioned again on the bottom? Some links are not mentioned there - why?

Al64 4 April 2007 (UTC)

So what about our discussion? Those who prefered the older version should say something - or am I right? Then why not going back to my version?

Al64 14 April 2007 (UTC)

photo src: www.xltemplates.us

Question regarding diagram (EXCEL spreadsheet) given

Is it just me or are some of the figures calculated in the spreadsheet incorrect. Can't work out how to reconstruct simple calculation like EBITA x Statutory Tax rate (Year 2009E, 26.50% of 941 gives $250 odd, not $354). Comments/corrections appreciated.

photo src: www.youtube.com

Question regarding DCF

For a while now, I've had a question regarding DCF that I've never seen addressed anywhere. I'm wondering if any mathematicians or financial wizards here are smart enough to figure it out. If so, it might be a caveat that is worth adding to the article.

Imagine there are two companies, ABC Corp and XYZ Corp. They both trade at the same price per share, earn $1.00 per share (assume FCF and earnings are equal), grow earnings at 7.5% per year, and have no debt. However, there is a difference between the two companies. ABC Corp has a 15% ROE and pays out a 50¢ dividend, while XYZ Corp has a 7.5% ROE and pays out no dividend.

Clearly, ABC Corp is the better investment because the only way XYZ Corp can match its growth is by not paying a dividend. So, the ABC Corp investor gets both 7.5% growth and a dividend, while the XYZ Corp investor only gets 7.5% growth. DDM would value ABC Corp as being more valuable that XYZ Corp. Using traditional DCF, however, the two companies would be valued the same.

(If you think that the two companies actually are the same value, then imagine that XYZ Corp also starts to pay out a 50¢ dividend. Its growth rate would drop to 3.25%. Would you still consider them equal?)

Does anyone know how a DCF calculation can properly address the difference in value between ABC Corp and XYZ Corp? The answer should be based on accurate mathematics, rather than answers that "feel right". --JHP 05:37, 15 May 2007 (UTC)

1. Discount rate should be lower for the dividend-paying company. Any valuation based on cash flows/certainty of cash flows will assign less risk to the investment that generates cash flows to the investor sooner rather than later, particularly if there is no ability to influence whether/when such funds will be received (as in your case).
2. Define and analyze the free cash flow more carefully. As currently defined, all of the growth is financed by reinvesting earnings, and in this sense is not free cash flow. Carried to an extreme, this gives an absurd result (there are no free cash flows), so there has to be some ability to specify the problem in a way that makes sense. One way to look at it is to require (for the purpose of analysis) that both companies pay equal dividends, and see where that leads. Clearly the one company can only generate growth at the current level by never paying a dividend.
3. The more complicated but complete answer: Specify some terms by which both companies will pay out cash. For a simple example, say XYZ must pay all of its earnings out starting at some date in the future, and pay out all earnings every year from then on. Use 7.5% as the discount rate for convenience, which gives the same valuation as a perpetuity of $1 starting today. Apply the same approach to ABC, and you get two distinct cash flow streams: $0.50 from now through year X, and the same stream as XYZ above. In other words, ABC's valuation will always be higher by the net present value of the stream of dividends to the date when full payout begins (the value of the full payout starting in the future should be the same).

Different Solution: This had me going for a minute or two... however the answer is mathematically simple and is all about definition of free cashflow (FCF). The FCF that you should be using is the cashflow after maintenance or replacement capital expenditure - i.e. the capital expenditure that is required to keep the business going. Also you should deduct (but not in perpetuity) any growth capital expenditure.

Based on the way you have phrased the question, it appears to imply two options:

Option 1

• Debt / Cash remains at zero (i.e. any earnings not paid out is re-invested)
• The ROE's remain constant (i.e. each marginal dollar earns the same as the previous dollar)
• The re-investment of capital is required to keep the businesses operating
• The earnings grows at 7.5% - which is an outcome of the above. i.e. ABC has equity in year 1 of $6.67 which earns a 15% return to give $1.00. ABC then grows that equity by reinvesting half each year, and so grows its equity by 7.5% each year. XYZ is the same, but it needs to invest all of its profits to grow its equity by 7.5%.

So, in this case one company needs to invest $1 per year and the other $0.50, and so the FCF should be $0.50 for ABC and $0.00 for XYZ. This would mean that XYZ is worthless unless you make the assumption that at some stage this company must accept a lower growth rate and return capital.

Option 2

• Debt / Cash remains at zero (i.e. any earnings not paid out is re-invested)
• The ROE's remain constant (i.e. each marginal dollar earns the same as the previous dollar)
• The earnings are equal to the FCF - which implies that excluding re-investment that earnings would not grow at all.
• The earnings grows at 7.5% because there is growth capital expenditure invested.

So, in this case you should do a DCF of how many years that the re-investment occurs for, and then keep earnings constant beyond that period. THEN, you need to deduct the NPV of all of the capital that has been invested, for ABC this will be lower than XYZ and so ABC will have a higher valuation. Damien74 23:45, 14 November 2007 (UTC)

Guys, this is so simple. This is a classic trick question. Accountants and engineers look at statements and not the real world and so they seem to make this mistake a lot. You are looking at the ROE as a measure of performance on the market value of the equity. In order for ROE to have any such relevance, you need to used a reconstructed balance sheet adjusted to market values. If there is no debt, then the equity on the un-reconstructed balance sheet will equal the book value of the assets. If the two firms have identical incomes and identical income growth, then they are performing identically from a valuation standpoint. If FCF = income, then there is no depreciation expense. Your calculation that XYZ paying dividends would change its return on equity is wrong. It earns income. No debt, so its all income to equity. Divide income by equity, get ROE of 7.5 %. It earns income, then pays half of the income as dividends, still have the same income, they will have less current assets, less retained earnings--will eventually increase ROE, not cut it by half, except for the fact that actual market value of assets go down ex-dividend, there should be no change in valuation. Since the two companies have the same share price with no debt, then they have the same WAAC and thus the market value of the assets must be the same. The only real difference between the two companies is that ABC has a lower book value to its assets than XYZ, which has nothing to do with valuation. In the future, as retained earnings continue to accumulate, the ROE of XYZ will continue to deteriorate, but its value will continue to climb as its assets grow.KTrimble (talk) 09:35, 6 April 2008 (UTC)

photo src: www.xltemplates.us

Mathematics

The claim "(1+i)^(-t) can of course also be expressed as exp(-it)" is only true in the limit of ${\displaystyle t\rightarrow \infty }$, with ${\displaystyle i\rightarrow 0}$ such that ${\displaystyle i\times t}$ remains constant, no? In any other case it's an approximation. --Preceding unsigned comment added by 68.165.65.171 (talk) 15:59, 15 February 2009 (UTC)

photo src: www.lexgstein.com

Discounted Cash Flow Calculator

Although there is a calculator in the external, i feel that it is not useful enough for people. First, the calculator has limited field. You can't select number of year you wish to project. Second, there is no way to enter the number of shares outstanding. Third, there is no chart. I would recommend adding this discounted cash flow calculator. What is others editor opinion? --Preceding unsigned comment added by Calculus1985 (talk o contribs) 14:02, 1 November 2010 (UTC)
I recommend the following DCF calculator. It is almost similare to the Excel spreadsheet.

"DPV" v. NPV confusion

I hadnt heard of "DPV" before. Looking at its description, isnt DPV simply another name for NPV? Would it not be better to call it NPV rather than DPV to avoid confusion?

Second point, would it not be a good idea to provide an evaluation of the different methods of DCF? The standard textbook Principals Of Corporate Finance by Brearly (sp?) and Myers gives convincing reasons as to why NPV is the best method of DCF. 92.28.244.187 (talk) 16:53, 4 July 2011 (UTC)

Ambiguous Sentence

In the introduction: "the opposite process -- taking cash flows and a price and inferring a discount rate, is called the yield." This is not sound right. Can somebody explain what's this about please???? (talk) 00:39, 21 January 2013 (UTC)

Example DCF

That section reads like a copy from a textbook. And the tone is not quite encyclopedic, such as the rhetorical question "But what about risk?", and first person "We assume ...". --Quest for Truth (talk) 10:24, 14 September 2015 (UTC)

The risk allowance is unclear

The paragraph

is unclear. I don't understand against what I add/distract/compare the 5% "risk valuation". The fact that the final calculation is "the same as above" hides the difference and makes the example not simpler but incomprehensible. 78.45.38.114 (talk) 18:10, 18 October 2015 (UTC)

I don't understand how future price 130,000 gives loss of 3,000. ${\displaystyle {\frac {130000}{1.05^{3}}}=112299;112299-100000=12299\neq -3000.}$ 95.132.143.157 (talk) 09:45, 7 November 2015 (UTC)

External links modified

Hello fellow Wikipedians,

I have just modified one external link on Discounted cash flow. Please take a moment to review my edit. If you have any questions, or need the bot to ignore the links, or the page altogether, please visit this simple FaQ for additional information. I made the following changes:

• Added archive https://web.archive.org/web/20080110115513/http://www.thestreet.com:80/university/personalfinance/10385275.html to http://www.thestreet.com/university/personalfinance/10385275.html

When you have finished reviewing my changes, please set the checked parameter below to true or failed to let others know (documentation at {{Sourcecheck}}).

You may set the |checked=, on this template, to true or failed to let other editors know you reviewed the change. If you find any errors, please use the tools below to fix them or call an editor by setting |needhelp= to your help request.

• If you have discovered URLs which were erroneously considered dead by the bot, you can report them with this tool.
• If you found an error with any archives or the URLs themselves, you can fix them with this tool.

If you are unable to use these tools, you may set |needhelp=<your help request> on this template to request help from an experienced user. Please include details about your problem, to help other editors.

Cheers.--InternetArchiveBot (Report bug) 21:00, 13 December 2016 (UTC)

Source of the article : Wikipedia