Calculating NPV with Large Number of Variable Cash Flows

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

You may use the Present Value function (PV) to calculate the Net Present Value (NPV) of an investment that has a series of per =NPV(.13/12,1470,1470,....,199633.33)

The PV function simplifies this calculation if set up as follows:

   A7:  13%            B7:  =A7/12
   A8:                 B8:
   A9:  -1470          B9:  6
   A10: -1633.33       B10: 95
   A11: -199633.33     B11: 1
   A12: =PV(B7,B9,A9)+PV(B7,SUM(B9:B10),A10)+PV(B7,SUM(B9:B11),A11)
        -PV(B7,B9,A10)-PV(B7,SUM(B9:B10),A11)

The solution process:

1. PV(13%/12,6,-1470) finds the PV of the first six periodic cash flows.

2. PV(13%/12,6+95,-1633.33) finds the PV of the next 95 periodic cash flows as if they were received from the beginning of the investment.

3. PV(13%/12,6+95+1,-199633.33) finds the PV of the last periodic cash flow as if it was received from the beginning of the investment.

4. PV(13%/12,6,-1633.33) and PV(13%/12,6+95,-199633.33) subtract the PV of the periodic cash flows that were not received.

REFERENCES

"Online Help," version 5.0 "Function Reference," version 4.0, pages 291, 341 "Function Reference," version 3.0, pages 162, 189