Iteratively sum multiple observations by group

[WillemM]

I have a set of mortgage loans, consisting of 1 or more loan parts, for which I want to calculate the cash flows. This is easily done when the interest rate is fixed. However, in this case the interest rate on the loan parts depends on the LtV (Loan amount divided by property value) of the whole loan. After every monthly redemption, the loan part amount as well as the loan amount (sum of the loan part amounts, Loan_Number is the "by group") decreases by the redemption amount. However I do not know how to calculate the loan amount efficiently after every iteration. See below for some sample data and the code that I have already.

data Loans; 
   input Loan_Number Loan_Part_Number Property_Value Loan_Amount Loan_Part_Amount N_Months_Left Interest_LtV_gt_50 Interest_LtV_le_50;
   datalines;
10001 1 500000 400000 400000 3 3.0 1.5
10004 1 300000 210000 130000 3 2.5 1.0
10004 3 300000 210000 80000 2 2.0 1.0
;
run;

data Cashflows_Test;
	set Loans;

/*	Set Month to 1 at the beginning*/
	Month = 1;
	do while (N_Months_Left > 0);
/*		calculate LtV*/
		LtV = Loan_Amount/Property_Value;

/*		Determine interest rate*/
		if LtV > 0.5 then interest = Interest_LtV_gt_50;
		else Interest = Interest_LtV_le_50;

/*		Calculate payment and redemption*/
		payment = mort(Loan_Part_Amount,.,Interest/100,N_Months_Left);
		Redemption = Payment - Loan_Part_Amount*Interest/100;

		output;

/*		Calculate Loan_Part_Amount and N_Months_Left after redemption and set Month to Month + 1 for the next iteration*/
		Loan_Part_Amount = Loan_Part_Amount - Redemption;
		N_Months_Left = N_Months_Left - 1;
		Month = Month + 1;
	end;
run;

This code does not update the loan amount after every iteration. I would appreciate it if you can help me with an efficient way to do this.

Thanks,

Willem

[ChrisNZ]

You never reset the loan amount, only Loan_Part_Amount.

You could write your (unchanged) logic as:

data Cashflows_Test;
	set Loans;

/*	Set Month to 1 at the beginning*/
	Month = 1;
	do while (N_Months_Left > 0);
/*		calculate LtV*/
		LtV = divide(Loan_Amount,Property_Value);

/*		Determine interest rate*/
		interest = ifn( LtV > 0.5, Interest_LtV_gt_50, Interest_LtV_le_50);

/*		Calculate payment and redemption*/
		payment = mort(Loan_Part_Amount, ., Interest/100, N_Months_Left);
		Redemption = Payment - Loan_Part_Amount * Interest / 100;

		output;

/*		Calculate Loan_Part_Amount and N_Months_Left after redemption and set Month to Month + 1 for the next iteration*/
		Loan_Part_Amount + - Redemption;
		N_Months_Left + - 1;         
		Month + 1;
	end;
run;

[WillemM]

Hi ChrisNZ,

Thanks for your improvements. Resetting Loan_Amount after every iteration is actually the main problem I'm facing because it's the sum of Loan_Part_Amount of multiple observations (grouped by Loan_Number). I don't know how to do that efficiently. Any help on that would be much appreciated.

Willem

[ChrisNZ]

I am unsure of the calculation you want but "sum of Loan_Part_Amount of multiple observations (grouped by Loan_Number)" is done this way:

LOAN_AMOUNT + Loan_Part_Amount ;

[WillemM]

Hi Chris,

I slightly amended that code in the original post as there was a typo in it: The initial Loan_Part_Amount of loan 10001, loan part 1 should be $400,000 instead of $150,000.

To further clarify my answer, have a look at the Loan_Amount for Loan 10004. For the first month, the Loan_Amount is $210,000, $130,000 from loan part 1 and $80,000 from loan part 3. However, the Loan_Part_Amount of both loan parts decreases because of the redemption to $87,732.17 and $40,396.04 respectively. What I want is that the Loan_Amount in those rows is equal to the sum of the Loan_Part_Amounts (i.e. $128,128.21), because that is the amount on which the determination of the interest rate is based. The same holds for all the numbers in yellow in the figure below:

So, the Loan_Amount in a certain Month, should be equal to the sum of the Loan_Part_Amounts of that Month, grouped by Loan_Number. This sum must be calculated during the loop because it is used to determine LtV, Interest and Redemption, which determines the Loan_Part_Amounts for the next iteration.

Thanks in advance,

Willem

[ChrisNZ]

Like this?

proc sql;
  create table WANT as 
  select a.*, b.NEW_AMOUNT
  from CASHFLOWS_TEST a
         natural join
      (select LOAN_NUMBER, MONTH, sum(LOAN_PART_AMOUNT) as NEW_AMOUNT from CASHFLOWS_TEST group by 1,2) b
  order by a.LOAN_NUMBER, a.LOAN_PART_NUMBER, a.MONTH ;

[WillemM]

This calculates the sum of the loan parts after having made all the cashflows, however because the interest rate and thus the redemption depend on the Loan_Amount after every iteration, the calculation goes all wrong.

Let me explain a bit further. The calculations have to be in this order for every month:

1. LtV = Loan_Amount/Property_Value

2. If LtV > 0.5 then Interest = Interest_LtV_gt_50 else Interest = Interst_LtV_le_50

3. Payment = mort(Loan_Part_Amount,.,Interest/100,N_Months_Left)

4. Redemption = Payment - Loan_Part_Amount*Interest/100

5. output

6. Loan_Part_Amount = Loan_Part_Amount - Redemption

7. N_Months_Left = N_Months_Left - 1;
8. Month = Month + 1;

9. Loan_Amount = sum(Loan_Part_Amount) grouped by Loan_Number

After this the calculations are done for the next month as long as N_Months_Left > 0

In my original code you can see the first 8 steps, however I can't manage to do step 9 in the same data step because it needs the calculated Loan_Part_Amount of more than 1 observation. 

Is there a way to do this in the same data step? 

The outcome should be the following:

data Cashflows;
	input Loan_Number Loan_Part_Number Month Property_Value Loan_Amount Loan_Part_Amount N_Months_Left Interest_LtV_gt_50 Interest_LtV_le_50 LtV Interest Payment Redemption;
   datalines;
10001 1 1 500000 400000.00 400000.00 3 3 1.5 0.8 3 141412.15  129412.15 
10001 1 2 500000 270587.85 270587.85 2 3 1.5 0.541175709 3 141412.15  133294.51 
10001 1 3 500000 137293.34 137293.34 1 3 1.5 0.27458669 1.5 139352.75  137293.34 
10004 1 1 300000 210000.00 130000.00 3 2.5 1 0.7 2.5 45517.83  42267.83 
10004 1 2 300000 128128.21 87732.17 2 2.5 1 0.427094026 1 44525.17 43647.84 
10004 1 3 300000 44084.32 44084.32 1 2.5 1 0.146947745 1 44525.17 44084.32 
10004 3 1 300000 210000.00 80000.00 2 2 1 0.7 2 41203.96 39603.96 
10004 3 2 300000 128128.21 40396.04 1 2 1 0.427094026 1 40800.00 40396.04 
;
run;

Thanks!

[ChrisNZ]

Something like this should not be far:

data CASHFLOWS_TEST;

  retain FIRST_OBS LAST_OBS MAX_MONTHS_LEFT;
  array REDEMPTIONS[3] ;*_temporary_;    

  set LOANS;                       
  by LOAN_NUMBER;
  if first.LOAN_NUMBER then do; FIRST_OBS=_N_; call missing (of REDEMPTIONS[*], MAX_MONTHS_LEFT); end;

  %* Calculate redemption for first iteration (first month) for each loan parts, save REDEMPTION values ;
  MONTH           = 1;
  LTV             = divide(LOAN_AMOUNT,PROPERTY_VALUE);
  INTEREST        = ifn( LTV > 0.5, INTEREST_LTV_GT_50, INTEREST_LTV_LE_50);
  PAYMENT         = mort(LOAN_PART_AMOUNT, ., INTEREST/100, N_MONTHS_LEFT);
  REDEMPTION      = PAYMENT - LOAN_PART_AMOUNT * INTEREST / 100;
  %* Update values for next iteration;
  MAX_MONTHS_LEFT = max(MAX_MONTHS_LEFT, N_MONTHS_LEFT); 
  REDEMPTIONS[LOAN_PART_NUMBER] = REDEMPTION*(N_MONTHS_LEFT>0); 
  output;

  %* Calculate next iterations once all loan parts have been read ;
  if last.LOAN_NUMBER then do;
    LAST_OBS=_N_;
    %* Read as long as some loan parts have remaining MONTH;
    do while(MAX_MONTHS_LEFT>1);
      MONTH           + 1;
      MAX_MONTHS_LEFT = 0; 
      do OBS=FIRST_OBS to LAST_OBS;                         
        set LOANS point=OBS;
        N_MONTHS_LEFT    + - MONTH +1;         
        %* If this loan part has no MONTH left, then leave;
        if ^N_MONTHS_LEFT then leave;
        %* Update loan amounts, remove previous amounts;
        LOAN_AMOUNT      + - sum(of REDEMPTIONS[*]);              
        LOAN_PART_AMOUNT + - REDEMPTIONS[LOAN_PART_NUMBER];
        %* Calculate redemption for MONTH;
        LTV              = divide(LOAN_AMOUNT,PROPERTY_VALUE);
        INTEREST         = ifn( LTV > 0.5, INTEREST_LTV_GT_50, INTEREST_LTV_LE_50);
        PAYMENT          = mort(LOAN_PART_AMOUNT, ., INTEREST/100, N_MONTHS_LEFT);
        REDEMPTION       = PAYMENT - LOAN_PART_AMOUNT * INTEREST / 100;        
        %* Update values for next iteration;
        MAX_MONTHS_LEFT  = max(MAX_MONTHS_LEFT, N_MONTHS_LEFT); 
        REDEMPTIONS[LOAN_PART_NUMBER] + REDEMPTION*(N_MONTHS_LEFT>0); 
        output;

      end;
    end;
  end;

run;

[WillemM]

Hi ChrisNZ,

The LOAN_AMOUNT is now updated between the calculations of loan parts. So only for the first loan part, the correct LOAN_AMOUNT is calculated. Calculating this correct for all loan parts is what I've been struggling with the most.

Willem