Neste artigo você aprenderá como gerar parcelas automaticamente no Excel passo-a-passo com download gratuito da planilha.
1. Introdução Gerar Parcelas Excel Automaticamente
Este simples artigo propõe a demonstração da distribuição de parcelas de pagamentos ao logo dos meses com base em um valor total e um período de meses.
Considere o cenário a seguir, em que temos um valor a distribuir igualmente em meses de datas futuras.
Neste cenário teremos 6 parcelas iguais de R$ 250,00. As datas serão, 05/01/2024, 05/02/2024, 05/03/2024, 05/04/2024, 05/05/2024 e 05/06/2024. Note que temos a 1ª parcela já determinada para o dia 05/01/2024, restando determinar as próximas 5 parcelas.
2. Fórmulas para Gerar Parcelas no Excel Automaticamente
Nossa tarefa divide-se em 2 partes: determinar as datas das parcelas (repetindo ou não a data já conhecida da 1ª parcela) e os valores iguais de cada parcela. A imagem seguinte ilustra o resultado desejado.
Parte 01 – Determinando as datas das parcelas
Para determinar as datas das parcelas propomos usar a função DATAM. Ela é capaz de gerar datas passadas ou futuras com base em uma data inicial, deslocando meses. No seu argumento inicial informamos a data usada como ponto de partida e no seu 2º e último argumento informamos o número de meses a deslocar.
Como teremos várias parcelas, devemos então obter a data da parcela 1, a data da parcela 2, a data da parcela 3 e assim por diante. No 2º argumento de DATAM podemos informar o número incremental de parcelas, ou seja: se teremos 6 parcelas, devemos informar para DATAM o conjunto de 1 a 6 (na mesma fórmula). Desta forma, DATAM conseguirá retornar de uma só vez 6 datas.
Precisamos criar com fórmula então uma lista que vai de 1 até 6 em nosso caso, podendo variar de acordo com o parcelamento (10 parcelas, 12 parcelas, 25 parcelas etc.). Para gerar uma lista numérica sequencial de 1 a 6 usamos a função SEQUÊNCIA. Basta escrever SEQUÊNCIA(6) que teremos uma lista de 1 até 6. Alteramos o valor 6 para o valor desejado de parcelas. Use, por exemplo, SEQUÊNCIA(15) para distribuir 15 parcelas.
Assim, nossa fórmula poderá ficar:=DATAM(C3;SEQUÊNCIA(C2)).
No entanto, estão sendo geradas 6 parcelas além da própria 1ª parcela. Podemos escolher gerar apenas as próximas 5 parcelas, alterando o valor de 6 para 5 em SEQUÊNCIA, ou seja: 1 parcela a menos. Por conta disso, subtraímos 1 da quantidade de parcelas (C2-1). A fórmula então adaptada ficaria =DATAM(C3;SEQUÊNCIA(C2-1)).
Agora temos apenas as 5 parcelas restantes.
Caso optemos por mostrar o parcelamento completo, incluindo a parcela inicial, devemos então distribuir todas as parcelas (em nosso caso todas as 6). No entanto, mantendo 6 parcelas conforme a penúltima imagem, teremos um deslocamento em que a nossa parcela 1 é demonstrada e distribuída para o mês seguinte ao mês correto. Para fazer a correção, temos pelo menos 2 alternativas:
Alternativa 1 acima: subtrair 1 do resultado de SEQUÊNCIA, que em vez de gerar a lista de 1 a 6 irá gerar uma lista de 0 a 5. A fórmula então adaptada ficaria =DATAM(C3;SEQUÊNCIA(C2)-1).
Alternativa 2 na imagem anterior: alterar a função SEQUÊNCIA de modo que a lista gerada já comece em 0. A fórmula então adaptada ficaria =DATAM(C3;SEQUÊNCIA(C2;;0)).
A determinação das datas empregou a função SEQUÊNCIA. Caso a sua versão do Excel não apresente esta função, uma alternativa deverá ser usada. A imagem a seguir mostra uma fórmula usando a função LINS que gera uma lista de 0 até um valor que é a penúltima parcela. Esta fórmula deve ser replicada para várias células da coluna suficientemente para abranger o número máximo de parcelas que desejar, ou seja: a fórmula deverá ser replicada (copiada para baixo ou arrastada) para 60 linhas, 120 linhas, 180 linhas etc., de acordo com o máximo parcelamento possível.
A fórmula inserida foi =SEERRO(DATAM($C$3;SE(LINS($B$7:B7)>$C$2;””;LINS($B$7:B7)-1));””).
Parte 02 – Determinando os valores das parcelas
Para determinar o valor da parcela basta realizar uma divisão simples: o valor pelo número de parcelas.
Precisamos apenas fazer este valor se repetir sempre e apenas para cada parcela. Caso a distribuição de datas esteja implementada com a função SEQUÊNCIA, o que implementa um intervalo dinâmico de datas, podemos usar uma simples função SE para repetir as parcelas. Ela usa como teste lógico a existência das datas em intervalo despejado.
A fórmula inserida foi =SE(B7#;C4/C2).
Caso não seja possível usar a função SEQUÊNCIA para distribuir as datas, cuja alternativa aqui apontada foi usar a função LINS, usaremos a função SE para verificar a existência de datas em cada linha.
A fórmula inserida foi =SE(B7<>””;$C$4/$C$2;””).
Quando houver data na linha (B7<>””), o valor da parcela é calculado (divisão do valor total pelo número de parcelas). Caso contrário, o resultado fica exibido “em branco”. Esta fórmula deve ser replicada para várias células da coluna suficientemente para abranger o número máximo de parcelas que desejar, ou seja: a fórmula deverá ser replicada (copiada para baixo ou arrastada) para 60 linhas, 120 linhas, 180 linhas etc., de acordo com o máximo parcelamento possível.
Download Planilha Gerar Parcelas Excel
Clique no botão abaixo para realizar o download do arquivo de exemplo:
