Finance example - NPV

In this module:

Net present value

An important concept in finance is the application of the Net Present Value method to project evaluation.

Example: ABC Farming Co is considering an investment of $80,000 in a new carrot washing machine, that is expected to generate the following incremental cash inflows: $10,000 (year 1), $20,000 (year 2), $15,000 (year 3), $50,000 (year 4), and $20,000 (year 5). Assume that all cash inflows occur at the end of the year. The discount rate is 5.0% pa.

Required: Calculate the net present value of the project, and present your answer in table form based on the NPV equation - $$\begin{equation} NPV=C_0 + \sum_{t=1}^{n} \frac{C_t}{(1+k)^t} \end{equation}$$ where \(C_0\) is the initial cash flow at time zero. The future cash flows at time \(t\) are denoted \(C_t\) over \(n\) periods. \(k\) is the periodic discount rate.

Using equation 1, the solution is:

$$ NPV=-80,000 + \frac{10,000}{(1+0.05)^{1}} + \frac{20,000}{(1+0.05)^{2}}+ \frac{15,000}{(1+0.05)^{3}}+ \frac{50,000}{(1+0.05)^{4}}+ \frac{20,000}{(1+0.05)^{5}} = \$17,428 $$

ABC Farming Co

The task is to replicate the NPV calculation in table format as illustrated in figure 1.

Fig 1: NPV evaluation - in table form

The following points are a summary of the session demonstration. All cell references relate to figure 1.

Data entry

Format of table

The completed table is shown in the Excel Web App #1 in figure 2.

Fig 2: Excel Web App #1 - the completed analysis panel

Excel pointers and the fill handle

Excel uses a number of specialised cursors or pointers for the mouse or other pointing device. In normal operation, the pointer is a open cross as shown in figure 3. Left click a cell, or click and drag when making a multi-cell selection.

Fig 3: General mode pointer - when selecting cells

To move the selection a short distance on the current worksheet, place the pointer on the border of the cell or selection. The pointer will change to open cross will change to a fine cross with arrow tips and an arrow pointer - see figure 4. Left click on the border of the selection and drag to new location.

Fig 4: Move selection mode pointer - when on border of selection

The fill handle is use to copy or extend the selection to adjacent cells. It is the small square in the bottom right hand corner of the selection, shown in the circle in figure 5.

Fig 5: Selection fill handle - the small square in the bottom right corner of the selection

When the pointer is placed over the fill handle, the pointer changes to a black closed cross - see figure 6. Either left click and drag to manually extend the selection, or double click to fill down for the length of the adjacent column vector.

Fig 6: Fill handle mode pointer - when pointer is over the fill handle

Move selection mode (figure 4) and fill handle mode (figure 6) are collectively described as drag-and-drop in Excel's editing options.

Drag-and-drop inoperative?

If the fill handle and cell drag-and-drop is not working, click on File > Options > Advanced and in the Editing Options group tick the Enable fill handle and cell drag-and-drop item.