Usar o Excel para Encontrar o Pagamento e os Juros Totais de um Empréstimo

Introdução

Nesta lição, você aprenderá a calcular os juros de um empréstimo usando o Microsoft Excel.

Este vídeo ilustra o material da lição abaixo. Assistir ao vídeo é opcional.

Usar o Excel para Calcular o Total de Juros Cobrados em um Empréstimo | Transcrição

Usar o Excel para Calcular o Pagamento e os Juros Totais de um Empréstimo

A Figura 1 abaixo ilustra todas as variáveis diferentes necessárias para calcular o pagamento. Veja aqui uma lista dos títulos de informações em cada célula na coluna A.

A2: Taxa de Juros Anual
A3: Taxa Periódica (taxa). Esta é a Taxa de Juros Anual dividida pelos Pagamentos por ano. Aparecerá como um argumento na função de pagamento.
A4: Número de Anos
A5: Pagamentos por Ano
A6: nper. Este é o número de pagamentos por ano multiplicado pelo número de anos. Aparecerá como um argumento na função de pagamento.
A7: Valor do Empréstimo (Vp = Valor Presente). Aparecerá como um argumento na função de pagamento.
A8: Pagamento Mensal (use a função PGTO)
A9: Valor do Empréstimo com Juros. Este é o pagamento mensal vezes o nper.
A10: Despesa com Juros. Este é o Valor do Empréstimo com Juros + Valor do Empréstimo.
A11: Despesa Média Mensal com Juros. Esta é a Despesa com Juros dividida pelo nper.

A coluna B conterá os números. Observe que, na Figura 1, a taxa periódica, o nper e o valor do empréstimo estão destacados em amarelo. Essas são as três variáveis que entram na função PGTO.

Figura 1

Exemplo 1

Encontre o pagamento mensal e os juros totais de um empréstimo com uma taxa de juros anual de 6% (0.06) ao longo de cinco anos.

Primeiro, digite os números conhecidos na coluna B do Excel:

Taxa de Juros Anual: .06
Número de Anos: 5
Pagamentos por Ano: 12

Use estas informações para calcular a Taxa Periódica (taxa) e o nper.

Figura 2

A taxa periódica é a parcela da taxa de juros anual que é paga em cada pagamento; portanto, é o mesmo que a taxa anual dividida pelo número de vezes que um pagamento é feito a cada ano.

No Excel, use as referências relativas para calcular a taxa periódica na célula B5. A fórmula fica assim: =B2/B5
Pressione Enter para calcular.

Figura 3

A Taxa Periódica (taxa) é 0.005. Em outras palavras, é 0.5% ao mês.

O nper é o número total de pagamentos feitos ao longo do empréstimo. Ele é calculado pegando o número de pagamentos por ano e multiplicando-o pelo número de anos.

No Excel, use as referências relativas para calcular o nper na célula B6. A fórmula fica assim: =B5*B4
Pressione Enter para calcular.

Figura 4

O nper neste exemplo é 60. O Valor do Empréstimo (Vp = Valor Presente) geralmente não precisa ser calculado. Neste exemplo, ele é dado: $12,000.

Figura 5

Use todas essas informações para calcular o pagamento mensal.

Na célula B8, digite =PGTO(
Insira os argumentos necessários usando referências relativas.
- A taxa é B3
- O nper é B6
- O Vp é B7
Separe cada um com uma vírgula e inclua um parêntese direito. A fórmula fica assim: =PGTO(B3,B6,B7)
Pressione Enter para calcular.

Figura 6

O Excel mostra que este é um número negativo porque é um pagamento a ser feito. No Excel, às vezes, um número negativo é mostrado em vermelho com parênteses ao redor dele. O pagamento do empréstimo é de $231.99 por mês durante 60 meses. Este pagamento mensal paga parte do principal e também a taxa de juros mensal.

Figura 7

Você pagará $231.99 todo mês durante 60 meses para quitar um empréstimo de $12,000. Lembre-se de que esse pagamento inclui o principal e a taxa de juros do empréstimo.

Para calcular o valor total de juros pagos ao longo dos 60 pagamentos, primeiro multiplique o pagamento mensal pelo número total de pagamentos, ou pelo nper.

Na célula B9, digite =B8*B6
Pressione Enter para calcular.

Figura 8

O Valor do Empréstimo com Juros durante cinco anos é de $13,919.62. Este valor total do custo do empréstimo inclui o valor original do empréstimo mais os juros. Observe que este é um número negativo. Para encontrar apenas os juros, some-os ao valor do empréstimo, pois o valor do empréstimo é um número positivo.

Na célula B10, digite =B9+B7
Pressione Enter para calcular.

Figura 9

A despesa com juros total deste empréstimo é de $1919.62. Para calcular a despesa média mensal com juros, divida esse valor total de juros pelo número de pagamentos feitos no empréstimo, ou nper.

Na célula B11, digite = B10/B6
Pressione Enter para calcular.

Figura 10

A despesa média mensal com juros é de $31.99.

Figura 11

Os pagamentos iniciais de um empréstimo geralmente pagam juros. À medida que o empréstimo é pago ao longo do tempo, uma parcela cada vez maior do pagamento cobre o principal.

Lembre-se

Para calcular o pagamento mensal de um empréstimo:
1. Organize qualquer informação fornecida em uma planilha do Excel.
2. Encontre a taxa periódica e o nper usando as informações fornecidas.
3. Use a função PGTO: =PGTO(taxa, nper, Vp).
Para calcular o total de juros de um empréstimo:
1. Encontre o valor total do empréstimo, incluindo juros, multiplicando o pagamento mensal pelo nper.
2. Encontre a diferença entre o valor total do empréstimo e o valor principal. (Valor Total do Empréstimo - Valor Principal) Observe que se o valor total do empréstimo for fornecido como um número negativo (como no exemplo acima), você adicionará o valor principal a ele.

Problemas Práticos

Use o Excel para calcular os juros totais de um empréstimo de $$45,000$ para uma pequena empresa que faz pagamentos mensais com uma taxa de juros de $7.5\%$ ao longo de $10$ ano. (
Solução

x

Solução: $$19,098.96$

)
Use o Excel para calcular os juros totais de um empréstimo de $$45,000$ para uma pequena empresa que faz pagamentos mensais com uma taxa de juros de $5.25\%$ ao longo de $15$ anos. (
Solução

x

Solução: $$20,114.09$

Detalhes:

Para descobrir o total de juros, comece descobrindo o valor total que você pagou. Você sabe o seguinte:

Valor do empréstimo $= vp = $45,000$
Taxa de juros anual $= r = 5.25\%$
Duração do empréstimo $= 15$ anos
Pagamentos por ano $= n = 12$

Usando estas informações, você pode encontrar o seguinte:

$\displaystyle\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0525}{12}\text{ or }\frac{5.25\%}{12}$

Número total de pagamentos $= nper = anos \times n = 15 \times 12 = 180$
Coloque as informações acima no Excel para que ele possa fazer os cálculos por você:

Sempre que você começa com “=”, isso informa ao Excel que você quer que ele faça um cálculo.

Para encontrar a taxa periódica, selecione a célula na qual você quer que o cálculo seja realizado e digite: =B3/B6

(A célula B3 contém a taxa de juros anual e a célula B6 contém o número de pagamentos por ano.)

Em seguida, selecione Enter.

Em seguida, calcule o número total de pagamentos (nper). O número total de pagamentos é encontrado multiplicando a duração do empréstimo pelo número de pagamentos por ano. Use o Excel para calcular o número total de pagamentos selecionando a célula correspondente e digitando o seguinte: =B5*B6

(A célula B5 contém a duração do empréstimo em anos e a célula B6 contém os pagamentos por ano.)

Em seguida, selecione Enter.

Para calcular o pagamento mensal, digite o seguinte na célula correspondente: =pgto(B4,B7,B2)

(A célula B4 contém a taxa periódica, ou TAXA, a célula B7 contém o número total de pagamentos, ou NPER, e a célula B2 contém o valor do empréstimo, ou VP.)

Em seguida, pressione Enter.

Para calcular o valor total do empréstimo com juros, multiplique o pagamento mensal pelo número total de pagamentos (nper). Faça com que o Excel calcule isto para você digitando o seguinte: =B8*B7

(A célula B8 contém o pagamento mensal. A célula B7 contém o número total de pagamentos.)

Em seguida, selecione Enter.

Para calcular os juros pagos, some o valor total com juros ao valor do empréstimo. Faça com que o Excel faça o cálculo digitando o seguinte: =B9+B2

(A célula B9 contém o valor total com juros. A célula B2 contém o valor do empréstimo.)

Em seguida, selecione Enter.

O valor que aparece em B10 é ($$20114.09$).

Portanto, o total de juros pagos é de $$20,114.09$.

)
Use o Excel para calcular os juros totais de um empréstimo de um carro pago mensalmente considerando os valores a seguir: (
Solução

x

Solução: $$2,494.39$

)
- Valor do empréstimo = $13,500$
- Taxa de juros anual = $5.75\%$
- Duração do empréstimo = 6 anos
Use o Excel para calcular os juros totais de um empréstimo de um carro pago mensalmente considerando os valores a seguir:(
Solução

x

Solução: $$1,818.09$

Detalhes:

Para descobrir o total de juros, comece descobrindo o valor total que você pagou.

Você sabe o seguinte:

Valor do empréstimo $= vp = $13,500$
Taxa de juros anual $= r = 4.25\%$
Duração do empréstimo $= 6$ anos
Pagamentos por ano $= n = 12$

Usando estas informações, você pode encontrar o seguinte:

$\displaystyle\text{Periodic Interest Rate}=\frac{\text{r}}{\text{n}}=\text{rate}=\frac{0.0425}{12}\text{ or }\frac{4.25\%}{12}$

Número total de pagamentos = $nper = anos \times n = 6 \times 12 = 72$

Coloque as informações acima no Excel para que ele possa fazer os cálculos por você:

Sempre que começamos com “=”, isto informa ao Excel que você quer que ele faça um cálculo.

Para encontrar a taxa periódica, selecione a célula na qual você quer que o cálculo seja realizado e digite: =B3/B6

(A célula B3 contém a taxa de juros anual e a célula B6 contém o número de pagamentos por ano.)

Em seguida, selecione Enter.

Em seguida, calcule o número total de pagamentos (nper). O número total de pagamentos é encontrado multiplicando a duração do empréstimo pelo número de pagamentos por ano. Use o Excel para calcular o número total de pagamentos selecionando a célula correspondente e digitando o seguinte: =B5*B6

(A célula B5 contém a duração do empréstimo em anos e a célula B6 contém os pagamentos por ano.)

Em seguida, selecione Enter.

Para calcular o pagamento mensal, digite o seguinte na célula correta: =pgto(B4,B7,B2)

(A célula B4 contém a taxa periódica, ou TAXA, a célula B7 contém o número total de pagamentos, ou NPER, e a célula B2 contém o valor do empréstimo, ou VP.)

Em seguida, selecione Enter.

Para calcular o valor total do empréstimo com juros, multiplique o pagamento mensal pelo número total de pagamentos (nper). Faça com que o Excel calcule isso para você digitando o seguinte: =B8*B7

(A célula B8 contém o pagamento mensal. A célula B7 contém o número total de pagamentos.)

Em seguida, selecione Enter.

Para calcular os juros pagos, some o valor total com juros ao valor do empréstimo. Faça com que o Excel efetue o cálculo digitando o seguinte: =B9+B2

(A célula B9 contém o valor total com juros. A célula B2 contém o valor do empréstimo.)

Em seguida, selecione Enter.

O valor que aparece na célula B10 é ($$1818.09$). Este é o valor total de juros pagos no empréstimo.

)
- Valor do empréstimo = $13,500\
- Taxa de juros anual = \(4.25\%$
- Duração do empréstimo = 6 anos
Use o Excel para calcular os juros totais de um empréstimo de um financiamento pago mensalmente considerando os valores a seguir: (
Solução

x

Solução: $$170,095.53$

)
- Valor do empréstimo = $245,500$
- Taxa de juros anual = $3.875\%$
- Duração do empréstimo = 30 anos
Use o Excel para calcular o total de juros de um celular por $$800$ a $5.25\%$ de juros ao longo de dois anos com base em pagamentos mensais.. (
Solução

x

Solução: $$44.48$

)