topic IRR function with large number of payments in SAS Programming

IRR function with large number of payments

J_Yetman — Mon, 06 Mar 2023 16:07:20 GMT

I need to calculate the IRR function with a large number of payments (loans with 360 monthly payments).

The IRR function does calculate the correct value if I list out all 360 payments one by one. I'm looking for a better way to write the code so I'm not writing out the monthly payment 360 times in the code.

Re: IRR function with large number of payments

ballardw — Mon, 06 Mar 2023 16:36:15 GMT

Do you have the payments in a data set? If so, show an example of the data set.

Here is one example using an array to hold multiple payments for use in IRR function. For pretty obvious reasons I'm not going to dummy up 100's of values.

data have;
   input p1-p5;
datalines;
-100 200 100 200 100
-50   10  20  30  40
;

data want;
  set have;
  array p(*) p1-p5;
  rate=irr(1,of p(*));
run;

@J_Yetman wrote:

I need to calculate the IRR function with a large number of payments (loans with 360 monthly payments).

The IRR function does calculate the correct value if I list out all 360 payments one by one. I'm looking for a better way to write the code so I'm not writing out the monthly payment 360 times in the code.

Re: IRR function with large number of payments

J_Yetman — Mon, 06 Mar 2023 16:40:10 GMT

The payments are not variable. It's the same payment repeating. So it would be something like:

IRR(1,-100000,500,500,500,....,500)

Re: IRR function with large number of payments

ballardw — Mon, 06 Mar 2023 17:55:57 GMT

Didn't answer the question: WHERE do these values currently reside?

If they are not in a data set then you basically have no option but to to type them in. Or read them into a data set to allow something similar to the way I showed above.

If they are in a data set, then show the structure of that data set.

IF it is ALWAYS the same value then you could build that in a data step like so;

data have;
  array p (50) ;
  p1=-10000;
  do i=2 to 50;
    p[i]=500;
  end;
  rate = irr(1,of p(*));
run;

Re: IRR function with large number of payments

J_Yetman — Mon, 06 Mar 2023 17:57:40 GMT

Thanks, I understand now. They currently aren't in a data set (at least not in the way that you mean - the value of the payment is pulled from a single variable field in a table). So I'd have to create one.

Re: IRR function with large number of payments

Tom — Mon, 06 Mar 2023 19:03:26 GMT

@J_Yetman wrote:

Thanks, I understand now. They currently aren't in a data set (at least not in the way that you mean - the value of the payment is pulled from a single variable field in a table). So I'd have to create one.

You could just use a little code generation to reuse the same variable name over and over.

For example this code uses the value of the variable PAYMENT 5 times (the REPEAT function wants the number of EXTRA copies not the total number of copies).

data test;
  input id payment ;
  irr5=IRR(1,-100000,of %sysfunc(repeat(%str(payment ),4)));
cards;
1 100
2 200
3 300
4 10000
;