Set aside an amount for interest and payment on debit (without having a fixed term)

I think my formulae might be too simple because I'm not sure where the cyclic bit is.

I put "=C2*10%" into cell D3 and "=MAX('Diseño deseado'!D3-D3,0)" into cell D4.

Thank you very much for your reply!

In the file I attached I only filled in a few lines to have data to work on, but in practice I will be adding lines every time I lend or receive money.

Note that in column D I will have to enter data, and only the last three columns are automated.

Basically, I want to not have to separate interest and payment. In my current design for everything to work, if one month I get paid 2,000 (1,000 interest and 1,000 to credit the debt balance), I have to put that amount separately in columns D and E. My goal is to be able to put 2,000 directly in one column and have it all automatically calculated.

Let's take an example:

If I lend 10,000, every month I should be paid 1,000 in interest. This is not a fixed term, so they could be like this for a year and, although they would have already paid me 12,000 in interest, they would still owe me the 10,000 of the original balance. This is because the original balance only decreases when one month I am paid an amount greater than the interest due.

In other words, if one month I am paid 2,000, the original balance would decrease to 9,000 and the following month I would only be paid 900. To achieve this, in my current design I should put the interest payment in column D and, if I am overpaid to reduce the original balance, put that excess amount in column E. So my goal is to not have to use two columns.

But, as I comment in the publication, in case my idea is possible, I also want to avoid that, if in the first month I only get paid 500, the missing 500 is taken as part of the debt and the following month it is calculated that I should be paid 1,050 of interest (10,500/10).

Originally Posted by Beamernsw

I think my formulae might be too simple because I'm not sure where the cyclic bit is.

I put "=C2*10%" into cell D3 and "=MAX('Diseño deseado'!D3-D3,0)" into cell D4.

Thank you very much for your reply!

In the file I attached I only filled in a few lines to have data to work on, but in practice I will be adding lines every time I lend or receive money.

Note that in column D I will have to enter data, and only the last three columns are automated.

Basically, I want to not have to separate interest and payment. In my current design for everything to work, if one month I get paid 2,000 (1,000 interest and 1,000 to credit the debt balance), I have to put that amount separately in columns D and E. My goal is to be able to put 2,000 directly in one column and have it all automatically calculated.

Let's take an example:

If I lend 10,000, every month I should be paid 1,000 in interest. This is not a fixed term, so they could be like this for a year and, although they would have already paid me 12,000 in interest, they would still owe me the 10,000 of the original balance. This is because the original balance only decreases when one month I am paid an amount greater than the interest due.

In other words, if one month I am paid 2,000, the original balance would decrease to 9,000 and the following month I would only be paid 900. To achieve this, in my current design I should put the interest payment in column D and, if I am overpaid to reduce the original balance, put that excess amount in column E. So my goal is to not have to use two columns.

But, as I comment in the publication, in case my idea is possible, I also want to avoid that, if in the first month I only get paid 500, the missing 500 is taken as part of the debt and the following month it is calculated that I should be paid 1,050 of interest (10,500/10).

Hi again, I've had a look and totally changed the way you were working it out (hope that's ok).
I think I have it doing what you are asking.

For an explanation for how I used the lookup function, I grabbed it from here:-
https://trumpexcel.com/find-last-occurrence/

Originally Posted by Beamernsw

Hi again, I've had a look and totally changed the way you were working it out (hope that's ok).
I think I have it doing what you are asking.

Thank you very much for your interest! I think you liked the challenge xD

Your approach is pretty good, but it still doesn't quite achieve what I'm looking for. Maybe it's because I'm not explaining myself well enough.

Let's take an example and calculate four rows of columns E, F and G! (E=balance payable, F=interest on debt, G=profit).

If in C3 I put that I lent 10,000 and the following month in D4 I put that I was paid 2,000, the values should be:
E4:9,000 (The 10,000 from the original loan - the 1,000 surplus from this month's payment).
F4:0 (this month the 1,000 of interest was paid)
G4:1,000 (so far, this is the total amount of interest paid in full, and is counted as profit because it is not reduced from the original loan).

The next month the interest payment should be 900 (10% of 10,000 - 1,000), if in D5 I put that I was paid 400, then the values should be:
E5:9,000 (the balance to be paid does not change because this month there was no 10% overage).
F5:500 (the 10% that corresponded to this month [900] - the 400 that were paid out
G5:1,400

Now I will just put the values to go faster and not be redundant. If D6:400, then:
E6:9,000
F6:1,000
G6:1,800

If D7:2,900 (900 of current interest, 1,000 of interest owed, 1,000 to reduce the balance due), then
E7:8,000
F7:0
G7:3,700

I hope I have explained myself well and that the example helps you!

I share how I solved the situation! The key was to realize that I could apply another logic to calculate what was the total amount of interest to be paid in the current month, and from there use logical conditions. For those who are interested, these are the formulas I used:

(The function names may be incorrect because I translated everything from Spanish, but I attach the final file).

E2: =LET(INTEREST,SEARCHX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,CREDIT,IF(($D2-SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,-1))>=INTEREST,$D2-INTEREST,0)-SEARCHX($A2,$A$1: $A1,$F$1:$F1,0,0,-1),PAYMENT,IF(((($D2-SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,0,-1)))<=INTEREST,$D2,$D2-CREDIT),(SEARCHX($A2,$A$1:$A1,$E$1:$E1,0,0,0,-1)+$C2)-CREDIT)

F2: =LET(INTEREST,FINDX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,PAGONET,IF(($D2-FINDX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)<INTEREST),$D2-FINDX($A2,$A$1:$A1,$F$1:$F1,$F1,0,0,-1),INTEREST+FINDX($A2,$A$1: $A1,$F$1:$F1,0,0,-1)),IF(PAGONET<INTEREST,SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,0,-1)+(INTEREST-$D2),SEARCHX($A2,$A$1:$A1,$F$1:$F1,$F$1:$F1,0,0,-1)-(PAGONET-INTEREST))))

G2: =SUM.SI($A$1:$A1,$A2,$E$1:$E1)/10-$F2

Originally Posted by apoh07

I share how I solved the situation! The key was to realize that I could apply another logic to calculate what was the total amount of interest to be paid in the current month, and from there use logical conditions. For those who are interested, these are the formulas I used:

(The function names may be incorrect because I translated everything from Spanish, but I attach the final file).

E2: =LET(INTEREST,SEARCHX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,CREDIT,IF(($D2-SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,-1))>=INTEREST,$D2-INTEREST,0)-SEARCHX($A2,$A$1: $A1,$F$1:$F1,0,0,-1),PAYMENT,IF(((($D2-SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,0,-1)))<=INTEREST,$D2,$D2-CREDIT),(SEARCHX($A2,$A$1:$A1,$E$1:$E1,0,0,0,-1)+$C2)-CREDIT)

F2: =LET(INTEREST,FINDX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,PAGONET,IF(($D2-FINDX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)<INTEREST),$D2-FINDX($A2,$A$1:$A1,$F$1:$F1,$F1,0,0,-1),INTEREST+FINDX($A2,$A$1: $A1,$F$1:$F1,0,0,-1)),IF(PAGONET<INTEREST,SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,0,-1)+(INTEREST-$D2),SEARCHX($A2,$A$1:$A1,$F$1:$F1,$F$1:$F1,0,0,-1)-(PAGONET-INTEREST))))

G2: =SUM.SI($A$1:$A1,$A2,$E$1:$E1)/10-$F2

I had to correct the formula in column E.

E2: =LET(INTEREST,(SEARCHX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10)+SEARCHX($A2,$A$1:$A1,$F$1:$F1,0,0,0,-1),CREDIT,IF($D2>INTEREST,$D2-INTEREST,0),SEARCHX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)+$C2-CREDIT)

Originally Posted by Beamernsw

Sorry I didn't get back to you before, I've been sick but all good now.
I'm glad you got it sorted out though

I'm glad that you're better!