Abans de prendre un préstec, seria bo calcular-ne tots els pagaments. Això salvarà al prestatari en el futur de diversos problemes i desil·lusions inesperats quan resulta que el pagament en excés és massa gran. Les eines d'Excel poden ajudar en aquest càlcul. Descobrim com calcular els pagaments del préstec de rendes anuals en aquest programa.
Càlcul del pagament
En primer lloc, he de dir que hi ha dos tipus de pagaments de crèdit:
- Diferenciat;
- Anualitat.
Amb un esquema diferenciat, el client realitza una quota igual per mes dels pagaments al banc sobre el cos del préstec més els pagaments d’interessos. L’import dels pagaments d’interessos cada mes disminueix a mesura que disminueix el cos del préstec del qual es calcula. Així, també es redueix el pagament mensual total.
Un esquema de rendes utilitza un enfocament lleugerament diferent. El client realitza mensualment la mateixa quantitat del pagament total, que consisteix en pagaments del cos del préstec i pagaments d'interessos. Inicialment, els pagaments d'interessos es comptabilitzen per l'import total del préstec, però a mesura que el cos disminueix, l'interès es redueix. Però l’import total del pagament es manté sense canvis a causa de l’augment mensual dels pagaments del cos del préstec. Així, amb el pas del temps, la proporció d’interès en el pagament total mensual disminueix, mentre que la proporció de pagament per organisme augmenta. Al mateix temps, el pagament mensual total en si mateix no canvia durant tot el període de préstec.
Només quan calculem el pagament de la renda vitalicia, ens aturem. A més, això és rellevant, ja que actualment la majoria dels bancs utilitzen aquest sistema en particular. També és convenient per als clients, ja que, en aquest cas, la quantitat total de pagament no canvia, quedant fixa. Els clients sempre saben quant de pagar.
Etapa 1: Càlcul de taxes mensuals
Per calcular la quota mensual quan s'utilitza el pla de rendes a Excel hi ha una funció especial: PMT. Pertany a la categoria d'operadors financers. La fórmula per a aquesta funció és la següent:
= PMT (tipus; nper; ps; bs; tipus)
Com podeu veure, la funció especificada té un nombre bastant gran d’arguments. És cert que els dos últims no són obligatoris.
Argument "Aposta" indica el tipus d’interès per a un període determinat. Si, per exemple, s’utilitza la taxa anual, però el préstec es paga mensualment, la taxa anual s’ha de dividir per 12 i utilitzeu el resultat com a argument. Si s’utilitza el mètode de pagament trimestral, en aquest cas s'hauria de dividir la taxa anual en 4 i així successivament
"Kper" indica el nombre total de períodes d'amortització del préstec. És a dir, si es pren un préstec per un any amb un pagament mensual, es considera el nombre de períodes 12si durant dos anys, llavors el nombre de períodes és 24. Si es pren un préstec per dos anys amb pagament trimestral, el nombre de períodes és igual a 8.
"Ps" indica el valor actual. En paraules senzilles, es tracta de l’import total del préstec al començament del préstec, és a dir, l’import que preneu, excloent els interessos i altres pagaments addicionals.
"B" - aquest és el valor futur. Aquest valor, que serà el cos del préstec en el moment de la finalització del contracte de préstec. En la majoria dels casos, aquest argument és "0", ja que el prestatari al final del període de préstec ha de pagar totalment el prestador. L’argument especificat és opcional. Per tant, si cau, es considera que és zero.
Argument "Tipus" determina el temps de càlcul: al final o al començament del període. En el primer cas, pren el valor "0", i en el segon - "1". La majoria de les institucions bancàries utilitzen exactament l’opció amb el pagament al final del període. Aquest argument també és opcional i, si ho ometeu, es considera que és zero.
Ara és hora de passar a un exemple concret de càlcul de la quota mensual mitjançant la funció PMT. Per al càlcul, utilitzem una taula amb les dades originals, on s'indica el tipus d'interès del préstec (12%), import del préstec (500.000 rubles) i el termini del préstec (24 mesos). En aquest cas, el pagament es fa mensualment al final de cada període.
- Seleccioneu l'element al full en què es mostrarà el resultat del càlcul i feu clic a la icona "Insereix la funció"situat a prop de la barra de fórmules.
- S'inicia la finestra. Màsters de funcions. A la categoria "Financer" seleccioneu el nom "PLT" i feu clic al botó "D'acord".
- Després d'això, s'obrirà la finestra d'argument d'operador. PMT.
Al camp "Aposta" ha d’entregar l’import dels interessos per al període. Això es pot fer manualment simplement posant un percentatge, però aquí es mostra en una cel·la separada del full, de manera que li donem un enllaç. Situeu el cursor al camp i feu clic a la cel·la corresponent. Però, com recordem, tenim un tipus d’interès anual a la taula i el període de pagament és igual a un mes. Per tant, dividim la taxa anual, o més aviat la referència a la cel·la en què està continguda, pel nombre 12corresponent al nombre de mesos d’un any. La divisió es realitza directament al camp de la finestra d’arguments.
Al camp "Kper" estableix el període de préstec. Ell és igual a nosaltres 24 mesos. Podeu introduir un número al camp 24 manualment, però, com en el cas anterior, proporcionem un enllaç a la ubicació d'aquest indicador a la taula original.
Al camp "Ps" indiqui el valor inicial del préstec. Ella és igual a 500.000 rubles. Com en els casos anteriors, indiquem la referència a l'element del full que conté aquest indicador.
Al camp "B" indica l’import del préstec, després del pagament complet. Com recordem, aquest valor és gairebé sempre zero. Establiu el número en aquest camp "0". Tot i que aquest argument es pot ometre del tot.
Al camp "Tipus" indiqui al començament o al final del mes el pagament es fa. Nosaltres, com en la majoria dels casos, es fa al final del mes. Per tant, configureu el número "0". Igual que en el cas de l’argument anterior, en aquest camp no es pot introduir res, llavors el programa assumirà per defecte que hi ha un valor igual a zero.
Un cop introduïdes totes les dades, feu clic al botó "D'acord".
- Després d'això, el resultat del càlcul es mostra a la cel·la que hem seleccionat al primer paràgraf d'aquest manual. Com podeu veure, el valor del pagament total mensual del préstec és 23536,74 rubles. No us confongueu el signe "-" davant d’aquesta quantitat. Així, Excel assenyala que es tracta d'un flux de caixa, és a dir, de pèrdues.
- Per calcular l’import total del pagament durant tot el període de préstec, tenint en compte l’amortització del cos del préstec i l’interès mensual, n'hi ha prou amb multiplicar l’import del pagament mensual (23536,74 rubles) per al nombre de mesos (24 mesos). Com podeu veure, l’import total dels pagaments durant tot el període de préstec en el nostre cas ha estat de 564881.67 rubles.
- Ara podeu calcular l’import del pagament en excés del préstec. Per això, cal retirar l'import total dels pagaments del préstec, inclosos els interessos i el cos del préstec, l'import inicial prestat. Però recordem que el primer d'aquests valors ja és signe "-". Per tant, en el nostre cas particular, resulta que s'han de plegar. Com podeu veure, l’import total del pagament excessiu del préstec durant tot el període ha estat de 64881,67 rubles.
Lliçó: Auxiliar de funcions d'Excel
Etapa 2: Detalls del pagament
I ara, amb l'ajuda d'altres operadors, Excel farà detalls de pagament mensuals per veure quant paguem el cos del préstec en un mes determinat i quant d'interessos equival. A aquests efectes, estem dibuixant en Excel una taula que omplirem de dades. Les files d’aquesta taula es corresponen amb el període corresponent, és a dir, el mes. Atès que el període d’acreditació és 24 mes, llavors el nombre de files també serà apropiat. Les columnes indiquen el pagament del cos del préstec, el pagament dels interessos, el pagament mensual total, que és la suma de les dues columnes anteriors, així com la quantitat restant a pagar.
- Per determinar l’import del pagament del cos del préstec, utilitzeu la funció OSPLTque només està destinat a aquest propòsit. Establiu el cursor a la cel·la, que es troba a la línia "1" i a la columna "Pagament del cos del préstec". Premeu el botó "Insereix la funció".
- Aneu a Auxiliar de funcions. A la categoria "Financer" marca el nom OSPLT i premeu el botó "D'acord".
- Es llança la finestra d’arguments de l’operador OSPLT. Té la següent sintaxi:
= OSPLT (Taxa; Període; Kper; Ps; Bs)Com podeu veure, els arguments d’aquesta funció coincideixen gairebé totalment amb els arguments de l’operador PMT, només en lloc de l’argument opcional "Tipus" S'ha afegit l’argument necessari "Període". Indica el nombre del període d'amortització, i en el nostre cas particular, el nombre del mes.
Empleneu els camps de la finestra de funció familiar OSPLT les mateixes dades que s’utilitzaven per a la funció PMT. Només tenint en compte el fet que en el futur s’utilitzarà la còpia de la fórmula a través del marcador d’ompliment, és necessari que tots els enllaços dels camps siguin absoluts perquè no canviïn. Per fer-ho, heu de posar un signe de dòlar davant de cada valor de les coordenades verticalment i horitzontalment. Però és més fàcil fer-ho simplement seleccionant les coordenades i prement la tecla de funció. F4. El signe del dòlar es col·locarà automàticament en els llocs adequats. A més, no oblideu que la taxa anual s'hauria de dividir en 12.
- Però ens queda un altre argument nou que la funció no tenia PMT. Aquest argument "Període". Al camp corresponent establim l’enllaç a la primera cel·la de la columna. "Període". Aquest element del full conté un número "1"que indica el nombre del primer mes d’acreditació. Però, a diferència dels camps anteriors, en el camp especificat deixem l’enllaç relatiu i no en fem referència absoluta.
Després d’introduir totes les dades que hem parlat anteriorment, feu clic al botó "D'acord".
- Després d'això, a la cel·la que hàgim assignat prèviament, es mostrarà l'import del pagament del cos del préstec per al primer mes. Ella ho farà 18536,74 rubles.
- Després, com es va esmentar anteriorment, hauríem de copiar aquesta fórmula a les cel·les restants de la columna mitjançant el marcador de farciment. Per fer-ho, establiu el cursor a la cantonada inferior dreta de la cel·la, que conté la fórmula. El cursor es converteix llavors en una creu, que s'anomena marcador de farciment. Manteniu premut el botó esquerre del ratolí i arrossegueu-lo fins al final de la taula.
- Com a resultat, s'omplen totes les cel·les de la columna. Ara tenim un calendari mensual d’amortització de préstecs. Com es va esmentar anteriorment, l’import del pagament d’aquest article augmenta amb cada nou període.
- Ara hem de fer un càlcul mensual dels pagaments d'interessos. A aquests efectes, utilitzarem l’operador PRPLT. Seleccioneu la primera cel·la buida de la columna. "Pagament d'interessos". Premeu el botó "Insereix la funció".
- A la finestra d’inici Màsters de funcions a la categoria "Financer" feu la selecció del nom PRPLT. Feu un clic al botó. "D'acord".
- S'inicia la finestra de arguments de funció. PRPLT. La seva sintaxi és la següent:
= PRPLT (Taxa; Període; Kper; Ps; Bs)Com podeu veure, els arguments d’aquesta funció són absolutament idèntics als de l’operador OSPLT. Per tant, simplement introduïm a la finestra les mateixes dades que vam introduir a la finestra d’argument anterior. No us oblideu de l’enllaç al camp "Període" ha de ser relatiu i, en tots els altres camps, les coordenades s'han de reduir a la forma absoluta. Després fes clic al botó "D'acord".
- A continuació, es mostra el resultat del càlcul de l'import del pagament dels interessos del préstec per al primer mes a la cel·la adequada.
- Després d’aplicar el marcador d’ompliment, fem una còpia de la fórmula als elements restants de la columna, obtenint així un calendari de pagaments mensuals per a interessos del préstec. Com podeu veure, com es va dir anteriorment, el valor d'aquest tipus de pagament disminueix de mes a mes.
- Ara hem de calcular el pagament mensual total. Per a aquest càlcul, no hauríeu de recórrer a cap operador, ja que podeu utilitzar una fórmula aritmètica simple. Plega el contingut de les cel·les del primer mes de les columnes "Pagament del cos del préstec" i "Pagament d'interessos". Per fer-ho, configureu el signe "=" a la primera cel·la de columna buida "Pagament mensual total". A continuació, feu clic als dos elements anteriors, establint entre ells el signe "+". Pressionem la tecla Introduïu.
- A continuació, utilitzeu el marcador de farciment, com en els casos anteriors, ompliu la columna amb dades. Com podeu veure, durant tota la durada del contracte, l’import del pagament mensual total, inclòs el pagament del cos del préstec i el pagament d’interessos, serà 23536,74 rubles. En realitat, ja hem calculat aquesta figura amb l’ajut de PMT. Però en aquest cas, es presenta més clarament, precisament com la quantitat de pagament del cos del préstec i dels interessos.
- Ara heu d’afegir dades a la columna on es mostrarà mensualment el saldo de l’import del préstec que encara s’ha de pagar. A la primera cel·la de la columna "Saldo pagable" El càlcul serà el més fàcil. Hem de retirar el valor inicial del préstec, que es mostra a la taula amb les dades primàries, el pagament del cos del préstec durant el primer mes a la taula de càlcul. Però, atès que un dels números que ja tenim ve amb un signe "-", no haurien de ser llevats, sinó plegats. Feu això i feu clic al botó. Introduïu.
- Però el càlcul del saldo a pagar després del segon mes i dels mesos posteriors serà una mica més difícil. Per fer-ho, hem de restar l'import total dels pagaments del cos del préstec per al període anterior del cos del préstec al començament del préstec. Establiu el signe "=" a la segona cel·la de la columna "Saldo pagable". A continuació, especifiqueu l’enllaç a la cel·la que conté l’import del préstec inicial. La fem absoluta seleccionant i prement la tecla. F4. A continuació, poseu un signe "+", ja que el segon valor serà negatiu per a nosaltres. Després fes clic al botó "Insereix la funció".
- Comença Auxiliar de funcionsen què heu de passar a la categoria "Matemàtica". Allà seleccionem la inscripció "SUMM" i feu clic al botó "D'acord".
- S'inicia la finestra d'argument de la funció. SUMA. L’operador especificat serveix per resumir les dades de les cel·les que necessitem realitzar a la columna "Pagament del cos del préstec". Té la següent sintaxi:
= SUMA (nombre1; número2; ...)Els arguments són referències a cel·les que contenen números. Posem el cursor al camp. "Nombre1". A continuació, mantenim premut el botó esquerre del ratolí i seleccioneu les dues primeres cel·les de la columna al full. "Pagament del cos del préstec". Com podeu veure, l’enllaç al rang es mostra al camp. Consta de dues parts separades per dos punts: referències a la primera cel·la de l’interval i l’últim. Per poder copiar la fórmula indicada en el futur mitjançant un marcador d’ompliment, fem la primera part de la referència a l’abast absolut. Seleccioneu-lo i feu clic a la tecla de funció. F4. La segona part de l’enllaç queda relativa. Ara, quan utilitzeu el marcador d’ompliment, es fixarà la primera cel·la de l’interval i l’últim s’estendrà quan es mou cap avall. Això és el que necessitem per complir els nostres objectius. A continuació, feu clic al botó "D'acord".
- Per tant, el resultat del saldo de deute creditici després del segon mes es mostra a la cel·la. Ara, començant per aquesta cel·la, copiem la fórmula als elements buits de la columna mitjançant el marcador d’ompliment.
- El càlcul mensual dels saldos dels préstecs s'ha realitzat durant tot el període de crèdit. Com hauria de ser, al final del termini, aquesta quantitat és zero.
Per tant, no només vam fer el càlcul del pagament del préstec, sinó que vam organitzar una mena de calculadora de préstecs. Que operarà sota el règim de rendes anuals. Si a la taula de fonts, per exemple, canviem la mida del préstec i el tipus d'interès anual, a la taula final es recalcularan automàticament les dades. Per tant, es pot utilitzar no només una vegada per a un cas específic, sinó que es pot utilitzar en diverses situacions per calcular les opcions de préstec mitjançant el règim de rendes.
Lliçó: Funcions financeres a Excel
Com podeu veure, mitjançant l’ús d’Excel a casa, podeu calcular fàcilment el pagament del préstec mensual total mitjançant l’esquema de rendes, mitjançant l’operador PMT. A més, amb l'ajuda de funcions OSPLT i PRPLT Podeu calcular l’import dels pagaments del cos del préstec i dels interessos per al període especificat. Mitjançant l’aplicació de tots aquests equipatges de funcions junts, és possible crear una calculadora de préstec poderosa que es pugui utilitzar més d'una vegada per calcular el pagament de la renda anual.
