how do i construct a sas do loop to get the amount of what's left to be paid?

Question

hope you can help me.

The below table wants to show the result I want to get by using a sas code. Basically, it's about calculating the amount of an installment, starting from par_value of 10.000€, with 6% interest rate for 10 years, paying a fixed installment at the end of each period.

year	fixed_installment	interests	capital_paid	loan_paid	to_be_paid
1	1.358,68 €	600,00 €	758,68 €	758,68 €	9.241,32 €
2	1.358,68 €	554,48 €	804,20 €	1.562,88 €	8.437,12 €
3	1.358,68 €	506,23 €	852,45 €	2.415,33 €	7.584,67 €
4	1.358,68 €	455,08 €	903,60 €	3.318,93 €	6.681,07 €
5	1.358,68 €	400,86 €	957,82 €	4.276,75 €	5.723,25 €
6	1.358,68 €	343,40 €	1.015,28 €	5.292,03 €	4.707,97 €
7	1.358,68 €	282,48 €	1.076,20 €	6.368,23 €	3.631,77 €
8	1.358,68 €	217,91 €	1.140,77 €	7.509,01 €	2.490,99 €
9	1.358,68 €	149,46 €	1.209,22 €	8.718,23 €	1.281,77 €
10	1.358,68 €	76,91 €	1.281,77 €	10.000,00 €	0,00 €

interests is obtained by multiplying the 6% interest rate per the par_value;

capital_paid is the difference between the installment and the interest paid for a given year.

loan_paid is the sum of the parts of the capital each year that got paid.

to_be_paid is the difference between the capital to pay at given year minus the capital_paid (the value of the first year in the first row is 10000-1358,68).

Answer 1

What did you tried?

I can propose something like this:

data test;
    attrib year par_value fixed_installment interest_rate interest capital_paid loan_paid to_be_paid format=best.;
    
    *-- Initial values --*;
    par_value = 10000;  
    fixed_installment = 1358.68;
    interest_rate = 0.06;
    loan_paid = 0;
    
    *-- Loop through 10 years --*;
    do year = 1 to 10;
        *-- Calculate interests --*;
        interest = round((par_value - loan_paid) * interest_rate, 0.01);
        
        *-- Calculate capital paid --*;
        capital_paid = fixed_installment - interest;
        
        *-- Calculate loan paid --*;
        loan_paid = loan_paid + capital_paid;
        
        *-- Calculate remaining loan --*;
        to_be_paid = par_value - loan_paid;
        
        output;
    end;
run;