BookmarkSubscribeRSS Feed
YoungSun
Calcite | Level 5

Hello 

 

I'm trying to make code with some cash flows and rates data.

 

The data is as follows 

 

Period       CF           Rate

1               1,200       0.04

2               1,100       0.05

3               1,000       0.06

 

Inv : 2,000

 

I want to get "x" value like this

 

2,000 = 1,200*(1-0.04*x) + 1,100*(1-0.04x)*(1-0.05x) + 1,000*(1-0.04x)*(1-0.05x)*(1-0.06x)

 

I use SAS Enterprise Guide that doesn't include proc optmodel, proc iml.

 

Could anyone help me out??

9 REPLIES 9
Reeza
Super User

You can do this with a data step and a RETAIN.

But are you sure your logic as shown is correct or what you want? From what I know of cash flow logic, your formula doesn't look correct.

 


@YoungSun wrote:

Hello 

 

I'm trying to make code with some cash flows and rates data.

 

The data is as follows 

 

Period       CF           Rate

1               1,200       0.04

2               1,100       0.05

3               1,000       0.06

 

Inv : 2,000

 

I want to get "x" value like this

 

2,000 = 1,200*(1-0.04*x) + 1,100*(1-0.04x)*(1-0.05x) + 1,000*(1-0.04x)*(1-0.05x)*(1-0.06x)

 

I use SAS Enterprise Guide that doesn't include proc optmodel, proc iml.

 

Could anyone help me out??


 

 

 

YoungSun
Calcite | Level 5

Thanks, Reeza

 

Yes, right~ it's not a typical cash flow logic. It's a special logic.

 

How can I find "x" value with RETAIN statements??

Reeza
Super User

I interpreted your question incorrectly. This is a problem for NLIN or possibly PROC OPTMODEL. It's also basic math, but that's rusty for me these days :).  I believe IML would also work. Do you have the licenses for SAS/IML and SAS/OR?


You can check with:

proc product_status;run;

 


@YoungSun wrote:

Thanks, Reeza

 

Yes, right~ it's not a typical cash flow logic. It's a special logic.

 

How can I find "x" value with RETAIN statements??


 

YoungSun
Calcite | Level 5

I don't have a license for IML or OR.

 

I'll check the procedure you recommended and if you don't mind I'll ask you again.

 

Thanks for your kindness.

mkeintz
PROC Star

You could do this in a data step by updating  and retaining a cumulative discount rate:

 

data want;
  set have;
  retain cum_disc_rate 1;

  cum_disc_rate=cum_disc_rate*(1-rate);
  cum_disc_cf=cf*cum_disc_rate;
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

--------------------------
YoungSun
Calcite | Level 5

Thanks for your reply but I want to find "x" value.

[2,000 = 1,200*(1-0.04*x) + 1,100*(1-0.04x)*(1-0.05x) + 1,000*(1-0.04x)*(1-0.05x)*(1-0.06x)]

 

Can I get "x" value by using PROC MODEL with RETAIN??

Reeza
Super User
Your title says Cash Flow but this looks like discounted value or present value calculation.
Ksharp
Super User

That is best solved by IML/OR ( FROOT() , POLYROOT() ). Calling @Rick_SAS

If you don't have IML, Try  PROC FCMP has a function SOLVE(). I remembered @PGStats post some kind of code .

YoungSun
Calcite | Level 5

Thanks. I'll check the posts you recommended.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 714 views
  • 0 likes
  • 4 in conversation