topic Re: Cash Flow and Changing Rate in SAS Programming

Cash Flow and Changing Rate

YoungSun — Tue, 10 Jul 2018 14:26:21 GMT

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

Re: Cash Flow and Changing Rate

Reeza — Tue, 10 Jul 2018 15:09:19 GMT

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

Re: Cash Flow and Changing Rate

Reeza — Tue, 10 Jul 2018 15:29:48 GMT

Your title says Cash Flow but this looks like discounted value or present value calculation.

Re: Cash Flow and Changing Rate

YoungSun — Wed, 11 Jul 2018 00:23:28 GMT

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

Re: Cash Flow and Changing Rate

Reeza — Wed, 11 Jul 2018 01:44:02 GMT

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

Re: Cash Flow and Changing Rate

YoungSun — Wed, 11 Jul 2018 02:18:13 GMT

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.

Re: Cash Flow and Changing Rate

mkeintz — Wed, 11 Jul 2018 05:34:22 GMT

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;

Re: Cash Flow and Changing Rate

YoungSun — Wed, 11 Jul 2018 06:07:15 GMT

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

Re: Cash Flow and Changing Rate

Ksharp — Wed, 11 Jul 2018 13:12:59 GMT

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 .

Re: Cash Flow and Changing Rate

YoungSun — Tue, 17 Jul 2018 01:00:02 GMT

Thanks. I'll check the posts you recommended.