DATA Step, Macro, Functions and more

variable arguments for XIRR

Reply
Occasional Contributor
Posts: 5

variable arguments for XIRR

[ Edited ]

Hi everybody,

 

I want to calculate the IRR for a series of loans. I can achieve, but I am sure there is a better way of doing it

 

Below is the code I am using, which has two drawbacks:

1- It looks long and clumsy

2- It works when all the loans have the same amount of payments. I guess I could replace missing values with zeros and it won't affect the outcome

 

Do you know if PROC FCMP or PROC SQL can help?

 

data dataset_loan;
input ID $6. date date9. amount;
format date date9. amount DOLLARX6.;
datalines;
Loan1 01JAN2017 -100
Loan1 01FEB2017 35
Loan1 01MAR2017 35
Loan1 01APR2017 35
Loan2 05JAN2017 -100
Loan2 09FEB2017 35
Loan2 12MAR2017 35
Loan2 14APR2017 35
;

PROC TRANSPOSE DATA=dataset_loan
OUT=dataset_amount
PREFIX=amount
NAME=Source
LABEL=Label
;
BY ID;
VAR amount;
RUN; QUIT;

PROC TRANSPOSE DATA=dataset_loan
OUT=dataset_date
PREFIX=date
NAME=Source
LABEL=Label
;
BY ID;
VAR date;
RUN; QUIT;

DATA dataset_xirr;
MERGE dataset_date dataset_amount;
BY ID;
DROP Source;
IRR = finance('xirr', of amount:, of date: );
FORMAT IRR PERCENT8.2;
RUN;

 

 

 

 

 

 

Ask a Question
Discussion stats
  • 0 replies
  • 84 views
  • 0 likes
  • 1 in conversation