Planifiqueu els reemborsaments del préstec amb fórmules Excel

L’amortització del préstec és l’acte de retornar diners prèviament prestats a un prestador, normalment mitjançant una sèrie de pagaments periòdics que inclouen els interessos principals. Sabíeu que podeu utilitzar el programa de programari Excel per calcular els reemborsaments del vostre préstec?

Aquest article és una guia pas a pas per configurar càlculs de préstecs.

Compres per emportar

• Utilitzeu Excel per obtenir un control de la vostra hipoteca mitjançant la determinació del vostre pagament mensual, el vostre tipus d’interès i la vostra programació de préstecs.
• Podeu aprofundir en el desglossament d’un préstec amb excel i crear un calendari d’amortització que us serveixi.
• Hi ha càlculs disponibles per a cada pas que podeu ajustar per satisfer les vostres necessitats específiques.
• El descompte i l'examen del vostre préstec pas a pas pot fer que el procés de reemborsament se senti menys aclaparador i més manejable.

Comprensió de la seva hipoteca

Amb Excel, podreu conèixer millor la vostra hipoteca en tres senzills passos. El primer pas determina el pagament mensual. El segon pas calcula el tipus d’interès i el tercer pas determina el calendari de préstecs.

Podeu crear una taula a Excel que us indicarà el tipus d’interès, el càlcul del préstec durant la durada del préstec, la descomposició del préstec, l’amortització i el pagament mensual.

Calculeu el pagament mensual

Primer, aquí es mostra com calcular el pagament mensual d’una hipoteca. Utilitzant el tipus d’interès anual, el principal i la durada, podem determinar l’import que s’ha de retornar mensualment.

La fórmula, tal com es mostra a la captura de pantalla superior, està redactada de la manera següent:

= -PMT (velocitat; longitud; present_valor; [futur_valor]; [tipus])

El signe menys davant de PMT és necessari ja que la fórmula retorna un nombre negatiu. Els primers tres arguments són la taxa del préstec, la durada del préstec (nombre de períodes) i el principal prestat. Els dos últims arguments són opcionals, el valor residual per defecte és zero; També es pot pagar per avançat (per a un) o al final (per zero).

La fórmula Excel usada per calcular el pagament mensual del préstec és:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Explicació: per a la taxa, utilitzem la taxa mensual (període de taxa), després calculem el nombre de períodes (120 durant 10 anys multiplicats per 12 mesos) i, finalment, indiquem el principal prestat. El nostre pagament mensual serà de 1.161, 88 dòlars en deu anys.

Calculeu el tipus d’interès anual

Hem vist com configurar el càlcul d’un pagament mensual d’una hipoteca. Però és possible que vulguem establir un pagament mensual màxim que podem permetre, que també mostra el nombre d’anys durant els quals hauríem de pagar el préstec. Per això, voldríem conèixer el tipus d'interès anual corresponent.

Com es mostra a la captura de pantalla anterior, primer calculem la taxa de període (mensual, en el nostre cas), i després la taxa anual. La fórmula que s’utilitzarà serà RATE, tal com es mostra a la captura de pantalla de dalt. Està redactat de la manera següent:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Els tres primers arguments són la durada del préstec (nombre de períodes), el pagament mensual per reemborsar el préstec i el principal prestat. Els tres últims arguments són opcionals i el valor residual per defecte és zero; També és opcional el terme argument per gestionar el venciment per avançat (per a un) o al final (per a zero). Finalment, l’argument d’estimació és opcional, però pot donar una estimació inicial de la tarifa.

La fórmula Excel usada per calcular la taxa de préstec és:

= TARIFA (12 * B4; -B2; B3) = TARIFA (12 * 13; -960; 120000)

Nota: les dades corresponents en el pagament mensual han de rebre un signe negatiu. Per això hi ha un signe menys abans de la fórmula. El període de taxa és del 0, 294%.

Utilitzem la fórmula = (1 + B5) és 12-1 ^ = (1 + 0, 294%) ^ 12-1 per obtenir la taxa anual del nostre préstec, que és del 3, 58%. És a dir, per demanar un préstec de 120.000 dòlars en més de 13 anys per pagar 960 dòlars mensuals, hauríem de negociar un préstec a una taxa màxima anual del 3, 58%.

L'ús de l'Excel és una manera excel·lent de fer un seguiment del que deveu i de trobar un calendari de reemborsament que minimitzi les tarifes que podríeu acabar deure.

Determinació de la longitud d'un préstec

Ara veurem com determinar la durada d’un préstec quan coneixeu la tarifa anual, el principal prestat i el pagament mensual que s’ha de pagar. És a dir, quant temps necessitarem per tornar una hipoteca de 120.000 dòlars amb una taxa del 3, 10% i un pagament mensual de 1.100 dòlars ">

La fórmula que utilitzarem és NPER, tal com es mostra a la captura de pantalla de dalt, i està redactada de la manera següent:

= NPER (taxa; pmt; present_valor; [futur_valor]; [tipus])

Els primers tres arguments són la taxa anual del préstec, el pagament mensual necessari per reemborsar el préstec i el principal prestat. Els dos últims arguments són opcionals, el valor residual per defecte és zero. El terme argument que es paga per avançat (per a un) o al final (per a zero) també és opcional.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Nota: les dades corresponents en el pagament mensual han de rebre un signe negatiu. És per això que tenim un signe menys abans de la fórmula. La durada del reemborsament és de 127, 97 períodes (mesos en el nostre cas).

Utilitzarem la fórmula = B5 / 12 = 127.97 / 12 durant el nombre d’anys per completar l’amortització del préstec. És a dir, per agafar en préstec 120.000 dòlars, amb una taxa anual del 3, 10% i per pagar 1.100 dòlars mensuals, hauríem de pagar els venciments de 128 mesos o 10 anys i 8 mesos.

Descomposar el préstec

Un pagament de préstec està format per principal i interessos. Els interessos es calculen per a cada període; per exemple, les amortitzacions mensuals de deu anys ens donaran 120 períodes.

El quadre anterior mostra el desglossament d’un préstec (un període total igual a 120) mitjançant les fórmules PPMT i IPMT. Els arguments de les dues fórmules són els mateixos i es desglossen de la manera següent:

= -PPMT (velocitat; num_period; longitud; principal; [residual]; [terme])

Els arguments són els mateixos que per a la fórmula PMT ja vista, tret de "num_period", que s'afegeix per mostrar el període durant el qual es pot desglossar el préstec donat el principal i els interessos. Aquí teniu un exemple:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

El resultat es mostra a la captura de pantalla superior "Descomposició del préstec" durant el període analitzat, que és "un;" és a dir, el primer període o el primer mes. Paguem 1.161, 88 dòlars desglossats en 856, 20 dòlars principals i interessos de 305, 68 dòlars.

Càlcul de préstecs en Excel

També és possible calcular l'amortització del capital i dels interessos durant diversos períodes, com ara els primers 12 mesos o els primers 15 mesos.

= -CUMPRINC (velocitat; longitud; principal; data d'inici; data de finalització; tipus)

Trobem els arguments, velocitat, longitud, principal i terme (que són obligatoris) que ja vam veure a la primera part amb la fórmula PMT. Però aquí, també necessitem els arguments "start_date" i "end_date". La "data d'inici" indica el començament del període que s'ha d'analitzar i la "data final" indica el final del període que s'ha d'analitzar.

Aquí teniu un exemple:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

El resultat es mostra a la captura de pantalla "Cumul 1er any", de manera que els períodes analitzats van des del 12 al primer període (primer mes) al dotzè (12è mes). Durant un any, pagaríem 10.419, 55 dòlars en concepte de capital i 3.522, 99 dòlars en interès.

Amortització del préstec

Les fórmules prèvies ens permeten crear el nostre calendari per període, saber quant pagarem mensualment en principal i interessos, i saber quant queda per pagar.

Creació d'una programació de préstecs

Per crear un calendari de préstecs, utilitzarem les diferents fórmules comentades anteriorment i les ampliarem al nombre de períodes.

A la columna del primer període, introduïu "1" com a primer període i arrossegueu la cel·la cap avall. En el nostre cas, necessitem 120 períodes, ja que un pagament de préstec de deu anys multiplicat per 12 mesos equival a 120.

La segona columna és l’import mensual que hem de pagar cada mes, que és constant durant tota la programació de préstecs. Per calcular la quantitat, inseriu la fórmula següent a la cel·la del nostre primer període:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

La tercera columna és el principal que es pagarà mensualment. Per exemple, per al 40è període, pagarem principalment 945, 51 dòlars en el nostre import total mensual de 1.161, 88 dòlars.

Per calcular l’import principal canviat, utilitzeu la fórmula següent:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

La quarta columna és l’interès, per la qual utilitzem la fórmula per calcular el principal que es reemborsarà a la nostra quantitat mensual per descobrir quants interessos s’han de pagar:

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

La cinquena columna conté l’import que queda per pagar. Per exemple, després del 40è pagament, haurem de pagar 83.994, 69 dòlars en 120.000 dòlars.

La fórmula és la següent:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

La fórmula utilitza una combinació de principal en un termini per davant de la cel·la que conté el principal prestat. Aquest període comença a canviar quan copiem i arrosseguem la cel·la cap avall. La taula següent mostra que al final dels 120 períodes es paga el nostre préstec.