Come creare il proprio calcolatore di ammortamento del prestito |

La maggior parte delle persone pensa che esista una sorta di magia voodoo per creare un prestito programma di ammortamento . Ma sono felice di dirti che non c'è - e ancora meglio - anche un principiante può crearne uno facilmente usando Microsoft Excel.

Una tabella di ammortamento del prestito ti dà molte buone informazioni: l'importo totale pagabile al mutuante, la parte che è il pagamento degli interessi, la parte che è il pagamento principale e il saldo del prestito in sospeso.

Quando un prestito viene ammortizzato, significa semplicemente un pagamento di interessi e un pagamento principale sono combinati in un pagamento periodico e gli importi di pagamento sono uguali per ogni periodo di pagamento. Ma una delle caratteristiche più interessanti di un prestito ammortizzato è che con ogni pagamento viene modificato l'importo degli interessi e del capitale.

Excel è uno strumento estremamente utile per calcolare la tua tabella di ammortamento. Puoi anche sperimentare con i diversi input per vedere come i diversi tassi di interesse, i periodi di rimborso e gli importi del prestito principale influenzano i pagamenti del prestito che farai.

Diamo un'occhiata a un esempio per illustrare il processo.

Supponiamo che tu abbia appena acquistato un'auto con un prestito di $ 20.000 che deve essere completamente rimborsato in 4 anni. Il tasso di interesse annuo sul prestito è del 3%. Poiché il prestito è ammortizzato, i pagamenti mensili saranno tutti uguali.

Il primo passo è calcolare l'importo mensile del pagamento. Questo può essere fatto usando la funzione PMT (che sta per "pagamento") in Excel.

Inizia facendo un nuovo foglio di calcolo e inserendo i seguenti dati necessari per il calcolo dell'importo del pagamento.

La colonna A conterrà le seguenti etichette per i nostri dati:

A1 = il capitale originale del prestito (importo del prestito)

A2 = la durata del prestito negli anni

A3 = il tasso di interesse annuale

A4 = il numero di pagamenti per anno

A5 = l'importo del pagamento (cosa stiamo calcolando)

La colonna B contenere i valori corrispondenti per ogni etichetta nella colonna A:

B1 = $ 20.000

B2 = 4

B3 = 3%

B4 = 12

B5 = il valore che stiamo cercando

La funzione PMT di Excel è disponibile nel menu delle funzioni finanziarie. Seleziona prima la cella B5, quindi fai clic sul menu delle funzioni finanziarie nella scheda Formule e seleziona la funzione PMT dall'elenco. Verrà visualizzata una casella in cui inserire i dati dal foglio di calcolo proprio come in questa immagine:

Ci sono alcune cose importanti da notare qui. Innanzitutto, dobbiamo effettuare due importanti aggiustamenti, perché stiamo calcolando pagamenti mensili. Dobbiamo dividere il nostro tasso annuo di 12 per ottenere il tasso di interesse mensile (Tasso), e dobbiamo moltiplicare 4 per 12 per ottenere il numero totale di mesi nel nostro periodo di rimborso (Nper).

In secondo luogo, il valore attuale (PV) viene inserito come valore negativo perché tale importo è dovuto al prestatore.

Una volta riempite le caselle, premere "OK".

Il tuo foglio di calcolo dovrebbe ora apparire come segue:

Ora inizieremo a costruire la tabella di ammortamento.

Salta una riga sotto il foglio di calcolo fatto nella parte 1 e, a partire dalla riga 7, effettua quanto segue etichette che iniziano dalla colonna A e finiscono nella colonna E:

A7 = Mese

B7 = Pagamento

C7 = Interesse

D7 = Principale

E7 = Bilancio

Queste sono le cinque voci per la tabella di ammortamento.

Ora è il momento di iniziare a riempire la tabella. Il primo passo è inserire il numero di mesi nella vita del prestito. Sotto l'intestazione "mese", digita "0" nella cella A8 (il momento in cui è stato emesso il prestito). Compila le celle successive in ordine sequenziale tramite il numero 48 (che ti metterà nella cella A56). Un trucco: una volta inserito 0, assicurati che la cella sia selezionata (A8), quindi trascina l'angolo in basso a destra della cella fino alla linea 56. Quindi seleziona "riempimento serie" dalla piccola casella che appare a destra di l'ultima cella. Questo dovrebbe riempire automaticamente le serie numeriche per i 48 mesi.

Successivamente, è possibile compilare tutti i pagamenti perché il valore di ciascun pagamento mensile è già stato determinato. A partire dal mese 1 (non c'è pagamento al mese 0 perché i pagamenti avvengono alla fine di ogni mese) compila il valore $ 442,69 per ogni mese 1-48. Successivamente, è possibile calcolare gli interessi e gli importi principali utilizzando le rispettive funzioni di Excel.

Il calcolo dell'interesse e delle componenti principali dei pagamenti mediante la funzione Excel è semplice e può essere utilizzato per compilare l'intera tabella di ammortamento. Per calcolare il pagamento degli interessi, selezionare la cella C9 e quindi fare clic sul menu delle funzioni finanziarie nella scheda delle formule. Seleziona la funzione IPMT (per "pagamento degli interessi") dall'elenco. Verrà visualizzata una casella in cui inserire i dati dal foglio di calcolo proprio come in questa immagine:

Nota: I segni "$" tra le lettere e i numeri delle celle sono necessari per la copia le formule fino alle celle rimanenti in seguito. Il "$" blocca la cella indicata, quindi le formule rimangono intatte quando le copiamo nelle celle rimanenti. Puoi bloccare le celle (e vedere "$") premendo F4.

Avanti possiamo calcolare l'importo del capitale nel pagamento. Analogamente al calcolo IPMT, selezionare la cella D9, quindi fare clic sul menu delle funzioni finanziarie nella scheda Formule. Seleziona la funzione PPMT (per "pagamento principale") dalla lista: verrà visualizzata una casella in cui inserire i dati dal foglio di calcolo proprio come in questa immagine:

Nota: Il principal potrebbe può essere calcolato utilizzando questa funzione o semplicemente sottraendo l'importo dell'interesse dall'ammontare totale del pagamento.Allo stesso modo, l'interesse potrebbe essere calcolato se abbiamo calcolato il capitale prima utilizzando la funzione PPMT e poi sottratto tale valore dal pagamento totale. per sapere come utilizzare entrambe le formule, che consente anche di compilare il resto della tabella in modo più efficiente come vedrete di seguito.

La colonna finale (colonna E) è la colonna del saldo rimanente, che può essere facilmente calcolata per ogni periodo ora che abbiamo determinato gli importi degli interessi e del capitale.Per il mese 0, non sono ancora stati effettuati pagamenti, quindi il saldo è di $ 20.000. Tuttavia, vogliamo che Excel calcoli automaticamente il saldo in ciascun periodo, quindi invece di manu inserendo "$ 20.000", inserire "= B1", facendo riferimento alla tabella sopra per il calcolo del pagamento. Per il mese 1, poiché il saldo viene calcolato sottraendo il saldo del mese precedente dal pagamento principale del mese corrente, immettere "= E8-D9" nella cella E9. Il saldo verrà calcolato automaticamente. La tabella di ammortamento ora è completa per il mese 1 e dovrebbe avere esattamente questo aspetto:

Tutti i dati per i restanti mesi possono essere compilati semplicemente selezionando le celle B9-E9, copiando la selezione e quindi incollando la selezione in celle B10-E10. Poiché abbiamo inserito le formule per ogni cella e bloccato le nostre celle che verranno utilizzate in ogni calcolo, i valori per interesse, capitale e saldo vengono calcolati automaticamente. Vedendo che funziona per il mese 2, l'intera tabella può essere completata semplicemente selezionando le colonne B-E fino al mese 48 e incollando le informazioni già copiate. Excel ora ha calcolato ogni valore per la tabella di ammortamento usando le formule che abbiamo inserito. La tabella completata dovrebbe essere simile a questa:

Primo anno di prestito:

Ultimo anno di prestito:

Nota: Queste tabelle contengono solo il primo e l'ultimo anno del tabella di ammortamento. Gli anni 2 e 3 si collocano tra queste due tabelle formando una tabella di ammortamento continua per tutti i 48 mesi.

Se sei interessato a calcolare le rate del mutuo per un mutuo a tasso fisso, puoi fare da te una tabella di ammortamento usando questi passaggi, oppure puoi usare il nostro calcolatore finanziario per farlo per te. Il nostro calcolatore ipotecario ti mostrerà il tuo pagamento mensile e un programma completo di ammortamento.