Modelo de cálculo de retenção na fonte em Excel
Modelo de cálculo de retenção na fonte em Excel
O fechamento do mês tem um momento de que quase ninguém gosta: o da pasta com as notas dos fornecedores. Alguém organiza tudo por data, anota o valor de cada nota na margem e aplica a mesma alíquota a todas porque é a que lembra de cabeça. O total bate com a estimativa do contador e ninguém questiona nada, até o fornecedor ligar dizendo que descontaram demais.
A retenção quase nunca erra na multiplicação. Erra porque o dado que multiplica mora na cabeça de quem calcula: a qual categoria cada pagamento pertence, que valor entra na base e qual alíquota vale. Quando essa informação não fica em uma coluna, não há como reconstruir o número três semanas depois. E quando a categoria é digitada à mão, com variações como «serviços», «Serviço» e «serv.», o resumo do período se parte em três pedaços que já não somam entre si.
Este modelo ataca justamente esse ponto: uma única tabela em que cada pagamento ocupa uma linha, a categoria é escolhida de uma lista fechada, a base fica na sua coluna, a alíquota é digitada por quem calcula e a retenção é produzida por fórmula. Abaixo, um resumo que agrupa por categoria sem que ninguém precise ordenar nada antes.
⬇ Baixar o modelo (Excel .xlsx)
O que é e o que resolve
É uma pasta de Excel com duas planilhas. A primeira, «Retencao», reúne o cabeçalho com empresa e período, a tabela de pagamentos, a linha de totais e o resumo por categoria. A segunda, «Instrucoes», percorre o procedimento em quatro passos, mantém o exemplo numérico escrito e lista os três erros que mais aparecem.
Serve para quem apura pagamentos a fornecedores em um negócio pequeno: o dono de uma loja de ferragens que compra de cinco distribuidores, a administradora de uma padaria que paga aluguel e contas de consumo, o contador externo que recebe as notas do mês numa sacola. Serve também para quem faz isso há anos num caderno e quer ver, antes de contratar um sistema, quais dados são realmente necessários para fechar um período.
Vale dizer sem rodeios: não é um documento fiscal nem contábil oficial. Não calcula impostos por conta própria e não substitui o livro de retenções do negócio. É uma ferramenta de controle interno, e o aviso está escrito na planilha de instruções para que ninguém a confunda com outra coisa.
O que a planilha contém
| Bloco | O que traz | Para que serve |
|---|---|---|
| Cabeçalho | Nome da empresa e período apurado | Fixa o corte para o arquivo não se confundir com o do mês anterior |
| Tabela de pagamentos | Oitenta linhas com data, fornecedor, documento, categoria, base, alíquota, retenção e observações | É o cálculo em si, um pagamento por linha |
| Linha de totais | Soma da base de cálculo e da retenção | Conferência do período num só olhar |
| Resumo por categoria | Seis categorias com base, retenção e número de pagamentos | Ver de onde vem o total e qual categoria pesa mais |
| Planilha «Instrucoes» | Procedimento, exemplo e aviso de uso | Para outra pessoa fechar o período sem precisar ligar para você |
As oitenta linhas são espaço de trabalho, não um limite: se o período trouxer mais pagamentos, insira linhas acima da linha de totais e copie as fórmulas para baixo; se trouxer menos, as que sobram ficam vazias e não sujam a soma. O resumo de baixo também não é digitado, ele se preenche sozinho, e uma linha em zero diz que não houve pagamentos daquela categoria, o que é diferente de ter esquecido de lançar.
As colunas, uma por uma
| Coluna | O que se digita | O que é calculado |
|---|---|---|
| Data | Data do pagamento ou da nota que o sustenta | Nada; ordena o período e ajuda a localizar o comprovante |
| Fornecedor | Nome do prestador ou do beneficiário | Nada |
| CNPJ/CPF | Número de identificação do fornecedor | Nada; é a chave que separa dois fornecedores de nome parecido |
| Categoria | Escolhida de uma lista fechada de seis opções | Nada; define em qual linha do resumo o lançamento cai |
| Base de cálculo | Valor do pagamento antes da retenção | Nada |
| Alíquota | A fração correspondente, digitada à mão | Nada; o modelo não impõe nenhuma |
| Retenção | Não se digita | Base vezes alíquota, arredondada em duas casas |
| Observações | Texto livre para exceções | Nada; é o que se lê quando alguém pergunta por uma diferença |
Duas colunas merecem explicação à parte. A primeira é «Categoria». Não é texto livre: é uma lista fechada de seis opções, e o resumo procura exatamente essas seis palavras. Uma categoria digitada à mão que não coincida com a lista não aparece no resumo, ainda que a linha some nos totais. Essa diferença entre «soma no total» e «aparece no resumo» é o que se observa quando a conferência não fecha.
A segunda é «Alíquota». A célula guarda um decimal, não um texto: 0,025 para dois e meio por cento, 0,04 para quatro, 0,10 para dez. Se alguém digitar 2,5 ou 25 ali, a retenção sai cem ou mil vezes maior e o erro não avisa, porque o resultado continua parecendo um número plausível. A planilha de instruções resume isso em uma linha: a alíquota entra como fração do total.
E há uma terceira decisão de projeto, a mais importante de todas: o modelo não traz alíquotas. As células dessa coluna nascem vazias para que quem calcula as preencha, porque a alíquota de cada categoria é um dado do negócio e do momento, não uma constante gravada no arquivo. Um arquivo antigo com alíquotas embutidas continuaria calculando com as do seu tempo, e ninguém perceberia até a reclamação do fornecedor.
Como o cálculo funciona, número por número
O exemplo que acompanha a planilha tem quatro pagamentos de março, com nomes e documentos de teste. Está ali para que as fórmulas possam ser acompanhadas na tela, sem abrir mais nada.
| Categoria | Base de cálculo | Alíquota | Retenção |
|---|---|---|---|
| Compras | 2.400.000 | 2,5 por cento | 60.000 |
| Serviços | 1.200.000 | 4 por cento | 48.000 |
| Honorários | 800.000 | 10 por cento | 80.000 |
| Arrendamento | 1.500.000 | 3,5 por cento | 52.500 |
| Total do período | 5.900.000 | — | 240.500 |
A compra de papelaria de 2.400.000 a 2,5 por cento retém 60.000; os serviços de 1.200.000 a 4 por cento, 48.000; os honorários de 800.000 a 10 por cento, 80.000; e o aluguel de 1.500.000 a 3,5 por cento, 52.500. Quatro linhas, quatro categorias e quatro alíquotas diferentes: exatamente o cenário em que o caderno de papel falha.
A base total do período é 5.900.000 e a retenção total é 240.500. Esse par de números é a prova de que a planilha está quadrada: o resumo por categoria tem de somar exatamente o mesmo que a linha de totais. Transporte e outros ficam em zero porque não houve pagamentos dessas duas categorias, e vê-los em zero também é informação.
A fórmula da coluna de retenção faz duas coisas ao mesmo tempo. Primeiro confere se a base está vazia; se estiver, deixa a célula em branco em vez de escrever um zero, para que uma linha pela metade não seja lida como pagamento de valor zero. Depois multiplica base por alíquota e arredonda em duas casas, o que evita diferenças de centavos ao comparar o resumo com o total. Se a alíquota está vazia, a função que converte o dado em número devolve zero, e a retenção também sai em zero sem exibir erro na tela.
Há um detalhe que costuma passar despercebido e que explica por que o total da planilha não é o total pago. O modelo soma bases e soma retenções separadamente; o dinheiro que de fato sai para o fornecedor é a diferença entre as duas, e essa diferença não está em coluna nenhuma. Nem deveria estar: quem paga precisa da base inteira para conciliar com a nota, e quem declara precisa da retenção isolada.
Passo a passo
- Baixe o arquivo e salve uma cópia com o período no nome. Assim o original fica limpo para o mês seguinte.
- Preencha empresa e período no cabeçalho: são os campos que depois dizem a que mês o arquivo pertence.
- Lance uma linha por pagamento: data, fornecedor, documento, categoria e base de cálculo. O documento separa dois fornecedores de nome parecido.
- Escolha a categoria na lista suspensa. Se o pagamento não couber em nenhuma das cinco categorias com nome, use «Outros»: o resumo não captura categorias inventadas.
- Digite a alíquota como fração: 0,025 e não 2,5. É o único número digitado à mão na parte que depois é calculada.
- Confira a coluna de retenção e o resumo. Uma categoria em zero que deveria ter pagamentos quase sempre é diferença de escrita com a lista.
- Compare os totais com o resumo e anote em «Observações» qualquer pagamento que tenha ficado de fora. Essa nota é a primeira coisa que se lê quando perguntam pela diferença.
Erros que se repetem
- Aplicar a mesma alíquota a todas as categorias: uma compra e um honorário não se apuram do mesmo jeito, e a planilha é dividida por categoria para que a diferença apareça.
- Digitar a alíquota como 25 em vez de 0,25: a retenção sai cem vezes maior e o número ainda parece razoável.
- Somar à base valores que já vêm com a retenção descontada: a base é o valor antes de reter, não o líquido transferido.
- Digitar a categoria à mão com uma variação fora da lista: a linha soma no total e desaparece do resumo.
- Deixar linhas com a base escrita e a alíquota vazia: a retenção fica em zero e o pagamento parece isento quando só está incompleto.
- Copiar a planilha do mês anterior e digitar por cima apenas algumas linhas: as antigas continuam ali e continuam somando.
- Confundir o total retido com o total pago: a diferença entre os dois é exatamente o dinheiro que vai para o fornecedor.
Com o que abre
O arquivo é um .xlsx e abre sem conversão no Excel de mesa, no Excel na web, no LibreOffice Calc e no Google Sheets. As quatro fórmulas que ele usa — condição, conversão em número, arredondamento e soma por critério — existem nas quatro aplicações e dão o mesmo resultado. No Google Sheets, vale conferir na importação se a coluna de alíquota ficou com formato de número e não de texto: se entrar como texto, a multiplicação devolve zero e a planilha parece quebrada.
A captura mostra a planilha como ela fica com o exemplo carregado: o cabeçalho com empresa e período, as quatro linhas de março com sua categoria e sua alíquota, a linha de totais em 5.900.000 de base e 240.500 retidos, e o resumo por categoria com suas seis linhas, duas em zero porque não houve pagamentos de transporte nem de outros. Repare na coluna de alíquota: os valores aparecem como 2,50 por cento, 4,00 por cento e assim por diante, porque a célula está formatada como percentual embora guarde a fração por dentro.
Antes de salvar o arquivo
Uma última conferência de trinta segundos evita quase todos os problemas: total da base menos total da retenção tem de dar o dinheiro que saiu, o resumo por categoria tem de somar o mesmo que a linha de totais, e nenhuma linha do resumo deveria estar em zero se a tabela tem lançamentos daquela categoria.
Vale ter presente o que este modelo é e o que não é. Organiza o cálculo, deixa rastro de cada número e permite que outra pessoa revise sem perguntar a quem montou, mas não substitui o critério de quem apura nem a revisão de um contador. É por isso que as alíquotas ficam em branco: decidir qual percentual se aplica a cada pagamento é do negócio e do seu assessor, não do arquivo. Kardex Tauro publica o modelo como download gratuito, junto com o restante dos seus modelos de controle interno.
