Monte a sua planilha de cobrança em Excel, com as fórmulas prontas
Duas abas, vinte e quatro colunas e doze fórmulas. É tudo o que uma planilha precisa para controlar empréstimos, calcular juros, mostrar o saldo e pintar de vermelho quem está atrasado. Está completa aqui embaixo: copie e fique com ela.
Não há arquivo para baixar nem e-mail para deixar. A planilha é a estrutura, não o anexo: com a estrutura você ajusta ao seu jeito de cobrar; com o anexo você briga com o jeito de outra pessoa.
O que a planilha precisa fazer para servir de algo
Uma planilha pode ficar numa lista de nomes com uma coluna de «deve». Isso ainda não é uma planilha de cobrança: é a caderneta, mas na tela. Para substituir a caderneta de verdade, ela tem que responder quatro perguntas sem você fazer conta nenhuma.
Quanto cada cliente deve hoje, sem você subtrair pagamentos na mão. Quanto ele já deveria ter pago a esta altura do crédito, que não é a mesma coisa. Quanto entrou hoje, para acertar o caixa antes de dormir. E quem está inadimplente, para saber quem visitar amanhã.
A quarta é a que separa uma planilha útil de uma planilha bonita. O atraso não se vê olhando o saldo: um cliente com saldo de R$ 800,00 pode estar impecável e outro com saldo de R$ 180,00 pode estar há dez dias sem aparecer. A única forma de saber é comparar o que ele pagou com o que já deveria ter pago pelos dias corridos. Essa comparação se faz com fórmula, não com memória.
O resto desta página monta essa planilha peça por peça. Se você prefere entender primeiro o cálculo de parcelas, capital e juros, comece pelo guia de tabela de amortização de um empréstimo e volte aqui.
Aba 1: «Emprestimos», uma linha por crédito
Cabeçalhos na linha 1 e dados a partir da linha 2. Você só escreve da coluna A à G: as dez seguintes se calculam sozinhas. Cole cada fórmula na linha 2 e arraste até embaixo.
| Col. | Campo | Fórmula (linha 2) | Para quê |
|---|---|---|---|
| A | ID | você digita | E-001, E-002… É a chave que amarra tudo. |
| B | Cliente | você digita | Nome completo. |
| C | Rota | você digita | Região ou bairro. Serve para filtrar a visita do dia. |
| D | Data da liberação | você digita | Formato Data abreviada. |
| E | Capital | você digita | O que você entregou na mão. |
| F | Juros | você digita | Formato Porcentagem. 20% se escreve 20%, não 0,2. |
| G | Nº de parcelas | você digita | Diárias, semanais ou quinzenais. |
| H | Total a pagar | =E2*(1+F2) | Capital mais juros do crédito. |
| I | Valor da parcela | =ARREDONDAR.PARA.CIMA(H2/G2;0) | Arredondado para o real de cima. |
| J | Última parcela | =H2-(G2-1)*I2 | Absorve a sobra do arredondamento. |
| K | Recebido | =SOMASE(Pagamentos!$B$2:$B$5000;$A2;Pagamentos!$D$2:$D$5000) | Soma todos os pagamentos daquele ID. |
| L | Saldo | =H2-K2 | O que falta receber. |
| M | Parcelas vencidas | =MÍNIMO($G2;MÁXIMO(0;DIATRABALHOTOTAL.INTL($D2;HOJE();"0000001")-1)) | Dias de cobrança corridos, sem domingos. |
| N | Deveria ter pago | =MÍNIMO($H2;$M2*$I2) | O que já deveria estar recebido. |
| O | Atraso | =MÁXIMO(0;$N2-$K2) | O atraso, em reais. |
| P | Parcelas em atraso | =SE($I2=0;0;ARREDONDAR.PARA.BAIXO($O2/$I2;0)) | O atraso, em parcelas. |
| Q | Status | =SE($L2<=0;"PAGO";SE($O2<=0;"EM DIA";SE($P2<3;"ATRASO";"INADIMPLENTE"))) | O semáforo da carteira. |
Os intervalos param na linha 500 de propósito. Se você apontar para a coluna inteira, o Excel recalcula um milhão de células a cada pagamento registrado e a planilha fica lenta no celular. Aumente o 500 quando precisar.
As três fórmulas do dinheiro, com números que você pode refazer
Um empréstimo de R$ 1.000,00 a 20%, em 26 parcelas diárias. É o caso típico da cobrança diária de segunda a sábado durante um mês. Siga a conta com a calculadora:
-
1. Total a pagar — coluna H
=E2*(1+F2)1.000,00 × 1,20 = 1.200,00. Os juros se aplicam sobre o capital uma vez só, que é como se combina na rua. Se a sua taxa é mensal e o prazo corre, você precisa de outra fórmula: explica o guia de como calcular os juros diários de um empréstimo.
-
2. Valor da parcela — coluna I
=ARREDONDAR.PARA.CIMA(H2/G2;0)1.200,00 ÷ 26 = 46,1538. Ninguém cobra quinze centavos. O 0 arredonda para o real de cima: 47,00. Para cima e não para baixo, porque a sobra se desconta no fim e não se cobra a mais do cliente.
-
3. A última parcela — coluna J
=H2-(G2-1)*I2As 25 primeiras somam 25 × 47,00 = 1.175,00. Então a última é 1.200,00 − 1.175,00 = 25,00. Essa diferença é a discussão mais comum do ofício: o cliente que paga a mais na última visita e fica com a dúvida. Com esta coluna, a planilha avisa antes.
São três colunas curtas e fazem o trabalho que, se não, acaba na calculadora do celular na frente do cliente. Com elas, dois créditos iguais se calculam igual mesmo que outra pessoa os monte.
Aba 2: «Pagamentos», uma linha por pagamento
Este é o erro que afunda quase toda planilha: registrar os pagamentos em cima do empréstimo, em colunas «Pagamento 1», «Pagamento 2», «Pagamento 3». Quando o cliente paga duas vezes no mesmo dia ou paga a mais, a linha fica sem espaço. Os pagamentos vão na aba deles, um embaixo do outro, para sempre.
| Col. | Campo | Fórmula (linha 2) | Para quê |
|---|---|---|---|
| A | Data | você digita | Ctrl + ; escreve a data de hoje sem soltar o teclado. |
| B | ID do empréstimo | você digita | Com lista suspensa: nunca se digita à mão. |
| C | Cliente | =SE($B2="";"";PROCV($B2;Emprestimos!$A$2:$B$500;2;FALSO)) | Preenche sozinho ao escolher o ID. |
| D | Valor | você digita | O pagamento. Aqui se digita e mais nada. |
| E | Forma de pagamento | você digita | Dinheiro, Pix, transferência… |
| F | Cobrador | você digita | Necessário para acertar o caixa por pessoa. |
| G | Saldo do empréstimo | =SE($B2="";"";PROCV($B2;Emprestimos!$A$2:$L$500;12;FALSO)) | Atenção: mostra o saldo de hoje, não o do dia do pagamento. |
A coluna B nunca se escreve à mão. Selecione-a, entre em Dados → Validação de dados → Permitir: Lista e ponha como origem =Emprestimos!$A$2:$A$500. A partir daí o ID se escolhe numa lista suspensa e some de uma vez o erro mais caro de todos: um pagamento registrado com um ID que não existe, que não soma em lugar nenhum e aparece como dinheiro perdido no fechamento.
Que a planilha detecte o atraso sozinha
A coluna M conta quantos dias de cobrança se passaram desde a liberação, pulando os domingos:
=MÍNIMO($G2;MÁXIMO(0;DIATRABALHOTOTAL.INTL($D2;HOJE();"0000001")-1)) Os sete dígitos de "0000001" são os dias de segunda a domingo, e o 1 marca o que não se cobra. Subtrai-se 1 porque no dia da liberação não se cobra parcela. O MÍNIMO impede que o contador siga correndo depois da última parcela, e o MÁXIMO evita números negativos se você registrar um empréstimo com data futura.
Com isso, o resto sai sozinho. Exemplo com o mesmo crédito de R$ 47,00 por parcela: correram 10 dias de cobrança e o cliente pagou 8 parcelas. Deveria ter pago 10 × 47,00 = 470,00 e pagou 8 × 47,00 = 376,00. O atraso é 94,00, ou seja, 2 parcelas. A coluna Q marca como ATRASO; na terceira parcela sem pagar passa a INADIMPLENTE.
Onde colocar o limite é decisão sua. Três parcelas funciona para cobrança diária. Para cobrança semanal, uma parcela vencida já é visita urgente: troque o 3 por 1 na coluna Q. Sobre quando ligar e o que dizer, o site tem um guia de cobrança preventiva que evita chegar a esta coluna.
Formatação condicional: que o atraso se veja de longe
Selecione o intervalo A2:Q500 e vá em Página Inicial → Formatação condicional → Nova regra → Usar uma fórmula para determinar quais células devem ser formatadas. Crie uma regra por linha:
| Fórmula da regra | Formato e para quê |
|---|---|
=$Q2="INADIMPLENTE" | Preenchimento vermelho. É a linha que tem que doer ao abrir o arquivo. |
=$Q2="ATRASO" | Preenchimento âmbar. Ainda se recupera com uma ligação. |
=$Q2="PAGO" | Texto cinza e riscado. Para de atrapalhar sem ser apagado. |
=$M2=$G2 | Borda grossa: o cliente já venceu todas as parcelas. |
O que faz esta parte falhar quase sempre é o cifrão. $Q2 leva cifrão na letra e não no número. Assim a regra olha sempre a coluna Q, mas desce linha por linha e pinta o registro inteiro. Se você escrever $Q$2, todas as linhas leem o status do primeiro cliente e a planilha inteira se pinta da mesma cor.
A fórmula se escreve pensando na célula de cima à esquerda do intervalo selecionado. O Excel propaga para o resto sem você fazer nada. E acrescente uma barra de dados sobre a coluna L: ver o saldo como barra em vez de número deixa à vista, em dois segundos, onde está concentrado o capital.
O fechamento de caixa do dia
Abra uma terceira aba e cole estas seis fórmulas em células soltas. É o painel que você olha às nove da noite antes de guardar o dinheiro.
| Indicador | Fórmula |
|---|---|
| Recebido hoje | =SOMASE(Pagamentos!$A$2:$A$5000;HOJE();Pagamentos!$D$2:$D$5000) |
| Recebido hoje por cobrador | =SOMASES(Pagamentos!$D$2:$D$5000;Pagamentos!$A$2:$A$5000;HOJE();Pagamentos!$F$2:$F$5000;"Luís") |
| Capital na rua | =SOMA(Emprestimos!$L$2:$L$500) |
| Carteira inadimplente | =SOMASE(Emprestimos!$Q$2:$Q$500;"INADIMPLENTE";Emprestimos!$L$2:$L$500) |
| Índice de inadimplência | =SE(SOMA(Emprestimos!$L$2:$L$500)=0;0;SOMASE(Emprestimos!$Q$2:$Q$500;"INADIMPLENTE";Emprestimos!$L$2:$L$500)/SOMA(Emprestimos!$L$2:$L$500)) |
| Clientes inadimplentes | =CONT.SE(Emprestimos!$Q$2:$Q$500;"INADIMPLENTE") |
Formate o índice de inadimplência como porcentagem. E antes de fechar: transforme as duas abas em Tabela com Ctrl + T. As fórmulas se copiam sozinhas para cada linha nova e acaba o clássico «esqueci de arrastar a fórmula» que deixa clientes fora da contagem.
Onde esta planilha fica curta
A planilha de cima funciona. Nós a usamos para explicar e ela faz o que promete. Mas seria desonesto deixar você com ela sem dizer onde ela quebra, porque quebra sempre nos mesmos seis pontos. O contraste com a alternativa está desenvolvido em Excel contra um app de cobrança.
Não vai para a rota com você
Excel no celular, em pé, com sol na cara e o cliente esperando, é inservível. Você acaba anotando na caderneta e passando a jornada inteira para a planilha depois que chega em casa. Nessa transferência cai o pagamento que ninguém alcançou a escrever, e ele não aparece até o cliente reclamar com o recibo na mão.
Não entrega recibo
A planilha calcula, mas o cliente vai embora sem comprovante. E o comprovante é o que encerra a discussão seis meses depois, quando alguém jura que já pagou aquela parcela.
Não guarda o passado
A coluna de saldo sempre mostra o saldo de hoje. Se você precisa saber como estava o empréstimo no dia 12 do mês passado, a planilha não sabe dizer: a única forma é congelar valores com Colar especial, e isso ninguém faz todos os dias.
Não aguenta duas mãos ao mesmo tempo
Dois cobradores não podem escrever no mesmo arquivo local. Se você sobe para a nuvem para compartilhar, ele para de funcionar justo onde não pega sinal, que é onde se cobra.
Não avisa quando você erra
Se você digita R$ 500,00 em vez de R$ 50,00, a planilha aceita e deixa o saldo negativo. E se você arrastar a fórmula uma linha a menos, um cliente fica invisível para sempre sem nada ficar vermelho.
Quebra no refinanciamento
O modelo assume parcela fixa. Um pagamento extraordinário, um prazo renegociado ou um empréstimo montado sobre o saldo anterior obrigam a reescrever a linha na mão, e aí se perde a rastreabilidade.
Quando ficar com a planilha e quando largar
Fique com a planilha se você cobra sozinho, se a sua carteira cabe numa caderneta, se os seus clientes não pedem comprovante e se você realmente senta toda noite para digitar. Nesse cenário a planilha é grátis, é sua e não depende de ninguém. Troque só quando ela atrapalhar, não porque alguém disse que Excel está ultrapassado.
Largue no dia em que entrar um segundo cobrador, no dia em que você começar a chegar em casa cansado demais para digitar, ou no primeiro dia em que um cliente discutir um saldo e você não tiver com que responder. Esses três momentos são os que transformam a planilha num risco, não numa ferramenta.
O que vem depois do Excel é uma plataforma de cobrança que faça na rua o que a planilha só faz na mesa: registrar o pagamento na porta do cliente sem sinal, imprimir o recibo em impressora Bluetooth de 58 ou 80 mm e deixar o saldo atualizado na hora. Se o seu problema específico é o acerto diário, comece pelo controle de empréstimos diários; se é a ordem das visitas, pelas rotas de cobrança.
E você não precisa abrir mão do Excel para isso. Os relatórios saem em Excel e CSV, então a planilha continua existindo para a sua contabilidade: o que muda é quem a preenche linha por linha. A versão gratuita vai até 20 clientes, com dois empréstimos cada um, o que dá para montar uma semana de rota em paralelo e comparar com a sua planilha antes de mover nada. Os limites de cada plano estão em planos e preços.
Perguntas sobre a planilha de cobrança em Excel
O que pergunta quem está montando a planilha ou pensando em largá-la.
Que vantagens um software de cobrança tem sobre a planilha?
Onde eu baixo a planilha de cobrança em Excel?
As fórmulas funcionam no Google Planilhas?
Como faço a planilha não contar os domingos?
Como evito que alguém apague as fórmulas sem querer?
Quantos clientes uma planilha de Excel para cobrança aguenta?
Posso passar a minha planilha para a CobrApp sem digitar tudo de novo?
R$ 3.470,00 Bate
O que uma rota de 37 visitas com 4 cobradores recolhe em um dia, com a carteira batendo no fechamento.
Quando a planilha não der mais conta
A CobrApp calcula os juros ao criar o empréstimo, imprime o recibo por Bluetooth e deixa o saldo atualizado na hora. Funciona sem internet e exporta para Excel quando você precisar.
A CobrApp não concede crédito nem empresta dinheiro. É uma plataforma tecnológica de gestão de cobrança e controle de carteira para empréstimos concedidos por terceiros.