In: Finance
Discuss how cash, accounting, and financial breakeven
are calculated. Be sure to include the
advantages/disadvantages of each. Also, discuss how NPV and IRR are
calculated in Excel. What are the required inputs to
each?
Cash break-even is that break-even point after adjustments has been made for depreciation and other non-cash items. Thus cash break-even = (fixed costs – non-cash expenses)/(selling price per unit – variable cost per unit). An advantage of cash break-even is that it helps to determine the cash appropriate level of sales. Certain non-cash expenses are not always relevant and the cash break-even point takes care of this. In terms of disadvantages it is not always easy to determine the exact amount of non-cash expenses and doing so may take considerable amount of time and resources.
Accounting break-even point is that sales level at which a business generates exactly zero profits. In accounting break-even there are no adjustments for non-cash expense and so accounting break-even = fixed costs/(selling price per unit – variable cost per unit). An advantage of accounting break-even point is that it can be used to easily compute profit for different sales volume, the quantum of sales volume to produce desired profit etc. In terms of disadvantages it assumes that costs vary in a linear fashion and this may not always be true and valid.
Financial break-even point is that level of EBIT (earnings before interest and taxes) which is equal to a firm’s fixed financial costs. Fixed financial costs are mainly interest expenses and preference dividend. Thus financial break-even = Interest + Preference dividend/(1-tax rate). The level of EBIT at financial break-even will lead to nil net income. The advantage of financial break-even point is that it helps in determining minimum level of EBIT that will be required to pay off commitments like interest, preference dividends and tax. In terms of disadvantage it only takes the investment consideration into perspective.
Computation of NPV in excel: To do this first compute PVIF (present value interest factor). PVIF = 1/(1+r)^n where “r” is the cost of capital and “n” is the year of cash flow. PV (present value) = cash flow in a year * PVIF of that year. NPV = sum of all PVs. The same has been shown using a hypothetical example in which r is taken as 5% and total life of project is 3 years.
Year | Cash flow | 1+r | PVIF | PV |
0 | -100 | 1.05 | 1.0000 | -100 |
1 | 50 | 0.9524 | 47.61905 | |
2 | 45 | 0.9070 | 40.81633 | |
3 | 40 | 0.8638 | 34.5535 | |
NPV | 22.99 |
IRR in excel can be computed in excel using (i) trial and error approach (ii) excel solver function (iii) goal seek function. Any of these methods can be used.
It should be noted that at IRR the NPV = 0. So using the trial and error method keep changing r till you get NPV as nil. In excel solver put the value of objective function as nil and in goal seek function you can set the value of cell for "r" as nil.
Taking the example of data from above i put r as 5.5% which gives me the following output:
Year | Cash flow | 1+r | PVIF | PV |
0 | -100 | 1.06 | 1.0000 | -100 |
1 | 50 | 0.9434 | 47.16981 | |
2 | 45 | 0.8900 | 40.04984 | |
3 | 40 | 0.8396 | 33.58477 | |
NPV | 20.80 |
Clearly NPV is positive and so r is much greater than 6%. I keep increasing r till the NPV cell becomes nil.
Year | Cash flow | 1+r | PVIF | PV |
0 | -100 | 1.1737 | 1.0000 | -100 |
1 | 50 | 0.8520 | 42.59911 | |
2 | 45 | 0.7259 | 32.66431 | |
3 | 40 | 0.6184 | 24.73725 | |
NPV | 0.00 |
Thus r = 1.1737 - 1
= 17.37%. This is the IRR.