Mathematical Techniques for Economists 112ECN
INVESTMENT APPRAISAL USING A SPREADSHEET
As the mathematics involved in calculating the NPV of a project can be quite time consuming, a spreadsheet program can be a great help. Although Excel has a built in NPV formula, this does not take the initial outlay into account and so care has to be taken when using it.
Example An investment requires an initial outlay of £25,000 with the following expected returns: £5,000 at the end of year 1
£6,000 at the end of year 2 £10,000 at the end of year 3 £10,000 at the end of year 4 £10,000 at the end of year 5
Is this a viable investment project if money can be invested elsewhere at 15%? Solution Follow the instructions set out below:
CELL
Enter
Explanation
A1
NPV Example
Label to remind you what example this is
A3
YEAR
Column heading label
B3
RETURN

Column heading label
C3
PV
Column heading label
C1
Interest rate =
Label to tell you interest rate goes in next cell.

D1
15%
Value of interest rate. (NB Excel automatically treats this % format as 0.15 in any calculations.)
A4 to A9
Enter numbers 0 to 5
These are the time periods
B4
-25000

Initial outlay (negative because it is a cost)
B5
5000
Returns at end of years 1 to 5

B6
6000
B7
10000
B8
10000
B9
10000
C4
=B4/(1+$D$1)^A4
Formula calculates PV corresponding to return in cell B4, time period in cell A4 and interest rate in cell D1. Note the $ to anchor cell D1.
C5 to C9
Copy cell C4 formula down column C

Calculates PV for return in each time period. Format to 2 d.p. as monetary values
B11
NPV =

Label to tell you NPV goes in next cell.

C11
=SUM(C4:C9)
Calculates NPV of project by summing PVs for each year in cells C4-C9, which includes the negative return of the initial outlay.
B13
Excel NPV
Label tells you Excel NPV goes in next cell.
B14
less cost =