cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
SNG1
Calcite | Level 5 SNG1
Calcite | Level 5

Calculating NPV in SAS with dates and amount

Posted 01-14-2019 01:12 PM (2474 views)

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
0 Likes
Reply
5 REPLIES 5
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Calculating NPV in SAS with dates and amount

Posted 01-14-2019 01:32 PM (2463 views)  |   In reply to SNG1

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
0 Likes
Reply
SNG1
Calcite | Level 5 SNG1
Calcite | Level 5

Re: Calculating NPV in SAS with dates and amount

Posted 01-14-2019 02:27 PM (2448 views)  |   In reply to PaigeMiller

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

0 Likes
Reply
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Calculating NPV in SAS with dates and amount

Posted 01-14-2019 03:49 PM (2439 views)  |   In reply to SNG1

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

--
Paige Miller
0 Likes
Reply
SNG1
Calcite | Level 5 SNG1
Calcite | Level 5

Re: Calculating NPV in SAS with dates and amount

Posted 01-14-2019 04:36 PM (2430 views)  |   In reply to PaigeMiller

Nope it does not

0 Likes
Reply
mkeintz
PROC Star mkeintz
PROC Star

Re: Calculating NPV in SAS with dates and amount

Posted 01-14-2019 07:37 PM (2414 views)  |   In reply to SNG1

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

--------------------------
0 Likes
Reply

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.

Watch How to Concatenate Values on YouTube

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
  • ‎01-14-2019 01:12 PM
  • 2475 views
  • 0 likes
  • 3 in conversation