Microsoft KB Archive/72086

{|
 * width="100%"|

Calculating NPV with Large Number of Variable Cash Flows

 * }

-

The information in this article applies to:


 * 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 NPV for this series of cash flows calculates to -165,561.70.

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.