Arma tu plantilla de cobranza en Excel, con las fórmulas puestas
Dos hojas, veinticuatro columnas y doce fórmulas. Eso es todo lo que necesita una hoja para llevar préstamos, calcular el interés, mostrar el saldo y pintar de rojo al que está en mora. Está completa aquí abajo: cópiala y quédate con ella.
No hay archivo que bajar ni correo que dejar. La plantilla es la estructura, no el adjunto: con la estructura la ajustas a tu forma de cobrar; con el adjunto te toca pelearte con la de otro.
Qué tiene que hacer la hoja para servir de algo
Una plantilla puede quedarse en una lista de nombres con una columna de «debe». Eso todavía no es una hoja de cobranza: es el cuaderno, pero en la pantalla. Para reemplazar de verdad al cuaderno, tiene que responder cuatro preguntas sin que tú hagas ninguna cuenta.
Cuánto debe cada cliente hoy, sin que tengas que restar abonos a mano. Cuánto tendría que haber pagado ya a esta altura del crédito, que no es lo mismo. Cuánto entró hoy, para cuadrar la caja antes de dormir. Y quién está en mora, para saber a quién visitar mañana.
La cuarta es la que separa una hoja útil de una hoja bonita. La mora no se ve mirando el saldo: un cliente con saldo de 400.000 puede estar impecable y otro con saldo de 90.000 puede llevar diez días sin aparecer. La única forma de saberlo es comparar lo que ha pagado contra lo que ya debería haber pagado según los días corridos. Esa comparación se hace con una fórmula, no con la memoria.
El resto de esta página monta esa hoja pieza por pieza. Si prefieres entender primero el cálculo de cuotas, capital e interés, empieza por la guía de tabla de amortización de un préstamo y vuelve aquí.
Hoja 1: «Prestamos», una fila por crédito
Encabezados en la fila 1 y datos desde la fila 2. Solo escribes de la columna A a la G: las diez siguientes se calculan solas. Pega cada fórmula en la fila 2 y arrástrala hasta abajo.
| Col. | Campo | Fórmula (fila 2) | Para qué |
|---|---|---|---|
| A | ID | se digita | P-001, P-002… Es la llave que amarra todo. |
| B | Cliente | se digita | Nombre completo. |
| C | Ruta | se digita | Zona o barrio. Sirve para filtrar la visita del día. |
| D | Fecha desembolso | se digita | Formato Fecha corta. |
| E | Capital | se digita | Lo que entregaste en la mano. |
| F | Interés | se digita | Formato Porcentaje. 20% se escribe 20%, no 0,2. |
| G | N.º de cuotas | se digita | Diarias, semanales o quincenales. |
| H | Total a pagar | =E2*(1+F2) | Capital más interés del crédito. |
| I | Valor cuota | =REDONDEAR.MAS(H2/G2;-2) | Redondeado a la centena de arriba. |
| J | Última cuota | =H2-(G2-1)*I2 | Absorbe el sobrante del redondeo. |
| K | Cobrado | =SUMAR.SI(Abonos!$B$2:$B$5000;$A2;Abonos!$D$2:$D$5000) | Suma todos los abonos de ese ID. |
| L | Saldo | =H2-K2 | Lo que falta por recoger. |
| M | Cuotas vencidas | =MIN($G2;MAX(0;DIAS.LAB.INTL($D2;HOY();"0000001")-1)) | Días de cobro corridos, sin domingos. |
| N | Debía llevar | =MIN($H2;$M2*$I2) | Lo que ya tendría que estar cobrado. |
| O | Atraso | =MAX(0;$N2-$K2) | La mora, en pesos. |
| P | Cuotas en mora | =SI($I2=0;0;REDONDEAR.MENOS($O2/$I2;0)) | La mora, en cuotas. |
| Q | Estado | =SI($L2<=0;"PAGADO";SI($O2<=0;"AL DÍA";SI($P2<3;"ATRASO";"MORA"))) | El semáforo de la cartera. |
Los rangos van acotados a la fila 500 a propósito. Si apuntas a la columna entera, Excel recalcula un millón de celdas cada vez que registras un abono y la hoja se vuelve lenta en un teléfono. Sube el 500 cuando lo necesites.
Las tres fórmulas del dinero, con números que puedes rehacer
Un préstamo de $500.000 al 20%, en 26 cuotas diarias. Es el caso típico del cobro diario de lunes a sábado durante un mes. Sigue la cuenta con la calculadora:
-
1. Total a pagar — columna H
=E2*(1+F2)500.000 × 1,20 = 600.000. El interés se aplica sobre el capital una sola vez, que es como se pacta en la calle. Si tu tasa es mensual y el plazo se corre, necesitas otra fórmula: lo explica la guía de cómo calcular el interés diario de un préstamo.
-
2. Valor de la cuota — columna I
=REDONDEAR.MAS(H2/G2;-2)600.000 ÷ 26 = 23.076,92. Nadie cobra 92 pesos. El -2 redondea a la centena de arriba: 23.100. Hacia arriba y no hacia abajo, porque el sobrante se descuenta al final y no se le cobra de más al cliente.
-
3. La última cuota — columna J
=H2-(G2-1)*I2Las 25 primeras cuotas suman 25 × 23.100 = 577.500. Entonces la última es 600.000 − 577.500 = 22.500. Ese peso de diferencia es la discusión más común del oficio: el cliente que paga 600 pesos de más en la última visita y se queda con la duda. Con esta columna, la hoja se lo dice antes.
Son tres columnas cortas y hacen el trabajo que si no acaba en la calculadora del celular delante del cliente. Con ellas, dos créditos iguales se calculan igual aunque los monte otra persona.
Hoja 2: «Abonos», una fila por pago
Este es el error que hunde a casi todas las plantillas: registrar los pagos encima del préstamo, en columnas «Pago 1», «Pago 2», «Pago 3». Cuando el cliente abona dos veces en un día o paga de más, la fila se queda sin espacio. Los abonos van en su propia hoja, uno debajo del otro, para siempre.
| Col. | Campo | Fórmula (fila 2) | Para qué |
|---|---|---|---|
| A | Fecha | se digita | Ctrl + ; escribe la fecha de hoy sin soltar el teclado. |
| B | ID préstamo | se digita | Con lista desplegable: nunca se teclea a mano. |
| C | Cliente | =SI($B2="";"";BUSCARV($B2;Prestamos!$A$2:$B$500;2;FALSO)) | Se rellena solo al elegir el ID. |
| D | Monto | se digita | El abono. Aquí se digita y nada más. |
| E | Forma de pago | se digita | Efectivo, transferencia, Nequi… |
| F | Cobrador | se digita | Necesario para cuadrar caja por persona. |
| G | Saldo del préstamo | =SI($B2="";"";BUSCARV($B2;Prestamos!$A$2:$L$500;12;FALSO)) | Ojo: muestra el saldo de hoy, no el del día del abono. |
La columna B nunca se escribe a mano. Selecciónala, entra en Datos → Validación de datos → Permitir: Lista y pon como origen =Prestamos!$A$2:$A$500. A partir de ahí el ID se elige de una lista desplegable y desaparece de un golpe el error más caro de todos: un abono registrado con un ID que no existe, que no suma en ninguna parte y que aparece como plata perdida al cierre.
Que la hoja detecte la mora sola
La columna M cuenta cuántos días de cobro han pasado desde el desembolso, saltándose los domingos:
=MIN($G2;MAX(0;DIAS.LAB.INTL($D2;HOY();"0000001")-1)) Los siete dígitos de "0000001" son los días de lunes a domingo, y el 1 marca el que no se cobra. Se resta 1 porque el día del desembolso no se cobra cuota. El MIN impide que el contador siga corriendo después de la última cuota, y el MAX evita números negativos si registras un préstamo con fecha futura.
Con eso, el resto sale solo. Ejemplo con el mismo crédito de 23.100 por cuota: han corrido 10 días de cobro y el cliente ha pagado 8 cuotas. Debía llevar 10 × 23.100 = 231.000 y lleva 8 × 23.100 = 184.800. El atraso es 46.200, o sea 2 cuotas. La columna Q lo marca como ATRASO; a la tercera cuota sin pagar pasa a MORA.
Dónde poner el umbral es decisión tuya. Tres cuotas funciona para cobro diario. Para cobro semanal, una sola cuota vencida ya es una visita urgente: cambia el 3 por un 1 en la columna Q. Sobre cuándo llamar y qué decir, el sitio tiene una guía de cobranza preventiva que evita llegar a esta columna.
Formato condicional: que la mora se vea de lejos
Selecciona el rango A2:Q500 y ve a Inicio → Formato condicional → Nueva regla → Utilice una fórmula que determine las celdas para aplicar formato. Crea una regla por cada línea:
| Fórmula de la regla | Formato y para qué |
|---|---|
=$Q2="MORA" | Relleno rojo. Es la fila que te tiene que doler al abrir el archivo. |
=$Q2="ATRASO" | Relleno ámbar. Todavía se recupera con una llamada. |
=$Q2="PAGADO" | Texto gris y tachado. Deja de estorbar sin borrarse. |
=$M2=$G2 | Borde grueso: al cliente ya se le vencieron todas las cuotas. |
Lo que hace fallar esta parte casi siempre es el signo peso. $Q2 lleva dólar en la letra y no en el número. Así la regla mira siempre la columna Q, pero baja fila por fila y pinta el renglón completo. Si escribes $Q$2, todas las filas leen el estado del primer cliente y la hoja entera se pinta del mismo color.
La fórmula se escribe pensando en la celda de arriba a la izquierda del rango seleccionado. Excel la propaga al resto sin que hagas nada. Y añade una barra de datos sobre la columna L: ver el saldo como barra en vez de como número deja a la vista, en dos segundos, dónde está concentrado el capital.
El cierre de caja del día
Abre una tercera hoja y pega estas seis fórmulas en celdas sueltas. Es el tablero que miras a las nueve de la noche antes de guardar la plata.
| Indicador | Fórmula |
|---|---|
| Recaudo de hoy | =SUMAR.SI(Abonos!$A$2:$A$5000;HOY();Abonos!$D$2:$D$5000) |
| Recaudo de hoy por cobrador | =SUMAR.SI.CONJUNTO(Abonos!$D$2:$D$5000;Abonos!$A$2:$A$5000;HOY();Abonos!$F$2:$F$5000;"Luis") |
| Capital en la calle | =SUMA(Prestamos!$L$2:$L$500) |
| Cartera en mora | =SUMAR.SI(Prestamos!$Q$2:$Q$500;"MORA";Prestamos!$L$2:$L$500) |
| Índice de mora | =SI(SUMA(Prestamos!$L$2:$L$500)=0;0;SUMAR.SI(Prestamos!$Q$2:$Q$500;"MORA";Prestamos!$L$2:$L$500)/SUMA(Prestamos!$L$2:$L$500)) |
| Clientes en mora | =CONTAR.SI(Prestamos!$Q$2:$Q$500;"MORA") |
Formatea el índice de mora como porcentaje. Y antes de cerrar: convierte las dos hojas en Tabla con Ctrl + T. Las fórmulas se copian solas a cada fila nueva y se acaba el clásico «se me olvidó arrastrar la fórmula» que deja clientes fuera del conteo.
Dónde se queda corta esta plantilla
La hoja de arriba funciona. La usamos para explicarla y hace lo que promete. Pero sería deshonesto dejarte con ella sin decirte dónde se rompe, porque se rompe siempre en los mismos seis puntos. El contraste con la alternativa está desarrollado en Excel frente a una app de cobranza.
No entra a la ruta contigo
Excel en el celular, de pie, con el sol de frente y el cliente esperando, es inservible. Terminas apuntando en la libreta y pasando la jornada entera a la hoja cuando ya llegaste a la casa. En ese trasvase se cae el abono que nadie alcanzó a escribir, y no aparece hasta que el cliente reclama con el recibo en la mano.
No entrega recibo
La hoja calcula, pero el cliente se va sin comprobante. Y el comprobante es lo que cierra la discusión seis meses después, cuando alguien jura que ya pagó esa cuota.
No guarda el pasado
La columna de saldo siempre muestra el saldo de hoy. Si necesitas saber cómo iba el préstamo el 12 del mes pasado, la hoja no te lo puede decir: la única forma es congelar valores con Pegado especial, y eso nadie lo hace todos los días.
No aguanta dos manos a la vez
Dos cobradores no pueden escribir en el mismo archivo local. Si lo subes a la nube para compartirlo, deja de funcionar justo donde no hay señal, que es donde se cobra.
No te avisa cuando te equivocas
Si digitas 250.000 en vez de 25.000, la hoja lo acepta y deja el saldo en negativo. Y si arrastras una fórmula una fila de menos, un cliente queda invisible para siempre sin que nada se ponga rojo.
Se rompe con la refinanciación
El modelo asume cuota fija. Un abono extraordinario, un plazo renegociado o un préstamo montado sobre el saldo anterior obligan a reescribir la fila a mano, y ahí se pierde la trazabilidad.
Cuándo quedarte con la hoja y cuándo soltarla
Quédate con la plantilla si cobras tú solo, si tu cartera cabe en una libreta, si tus clientes no piden comprobante y si de verdad te sientas todas las noches a digitar. En ese escenario la hoja es gratis, es tuya y no depende de nadie. Cámbiala solo cuando te estorbe, no porque alguien te diga que Excel está mandado a recoger.
Suéltala el día que entre un segundo cobrador, el día que empieces a llegar a la casa demasiado cansado para transcribir, o el primer día que un cliente te discuta un saldo y no tengas con qué responderle. Esos tres momentos son los que convierten la hoja en un riesgo, no en una herramienta.
Lo que sigue después de Excel es una plataforma de cobranza que haga en la calle lo que la hoja solo hace en la mesa: registrar el abono en la puerta del cliente sin señal, imprimir el recibo por impresora Bluetooth de 58 u 80 mm y dejar el saldo actualizado en el momento. Si tu problema puntual es el cuadre diario, empieza por el control de préstamos diarios; si es el orden de las visitas, por las rutas de cobro.
Y no tienes que renunciar a Excel para eso. Los reportes bajan en Excel y CSV, de modo que la hoja sigue existiendo para tu contabilidad: lo que cambia es quién la llena renglón por renglón. La versión gratuita llega a 20 clientes, con dos préstamos cada uno, que da para montar una semana de ruta en paralelo y compararla contra tu plantilla antes de mover nada. Los límites de cada plan están en planes y precios.
Preguntas sobre la plantilla de cobranza en Excel
Lo que se pregunta quien está armando su hoja o pensando en dejarla.
¿Qué ventajas tiene un software de cobranza frente a Excel?
¿Dónde descargo la plantilla de cobranza en Excel?
¿Las fórmulas funcionan en Google Sheets?
¿Cómo hago que la plantilla no cuente los domingos?
¿Cómo evito que alguien borre las fórmulas por accidente?
¿Cuántos clientes aguanta una plantilla de Excel para cobranza?
¿Puedo pasar mi plantilla de Excel a CobrApp sin volver a digitar todo?
$ 1.284.500 Cuadra
Lo que recoge en un día una ruta de 37 visitas con 4 cobradores, con la cartera cuadrada al cierre.
Cuando la hoja ya no dé más
CobrApp calcula el interés al crear el préstamo, imprime el recibo por Bluetooth y deja el saldo actualizado en el momento. Funciona sin internet y exporta a Excel cuando lo necesites.
CobrApp no otorga créditos ni presta dinero. Es una plataforma tecnológica de gestión de cobranza y control de cartera para préstamos otorgados por terceros.