Separar una cantidad en intereses y pago al adeudo (sin tener un plazo fijo)

[apoh07]

¡Hola!

Llevo varios días creando un excel para el manejo de los préstamos que hago. Hasta ahora tengo este diseño:

Es bastante simple y la idea actual es tener una hoja por cada persona (a menos que me propongan algo mejor). Bueno, el punto es que con el diseño actual creo que no es difícil automatizar las últimas tres columnas, pero aquí viene lo divertido: aunque es innecesario, me planteé la duda de si es posible combinar las columnas C y D en sólo una.

Así que he estado como tres días intentando encontrar la manera de, por ejemplo y basándome en los datos de la imagen, poder separar las cantidades que ponga en la columna C en el monto del interés de ese mes (el 10% del saldo a pagar del mes anterior) y, si es mayor, el monto que se va al saldo a pagar. Es decir, separar los 2,000 de C3 en 1,000 del interés del mes y los 1,000 que se abonarían a la deuda total.

Hasta el momento, he logrado calcular el interés del mes actual y los intereses totales que se debían haber pagado hasta el mes actual (en mi mente estos datos me servirían), pero siempre me topo con el problema de no poder separar la cantidad de C3 en "interés" y "pago". Otro detalle es que tampoco quiero que si un mes me pagan menos del 10% que tocaba, el faltante se sume al saldo a pagar y el siguiente mes se cobre interés también sobre ese faltante.

No sé si me expliqué bien, pero en resumen intento que los datos en rojo se calculen automáticamente en este diseño:

De nuevo, sé que lo más sencillo es mantener las columnas de Intereses y Pagos, pero quiero ver si mi idea es posible

De antemano muchas gracias por su tiempo y atención. ¡Quedo atento a su respuesta!

Préstamos2.xlsx

[apoh07]

Comparto cómo solucioné la situación! La clave fue darme cuenta de que podía aplicar otra lógica para calcular cuál era la cantidad total de interés a pagar en el mes actual, y de ahí usar condiciones lógicas. Para quienes les interese, estas son las fórmulas que usé:

E2: =LET(INTERESES,BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,ABONO,SI(($D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1))>=INTERESES,$D2-INTERESES,0)-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1),PAGO,SI((($D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)))<=INTERESES,$D2,$D2-ABONO),(BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)+$C2)-ABONO)

F2: =LET(INTERESES,BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,PAGONETO,SI(($D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)<INTERESES),$D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1),INTERESES+BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)),SI(PAGONETO<INTERESES,BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)+(INTERESES-$D2),BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)-(PAGONETO-INTERESES)))

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

Préstamos.xlsx

[apoh07]

hace 9 horas, apoh07 dijo:

Comparto cómo solucioné la situación! La clave fue darme cuenta de que podía aplicar otra lógica para calcular cuál era la cantidad total de interés a pagar en el mes actual, y de ahí usar condiciones lógicas. Para quienes les interese, estas son las fórmulas que usé:

E2: =LET(INTERESES,BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,ABONO,SI(($D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1))>=INTERESES,$D2-INTERESES,0)-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1),PAGO,SI((($D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)))<=INTERESES,$D2,$D2-ABONO),(BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)+$C2)-ABONO)

F2: =LET(INTERESES,BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10,PAGONETO,SI(($D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)<INTERESES),$D2-BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1),INTERESES+BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)),SI(PAGONETO<INTERESES,BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)+(INTERESES-$D2),BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1)-(PAGONETO-INTERESES)))

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

Préstamos.xlsx 13.48 kB · 1 descarga

  Tuve que corregir la fórmula de la columna E.

E2: =LET(INTERESES,(BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)/10)+BUSCARX($A2,$A$1:$A1,$F$1:$F1,0,0,-1),ABONO,SI($D2>INTERESES,$D2-INTERESES,0),BUSCARX($A2,$A$1:$A1,$E$1:$E1,0,0,-1)+$C2-ABONO)