Net present value - NPV

The idea of using the present value to estimate the worth of a sum of money received in the future can be extended to an arbitrary cashflow.

If there are a series of cash amounts that become available on different dates then the best way to gauge their value is to reduce each one to its present value and then sum them to form the ‘Net Present Value’.

That is if $Si is recieved at the end of time period i then the NPV is given by:

NPV= S1/(1+I)+S2/(1+I)^2+S3/(1+I)^3
                       ....Sn/(1+I)^n

That is, the NPV is sum of all of the cash sums each one discounted by the appropriate factor.

In more mathematical terms the NPV is

For example, if an investment promises to generate a cashflow of $100 at the end of the first year, $200 at the end of the second and $500 as a closing payment then the Net Present Value is:

=100/(1+I)+200/(1+I)^2+500/(1+I)^3

where I is the effective annual rate of interest.

If I is assumed to be 8% then the NPV is $660.98 which should be compared to the total income of $800.

Spreadsheet NPV

Most spreadsheets have an NPV function which will calculate the present value of a cashflow:

 NPV(I,range)

where range is the part of the row or column that holds the cashflow values.

An alternative form is often provided:

 NPV(I,list of values)

where the values are entered directly into the formula.

The cashflows are assumed to arrive at the end of equal periods and the interest rate specified has to be appropriate for this period.

For example, in the case of the cashflows described earlier, the spreadsheet shown below demonstrates the two methods of calculation - calculating the PV for each individual value and then adding the results up or more directly in one step using the NPV function.

The cashflows are entered into B3..B5 and the present values of amount is calculate in column C. The NPV in C7 is obtained by summing the present values listed above. That is in C3 the formula is:

=PV($B$1,A3,,-B3)

which is copied down the column and summed in C7.

Alternatively the formula:

 NPV(B1,B3..B5)

entered into D7 calculates the NPV of all of the values in one step.

Irregular time periods

The NPV function as supplied in most spreadsheets will only work with equal intervals.

It assumes that each cash sum specified is received at the end of a regular time period.

If you want to calculate the PV of a cashflow at unequal intervals you can always use the basic formula on each payment - that is discount each sum and find the total.

For example, if you receive payments of:

1. $500 after 6 months
2. $1000 at the end of one year
3. $1500 at the end of the second year
4. $2000 at the end of the third year

then the NPV can be calculated as shown in below 

 An irregular cash flow

The cashflow and the time received are entered normally in column A with fractional years used to express 6 months i.e. 0.5 of a year.

The present value of each cashflow is calculated by entering:

=B3/(1+$B$1)^A3

into C3 and then copying this down the column. Notice the the discount formula uses the interest rate stored in B3.

Finally to get the NPV enter the formula:

 =SUM(C3..C6)

into C7.

This first principles method can always be used to calculate the NPV. As long as you record the time that each sum is received using fractions if necessary then the NPV calculated will be correct.

An alternative to the direct approach is to reduce all of the payments to the smallest interval of time and use the NPV function. For example, in the cashflow used earlier the smallest interval is 6 months so we could record all of the cashflows in terms of a basic 6 month period.

Of course some of these cashflows would be zero but this doesn’t alter the calculation of the NPV.

You can see the result of doing this below. You can see that the annual payments are simply the consequence of no payment half way through the year! The cashflow can now be reduced to its NPV using the NPV function in the usual way.

 Converting irregular cash flows into regular.