BookmarkSubscribeRSS Feed
SNG1
Calcite | Level 5

Hello All,

I have a Data Set with about 3M rows...I am trying to create a NPV calculation using SAS:

 

the data looks like below - 

 

The formula in excel that I am trying to emulate is

=XNPV(59.85/100,$D$2:$D$53,$C$2:$C$53)

 

the portion $D$2:$D$53,$C$2:$C$53 will be different for each ID for example the formula for ID = 29 is 

 

=XNPV(59.85/100,$D$2:$D$53,$C$2:$C$53)

 

The formula for ID = 31 is

 

=XNPV(59.85/100,$D$54:$D$79,$C$54:$C$79)

 

The issue I run into is to how to let SAS know where to start and stop the calculation for each ID

 

Hope someone can HELP me as I tried a couple of things and it seems SAS is unable to read the formula in an array and I have to transpose column C and column D to get the end result...

 

IDDateCash FlowPV
292018/02/05    3,500.001295.975
292018/02/09        (57.56)1295.975
292018/02/16        (57.56)1295.975
292018/02/23        (57.56)1295.975
292018/03/02        (57.56)1295.975
292018/03/09        (57.56)1295.975
292018/03/16        (57.56)1295.975
292018/03/23        (57.56)1295.975
292018/03/29        (57.56)1295.975
292018/04/06        (57.56)1295.975
292018/04/13        (57.56)1295.975
292018/04/20        (57.56)1295.975
292018/04/27        (57.56)1295.975
292018/05/04        (57.56)1295.975
292018/05/11        (57.56)1295.975
292018/05/18        (57.56)1295.975
292018/05/25        (57.56)1295.975
292018/06/01        (57.56)1295.975
292018/06/08        (57.56)1295.975
292018/06/15        (57.56)1295.975
292018/06/22        (57.56)1295.975
292018/06/29        (57.56)1295.975
292018/07/06        (57.56)1295.975
292018/07/13        (57.56)1295.975
292018/07/20        (57.56)1295.975
292018/07/27        (57.56)1295.975
292018/08/03        (57.56)1295.975
292018/08/10        (57.56)1295.975
292018/08/17        (57.56)1295.975
292018/08/24        (57.56)1295.975
292018/08/31        (57.56)1295.975
292018/09/06          57.561295.975
292018/09/06          50.001295.975
292018/09/07        (57.56)1295.975
292018/09/11        (57.56)1295.975
292018/09/14        (57.56)1295.975
292018/09/21        (57.56)1295.975
292018/09/28        (57.56)1295.975
292018/10/05        (57.56)1295.975
292018/10/12        (57.56)1295.975
292018/10/19        (57.56)1295.975
292018/10/26        (57.56)1295.975
292018/11/02        (57.56)1295.975
292018/11/09        (57.56)1295.975
292018/11/16        (57.56)1295.975
292018/11/23        (57.56)1295.975
292018/11/30        (57.56)1295.975
292018/12/07        (57.56)1295.975
292018/12/14        (57.56)1295.975
292018/12/21        (57.56)1295.975
292018/12/28        (57.56)1295.975
292019/01/04        (57.56)1295.975
312018/09/08    5,000.003514.378
312018/09/14        (68.96)3514.378
312018/09/21        (68.96)3514.378
312018/09/28        (68.96)3514.378
312018/10/05        (68.96)3514.378
312018/10/12        (68.96)3514.378
312018/10/19        (68.96)3514.378
312018/10/26        (68.96)3514.378
312018/11/02        (68.96)3514.378
312018/11/09        (68.96)3514.378
312018/11/15          68.963514.378
312018/11/15          50.003514.378
312018/11/16        (68.96)3514.378
312018/11/16     (118.96)3514.378
312018/11/23        (68.96)3514.378
312018/11/30        (68.96)3514.378
312018/12/07        (68.96)3514.378
312018/12/14        (68.96)3514.378
312018/12/19          50.003514.378
312018/12/19          68.963514.378
312018/12/20        (68.96)3514.378
312018/12/21        (68.96)3514.378
312018/12/27        (18.96)3514.378
312018/12/28        (68.96)3514.378
312019/01/04        (68.96)3514.378
312019/01/04     (500.00)3514.378
5 REPLIES 5
PaigeMiller
Diamond | Level 26

SAS has an NETPV function which ought to do the calculations you need.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lefunctionsref&docsetTarg...

 

However, first I think you need to do a transpose of your data set, BY ID. Then, each row of the SAS data set is for one ID, and the NETPV function ought to then work on all of your IDs.

--
Paige Miller
SNG1
Calcite | Level 5

Thanks...that is helpful I can transpose the dates and cash flow...but the issue I am facing is how do I write that in the npv formula in SAS:

 

For Example:

 

I can write - PV_at_eff_date=finance('xnpv',59.85/100,d1-dn,c1-cn)

 

But D1 to Dn is the number of columns created for Days and c1 - cn is the number or columns created for cashflow 

 

So for example ID 29 would be D1,D2,D3,...D53 for days and C1,C2,C3,...C53 for cashflow 

                         ID 31 would be D1,D2,D3,...D22 for days and C1,C2,C3,...C22 for cashflow

 

How do I incorporate this into the NPV formula to give me the NPV of each ID using the different column numbers for Dates and CashFlow

PaigeMiller
Diamond | Level 26

I believe the NETPV formula assumes the time intervals are constant, so you ought to be able to leave those out.

--
Paige Miller
SNG1
Calcite | Level 5

Nope it does not

mkeintz
PROC Star

If you have a varying number of flows, or unequally spaced dates, then proc transpose might require a second intermediate step.  However, you could also directly apply the formula for xpnv as

   SUM (over all flows)    of    flow{i}/ [(1+rate)**((date{I}-date{0})/365)  ]

 

And you can apply this to all the successive records in your dataset as you apparently have them.  You only need to guarantee that

  1. The data are grouped by ID
  2. The first record for each ID group is date{0}.  It doesn't matter how the remaining records are sorted

 

 

data have;
  input id  date yymmdd10. flow :comma10.2   pv;
  format date date9.   flow comma9.2;
datalines;
29 2018/02/05     3,500.00 1295.975 
29 2018/02/09         (57.56) 1295.975 
29 2018/02/16         (57.56) 1295.975 
29 2018/02/23         (57.56) 1295.975 
29 2018/03/02         (57.56) 1295.975 
29 2018/03/09         (57.56) 1295.975 
29 2018/03/16         (57.56) 1295.975 
29 2018/03/23         (57.56) 1295.975 
29 2018/03/29         (57.56) 1295.975 
29 2018/04/06         (57.56) 1295.975 
29 2018/04/13         (57.56) 1295.975 
29 2018/04/20         (57.56) 1295.975 
29 2018/04/27         (57.56) 1295.975 
29 2018/05/04         (57.56) 1295.975 
29 2018/05/11         (57.56) 1295.975 
29 2018/05/18         (57.56) 1295.975 
29 2018/05/25         (57.56) 1295.975 
29 2018/06/01         (57.56) 1295.975 
29 2018/06/08         (57.56) 1295.975 
29 2018/06/15         (57.56) 1295.975 
29 2018/06/22         (57.56) 1295.975 
29 2018/06/29         (57.56) 1295.975 
29 2018/07/06         (57.56) 1295.975 
29 2018/07/13         (57.56) 1295.975 
29 2018/07/20         (57.56) 1295.975 
29 2018/07/27         (57.56) 1295.975 
29 2018/08/03         (57.56) 1295.975 
29 2018/08/10         (57.56) 1295.975 
29 2018/08/17         (57.56) 1295.975 
29 2018/08/24         (57.56) 1295.975 
29 2018/08/31         (57.56) 1295.975 
29 2018/09/06           57.56 1295.975 
29 2018/09/06           50.00 1295.975 
29 2018/09/07         (57.56) 1295.975 
29 2018/09/11         (57.56) 1295.975 
29 2018/09/14         (57.56) 1295.975 
29 2018/09/21         (57.56) 1295.975 
29 2018/09/28         (57.56) 1295.975 
29 2018/10/05         (57.56) 1295.975 
29 2018/10/12         (57.56) 1295.975 
29 2018/10/19         (57.56) 1295.975 
29 2018/10/26         (57.56) 1295.975 
29 2018/11/02         (57.56) 1295.975 
29 2018/11/09         (57.56) 1295.975 
29 2018/11/16         (57.56) 1295.975 
29 2018/11/23         (57.56) 1295.975 
29 2018/11/30         (57.56) 1295.975 
29 2018/12/07         (57.56) 1295.975 
29 2018/12/14         (57.56) 1295.975 
29 2018/12/21         (57.56) 1295.975 
29 2018/12/28         (57.56) 1295.975 
29 2019/01/04         (57.56) 1295.975 
31 2018/09/08     5,000.00 3514.378 
31 2018/09/14         (68.96) 3514.378 
31 2018/09/21         (68.96) 3514.378 
31 2018/09/28         (68.96) 3514.378 
31 2018/10/05         (68.96) 3514.378 
31 2018/10/12         (68.96) 3514.378 
31 2018/10/19         (68.96) 3514.378 
31 2018/10/26         (68.96) 3514.378 
31 2018/11/02         (68.96) 3514.378 
31 2018/11/09         (68.96) 3514.378 
31 2018/11/15           68.96 3514.378 
31 2018/11/15           50.00 3514.378 
31 2018/11/16         (68.96) 3514.378 
31 2018/11/16      (118.96) 3514.378 
31 2018/11/23         (68.96) 3514.378 
31 2018/11/30         (68.96) 3514.378 
31 2018/12/07         (68.96) 3514.378 
31 2018/12/14         (68.96) 3514.378 
31 2018/12/19           50.00 3514.378 
31 2018/12/19           68.96 3514.378 
31 2018/12/20         (68.96) 3514.378 
31 2018/12/21         (68.96) 3514.378 
31 2018/12/27         (18.96) 3514.378 
31 2018/12/28         (68.96) 3514.378 
31 2019/01/04         (68.96) 3514.378 
31 2019/01/04      (500.00) 3514.378 
run;

data want (drop=_:);
 _rate=59.85/100;
 do until (last.id);
   set have;
   by id;
   if first.id then _date0=date;
   xnpv=sum(xnpv
           ,flow/((1+_rate)**((date-_date0)/365))
           );
 end;
 do until (last.id);
   set have;
   by id;
   output;
 end;
run;

 

The program above completely one ID group, progressively calculating XNPV.  Then it rereads the same ID group and output the observations with the newly created complete XNPV.

 

Note the DO UNTIL (last.id) has an embedded SET  with BY statement in the do group.  So it's telling sas to read every observation from the first record of an id group to the last record of the same group.

 

If, instead of only the final XPNV, you want XNPV for each successive flow, you could output during the first read of an ID group:

 

data want (drop=_:);
 _rate=59.85/100;
 do until (last.id);
   set have;
   by id;
   if first.id then _date0=date;
   xnpv=sum(xnpv
          	 ,flow/((1+_rate)**((date-_date0)/365))
           );
   output;
 end;
run;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2476 views
  • 0 likes
  • 3 in conversation