Autor: Bruno Rafael Santos <rafaelocremix at gmail.com>
http://www.vivaolinux.com.br/artigo/Calculando-o-Imposto-de-Renda-2015-com-o-LibreOffice-Calc
NOÇÕES BÁSICAS SOBRE O CÁLCULO DO IR
Neste breve artigo busco apresentar algumas funções avançadas do LibreOffice Calc utilizando como exemplo uma declaração completa do Imposto de Renda 2015. Trataremos aqui principalmente de: soma condicional, intervalos, células nomeadas, condições, referências, formatos e relações.Algumas notas aos leitores:
- Ao longo do artigo descreverei fórmulas matemáticas e funções do LibreOffice. Para as do LibreOffice procurarei utilizar a sintaxe nativa. Eventualmente, para tornar as funções mais legíveis, utilizarei uma sintaxe parecida com a de uma linguagem script como o Python.
- Utilizarei uma notação de matemática financeira onde os valores positivos significam entrada de dinheiro e os negativos significam saída. Isto simplifica enormemente a montagem das fórmulas, mas pode ser difícil de entender no começo; procurarei tratar disso com calma à seguir.
- Aviso de imediato que este artigo é extremamente longo e chato de ler porque é cheio de detalhes maléficos sobre sintaxe de fórmulas.
Primeiro falaremos sobre como funciona o cálculo do Imposto de Renda para sabermos com o que estamos lidando. Serei breve nesta parte pois existe muita documentação na rede. Por acaso, a fonte primária de informação sobre o IR deste artigo é o próprio site da receita que possui um excelente guia sobre o assunto.
Utilizarei como exemplo uma declaração completa de Ajuste Anual feita para uma pessoa apenas, sem dependentes ou alimentandos. A mecânica do cálculo de fato muda muito pouco; a presença de dependentes e pensões alimentícias afeta mais a forma como os dados são organizados em vez de seu tratamento.
O cálculo do imposto de renda anual é relativamente simples. Ele consiste basicamente de uma fração aplicada à base de cálculo somada a uma dedução de incentivo. A obtenção da base de cálculo em si é que é complicada pois é uma combinação de somas e subtrações de rendas e deduções calculadas sobre estas rendas.
Fórmula 1 - Cálculo do Imposto de Renda
imposto_de_renda = base_de_calculo * aliquota + deducao_de_incentivo
Na tabela abaixo (tabela 1) temos os valores que a Receita aplicará este ano aos contribuintes. A base de cálculo é dada como o teto de cada categoria e isso é um ponto importante quando formos tratar a tabela mais adiante. As alíquotas são simples porcentagens e a dedução de incentivo é uma "bondade" a ser subtraída do valor obtido.
Tabela 1 - Alíquotas e Deduções aplicadas em 2015:
Existem também os Não-Tributáveis. Estes devem ser declarados, mas não implicam na base de cálculo como bolsas de estudo (CNPQ, CAPES etc), venda do único imóvel etc, estes serão ignorados aqui.
As Deduções são gastos ou valores cobrados do contribuinte ao longo do ano, geralmente na forma de impostos, que podem ser usados para reduzir o valor de sua renda tributável até o valor da base de cálculo. As deduções básicas são IRRF (imposto de renda retido na fonte) e INSS (contribuição previdenciária oficial). Porém, existem outras formas de dedução que são:
- Previdência Privada PGBL - permite abater até 12% da renda tributável em aplicações de previdência privada.
- Saúde - custos com médicos, clínicas e planos de saúde podem ser abatidos integralmente da renda tributável.
- Instrução/Educação - alguns gastos com educação podem ser abatidos como escolas de ensino fundamental, médio, faculdade e pós-graduação lato-sensu e strictu-sensu. Cursos de inglês e alguns tipos de especializações estão de fora.
Então, resumiremos isso tudo a algumas fórmulas:
- Renda - valor total recebido de rendimentos tributáveis. Se você está contratado via CLT, isso deve ser aproximadamente o seu salário bruto vezes 12.
- INSS - valor das contribuições ao INSS ao longo do ano. Note que isso geralmente é um valor constante ao longo do ano (exceto nas férias e em meses com bônus). Sempre é 100% do valor.
- IRRF - imposto de renda retido na fonte é o valor do IR já cobrado no contra-cheque e que pode ser restituído. Sempre é 100% do valor.
- Saúde - total das deduções com saúde. Sempre é 100% do valor.
- Educação - total dos gastos com educação. Embora o valor total possa ser deduzido, ele possui um teto baixo que este ano é de R$ 3.375,83. Qualquer valor além disto é ignorado.
educacao = se(educacao > 3375,83; 3375,83; educacao) - PGBL - valor aplicado em previdência PGBL. Assim como os de educação, possui um teto que é de 12% da renda total, logo:
pgbl = se(pgbl > renda * 15%; renda * 15%; pgbl) - Deduções - valor total das deduções possíveis no ano, segundo as instruções acima.
deducoes = saude + educacao + pgbl + inss + irrf
E finalmente:
base_de_calculo = renda + deducoes
Notem que a fórmula é "+ deducoes" em vez de "- deducoes" porque estou utilizando valores negativos para sinalizar débitos/saídas e positivos para créditos/entrada. Isso facilita a montagem das fórmulas e funções, mas exige atenção à formatação dos dados e é isso que veremos na próxima parte.
Então vamos aos dados que utilizaremos como exemplo:
- Renda = R$ 84.000,00 (aproximadamente um salário bruto de R$ 7.000,00 por mês)
- IRRF = R$ -3.600,00 (valor já pago ao IR)
- INSS = R$ -14.000,00 (valor pago ao inss)
- PGBL = R$ -10.000,00 (valor pago à previdência privada)
- Saúde = R$ -8.000,00 (gastos com plano de saúde)
- Instrução = R$ -15.000,00 (valor pago com educação no ano)
ESTRUTURA DA PLANILHA DE DADOS
Sairemos agora da teoria e vamos à prática do Calc. O objetivo desta planilha é tanto ensinar como aplicar algumas ferramentas do Calc na vida prática quanto criar uma planilha que nos ajude a planejar o Imposto de Renda do ano que vem para utilizar ao máximo as deduções. A estrutura geral da planilha de dados será assim:
Tabela 2 - planilha de Movimentação Mensal
As classes abaixo do somatório são os indicadores de onde aqueles totais devem ser utilizados ao longo da planilha. A lista completa é: Renda, IRRF, INSS, Saúde, Instrução e PGBL.
Tabela 2 - planilha de Movimentação Mensal
DATAS
A primeira coisa são organizar as datas. Para definir os meses podemos tanto usar texto normal quanto datas e depois formatá-las para parecerem texto. Gosto mais da segunda abordagem porque ela abre margem para outra coisa, usar a primeira data como referência para as demais. Segue o passo a passo:
1. Coloque a primeira data (Janeiro) como 01/01/2015. O Calc entenderá que é uma data e a tratará como tal.
2. Na célula abaixo, faça uma referência para a célula anterior e adicione a ela o número de dias daquele mês com a função diasnomês(). Por exemplo, se a primeira célula é A1:
= A1 + diasnomês(A1)
3. Agora estenda isto pelas próximas 10 células e teremos um ano completo.
Agora mudaremos a aparência das células. Clique com o direito na célula e escolha "Formatar Célula" ou selecione as células a formatar e vá no menu Formatar > Célula.
Logo na primeira aba - Números - podemos escolhe os formatos nas células selecionadas. No momento o Calc já deve ter escolhido tratar os dados como datas, então um dos formatos de datas está selecionados. Mudaremos o formato para considerar apenas o mês e ignorar o dia e ano:
1. Selecione um dos formatos de datas, qualquer um.
2. Vá em Código de Formato e modifique o campo para MMMM para ter o nome do mês como valor. Note que para cada M o formato muda na seguinte ordem: número do mês, número com zero, nome abreviado e nome completo.
3. Clique em Ok.
Agora todas as células selecionadas exibirão o nome do mês apenas.
1. Coloque a primeira data (Janeiro) como 01/01/2015. O Calc entenderá que é uma data e a tratará como tal.
2. Na célula abaixo, faça uma referência para a célula anterior e adicione a ela o número de dias daquele mês com a função diasnomês(). Por exemplo, se a primeira célula é A1:
= A1 + diasnomês(A1)
3. Agora estenda isto pelas próximas 10 células e teremos um ano completo.
Agora mudaremos a aparência das células. Clique com o direito na célula e escolha "Formatar Célula" ou selecione as células a formatar e vá no menu Formatar > Célula.
Logo na primeira aba - Números - podemos escolhe os formatos nas células selecionadas. No momento o Calc já deve ter escolhido tratar os dados como datas, então um dos formatos de datas está selecionados. Mudaremos o formato para considerar apenas o mês e ignorar o dia e ano:
1. Selecione um dos formatos de datas, qualquer um.
2. Vá em Código de Formato e modifique o campo para MMMM para ter o nome do mês como valor. Note que para cada M o formato muda na seguinte ordem: número do mês, número com zero, nome abreviado e nome completo.
3. Clique em Ok.
Agora todas as células selecionadas exibirão o nome do mês apenas.
ESTILOS E FORMATOS DE VALORES
Agora vamos aos valores de créditos e débitos nas colunas. Para facilitar a montagem das fórmulas utilizaremos valores negativos para as saídas/deduções e positivos para entradas/rendas. Assim nos preocupamos apenas com a entrada dos valores e menos com contabilidade.
Ao contrário do que aconteceu com as datas, utilizaremos bastante formatos de valores monetários, então seria melhor criarmos um estilo de célula para controlar isto para nós. Para ver, editar e criar novos estilos vá até o menu Formatar > Estilos e Formatação ou aperte F11.
Isto abre um painel que geralmente fica do lado direito. São dois estilos possíveis: de célula e de página. Os de página são para criar relatórios e impressão, os de célula (os da esquerda) são os que nos interessam.
O estilo padrão serve de referência para os demais, você pode edita-lo para a modificar as referências de estilo em todo o documento. Para criar um estilo novo, clique em um deles para ser o "pai" - o novo estilo herda as configurações do "pai" - ou clique diretamente no vazio com o botão direito e escolha "Novo", isto faz do Estilo Padrão o pai e esta é a minha sugestão.
No novo estilo modificaremos apenas os formatos numéricos:
1. Em organizador, podemos dar um nome para o estilo e modificar quem é o "pai". Eu o chamei de "Financeiro".
2. Em Números, escolha um dos formatos de Moeda - preferencialmente um dos vermelhos.
3. Em Código do Formato coloque o seguinte valor: [BLUE][$R$-416] #.##0,00;[RED]-[$R$-416] #.##0,00.
Isto significa: "Valores positivos em azul e com duas casas decimais; valores negativos em vermelho e com duas casas decimais".
4. Clique em Ok.
Com isso agora temos um estilo que configura automaticamente valores das células em reais e os colore de acordo com o valor. Agora basta selecionar as células que seguirão este formato e clicar no estilo para aplicá-lo à elas.
Vamos às linhas de totais e classes. Nos totais, basta a função soma() para somar os valores da coluna. Depois de aplica-lo à primeira, podemos aplicar às demais colunas estendendo para a direita, como fizermos com as datas. Quanto às classes, elas são texto puro e sem nenhum formato específico.
Ao contrário do que aconteceu com as datas, utilizaremos bastante formatos de valores monetários, então seria melhor criarmos um estilo de célula para controlar isto para nós. Para ver, editar e criar novos estilos vá até o menu Formatar > Estilos e Formatação ou aperte F11.
Isto abre um painel que geralmente fica do lado direito. São dois estilos possíveis: de célula e de página. Os de página são para criar relatórios e impressão, os de célula (os da esquerda) são os que nos interessam.
O estilo padrão serve de referência para os demais, você pode edita-lo para a modificar as referências de estilo em todo o documento. Para criar um estilo novo, clique em um deles para ser o "pai" - o novo estilo herda as configurações do "pai" - ou clique diretamente no vazio com o botão direito e escolha "Novo", isto faz do Estilo Padrão o pai e esta é a minha sugestão.
No novo estilo modificaremos apenas os formatos numéricos:
1. Em organizador, podemos dar um nome para o estilo e modificar quem é o "pai". Eu o chamei de "Financeiro".
2. Em Números, escolha um dos formatos de Moeda - preferencialmente um dos vermelhos.
3. Em Código do Formato coloque o seguinte valor: [BLUE][$R$-416] #.##0,00;[RED]-[$R$-416] #.##0,00.
Isto significa: "Valores positivos em azul e com duas casas decimais; valores negativos em vermelho e com duas casas decimais".
4. Clique em Ok.
Com isso agora temos um estilo que configura automaticamente valores das células em reais e os colore de acordo com o valor. Agora basta selecionar as células que seguirão este formato e clicar no estilo para aplicá-lo à elas.
Vamos às linhas de totais e classes. Nos totais, basta a função soma() para somar os valores da coluna. Depois de aplica-lo à primeira, podemos aplicar às demais colunas estendendo para a direita, como fizermos com as datas. Quanto às classes, elas são texto puro e sem nenhum formato específico.
CLASSES DE DADOS E INTERVALOS
Antes de montarmos as fórmulas para a Tabela 2, precisamos criar intervalos nomeados na Tabela 1 para facilitar. Por exemplo, o seguinte intervalo:
B15:AMJ15
Contém todas as células da linha 15 exceto a primeira. Por acaso, é a linha onde ficam os totais da Tabela 1. Seria mais fácil usar esse intervalo de células na planilha se ele tivesse um nome. Notem que do lado esquerdo da barra de fórmulas há um indicador da célula ou intervalo de células atuais (se não aparecer, vá no menu Exibir > Barra de Fórmulas), mas ele serve para outra coisa também:
1. Selecione o intervalo de células que corresponde ao total da Tabela 1.
2. Use Ctrl + clique para excluir a primeira coluna, que contém o nome da linha. Agora você tem um intervalo de células selecionado semelhante ao que exemplifiquei acima.
3. Vá no indicador esquerdo da barra de fórmulas e escreva um nome para este intervalo ali, por cima da seleção mesmo. Aqui é "ir_movimentação_total".
Com isso o termo ir_movimentação_total passa a ser um indicativo para aquele intervalo de células específico. Mais detalhes sobre os intervalos definidos, o que eles são ou seu escopo (se são exclusivos da planilha atual ou servem a todo o documento) podem ser vistos com o menu Inserir > Nomes Gerenciar... ou com Ctrl + F3.
Os nomes são muito úteis para planilhas mais avançadas - por exemplo podem ser usados como filtros avançados em tabelas muito grandes - mas sua maior virtude é simplificar enormemente a sintaxe das fórmulas. Veja só a diferença disto:
=SE(
SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
C20;
SOMASE(ir_classes; A20; ir_movimentação_total)
)
Para isso:
=SE(
SOMASE($IR.$B$16:$AMJ$16; A20; $IR.$B$15:$AMJ$15) < C20;
C20;
SOMASE($IR.$B$16:$AMJ$16; A20; $IR.$B$15:$AMJ$15))
Faça a mesma coisa para a linha de Classes da tabela 1 (usei o nome "ir_classes") e pronto: agora podemos criar fórmulas mais amigáveis para a Tabela 2.
B15:AMJ15
Contém todas as células da linha 15 exceto a primeira. Por acaso, é a linha onde ficam os totais da Tabela 1. Seria mais fácil usar esse intervalo de células na planilha se ele tivesse um nome. Notem que do lado esquerdo da barra de fórmulas há um indicador da célula ou intervalo de células atuais (se não aparecer, vá no menu Exibir > Barra de Fórmulas), mas ele serve para outra coisa também:
1. Selecione o intervalo de células que corresponde ao total da Tabela 1.
2. Use Ctrl + clique para excluir a primeira coluna, que contém o nome da linha. Agora você tem um intervalo de células selecionado semelhante ao que exemplifiquei acima.
3. Vá no indicador esquerdo da barra de fórmulas e escreva um nome para este intervalo ali, por cima da seleção mesmo. Aqui é "ir_movimentação_total".
Com isso o termo ir_movimentação_total passa a ser um indicativo para aquele intervalo de células específico. Mais detalhes sobre os intervalos definidos, o que eles são ou seu escopo (se são exclusivos da planilha atual ou servem a todo o documento) podem ser vistos com o menu Inserir > Nomes Gerenciar... ou com Ctrl + F3.
Os nomes são muito úteis para planilhas mais avançadas - por exemplo podem ser usados como filtros avançados em tabelas muito grandes - mas sua maior virtude é simplificar enormemente a sintaxe das fórmulas. Veja só a diferença disto:
=SE(
SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
C20;
SOMASE(ir_classes; A20; ir_movimentação_total)
)
Para isso:
=SE(
SOMASE($IR.$B$16:$AMJ$16; A20; $IR.$B$15:$AMJ$15) < C20;
C20;
SOMASE($IR.$B$16:$AMJ$16; A20; $IR.$B$15:$AMJ$15))
Faça a mesma coisa para a linha de Classes da tabela 1 (usei o nome "ir_classes") e pronto: agora podemos criar fórmulas mais amigáveis para a Tabela 2.
CÁLCULO DAS DEDUÇÕES
Agora que temos os dados no lugar organizados, veremos como calcular as deduções automaticamente. A tabela de Deduções (Tabela 2) possui o seguinte formato: na primeira coluna colocamos as classes de deduções (Saúde, Instrução, PGBL, INSS), na segunda são os valores calculados a partir dos Totais e Classes da Movimentação Mensal, na terceira colocaremos os valores máximos para cada dedução. O formato final é assim:
Tabela 2 - Deduções:
Os nomes das classes utilizados nesta tabela devem ser os mesmos utilizados na tabela de Movimentação Mensal pois são esses nomes que servirão para conectar uma coisa com a outra.
Agora criaremos a fórmula para a coluna Executado da Tabela 2. Nós precisamos que essa célula contenha a soma dos valores da Tabela 1 onde a classe seja igual à da coluna Classes na Tabela 2 e ao mesmo tempo não ultrapasse o valor máximo na coluna Disponível.
Para a primeira parte do problema utilizaremos a função somase() que faz soma segundo uma condição. Uma versão mais radical dela, a somases(), o faz com múltiplas condições. A fórmula básica seria:
Fórmula 2 - soma condicional
SOMASE(ir_classes; A20; ir_movimentação_total)
Nesta fórmula ir_classes indica onde a condição deve ser localizada, A20 indica o valor da condição que, neste caso, coincide com a coluna Classe na Tabela 2 e ir_movimentação_total o intervalo de onde os valores são somados. Essa fórmula funciona muito bem graças aos intervalos nomeados pois conforme você adiciona mais dados na Tabela 1 eles são processados diretamente na Tabela 2 sem problemas.
Mas esta fórmula possui um problema, ela só funciona corretamente para Saúde e INSS, onde a dedução é de 100%. Para Instrução a dedução máxima é um valor fixo que é dado no ano e para o PGBL é 15% da renda total apenas. A solução para isso é adicionar um se() para verificar se o valor está ultrapassando o limite. A versão modificada ficará assim:
Fórmula 3 - soma condicional com teto
=SE(
SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
C20;
SOMASE(ir_classes; A20; ir_movimentação_total)
)
Ela primeiro verifica se o valor ultrapassa o teto (coluna "Disponível" da Tabela 2), se sim, ela força o valor teto, se não, ela usa o total. Simples assim.
A coluna "Disponível" também usa somase(), mas com algumas exceções. As classes Saúde e INSS usam a formula normal do somase() que exemplifiquei anteriormente, a classe Instrução usa um valor fixo que devem ser inserido manualmente e a do PGBL usa a somase(), mas toma como referência o somatório da renda - que por acaso é uma classe também, mas não de dedução - e dela extrai os 12%. A formula fica assim:
Fórmula 4 - teto do PGBL
=-SOMASE(ir_classes; "renda"; ir_movimentação_total) * 0,12
Notem o sinal de menos no início da fórmula. Acontece aqui o seguinte, ela está sendo calculada a partir de uma classe que tem valor positivo (a "Renda") e por isso o sinal dela tem de ser invertido para ficar compatível com as demais.
Agora basta adicionarmos o somatório de cada coluna (Executado e Disponível) no fim utilizando a função soma(). Precisaremos destes totais mais tarde.
Tabela 2 - Deduções:
Agora criaremos a fórmula para a coluna Executado da Tabela 2. Nós precisamos que essa célula contenha a soma dos valores da Tabela 1 onde a classe seja igual à da coluna Classes na Tabela 2 e ao mesmo tempo não ultrapasse o valor máximo na coluna Disponível.
Para a primeira parte do problema utilizaremos a função somase() que faz soma segundo uma condição. Uma versão mais radical dela, a somases(), o faz com múltiplas condições. A fórmula básica seria:
Fórmula 2 - soma condicional
SOMASE(ir_classes; A20; ir_movimentação_total)
Nesta fórmula ir_classes indica onde a condição deve ser localizada, A20 indica o valor da condição que, neste caso, coincide com a coluna Classe na Tabela 2 e ir_movimentação_total o intervalo de onde os valores são somados. Essa fórmula funciona muito bem graças aos intervalos nomeados pois conforme você adiciona mais dados na Tabela 1 eles são processados diretamente na Tabela 2 sem problemas.
Mas esta fórmula possui um problema, ela só funciona corretamente para Saúde e INSS, onde a dedução é de 100%. Para Instrução a dedução máxima é um valor fixo que é dado no ano e para o PGBL é 15% da renda total apenas. A solução para isso é adicionar um se() para verificar se o valor está ultrapassando o limite. A versão modificada ficará assim:
Fórmula 3 - soma condicional com teto
=SE(
SOMASE(ir_classes; A20; ir_movimentação_total) < C20;
C20;
SOMASE(ir_classes; A20; ir_movimentação_total)
)
Ela primeiro verifica se o valor ultrapassa o teto (coluna "Disponível" da Tabela 2), se sim, ela força o valor teto, se não, ela usa o total. Simples assim.
A coluna "Disponível" também usa somase(), mas com algumas exceções. As classes Saúde e INSS usam a formula normal do somase() que exemplifiquei anteriormente, a classe Instrução usa um valor fixo que devem ser inserido manualmente e a do PGBL usa a somase(), mas toma como referência o somatório da renda - que por acaso é uma classe também, mas não de dedução - e dela extrai os 12%. A formula fica assim:
Fórmula 4 - teto do PGBL
=-SOMASE(ir_classes; "renda"; ir_movimentação_total) * 0,12
Notem o sinal de menos no início da fórmula. Acontece aqui o seguinte, ela está sendo calculada a partir de uma classe que tem valor positivo (a "Renda") e por isso o sinal dela tem de ser invertido para ficar compatível com as demais.
Agora basta adicionarmos o somatório de cada coluna (Executado e Disponível) no fim utilizando a função soma(). Precisaremos destes totais mais tarde.
CÁLCULO DO IMPOSTO
Estamos chegando ao final. A próxima tabela (Tabela 3) é onde calculamos o imposto de renda. Mas para ela funcionar precisamos da Tabela 4, com os valores limite do imposto.
Tabela 4 - Tabela de Cálculo:
A Tabela 4 é a mesma que é obtida no site da receita, apenas excluí os intervalos a aproveitei apenas o teto de cada categoria. É mais fácil de implementar do que usar os intervalos um a um.
Tabela 3 - Cálculo:
A Tabela 3 é curtinha, porém trabalhosa pois cada célula possui sua própria fórmula, notem que cada célula também possui um nome próprio pois isso facilita a leitura e montagem das fórmulas. Veremos agora os mais básicos:
Renda Bruta (ir_renda_bruta) - é a renda total do contribuinte. É a mesma soma que é utilizada para calcular o teto do PGBL, portanto, uma versão simplificada da Fórmula 4:
Fórmula 5 - renda bruta
= SOMASE(ir_classes; "renda"; ir_movimentação_total)
Deduções Legais (ir_deduções) - é o total Executado da Tabela 2. Basta fazer uma referência aqui, no caso do modelo, é a célula B24.
= B24
Base de Cálculo (ir_base) - já discutimos isso antes. É simplesmente Renda Bruta + Deduções:
Fórmula 6 - base de cálculo
= ir_renda_bruta + ir_deduções
IR Pago (ir_pago) - este é novidade. Ele é a soma dos IRRF da Tabela 1. Ele é utilizado para calcular a restituição/imposto a pagar logo mais:
Fórmula 7 - ir retido na fonte
= SOMASE(ir_classes; "irrf"; ir_movimentação_total)
Agora faremos Faixa de Renda e Dedução de Incentivo que são duas células interligadas. Primeiro trataremos de Faixa de Renda.
Agora que temos a nossa base de cálculo, temos que localizá-la dentro da Tabela 4 para saber em qual alíquota de imposto estamos. Como os valores são discretos, temos que comparar um por um...
Fórmula 8 - alíquota
= SE(ir_base > A42; B43;
SE(ir_base > A41; B42;
SE(B32 > A40; B41;
SE(ir_base > A39; B40; 0)
)
)
)
Basicamente a fórmula analisa a Tabela 4 de trás para frente, verificando se ir_base é maior que o teto, se for, então estamos no intervalo certo, se não, tentamos o próximo. Notem que os se() ficam um dentro do outro para evitar que um valor pequeno caia em mais de uma categoria.
Poderíamos utilizar a mesma abordagem para Dedução de Incentivo, mas agora que temos a alíquota podemos utilizá-la como referência para a função proc():
Fórmula 9 - dedução de incentivo
= PROC(ir_imposto; ir_aliquotas; ir_incentivos_lista)
proc() busca nas linhas ou colunas da tabela a partir de um valor específico. Sua única limitação é ser restrita a comparações diretas (nada de >, <=...) e os valores de referência devem estar em ordem crescente.
Agora que temos a alíquota e a dedução de incentivo, podemos partir para as duas células que faltam.
IR Devido é calculado da seguinte forma:
Fórmula 10 - ir_devido
= - (ir_base * ir_imposto + ir_incentivos)
É basicamente uma fração da base de calculo, mais a dedução de incentivo que reduz este valor. Depois tudo é convertido para negativo pois isso é uma dívida a pagar.
Fórmula 11 - Imposto a pagar/restituir
= ir_devido - ir_pago
Esta última fórmula é bem simples. Agora que temos a dívida do ir, abatemos dela o ir que já pagamos. Notem que é a primeira vez que usamos uma subtração, pois estamos lidando com dois números negativos.
O valor final da Fórmula 11 nos dirá se temos restituição ou mais imposto a pagar e isso nos leva à Fórmula 12:
Fórmula 12 - pagar/restituir
= SE(B35 < 0; "Imposto Devido"; "Imposto a restituir")
A nossa última fórmula é só uma firula que muda o rótulo da fórmula 11 de acordo com seu resultado.
Vocês podem baixar o modelo final (planilha_ir.ods) que fizemos aqui para ver as fórmulas funcionando e fazer aplicações se quiserem.
Um último acréscimo: a conta acima possui um erro de 1 centavo em relação ao programa da Receita que eu não consigo corrigir. Fiquem à vontade para dar sugestões.
Tabela 4 - Tabela de Cálculo:
Tabela 3 - Cálculo:
Renda Bruta (ir_renda_bruta) - é a renda total do contribuinte. É a mesma soma que é utilizada para calcular o teto do PGBL, portanto, uma versão simplificada da Fórmula 4:
Fórmula 5 - renda bruta
= SOMASE(ir_classes; "renda"; ir_movimentação_total)
Deduções Legais (ir_deduções) - é o total Executado da Tabela 2. Basta fazer uma referência aqui, no caso do modelo, é a célula B24.
= B24
Base de Cálculo (ir_base) - já discutimos isso antes. É simplesmente Renda Bruta + Deduções:
Fórmula 6 - base de cálculo
= ir_renda_bruta + ir_deduções
IR Pago (ir_pago) - este é novidade. Ele é a soma dos IRRF da Tabela 1. Ele é utilizado para calcular a restituição/imposto a pagar logo mais:
Fórmula 7 - ir retido na fonte
= SOMASE(ir_classes; "irrf"; ir_movimentação_total)
Agora faremos Faixa de Renda e Dedução de Incentivo que são duas células interligadas. Primeiro trataremos de Faixa de Renda.
Agora que temos a nossa base de cálculo, temos que localizá-la dentro da Tabela 4 para saber em qual alíquota de imposto estamos. Como os valores são discretos, temos que comparar um por um...
Fórmula 8 - alíquota
= SE(ir_base > A42; B43;
SE(ir_base > A41; B42;
SE(B32 > A40; B41;
SE(ir_base > A39; B40; 0)
)
)
)
Basicamente a fórmula analisa a Tabela 4 de trás para frente, verificando se ir_base é maior que o teto, se for, então estamos no intervalo certo, se não, tentamos o próximo. Notem que os se() ficam um dentro do outro para evitar que um valor pequeno caia em mais de uma categoria.
Poderíamos utilizar a mesma abordagem para Dedução de Incentivo, mas agora que temos a alíquota podemos utilizá-la como referência para a função proc():
Fórmula 9 - dedução de incentivo
= PROC(ir_imposto; ir_aliquotas; ir_incentivos_lista)
proc() busca nas linhas ou colunas da tabela a partir de um valor específico. Sua única limitação é ser restrita a comparações diretas (nada de >, <=...) e os valores de referência devem estar em ordem crescente.
Agora que temos a alíquota e a dedução de incentivo, podemos partir para as duas células que faltam.
IR Devido é calculado da seguinte forma:
Fórmula 10 - ir_devido
= - (ir_base * ir_imposto + ir_incentivos)
É basicamente uma fração da base de calculo, mais a dedução de incentivo que reduz este valor. Depois tudo é convertido para negativo pois isso é uma dívida a pagar.
Fórmula 11 - Imposto a pagar/restituir
= ir_devido - ir_pago
Esta última fórmula é bem simples. Agora que temos a dívida do ir, abatemos dela o ir que já pagamos. Notem que é a primeira vez que usamos uma subtração, pois estamos lidando com dois números negativos.
O valor final da Fórmula 11 nos dirá se temos restituição ou mais imposto a pagar e isso nos leva à Fórmula 12:
Fórmula 12 - pagar/restituir
= SE(B35 < 0; "Imposto Devido"; "Imposto a restituir")
A nossa última fórmula é só uma firula que muda o rótulo da fórmula 11 de acordo com seu resultado.
Vocês podem baixar o modelo final (planilha_ir.ods) que fizemos aqui para ver as fórmulas funcionando e fazer aplicações se quiserem.
CONCLUSÕES
Espero que eu tenha sido objetivo o bastante para ajudar em vez de confundir mais. A maioria das pessoas que trabalham com planilhas possuem apenas umas vaga ideia do que elas podem fazer, então sugiro fortemente aos interessados uma consulta à ajuda do LibreOffice, que por sinal é bastante didática. Espero que isso ajude quem está começando, principalmente com o Calc, a explorar os recursos desta ferramenta.Um último acréscimo: a conta acima possui um erro de 1 centavo em relação ao programa da Receita que eu não consigo corrigir. Fiquem à vontade para dar sugestões.
