Cash Flow and Changing Rate

Reply
Occasional Contributor
Posts: 5

Cash Flow and Changing Rate

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

Super User
Posts: 23,778

Re: Cash Flow and Changing Rate

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


 

 

 

Occasional Contributor
Posts: 5

Re: Cash Flow and Changing Rate

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

Super User
Posts: 23,778

Re: Cash Flow and Changing Rate

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 Smiley Happy.  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??


 

Occasional Contributor
Posts: 5

Re: Cash Flow and Changing Rate

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.

Trusted Advisor
Posts: 1,346

Re: Cash Flow and Changing Rate

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;
Occasional Contributor
Posts: 5

Re: Cash Flow and Changing Rate

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

Super User
Posts: 23,778

Re: Cash Flow and Changing Rate

Your title says Cash Flow but this looks like discounted value or present value calculation.
Super User
Posts: 10,787

Re: Cash Flow and Changing Rate

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 .

Occasional Contributor
Posts: 5

Re: Cash Flow and Changing Rate

Thanks. I'll check the posts you recommended.

 

Ask a Question
Discussion stats
  • 9 replies
  • 103 views
  • 0 likes
  • 4 in conversation