The net present value DATA step functions, NPV, and NETPV, assume that payments occur at the beginning of each period while Excel's NPV function assumes that payments occur at the end of each period. When comparing the results from SAS®  and Excel, you will need to either add an initial payment of 0 while using the SAS functions or exclude the first payment and add it back when using the Excel function.

Here is sample SAS code to illustrate the differences between SAS and Excel with the NETPV function: 

 

data _null_;
/*Add an initial payment of 0 for cash flows to be interpreted as end of period. */

/*Default behavior in Excel.*/

npv_end_of_period = netpv(.10, 1, 0, -10000, 3000, 4200, 6800);
put 'End of Period: ' npv_end_of_period dollar9.2;

/*Default behavior in SAS - beginning of period values.*/

npv_beginning_of_period = netpv(.10, 1, -10000, 3000, 4200, 6800);
put 'Beginning of Period: ' npv_beginning_of_period dollar9.2;

run;

 

The results from the PUT statements above: 

End of Period: $1,188.44
Beginning of Period: $1,307.29

 

Here is an example showing Excel's NPV function:

 

         A                             B             C
1   description                         data    
2   rate                                0.1    
3   c0                               -10000    
4   c1                                 3000    
5   c2                                 4200    
6   c3                                 6800    
7   npv - end of period            $1,188.44    =NPV(B2,B3,B4,B5,B6)
8   npv - beginning of period    $1,307.29    =NPV(B2,B4,B5,B6)+B3