Pre-published version. Not for further distribution
Figure 7: Tracing precedents for the formula in cell C12 for NPV as calculated for period 0, in the NPV Tool workbook for the Biofuels Example.
The formula in the Biofuels Example for cell C12 is reproduced below from the entry line at the top of the spreadsheet:
=NPV(C4,D10,E10,F10,G10,G10,H10,I10,J10,K10,L10,M10,N10,O10,P10,Q10,R10,S10,T10,U10,V10,W10)+C11
The first item after the parenthesis is a reference to cell C4, the cell which contains the discount rate assigned for period 0. This 75%
rate is being applied to all the estimated future cash flows because at this time the risk has not been brought down further. Only one
rate is applied to all future cash flows for the NPV as of period 0, which is why this tab is entitled “NPV – single rate”.
Now look at the links to the future cash flows in Figure 6. Note that these links go out to the end of the periods for which cash flows
are being estimated, which in this case is period 20.
Because this method involves calculating at the end of the period, after making the calculation in this spreadsheet, until break-even is
reached, it is necessary to include the net of investment and revenues for the just ended current cash flow period as we have
negative cash flow for the period. That means additional investment in the project is required to cover the loss. This is seen in the last
entry in the formula, which is +C11 to add the amount of investment to date listed in cell C11 (25,000). Please be aware the
investment to date value is not in the Excel NPV formula entry box or the formula when it runs. For that reason, investment to date
was added. Without it, the NPV is not accurate for NPD. It is not necessary to add it when there is no loss, as the investment for the
period is captured in the positive net cash flow on the “Gross profit” line (row 10).
Investments not recouped are carried forward until break-even is achieved. We see this in period 6, for example, in the NPV Tool
workbook for the Biofuels Example. At the end of the Launch stage at period 6, the sum of gross profit minus the investment to date
Period Half-year
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Discount rate
75.0% 50.0% 45.0% 35.0% 20.0% 15.0% 10.0% 5.0% 20.0% 75.0% 100.0%
Units sold
0 0 0 0 0 0 5 20 30 40 40 40 45 45 45 40 40 20 5 5 0
Average revenue per unit 0
0 0 0 0 0 550,000 550,000 550,000 550,000 550,000 550,000 550,000 400,000 400,000 300,000 300,000 300,000 300,000 260,000 0
Revenues 0 0 0 0 0 0 2,750,000 11,000,000 16,500,000 22,000,000 22,000,000 22,000,000 24,750,000 18,000,000 18,000,000 12,000,000 12,000,000 6,000,000 1,500,000 1,300,000 0
Average cost of goods per unit
0 0 0 0 0 0 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 0
Expenses
25,000 50,000 100,000 300,000 500,000 50,000
1,250,000 5,000,000 7,500,000 10,000,000 10,000,000 10,000,000 11,250,000 11,250,000 11,250,000 10,000,000 10,000,000 5,000,000 1,250,000 1,250,000 0
Gross profit
(25,000) (50,000) (100,000) (300,000) (500,000) (50,000)
1,500,000 6,000,000 9,000,000 12,000,000 12,000,000 12,000,000 13,500,000 6,750,000 6,750,000 2,000,000 2,000,000 1,000,000 250,000 50,000 0
Investment to date
(25,000) (75,000) (175,000) (475,000) (975,000) (1,025,000)
475,000
Net present value 27,349 1,224,061 2,960,717 26,736,051 42,268,339 59,144,750 71,803,699 243,056 28,571 0 0
Total gross profit (not discounted)
83,775,000
Internal rate of return
101%