MeadInKent
| Feedback | Financial Functions | Links |

Making investment decisions using Excel

Calculating Net Present Value (NPV) and Internal Rate of Return (IRR)

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)
  • Initial investment to purchase assets
  • Operating costs such as labour and materials
  • Tax payments
  • Project management expenses
  • Any other outflow caused by accepting the project
Cash Inflows (income)
  • Project revenues and grants
  • Eventual scrap value of assets
  • Any other inflow caused by accepting the project

Note that this technique uses cash flows, therefore depreciation is not a consideration and is excluded.

Would you like to learn more?

Support this website

Business Analysis: Microsoft Excel 2010

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

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

Excel 2010 Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this 
file. It has been rewritten for Excel 2010.

file: excel_npv.htm meadinkent.co.uk 2014 Page last updated Jan14 CMIDX S1 P4 Y