Excel Planilha de Consórcios descobre Taxa adm e Valor parcela

[Netobarbuio]

Bom dia!! 

preciso de uma ajuda para as condições informadas, os cálculos são para achar a taxa administrativa de consorcio de imóvel, já tentei monta a planilha e não consigo chegar no resultado!

algum com melhor conhecimento poderia ajudar!!
segue abaixo as condições:
 

Célula                                                                                        Nome                                                                                                      Valor                                                                      Fórmula

A1                                                                                  Número de Períodos do Crédito                                                                         200

A2                                                                                     Valor Presente do Crédito                                                                          R$ 400.000,00

A3                                                                                  Valor Presente do Crédito Menos o Lance                                                     R$ 300.000,00                                                           =A2-B1

B1                                                                                         Valor do Lance                                                                                      R$ 100.000,00

B2                                                                               Valor Futuro do Crédito                                                                                    R$ 512.000,00                                                            =200*2560 

B3                                                                     Novo Valor Futuro do Crédito com o Lance Deduzido nas Parcelas                         R$ 385.121,64                                                      =A3*(1+D3)^C3

C1                                                                                    Número de Períodos do Crédito                                                                         200

C2                                                                                    Número de Períodos do Crédito                                                                        200

C3                                                                         Número de Períodos Restantes do Crédito com o Lance                                            199                                                                          =A1-1

D1                                                                               Taxa de Juros Efetiva Mensal do Crédito                                                               0,12%                                                           =(B2/A2)^(1/C2)-1

D2                                                                     Taxa de Juros Efetiva Mensal do Crédito Sem Lance                                                     0,12%                                                            =(B2/A2)^(1/C2)-1 
D3                                                                                   Com Lance Deduzido nas Parcelas                                                                    0,18%                                                           =(B2/A3)^(1/C3)-1

E1                                                                             Taxa de Juros Efetiva Anual do Crédito                                                              R$ 2.560,00                                                                 =D2*12

E2                                                                             Taxa de Juros Efetiva Anual do Crédito                                                               R$ 2.560,00                                                                  =D2*12

E3                                                                       Taxa de Juros Efetiva Anual do Crédito com o Lance                                               R$ 1.930,76                                                                     =D3*12

F1                                                                                Parcela Sem Lance do Crédito                                                                        R$ 1.930,76                                                       =PGTO(D2;C2;A2;0;0)

F2                                                                    Parcela Com Lance Deduzido nas Parcelas do Crédito                                             R$ 1.930,76                                                   =PGTO(D3;C3;A3;B3;0)   ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Seria de as informações ficar assim conforme abaixo : quando digitar as informações a planilha devirá informar    ****** Taxa e a parcela******

 

 

Valor Presente                  Valor Futuro                Número de Períodos              Taxa de Juros Efetiva               Taxa de Juros Efetiva             Anual Parcela Sem Lance           Parcela Com Lance

R$400.000,00                  R$512.000,00                      200                                            0,12%                                     1,44%                                        R$2560,00

R$300.000,00                  R$385.121,64                      199                                            0,18%                                     2,16%                                                                                       R$1930,76

                                               

[Luan Valle]

@Netobarbuio Em qual célula você precisa digitar essas informações, e em qual célula ou de que forma você quer que a planilha te informe o resultado? Poderia disponibilizar essa planilha?

[Netobarbuio]

Segue uma foto de como estou tentando colocar na planilha ...celular com cor sera o resultado

segue

Eu tenho esse resultado que consegui pelo Chatgbt 4

[Midori]

3 horas atrás, Netobarbuio disse:

quando digitar as informações a planilha devirá informar    ****** Taxa e a parcela******

No seu comentário já tem a fórmula da taxa, (B2/A2)^(1/C2)-1, e a parcela é a divisão do valor futuro pelo período.

[Netobarbuio]

ja lancei as informações conforme a foto na outra resposta mas não consigo chegar no resultado correto 

Se tiver alguém com mais experiencia no assunto ou no Excel e formulas puder ajudar 

Preciso muito da planilha e não consigo chegar no resultado

 

[Midori]

@Netobarbuio Não chega no resultado porque está usando dois métodos. O valor futuro de 512 mil não considera amortizações. E se for isso mesmo que quer fazer a parcela é apenas a divisão desse valor pelo período.

 

Para o cálculo de parcelas com PGTO tem as amortizações. Assim a parcela do empréstimo de 400 mil, período 200 e 0,12351% a.m será 2.258,40, como fez em F1, e não 2.560,00. E o valor total 451.681,62.

[Netobarbuio]

você consegue me ajuda coloca isso planilha

Pra que eu possa entender as duas forma e conseguir chegar nos valores corretor de forma clara 

Posso manda se você quiser a planilha de montei e você ajusta

Que acontece e que quando eles mandam os valores o que chega é assim

Crédito 400 mil 

Parcelas 200

Valor parcela R$ 2.560.00

Preciso de uma planilha que com essas informações ela me informe taxa a.a. e taxa a.m. e depois ela refaça cálculo com lance 100 mil

 

 

 

Em 06/09/2023 às 07:49, Luan Valle disse:

@Netobarbuio Em qual célula você precisa digitar essas informações, e em qual célula ou de que forma você quer que a planilha te informe o resultado? Poderia disponibilizar essa planilha?

 

As informações que chega a mim 

Crédito 400 mil

Valor parcela 2.560.00

Parcela 200

Preciso com essas informações que a planilha me informe resultado taxa a.a e da taxa a.m porém quando eu der lance também faça outro cálculo e informe valor da parcela que fica após o lance junto taxa a.a e taxa a.m

[Midori]

1 hora atrás, Netobarbuio disse:

Que acontece e que quando eles mandam os valores o que chega é assim

Crédito 400 mil 

Parcelas 200

Valor parcela R$ 2.560.00

Preciso de uma planilha que com essas informações ela me informe taxa a.a. e taxa a.m. e depois ela refaça cálculo com lance 100 mil

 

Assim o valor futuro é o produto das parcelas pelo período, e para chegar na taxa tem o cálculo dos juros compostos. Com essas variáveis este é o resultado,

 

 

B4 = B3*B2

B5 = (B4/B1)^(1/B2)-1

B6 = (1+B5)^12-1