xnpv excel formula for mip and eg

[Kdumlu]

Hello everyone, 

I have a data in excel which calculate XNPV for every payment as you can see in pic.

I have to find these values in MIP. However I have so many account with different number of payments and dates. I tried with open formula of XNPV but I cannot get any result. 

Thanks for help.

[ballardw]

Please post EXCEL related questions to an Excel forum.

If the data is not in SAS then this question is not related to SAS.

[Kdumlu]

The data is in sas now. I take it to sas and calculate the colomns like payment, date etc. However, I couldnt calculate colomn for xnpv

[ballardw]

SAS has function named FINANCE that does many financial calculations. The first parameter is the type of calculation and then follows with the parameters used for the calculation.

Somevar = Finance('XNPV',rate, <values>,<dates>);

An example from the documentation:

data _null_;
   r=.09;
   v1=−10000; d1=mdy(1, 1, 2008);
   v2=2750; d2=mdy(3, 1, 2008);
   v3=4250; d3=mdy(10, 30, 2008);
   v4=3250; d4=mdy(2, 15, 2009);
   v5=2750; d5=mdy(4, 1, 2009);
   r=finance('xnpv', r, v1, v2, v3, v4, v5, d1, d2, d3, d4, d5);
   put r=;
run;

To use variables then all the matching value and date pairs must be on the same record, which likely means that you need to reshape your data (since you haven't shown any I have to assume you have it similar to your Excel example).

This should be doable with proc transpose. The value needs to correspond to the date in order.

Note that the first value is negative and corresponds to a cost or payment that occurs at the beginning of the investment.