Integração com EXCEL

Objetivo

Apresentar as integrações de movimentações entre o módulo Aplicações e Empréstimos e a Planilha de Cálculo Excel.

Visão Geral

Para os cálculos das correções das operações financeiras, bem como as parcelas dos empréstimos, e os impostos (opcional) é utilizado pelo módulo Aplicações e Empréstimos a Planilha de Cálculo Excel.

A utilização da Planilha de Cálculo Excel para efetuar os cálculos no módulo Aplicações e Empréstimos possui as seguintes vantagens:

Flexibilidade no tratamento dos cálculos dos produtos financeiros sem a alteração nos programas do Módulo.

O usuário pode utilizar quantas planilhas desejar, sendo que OBRIGATORIAMENTE a primeira planilha deve ser a de entrada e a terceira planilha deve ser a de saída, as quais não podem ser modificadas pelo usuário.

APLICAÇÕES

A integração das Aplicações com a Planilha de Cálculo Excel ocorre em dois pontos que possuem parâmetros distintos, são eles:

CÁLCULO DOS RENDIMENTOS

Exemplo

Planilha – Entrada Planilha – Cálculo Planilha - Retorno

	A	B	C		A	B	C		A	B	C
1	Taxa Pré	3%		1	Taxa	5,06%		1	Vl Correção	50,6
2	Taxa Pós	2%		2	UMP's	1		2
3	Cota Inicial	0		3	Valor	1.000,00		3
4	Cota Final	0		4				4
5	% Mín Var Taxa	0		5	Correção	50,6		5
6	% Max Var Taxa	0		6				6
7	% Garantia Taxa	100%		7				7
8	Qtd Dias UMP Oper	30		8				8
9	Qtd Dias Uteis Oper	22		9				9
10	Qtd Dias Cor Oper	30		10				10
11	Utilização Usuário 1			11				11
12	Utilização Usuário 2			12				12
13	Utilização Usuário 3			13				13
14	Utilização Usuário 4			14				14
15	Utilização Usuário 5			15				15
16	Saldo Operação	1.000,00		16				16

onde,

A planilha de entrada é gerada pelo sistema e coloca os valores nas células de uma forma padrão, ou seja, para todas as planilhas de aplicações a planilha de entrada deve ser desta forma. Para a planilha de saída, o sistema sempre deve buscar o valor da correção na célula B1. A planilha 2 é definida pelo usuário, que pode fazer cópia de células da planilha 1 (como no caso das células B2 e B3 da planilha 2 que são cópias dos valores das células B8 e B16 da planilha 1 respectivamente) e criar novas células de cálculo utilizando informações da planilha 1 (como no caso da célula B1 da planilha 2 que é calculada a partir das células B1 e B2 da planilha 1) e da célula B5 que é o cálculo da correção efetivamente.

Taxa Pré-Fixada	É o percentual da taxa pré-fixada para a operação financeira. Este valor é passado para a planilha Excel já convertido para a UMP do produto financeiro.
Taxa Pós-Fixada	É o percentual da taxa pós-fixada para a operação financeira. Este valor é passado para a planilha Excel já convertido para a UMP do produto financeiro. Caso o indicador da operação financeira seja por cotas, este valor é passado como 0 (zero).
Valor Cota Inicial	É o valor da cota no início do período da correção. Caso o indicador econômico da operação financeira seja por percentual, este valor é passado como zero.
Valor Cota Final	É o valor da cota no fim do período da correção. Caso o indicador econômico da operação financeira seja por percentual, este valor é passado como zero.
Percentual Mínimo de Variação da Taxa	É o valor mínimo de variação da taxa pós-fixada. Também conhecido por FLOOR
Percentual Máximo de Variação da Taxa	É o valor máximo de variação da taxa pré-fixada. Também conhecido por CAP
Percentual Garantia Taxa	É o valor a ser considerado da taxa pós-fixada. Exemplo 90% do CDI
Qtd Dias UMP Correção	É a quantidade de dias da unidade de medida de período da operação financeira
Qtd Dias Úteis Operação	É a quantidade de dias úteis da operação financeira
Qtd Dias Corridos Operação	É a quantidade de dias corridos da operação financeira
Campos de Utilização do Usuário	São atributos definidos pelo usuário para utilização na planilha de cálculo. São 5 (cinco) campos para que o usuário possa adaptar características de cada produto financeiro.
Saldo da Operação	Saldo da aplicação a ser corrigido

CÁLCULO DOS IMPOSTOS

Para o cálculo dos impostos é passado para a planilha de entrada somente o valor base para cálculo do imposto que pode ser de acordo com o movimento:

Aplicação: valor do movimento de aplicação;
Correção: valor da correção;
Resgate: valor do saldo, valor da correção, valor do resgate;

Para o cálculo do imposto é utilizada a mesma planilha que para o cálculo da correção da aplicação, porém para os impostos são utilizadas células diferentes para o valor base (célula E2 da planilha 1) e também células diferentes para o valor do retorno (definidas pelo usuário para cada imposto no cadastro de Impostos do Produto Financeiro).

Exemplo:

Planilha para cálculo do imposto incluindo as células de entrada das aplicações para cálculo das correções.

Planilha – Entrada					Planilha – Cálculo	Planilha - Retorno
	A	B	C	D	E		A	B	C		A	B	C
1				Cálculo Imposto		1	Taxa			1	Valor Correção
2				Vl.Base Imposto	1.000,00	2	UMP's			2
3						3	Valor			3	IOF	2,53
4						4				4	IR	7,59
5						5	Correção			5
6						6				6
7						7	Valor IOF	2,53		7
8						8	Valor IR	7,59		8
9						9				9
10						10				10
11						11				11
12						12				12
13						13				13
14						14				14
15						15				15
16						16				16

No exemplo acima, estariam parametrizadas na tabela de Impostos do Produto Financeiro a célula do I.O.F. (B3) e a célula do I.R. (B4). Estas células são parametrizadas pelo usuário e devem estar corretamente cadastradas no sistema para que ele possa buscar corretamente na planilha o valor do respectivo imposto.

É importante salientar que para cada imposto vinculado ao produto financeiro que incida sobre o movimento será executada uma chamada na planilha para efetuar o cálculo do valor de imposto. Isto se deve ao valor base do cálculo do imposto poder ser diferente para cada imposto. Ou seja, no exemplo acima a integração seria chamada uma vez para o IOF e outra para o IR.

EMPRÉSTIMOS

Para os empréstimos a integração com o Excel ocorre para o cálculo dos impostos, para o cálculo das parcelas e correção.

O cálculo dos impostos é da mesma maneira que para as aplicações, ou seja, o sistema passa o valor base para o cálculo do imposto e busca na célula informada na tabela Imposto Produto Financeiro o valor do imposto.

Para os cálculos dos empréstimos (parcelas, correção do saldo e correção do valor da parcela), o sistema irá montar na planilha de Entrada:

Uma área com os dados gerais para o cálculo;
Uma área contendo as datas das correções, datas das parcelas de amortização, datas das parcelas de juros e datas e valores das parcelas de liberação dos empréstimos. Esta segunda área somente é utilizada para o cálculo das parcelas.

Exemplo:

Planilha de Entrada para cálculo das parcelas dos Empréstimos

	A	B	C	D	E
1	Taxa Pré	2%
2	Taxa Pós
3	Taxa Del Credere	0,17%
4	Cota Inicial	0
5	Cota Final	0
6	% Max Var Taxa	0
7	% Max Vir Taxa	0
8	% Garantia Taxa	0
9	Qtd Dias UMP Oper	30
10	Qtd Dias Operação	0
11	Qtd Dias Úteis Oper	0
12	Quantidade Parcelas	10
13	Fator Financeiro Base	1
14	Tipo de Dia UMP Produto	Período
15	Tipo de Dia Cálculo Juros	Corridos
16	Forma Pagamento Parcela	Postecipadas
17	Atributo Utilização Usuário 1
18	Atributo Utilização Usuário 2
19	Atributo Utilização Usuário 3
20	Atributo Utilização Usuário 4
21	Atributo Utilização Usuário 5
22	Saldo Operação Financeira
23
24
25
26	Parcelas de Pagamento
27	Tipo Parcela	Nr. Parcela	Data	Valor	Fixa
28	Liberação	1	01/02/1999	10.000,00	sim
29	Pagamento	1	03/03/1999		não
30	Pagamento	2	02/04/1999		não
31	Pagamento	3	02/05/1999		não
32	Pagamento	4	01/06/1999		não
33	Pagamento	5	01/07/1999		não
34	Pagamento	6	31/07/1999		não
35	Pagamento	7	30/08/1999		não
36	Pagamento	8	29/09/1999		não
37	Pagamento	9	29/10/1999		não
38	Pagamento	10	28/11/1999		não

onde,

Parâmetro	Descrição	Correção Saldo	Cálculo Parcelas
Taxa Pré-Fixada	É o percentual da taxa pré-fixada. Este valor é passado para o Excel já convertido para a UMP da Operação Financeira	sim	sim
Taxa Pós-Fixada	É o percentual da taxa pós-fixada. Este valor é passado para o Excel já convertido para a UMP da Operação Financeira	sim	não
Taxa Del-Credere	É o percentual da taxa del-credere. Este valor é passado para o Excel já convertido para a UMP da Operação Financeira	sim	sim
Cota Inicial	Para operações financeiras onde a taxa é informada em valores e não em percentuais. É a cotação do indicador econômico na data da última correção da operação financeira	sim	não
Cota Final	Para operações financeiras onde a taxa é informada em valores e não em percentuais. É a cotação do indicador econômico na data da correção da operação financeira	sim	não
% Min Variação Taxa	Valor mínimo de variação da taxa do empréstimo.	sim	não
% Max Variação Taxa	Valor máximo de variação da taxa do empréstimo.	sim	não
% Garantia Taxa	Percentual de Garantia da Taxa de Juros	sim	não
Qtd Dias UMP Operação	É o número de dias da UMP da operação financeira	sim	sim
Qtd Dias Operação	É a quantidade de dias a corrigir a operação financeira	sim	não
Qtd Dias Úteis Operação	É a quantidade de dias úteis a corrigir a operação financeira	sim	não
Qtd Parcela	É a quantidade de parcelas de pagamento da operação financeiro	não	sim
Fator Financeiro Base	É o fator financeiro base para o cálculo do valor das parcelas. Para correção dos empréstimos será o fator financeiro da última correção.	sim	sim
Tipo de Dias UMP Produto	Auxilia no cálculo dos juros. Pode assumir os seguintes valores: Data/Data ou Período.	sim	sim
Tipo de Dia para Cálculo dos Juros	Tipo de dia considerado para o cálculo dos juros. Pode assumir os seguintes valores: Dias Úteis ou Dias Corridos. Este parâmetro é auxiliado pela parte de feriados.	sim	sim
Forma de Pagamento das Parcelas	Auxilia no cálculo do valor das parcelas. Pode assumir os seguintes valores: Postecipada ou Antecipada.	não	sim
Atributos Utilização Usuário	São atributos que o usuário alimenta e utiliza da forma que desejar. Servem para regras de cálculo específica para um produto financeiro. São 5 atributos.	sim	sim
Saldo Operação Financeira	De acordo com o tipo da operação de cálculo será passado um valor. Para cálculo das parcelas, este valor não é passado. Para Correções de Saldo, é passado o valor do saldo do empréstimo.	sim	não
Tipo	É o tipo da parcela do Empréstimo. Pode assumir os seguintes valores: Liberação, Juros e Pagamento.	não	sim
Data	Data de Vencimento da Parcela	não	sim
Valor	Valor da Parcela. Somente estará preenchido para parcelas de Liberação	não	sim
Parcela Pagamento	É o número da parcela de pagamento. Somente estará preenchido para parcelas de pagamento.	não	sim
Parcela Fixa	Valor da Parcela Fixo ou Não	não	sim

Para a área “2” são passados todas as parcelas do empréstimo com sua respectiva data. Para as parcelas que o valor não for fixado, virá zerado.

A planilha de cálculo é criada pelo usuário tomando como dados base os dados da planilha de entrada. Não existe um padrão, sendo que o usuário pode alterar da forma que desejar.

Exemplo:

Planilha de cálculo gerada com as informações acima (é importante lembrar que esta planilha é de livre utilização do usuário, porém com base nesta planilha serão geradas as informações para a planilha de saída):

	A	B	C	D	E	F	G	H	I	J	K
1	Data	/P	Saldo Inicial	Liberações	Encargos Período	Encargos BNDES	Del Credere	Amortização	Valor Pagto	Saldo Final	Tipo Movimento
2	01/02/99	1	-	10.000,00	-	-	-	-	-	10.000,00	Liberação
3	03/03/99	1	10.000,00		216,67	200,00	16,67	906,33	1.123,00	9.093,67	Pagamento
4	02/04/99	2	9.093,67		197,03	181,87	15,16	925,97	1.123,00	8.167,71	Pagamento
5	02/05/99	3	8.167,71	-	176,97	163,35	13,61	946,03	1.123,00	7.221,68	Pagamento
6	01/06/99	4	7.221,68	-	156,47	144,43	12,04	966,53	1.123,00	6.255,15	Pagamento
7	01/07/99	5	6.255,15	-	135,53	125,10	10,43	987,47	1.123,00	5.267,69	Pagamento
8	31/07/99	6	5.267,69	-	114,13	105,35	8,78	1.008,86	1.123,00	4.258,82	Pagamento
9	30/08/99	7	4.258,82	-	92,27	85,18	7,10	1.030,72	1.123,00	3.228,10	Pagamento
10	29/09/99	8	3.228,10	-	69,94	64,56	5,38	1.053,05	1.123,00	2.175,05	Pagamento
11	29/10/99	9	2.175,05	-	47,13	43,50	3,63	1.075,87	1.123,00	1.099,18	Pagamento
12	28/11/99	10	1.099,18	-	23,82	21,98	1,83	1.099,18	1.123,00	(0,00)	Pagamento

A planilha de saída deve ser gerada de acordo com um padrão, pois é nesta planilha que o sistema buscará os dados para geração dentro do sistema. Esta planilha deve ter as informações, conforme modelo abaixo (o modelo foi gerado tomando como base os mesmos dados utilizados nas planilhas acima). Os valores deverão estar em fatores financeiros. Segue o modelo da planilha de saída:

	A	B	C	D	E	F	G
1	Data	Tipo	/P	Liberação
2	01/02/99	Liberação	1	10.000,00
3	03/03/99	Pagamento	1	1.123,00
4	02/04/99	Pagamento	2	1.123,00
5	02/05/99	Pagamento	3	1.123,00
6	01/06/99	Pagamento	4	1.123,00
7	01/07/99	Pagamento	5	1.123,00
8	31/07/99	Pagamento	6	1.123,00
9	30/08/99	Pagamento	7	1.123,00
10	29/09/99	Pagamento	8	1.123,00
11	29/10/99	Pagamento	9	1.123,00
12	28/11/99	Pagamento	10	1.123,00
13	00/01/00	-	0	1.123,00

Exemplo:

Planilha de Entrada para o cálculo da Correção do Empréstimo:

Planilha 1 – Entrada:

	A	B	C	D	E
1	Taxa Pré	2%
2	Taxa Pós	1,43%
3	Taxa Del Credere	0,17%
4	Cota Inicial	0
5	Cota Final	0
6	% Min Variação Taxa	1%
7	% Max Variação Taxa	3%
8	% Garantia Taxa	100%
9	Qtd Dias UMP Operação	30
10	Qtd Dias Operação	30
11	Qtd Dias Úteis Operação	22
12	Quantidade de Parcelas
13	Fator Financeiro Base	1
14	Tipo de Dias UMP Produto	Período
15	Tipo de Dia Cálculo Juros	Corridos
16	Forma Pagamento Parcelas
17	Atributo Utilização Usuário 1
18	Atributo Utilização Usuário 2
19	Atributo Utilização Usuário 3
20	Atributo Utilização Usuário 4
21	Atributo Utilização Usuário 5
22	Saldo Operação Financeira	10.000,00
23
24
25
26	Parcelas de Pagamento
27	Tipo Parcela	Número Parcela	Data	Valor	Fixa
28
29

A planilha de cálculo é criada pelo usuário tomando como dados base os dados da planilha de entrada. Não existe um padrão congelado, sendo que o usuário pode alterar da forma que desejar. No exemplo foi utilizada uma planilha diferente da planilha de cálculo das parcelas, porém poderia ser a mesma desde que em células diferentes.

Exemplo:

Planilha de cálculo gerada com as informações acima (é importante lembrar que esta planilha é de livre utilização do usuário, porém com base nesta planilha serão geradas as informações para a planilha de saída):

	A	B	C	D
1	Taxa Correção:	3,62860%
2	Valor a Corrigir:	10.000,00
3
4	Valor da Correção:	345,86
6	Valor da Comissão Del Credere	17,00
7	Total da Correção	362,86
8
9	Fator Financeiro Anterior	1
10	Fator Financeiro Atual	1,02330
11

A planilha de saída deve ser gerada de acordo com um padrão, pois é nesta planilha que o sistema irá buscar os dados para geração dentro do sistema. Esta planilha deverá ter as informações, conforme modelo abaixo (o modelo foi gerado tomando como base os mesmos dados utilizados nas planilhas acima). Os valores deverão estar em fatores financeiros. Segue o modelo da planilha de saída:

	A	B	C	D	E	F	G
1						Juros Calculados:	345,86
2						Comissão Del Credere Calculada:	17,00
3						Fator Financeiro Atualizado:	1,02330
4
5

A Datasul fornece apenas as planilhas de exemplo, ficando a cargo do usuário a criação de novas planilhas.

Cálculo/Recálculo das Parcelas do Empréstimo

Possibilita atualizar o valor principal das parcelas de pagamento. Este valor será calculado em planilha de cálculo Excel e retornado para o sistema. Este valor será utilizado para que o sistema traga automaticamente o valor principal do pagamento para os pagamentos via parcelas de pagamento. No caso de parcelas de juros o valor do principal será sempre zero e no caso de parcelas de liberação o valor do principal será sempre o valor total da parcela.

As planilhas de cálculo Excel referentes a empréstimos devem ser preparadas para retornar o valor principal das parcelas.

Atualização de Liberação de Empréstimo

A operação financeira será atualizada pelo seu valor principal da liberação. Caso o empréstimo possua despesa bancária ou imposto que aumente o valor saldo do empréstimo, este valor é somado ao valor do empréstimo.

Correção das Operações Financeiras de Empréstimo

Nesta função é passado para a planilha de cálculo Excel o valor correspondente ao valor principal da operação. No momento da correção o saldo do principal não é modificado, sendo alterado o saldo total da operação financeira.

As planilhas de cálculo Excel devem ser preparadas para receber o saldo do principal.

Conteúdos Relacionados:

Cálculo de Juros Lineares para as Operações de Aplicações e Empréstimos