| | 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 |