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 ... View more

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!       ... View more

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 ... View more

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 ... View more