Fórmula para calcular unha anualidade en Excel

Os pagos de préstamos son máis fáciles e rápidos de calcular con Microsoft Office Excel. Dedícase moito máis tempo ao cálculo manual. Este artigo centrarase nos pagos de anualidades, as características do seu cálculo, as vantaxes e os inconvenientes.

Que é unha anualidade

Un método de reembolso mensual dun préstamo, no que o importe depositado non cambia durante todo o período do préstamo. Eses. en determinadas datas de cada mes, unha persoa deposita unha cantidade específica de diñeiro ata que o préstamo sexa totalmente reembolsado.

Ademais, os xuros do préstamo xa están incluídos no importe total pagado ao banco.

Anualidade de clasificación

Os pagos de anualidades pódense dividir nos seguintes tipos:

1. Fixo. Os pagos que non varían teñen unha taxa fixa independentemente das condicións externas.
2. Moeda. A posibilidade de cambiar o importe do pago en caso de caída ou aumento do tipo de cambio.
3. indexado. Pagos dependendo do nivel, indicador de inflación. Durante o período de préstamo, o seu tamaño a miúdo cambia.
4. Variables. Anualidades, que poden cambiar en función do estado do sistema financeiro, instrumentos.

Presta atención! Os pagos fixos son preferibles para todos os prestameiros, porque teñen pouco risco.

Vantaxes e inconvenientes dos pagos de anualidades

Para comprender mellor o tema, é necesario estudar as principais características deste tipo de pagamentos de préstamos. Ten as seguintes vantaxes:

• Establecemento dun importe concreto de pagamento e da data do seu pagamento.
• Alta dispoñibilidade para os prestatarios. Case calquera persoa pode solicitar unha anualidade, independentemente da súa situación financeira.
• A posibilidade de baixar o importe da cota mensual cun aumento da inflación.

Non sen inconvenientes:

• Alta taxa. O prestatario pagará en exceso unha cantidade de diñeiro maior en comparación co pago diferencial.
• Problemas derivados do desexo de pagar a débeda antes do previsto.
• Non hai recálculos para pagos anticipados.

Cal é o pago do préstamo?

O pagamento da anualidade ten os seguintes compoñentes:

• Xuros pagados por unha persoa ao pagar un préstamo.
• Parte do importe principal.

Como resultado, o importe total dos xuros case sempre supera a cantidade aportada polo prestatario para reducir a débeda.

Fórmula básica de pago de anualidades en Excel

Como se mencionou anteriormente, en Microsoft Office Excel pode traballar con varios tipos de pagamentos de préstamos e anticipos. A anualidade non é unha excepción. En xeral, a fórmula coa que pode calcular rapidamente as contribucións á anualidade é a seguinte:  

Importante! É imposible abrir corchetes no denominador desta expresión para simplificala.

Os principais valores da fórmula decífranse do seguinte xeito:

• AP - pago de anualidades (o nome é abreviado).
• O - o tamaño da débeda principal do prestatario.
• PD: a taxa de interese presentada mensualmente por un banco en particular.
• C é o número de meses que dura o préstamo.

Para asimilar a información abonda con poñer algúns exemplos do uso desta fórmula. Delas serán discutidas máis adiante.

Exemplos de uso da función PMT en Excel

Damos unha condición sinxela do problema. É necesario calcular o pagamento mensual do préstamo se o banco presenta un xuro do 23% e o importe total é de 25000 rublos. O préstamo terá unha duración de 3 anos. O problema resólvese segundo o algoritmo:

1. Fai unha folla de cálculo xeral en Excel baseada nos datos de orixe.

2. Active a función PMT e introduza os argumentos para ela na caixa correspondente.
3. No campo "Aposta", introduza a fórmula "B3/B5". Este será o tipo de interese do préstamo.
4. Na liña "Nper" escriba o valor na forma "B4*B5". Este será o número total de pagamentos durante todo o prazo do préstamo.
5. Encha o campo "PS". Aquí cómpre indicar a cantidade inicial tomada do banco, escribindo o valor "B2".

6. Asegúrese de que despois de facer clic en "Aceptar" na táboa de orixe, calculouse o valor "Pago mensual".

Información adicional! Un número negativo indica que o prestatario está gastando diñeiro.

Un exemplo de cálculo do importe do sobrepago dun préstamo en Excel

Neste problema, cómpre calcular a cantidade que pagará de máis unha persoa que tomou un préstamo de 50000 rublos a un tipo de xuro do 27% durante 5 anos. En total, o prestatario realiza 12 pagamentos ao ano. Solución:

1. Compile a táboa de datos orixinal.

2. Do importe total dos pagos, resta o importe inicial segundo a fórmula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Debe inserirse na barra de fórmulas na parte superior do menú principal do programa.
3. Como resultado, o importe dos sobrepagos aparecerá na última liña da placa creada. O prestatario pagará en exceso 41606 rublos.

A fórmula para calcular o pagamento mensual óptimo do préstamo en Excel

Unha tarefa coa seguinte condición: o cliente rexistrou unha conta bancaria por 200000 rublos con posibilidade de reposición mensual. É necesario calcular a cantidade de pagamento que unha persoa debe facer cada mes, para que despois de 4 anos teña 2000000 rublos na súa conta. A taxa é do 11%. Solución:

1. Crea unha folla de cálculo baseada nos datos orixinais.

2. Introduza a fórmula na liña de entrada de Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» e prema "Intro" no teclado. As letras diferirán segundo as celas nas que estea colocada a táboa.
3. Comproba que o importe da cotización se calcula automaticamente na última liña da táboa.

Presta atención! Así, para que o cliente acumule 4 rublos a un ritmo de 2000000% en 11 anos, debe depositar 28188 rublos cada mes. O menos no importe indica que o cliente incorre en perdas ao dar diñeiro ao banco.

Características do uso da función PMT en Excel

En xeral, esta fórmula escríbese do seguinte xeito: =PMT(taxa; nper; ps; [bs]; [tipo]). A función ten as seguintes características:

1. Cando se calculan as cotizacións mensuais só se ten en conta a taxa anual.
2. Ao especificar o tipo de xuro, é importante recalcular en función do número de cotas ao ano.
3. En lugar do argumento "Nper" na fórmula, indícase un número específico. Este é o período de pago.

Cálculo do pago

En xeral, o pago da anualidade calcúlase en dúas etapas. Para comprender o tema, cada unha das etapas debe considerarse por separado. Isto será discutido máis adiante.

Fase 1: cálculo da mensualidade

Para calcular en Excel a cantidade que debes pagar cada mes nun préstamo cunha taxa fixa, debes:

1. Compile a táboa de orixe e selecciona a cela na que queres mostrar o resultado e fai clic no botón "Inserir función" na parte superior.

2. Na lista de funcións, seleccione "PLT" e prema en "Aceptar".

3. Na seguinte xanela, establece os argumentos para a función, indicando as liñas correspondentes na táboa compilada. Ao final de cada liña, cómpre facer clic na icona e, a continuación, seleccionar a cela desexada na matriz.

4. Cando se cubran todos os argumentos, escribirase a fórmula adecuada na liña para introducir valores e o resultado do cálculo cun signo menos aparecerá no campo da táboa "Pagamento mensual".

Importante! Despois de calcular a cota, poderase calcular o importe que o prestatario pagará en exceso durante todo o período do préstamo.

Fase 2: detalles do pagamento

O importe do sobrepago pódese calcular mensualmente. Como resultado, unha persoa entenderá canto diñeiro gastará nun préstamo cada mes. O cálculo detallado realízase do seguinte xeito:

1. Crea unha folla de cálculo durante 24 meses.

2. Coloque o cursor na primeira cela da táboa e insira a función "OSPLT".

3. Encha os argumentos da función do mesmo xeito.

4. Ao cubrir o campo "Período", cómpre referirse ao primeiro mes da táboa, indicando a cela 1.

5. Comprobe que a primeira cela da columna "Pago polo corpo do préstamo" estea cuberta.
6. Para encher todas as filas da primeira columna, cómpre estirar a cela ata o final da táboa

7. Seleccione a función "PRPLT" para cubrir a segunda columna da táboa.
8. Encha todos os argumentos na xanela aberta segundo a captura de pantalla que aparece a continuación.

9. Calcule o pago mensual total sumando os valores das dúas columnas anteriores.

10. Para calcular o "Saldo a pagar", cómpre engadir o tipo de xuro ao pagamento do corpo do préstamo e estiralo ata o final da placa para cubrir todos os meses do préstamo.

Información adicional! Ao calcular o resto, os signos de dólar deben colgarse na fórmula para que non se saia ao estirar.

Cálculo de anualidades nun préstamo en Excel

A función PMT encárgase de calcular a anualidade en Excel. O principio de cálculo en xeral é realizar os seguintes pasos:

1. Compile a táboa de datos orixinal.
2. Elabora un calendario de amortización da débeda para cada mes.
3. Seleccione a primeira cela da columna "Pagos do préstamo" e introduza a fórmula de cálculo "PLT ($B3/12;$B$4;$B$2)".
4. O valor resultante esténdese para todas as columnas da placa.

Cálculo en MS Excel a amortización do principal da débeda

Os pagos das anualidades deberán realizarse mensualmente en cantidades fixas. E o tipo de interese non cambia.

Cálculo do saldo do importe principal (con BS=0, tipo=0)

Supoñamos que un préstamo de 100000 rublos se toma durante 10 anos ao 9%. É necesario calcular o importe da débeda principal no 1o mes do 3o ano. Solución:

1. Elabora unha folla de datos e calcula o pago mensual mediante a fórmula de PV anterior.
2. Calcula a parte do pago necesario para saldar parte da débeda mediante a fórmula «=-PMT-(PS-PS1)*elemento=-PMT-(PS +PMT+PS*elemento)».
3. Calcula o importe da débeda principal durante 120 períodos mediante unha fórmula coñecida.
4. Usando o operador HPMT, atopa o importe dos xuros pagados para o mes 25.
5. Comproba o resultado.

Cálculo do importe do principal que se pagou entre dous períodos

Este cálculo faise mellor dun xeito sinxelo. Debe utilizar as seguintes fórmulas para calcular a cantidade no intervalo de dous períodos:

• =«-BS(elemento; con_período; plt; [ps]; [tipo]) /(1+tipo *elemento)».
• = "+ BS(taxa; período_inicio-1; plt; [ps]; [tipo]) /IF(período_inicio =1; 1; 1+tipo *taxa)".

Presta atención! As letras entre parénteses substitúense por valores específicos.

Amortización anticipada con prazo reducido ou pagamento

Se precisa reducir o prazo do préstamo, terá que realizar cálculos adicionais mediante o operador IF. Así, será posible controlar o saldo cero, que non debe alcanzarse antes do remate do período de pago.

Para reducir os pagos, cómpre recalcular a cotización de cada mes anterior.

Calculadora de préstamos con pagos irregulares

Existen varias opcións de anualidades nas que o prestatario pode depositar cantidades variables en calquera día do mes. En tal situación, o saldo da débeda e os xuros calcúlanse para cada día. Ao mesmo tempo, en Excel necesitas:

1. Introduza os días do mes para os que se realizan os pagos e indica o seu número.
2. Comprobe as cantidades negativas e positivas. Prefírense os negativos.
3. Conta os días entre dúas datas nas que se depositou o diñeiro.

Cálculo do pago periódico en MS Excel. Depósito a prazo

En Excel, pode calcular rapidamente o importe dos pagamentos regulares, sempre que xa se acumule un importe fixo. Esta acción realízase mediante a función PMT despois de compilar a táboa inicial.

Conclusión

Así, os pagos de anualidades son máis fáciles, rápidos e eficientes de calcular en Excel. O operador PMT é responsable do seu cálculo. Podes atopar exemplos máis detallados arriba.