Fórmula per calcular una anualitat en Excel

Els pagaments de préstecs són més fàcils i ràpids de calcular amb Microsoft Office Excel. Es dedica molt més temps al càlcul manual. Aquest article se centrarà en els pagaments de les anualitats, les característiques del seu càlcul, els avantatges i els inconvenients.

Què és un pagament anual

Un mètode d'amortització mensual d'un préstec, en el qual l'import dipositat no canvia durant tot el període del préstec. Aquells. en determinades dates de cada mes, una persona diposita una quantitat específica de diners fins que el préstec es reemborsa completament.

A més, els interessos del préstec ja estan inclosos en l'import total pagat al banc.

Classificació anualitat

Els pagaments anuals es poden dividir en els següents tipus:

1. Arreglat. Els pagaments que no canvien tenen una tarifa fixa independentment de les condicions externes.
2. Moneda. La possibilitat de canviar l'import del pagament en cas de caiguda o pujada del tipus de canvi.
3. indexat. Pagaments en funció del nivell, indicador d'inflació. Durant el període de préstec, la seva mida sovint canvia.
4. Les variables. Instruments anuals, que poden canviar en funció de l'estat del sistema financer.

Presta atenció! Els pagaments fixos són preferibles per a tots els prestataris, perquè tenen poc risc.

Avantatges i inconvenients dels pagaments d'anualitats

Per entendre millor el tema, cal estudiar les característiques clau d'aquest tipus de pagaments de préstecs. Té els següents avantatges:

• Establir un import concret de pagament i la data del seu pagament.
• Alta disponibilitat per als prestataris. Gairebé qualsevol persona pot sol·licitar una anualitat, independentment de la seva situació financera.
• La possibilitat de rebaixar l'import de la quota mensual amb un augment de la inflació.

No sense inconvenients:

• Taxa alta. El prestatari pagarà en excés una quantitat de diners més gran en comparació amb el pagament diferencial.
• Problemes derivats del desig de pagar el deute abans del previst.
• Sense recàlculs per pagaments anticipats.

Què és el pagament del préstec?

El pagament de la renda anual té els components següents:

• Interessos pagats per una persona en pagar un préstec.
• Part de l'import principal.

Com a resultat, l'import total dels interessos gairebé sempre supera l'import aportat pel prestatari per reduir el deute.

Fórmula bàsica de pagament d'anualitats en Excel

Com s'ha esmentat anteriorment, a Microsoft Office Excel podeu treballar amb diversos tipus de pagaments de préstecs i bestretes. La anualitat no és una excepció. En general, la fórmula amb la qual podeu calcular ràpidament les aportacions a les anualitats és la següent:  

Important És impossible obrir claudàtors en el denominador d'aquesta expressió per simplificar-la.

Els valors principals de la fórmula es desxifren de la següent manera:

• AP - pagament d'anualitats (el nom s'abreuja).
• O: la mida del deute principal del prestatari.
• PS: el tipus d'interès proposat mensualment per un banc concret.
• C és el nombre de mesos que dura el préstec.

Per assimilar la informació n'hi ha prou amb posar alguns exemples de l'ús d'aquesta fórmula. Se'n parlaran més endavant.

Exemples d'ús de la funció PMT a Excel

Donem una condició simple del problema. Cal calcular el pagament mensual del préstec si el banc proposa un interès del 23% i l'import total és de 25000 rubles. El préstec tindrà una durada de 3 anys. El problema es resol segons l'algorisme:

1. Feu un full de càlcul general en Excel basat en les dades d'origen.

2. Activeu la funció PMT i introduïu-ne els arguments a la casella corresponent.
3. Al camp "Aposta", introduïu la fórmula "B3/B5". Aquest serà el tipus d'interès del préstec.
4. A la línia "Nper" escriviu el valor en la forma "B4*B5". Aquest serà el nombre total de pagaments durant tota la durada del préstec.
5. Ompliu el camp "PS". Aquí cal indicar l'import inicial pres del banc, escrivint el valor “B2”.

6. Assegureu-vos que després de fer clic a "D'acord" a la taula d'origen, s'ha calculat el valor "Pagament mensual".

Informació adicional! Un nombre negatiu indica que el prestatari està gastant diners.

Un exemple de càlcul de l'import del pagament en excés d'un préstec a Excel

En aquest problema, cal calcular la quantitat que pagarà en excés una persona que hagi pres un préstec de 50000 rubles a un tipus d'interès del 27% durant 5 anys. En total, el prestatari fa 12 pagaments a l'any. Solució:

1. Compilar la taula de dades original.

2. De l'import total dels pagaments, resta l'import inicial segons la fórmula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». S'ha d'inserir a la barra de fórmules a la part superior del menú principal del programa.
3. Com a resultat, l'import dels sobrepagaments apareixerà a l'última línia de la placa creada. El prestatari pagarà en excés 41606 rubles a la part superior.

La fórmula per calcular el pagament mensual òptim del préstec en Excel

Una tasca amb la següent condició: el client ha registrat un compte bancari per 200000 rubles amb possibilitat de reposició mensual. Cal calcular la quantitat de pagament que una persona ha de fer cada mes, de manera que després de 4 anys tingui 2000000 rubles al seu compte. La taxa és de l'11%. Solució:

1. Creeu un full de càlcul basat en les dades originals.

2. Introduïu la fórmula a la línia d'entrada d'Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» i premeu "Enter" des del teclat. Les lletres seran diferents segons les cel·les en què es col·loqui la taula.
3. Comproveu que l'import de la cotització es calcula automàticament a l'última línia de la taula.

Presta atenció! Per tant, perquè el client acumuli 4 rubles a una taxa del 2000000% en 11 anys, ha de dipositar 28188 rubles cada mes. El menys de l'import indica que el client incorre en pèrdues donant diners al banc.

Característiques de l'ús de la funció PMT a Excel

En general, aquesta fórmula s'escriu de la següent manera: =PMT(taxa; nper; ps; [bs]; [tipus]). La funció té les següents característiques:

1. Quan es calculen les quotes mensuals, només es té en compte la taxa anual.
2. A l'hora d'especificar el tipus d'interès, és important tornar a calcular en funció del nombre de quotes anuals.
3. En lloc de l'argument "Nper" a la fórmula, s'indica un nombre específic. Aquest és el període de pagament.

Càlcul de pagament

En general, el pagament de les anualitats es calcula en dues etapes. Per entendre el tema, cada una de les etapes s'ha de considerar per separat. Això es discutirà més endavant.

Fase 1: càlcul de la quota mensual

Per calcular en Excel l'import que has de pagar cada mes en un préstec amb tipus fix, has de:

1. Compileu la taula d'origen i seleccioneu la cel·la en què voleu mostrar el resultat i feu clic al botó "Insereix funció" a la part superior.

2. A la llista de funcions, seleccioneu "PLT" i feu clic a "D'acord".

3. A la finestra següent, establiu els arguments de la funció, indicant les línies corresponents a la taula compilada. Al final de cada línia, heu de fer clic a la icona i, a continuació, seleccionar la cel·la desitjada a la matriu.

4. Quan s'omplen tots els arguments, s'escriurà la fórmula adequada a la línia per introduir valors i el resultat del càlcul amb el signe menys apareixerà al camp de la taula "Pagament mensual".

Important Després de calcular la quota, es podrà calcular l'import que el prestatari pagarà en excés durant tot el període del préstec.

Etapa 2: dades de pagament

L'import del sobrepagament es pot calcular mensualment. Com a resultat, una persona entendrà quants diners gastarà en un préstec cada mes. El càlcul detallat es realitza de la següent manera:

1. Creeu un full de càlcul durant 24 mesos.

2. Col·loqueu el cursor a la primera cel·la de la taula i inseriu la funció "OSPLT".

3. Ompliu els arguments de la funció de la mateixa manera.

4. En omplir el camp "Període", cal que faci referència al primer mes de la taula, indicant la cel·la 1.

5. Comproveu que la primera cel·la de la columna "Pagament per part del cos del préstec" estigui emplenada.
6. Per omplir totes les files de la primera columna, cal estirar la cel·la fins al final de la taula

7. Seleccioneu la funció "PRPLT" per omplir la segona columna de la taula.
8. Ompliu tots els arguments a la finestra oberta d'acord amb la captura de pantalla següent.

9. Calcula el pagament mensual total sumant els valors de les dues columnes anteriors.

10. Per calcular el "Saldo a pagar", cal afegir el tipus d'interès al pagament del cos del préstec i estirar-lo fins al final de la placa per omplir tots els mesos del préstec.

Informació adicional! En calcular la resta, els signes del dòlar s'han de penjar a la fórmula perquè no es mogui quan s'estira.

Càlcul de pagaments anuals d'un préstec en Excel

La funció PMT és l'encarregada de calcular l'anualitat a Excel. El principi de càlcul en general és realitzar els passos següents:

1. Compilar la taula de dades original.
2. Elaboreu un calendari d'amortització del deute per a cada mes.
3. Seleccioneu la primera cel·la de la columna "Pagaments del préstec" i introduïu la fórmula de càlcul "PLT ($B3/12;$B$4;$B$2)".
4. El valor resultant s'estira per a totes les columnes de la placa.

Càlcul en MS Excel amortització de l'import principal del deute

Els pagaments de les anualitats s'han de fer mensualment en quantitats fixes. I el tipus d'interès no canvia.

Càlcul del saldo de l'import principal (amb BS=0, tipus=0)

Suposem que es pren un préstec de 100000 rubles durant 10 anys al 9%. Cal calcular l'import del deute principal el 1r mes del 3r any. Solució:

1. Elaboreu un full de dades i calculeu el pagament mensual mitjançant la fórmula de PV anterior.
2. Calcula la quota del pagament necessària per saldar part del deute mitjançant la fórmula «=-PMT-(PS-PS1)*element=-PMT-(PS +PMT+PS*element)».
3. Calcula l'import del deute principal durant 120 períodes mitjançant una fórmula coneguda.
4. Mitjançant l'operador HPMT, trobeu la quantitat d'interès pagat per al mes 25.
5. Comproveu el resultat.

Càlcul de l'import de principal que es va pagar entre dos períodes

Aquest càlcul es fa millor d'una manera senzilla. Heu d'utilitzar les fórmules següents per calcular la quantitat de l'interval per a dos períodes:

• =«-BS(element; con_període; plt; [ps]; [tipus]) /(1+tipus *element)».
• = "+ BS(taxa; període_inici-1; plt; [ps]; [tipus]) /IF (període_inici =1; 1; 1+tipus *taxa)".

Presta atenció! Les lletres entre parèntesis se substitueixen per valors específics.

Reemborsament anticipat amb termini reduït o pagament

Si necessiteu reduir el termini del préstec, haureu de realitzar càlculs addicionals mitjançant l'operador IF. Així serà possible controlar el saldo zero, que no s'ha d'assolir abans del final del període de pagament.

Per reduir els pagaments, cal tornar a calcular la contribució de cada mes anterior.

Calculadora de préstecs amb pagaments irregulars

Hi ha diverses opcions d'anualitat on el prestatari pot dipositar quantitats variables qualsevol dia del mes. En aquesta situació, el saldo del deute i els interessos es calculen per a cada dia. Al mateix temps, a Excel necessiteu:

1. Introduïu els dies del mes en què es realitzen els pagaments i indiqueu-ne el número.
2. Comproveu les quantitats positives i negatives. Es prefereixen els negatius.
3. Compteu els dies entre dues dates en què es van dipositar diners.

Càlcul del pagament periòdic en MS Excel. Dipòsit a termini

A Excel, podeu calcular ràpidament l'import dels pagaments regulars, sempre que ja s'hagi acumulat una quantitat fixa. Aquesta acció es realitza mitjançant la funció PMT després de compilar la taula inicial.

Conclusió

Així, els pagaments de les anualitats són més fàcils, ràpids i eficients de calcular a Excel. L'operador PMT és responsable del seu càlcul. Es poden trobar exemples més detallats més amunt.