 Feedback  Financial Functions  Links  
NPV and IRR are statistical tools for appraising projects and assisting in investment decision making. They measure cash flows over the period of a project and take account of the 'time value of money'. Monies spent or received at different times can not be directly compared. In general it is preferable to hold a sum of money now rather than in the future so that it can be put to some alternative use such as earning interest or being invested. The time value of money is specified as a discount rate (which is effectively the same as an interest rate) or the cost of capital.
Cash Outflows
(expenditure)

Cash Inflows (income)

Note that this technique uses cash flows, therefore depreciation is not a consideration and is excluded.
A decision is required about how anticipated future inflation is to be treated. If particular cash flows will be affected differently by inflation (e.g. Labour costs rising at a faster rate than income) you may choose to build it into your calculations. If all values are likely to rise at a general rate, it can be completely disregarded and the answer will be unaffected.
If you use cash flow figures that are increased each period for inflation, you must multiply the discount rate by the general inflation rate. If the discount rate is 10% and inflation 15% the NPV calculation must use: (1+0.10) x (1+0.15) = 1.265. Thus the discount rate to be used would be 26.5%.
Before building your spreadsheet model another key consideration is whether the initial investment expense is immediate or at the end of the first period. Many text book examples show the investment occurring immediately (i.e. period 0) while all other cash flows begin at the end of the first period. The Excel NPV function assumes that the initial outlay (or cash flow) is all at the end of the first period. Your formula will need to be adjusted if this is not the case.
  K  L 
2  Operation of new vehicle  Value 
3  Cost of new van  14,500 
4  Fuel  1,600 
5  Maintenance  variable 
6  Depreciation  n/a 
7  Driver  16,200 
8  New sales (deliveries)  125 
9  Courier charge per delivery  16 
10  Number of deliveries  1,250 
11  Final sale of vehicle  2,300 
12  Profit on each sale  5 
13  Interest rate  5% 
Example
A firm is considering whether or not to invest in a new van and driver to replace its current use of a courier company. The main cash outflows and inflows are shown in the range L3:L12 (right) and a rate of 5% is used as a discount rate since otherwise the money could sit in the bank earning interest.
It is considered that a new van with advertising painted on the side will raise the profile of the company and therefore increase sales by 125 each year  each sale making a profit of £5.
The project is planned for a 6 year period, at the end of which the van will be sold for an estimated price of £2,300. The outlay on the new van will be immediate in terms of the cash flow.
  A  B  C  D  E  F  G  H  I 
15  Expense  Income  
16  Fixed  Maint  Total  Courier  New Sales  Sale of Van  Total  Cash Flow  
17  year 0  14,500  14,500  14,500  
18  year 1  17,800  200  18,000  20,000  625  20,625  2,625  
19  year 2  17,800  250  18,050  20,000  625  20,625  2,575  
20  year 3  17,800  300  18,100  20,000  625  20,625  2,525  
21  year 4  17,800  400  18,200  20,000  625  20,625  2,425  
22  year 5  17,800  550  18,350  20,000  625  20,625  2,275  
23  year 6  17,800  700  18,500  20,000  625  2,300  22,925  4,425 
24  2,350  
25  NPV  403.63  IRR  4.18%  www.meadinkent.co.uk 
The straight forward cash flow shows that the project will generate additional income of £2,350. The net present value (cell C25) however adjusts this cash flow by the discount rate (5%) and shows that the return is in fact negative. In other words the business would be better off with its existing arrangements and not buying a van. Alternatively the money could be invested in a better alternative project.
The internal rate of return (cell E25) indicates the discount rate which is necessary for a NPV of 0 (i.e. breakeven). If the 5% value was replaced with a value of 4.18% the NPV would be very close to zero.
The two excel functions used above are as follows:
[C25]=NPV(L13, I18:I23)+I17  =NPV(discount rate, cash flow values)  Note that the initial outlay in cell I17 is excluded from the NPV formula because it occurs immediately and should not be discounted. If it occurred at the end of the first year it would be included within the NPV formula (i.e. using the range I17:I23). 
[E25]=IRR(I17:I23, 0.04)  =IRR(cash flow values, guessed discount rate)  The guessed discount rate is an optional argument and not normally required. 
If there are alternative projects of which only one can be selected, you should choose which ever has the largest positive NPV. It should be noted however that the longer the timespan of a project, the greater the uncertainty and potential risk. Projects with shorter payback periods might be deemed preferable. If funds are unlimited, any project with an IRR greater than the discount rate is worthwhile.
Get this information as a document accompanied by Excel worksheets 
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. 
file: excel_npv.htm  © meadinkent.co.uk 2016  Page last updated Jan14  CMIDX S1 P4 Y 