topic Re: Calculating NPV in SAS with dates and amount in SAS Programming

Calculating NPV in SAS with dates and amount

SNG1 — Mon, 14 Jan 2019 18:12:49 GMT

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...

ID	Date	Cash Flow	PV
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

Re: Calculating NPV in SAS with dates and amount

PaigeMiller — Mon, 14 Jan 2019 18:33:45 GMT

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&docsetTarget=p1ar4i3qaux7xsn1wbasf47a18zw.htm&locale=en

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.

Re: Calculating NPV in SAS with dates and amount

SNG1 — Mon, 14 Jan 2019 19:27:08 GMT

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

Re: Calculating NPV in SAS with dates and amount

PaigeMiller — Mon, 14 Jan 2019 20:49:04 GMT

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

Re: Calculating NPV in SAS with dates and amount

SNG1 — Mon, 14 Jan 2019 21:36:32 GMT

Nope it does not

Re: Calculating NPV in SAS with dates and amount

mkeintz — Tue, 15 Jan 2019 00:37:26 GMT

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

The data are grouped by ID
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;