Il tutorial mostra come costruire un piano di ammortamento in Excel per dettagliare i pagamenti periodici su un prestito di ammortamento o ipoteca.
Un prestito ammortizzante è solo un modo elegante per definire un prestito che viene rimborsato a rate per tutta la durata del prestito.
Fondamentalmente, tutti i prestiti sono ammortizzati in un modo o nell’altro. Ad esempio, un prestito completamente ammortizzante per 24 mesi avrà 24 pagamenti mensili uguali., Ogni pagamento applica un certo importo verso il capitale e un po ‘ verso gli interessi. Per dettagliare ogni pagamento su un prestito, è possibile costruire un programma di ammortamento del prestito.
Un piano di ammortamento è una tabella che elenca i pagamenti periodici su un prestito o un mutuo nel tempo, suddivide ogni pagamento in capitale e interessi e mostra il saldo residuo dopo ogni pagamento.,
- Come creare un prestito, piano di ammortamento in Excel
- piano di Ammortamento per un numero variabile di periodi
- Prestito, piano di ammortamento con i pagamenti supplementari
- Excel ammortamento modello
Come creare un prestito, piano di ammortamento in Excel
costruire un prestito o un mutuo, piano di ammortamento in Excel, ci sarà bisogno di utilizzare le seguenti funzioni:
- funzione RATA calcola la quantità totale di un pagamento periodico. Tale importo rimane costante per l’intera durata del prestito.,
- Funzione PPMT-ottiene la parte principale di ogni pagamento che va verso il capitale del prestito, cioè l’importo preso in prestito. Questo importo aumenta per i pagamenti successivi.
- Funzione IPMT-trova la parte di interesse di ogni pagamento che va verso gli interessi. Questo importo diminuisce con ogni pagamento.
Ora, passiamo attraverso il processo passo dopo passo.,
Impostare la tabella di ammortamento
Per cominciare, definire le celle di input, dove potrete inserire le componenti di un prestito:
- C2 – tasso di interesse annuo
- C3 – durata del prestito in anni
- C4 – numero di pagamenti per anno
- C5 – importo del prestito
La prossima cosa da fare è quella di creare una tabella di ammortamento con le etichette (Periodo di Pagamento, gli Interessi, il Principale, di Equilibrio) in A7:E7., Nella colonna Periodo, inserisci una serie di numeri pari al numero totale di pagamenti (1 – 24 in questo esempio):
Con tutti i componenti noti sul posto, arriviamo alla parte più interessante: le formule di ammortamento del prestito.
Calcola l’importo totale del pagamento (formula PMT)
L’importo del pagamento viene calcolato con la funzione PMT(rate, nper, pv,,).
Per gestire correttamente diverse frequenze di pagamento (come settimanale, mensile, trimestrale, ecc.,), dovresti essere coerente con i valori forniti per gli argomenti rate e nper:
- Rate-dividere il tasso di interesse annuale per il numero di periodi di pagamento all’anno (C C 2 2/C C 4 4).
- Nper – moltiplica il numero di anni per il numero di periodi di pagamento all’anno (C C 3 3*C C 4 4).
- Per l’argomento pv, inserire l’importo del prestito (C C 5 5).
- Gli argomenti fv e type possono essere omessi poiché i loro valori predefiniti funzionano bene per noi (il saldo dopo l’ultimo pagamento dovrebbe essere 0; i pagamenti vengono effettuati alla fine di ogni periodo).,
Mettendo insieme gli argomenti di cui sopra, otteniamo questa formula:
=PMT($C$2/$C$4, $C$3*$C$4, $C$5)
Si prega di prestare attenzione, che usiamo riferimenti di cella assoluti perché questa formula dovrebbe copiare nelle celle sottostanti senza alcuna modifica.,
Immettere il PMT formula in B8, trascinare verso il basso la colonna, e si vedrà un costante importo del pagamento per tutti i periodi:
Calcolare l’interesse (INTERESSI formula)
Per trovare l’interesse da parte di ciascun pagamento periodico, utilizzare gli INTERESSI(tasso di, per, numero rate, pv, , ) funzione:
=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
Tutti gli argomenti sono gli stessi, come nel PMT formula, tranne che per un argomento che specifica il periodo di pagamento., Questo argomento viene fornito come riferimento relativo alla cella (A8) perché dovrebbe cambiare in base alla posizione relativa di una riga in cui viene copiata la formula.,
Questa formula va a C8, e poi si copia verso il basso per il numero di celle bisogno di:
Trova principale (PPMT formula)
calcolare la parte principale di ogni pagamento periodico, utilizzare questo PPMT formula:
=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)
La sintassi e gli argomenti sono esattamente gli stessi INTERESSI formula discusso in precedenza:
Questa formula va alla colonna D, a partire da D8:
Ottieni il saldo residuo
Per calcolare il saldo residuo per ogni periodo, utilizzeremo due formule diverse.
Per trovare l’equilibrio dopo il primo pagamento in E8, aggiungere l’importo del prestito (C5) e la direttrice del primo periodo (D8):
=C5+D8
Poiché l’importo del prestito è un numero positivo e principale è un numero negativo, quest’ultimo è, infatti, sottratto dall’ex.,
Per il secondo e tutti i periodi successivi, sommate il saldo precedente e il principale di questo periodo:
=E8+D9
La formula precedente va a E9, quindi la copiate nella colonna. A causa dell’uso di riferimenti di cella relativi, la formula si regola correttamente per ogni riga.
Questo è tutto! Il nostro programma di ammortamento mensile del prestito è fatto:
Suggerimento: Restituisci i pagamenti come numeri positivi
Poiché un prestito viene pagato dal tuo conto bancario, le funzioni Excel restituiscono il pagamento, gli interessi e il capitale come numeri negativi., Per impostazione predefinita, questi valori sono evidenziati in rosso e racchiusi tra parentesi come si può vedere nell’immagine sopra.
Se si preferisce avere tutti i risultati come numeri positivi, inserire un segno meno prima delle funzioni PMT, IPMT e PPMT.
Per l’Equilibrio formule, utilizzare la sottrazione invece di aggiunta come mostrato nella schermata sotto:
piano di Ammortamento per un numero variabile di periodi
Nell’esempio precedente, abbiamo costruito un prestito, piano di ammortamento per un determinato numero di periodi di pagamento., Questa soluzione rapida una tantum funziona bene per un prestito specifico o mutuo.
Se stai cercando di creare un programma di ammortamento riutilizzabile con un numero variabile di periodi, dovrai adottare un approccio più completo descritto di seguito.
Inserisci il numero massimo di periodi
Nella colonna Periodo, inserisci il numero massimo di pagamenti che consentirai per qualsiasi prestito, ad esempio, da 1 a 360. È possibile sfruttare la funzione di riempimento automatico di Excel per inserire una serie di numeri più velocemente.,
Usa le dichiarazioni IF nelle formule di ammortamento
Poiché ora hai molti numeri di periodo eccessivi, devi in qualche modo limitare i calcoli al numero effettivo di pagamenti per un particolare prestito. Questo può essere fatto avvolgendo ogni formula in un’istruzione IF. Il test logico dell’istruzione IF verifica se il numero di periodo nella riga corrente è minore o uguale al numero totale di pagamenti. Se il test logico è TRUE, viene calcolata la funzione corrispondente; se FALSE, viene restituita una stringa vuota.,
Supponendo che il periodo 1 sia nella riga 8, immettere le seguenti formule nelle celle corrispondenti e quindi copiarle nell’intera tabella.,
di Pagamento (B8):
=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")
Interesse (C8):
=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
le Principali (D8):
=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")
Bilanciamento:
Per il Periodo 1 (E8), la formula è la stessa dell’esempio precedente:
=C5+D8
Per un Periodo di 2 (E9) e per tutti i periodi successivi, la formula prende questa forma:
=IF(A9<=$C$3*$C$4, E8+D9, "")
Come risultato, si ha un correttamente calcolato il piano di ammortamento e un mucchio di righe vuote con il periodo di numeri dopo il prestito è pagato.,
Nascondi numeri di periodi extra
Se riesci a vivere con un mucchio di numeri di periodi superflui visualizzati dopo l’ultimo pagamento, puoi considerare il lavoro svolto e saltare questo passaggio. Se cerchi la perfezione, nascondi tutti i periodi inutilizzati creando una regola di formattazione condizionale che imposta il colore del carattere su bianco per tutte le righe dopo l’ultimo pagamento.,
Per questo, seleziona tutte le righe di dati se la tua tabella di ammortamento (A8:E367 nel nostro caso) e fai clic sulla scheda Home >Formattazione condizionale >Nuova regola New > Usa una formula per determinare quali celle formattare.
Nella casella corrispondente, inserire la seguente formula che controlla se il numero di periodo nella colonna A è maggiore del numero totale di pagamenti:
=$A8>$C$3*$C$4
Dopo di che, fare clic sul pulsante Formato… e scegliere il colore del carattere bianco. Fatto!,
Crea un riepilogo del prestito
Per visualizzare a colpo d’occhio le informazioni di riepilogo sul tuo prestito, aggiungi un paio di formule in cima al tuo programma di ammortamento.
Pagamenti totali (F2):
=-SUM(B8:B367)
Interessi totali (F3):
=-SUM(C8:C367)
Se si dispone di pagamenti come numeri positivi, rimuovere il segno meno dalle formule di cui sopra.
Questo è tutto! Il nostro programma di ammortamento del prestito è completato e buono per andare!,
Scarica programma di ammortamento del prestito per Excel
Come fare un programma di ammortamento del prestito con pagamenti extra in Excel
I programmi di ammortamento discussi negli esempi precedenti sono facili da creare e seguire (si spera :). Tuttavia, lasciano fuori una caratteristica utile che molti pagatori di prestito sono interessati a-pagamenti aggiuntivi per pagare un prestito più veloce. In questo esempio, vedremo come creare un programma di ammortamento del prestito con pagamenti extra.
Definire le celle di input
Come al solito, iniziare con l’impostazione delle celle di input., In questo caso, lasciate che il nome di queste cellule, come scritto qui sotto per fare le nostre formule più facile da leggere:
- Tassointeresse – C2 (tasso di interesse annuo)
- LoanTerm – C3 (durata del prestito in anni)
- PaymentsPerYear – C4 (numero di pagamenti per anno)
- LoanAmount – C5 (totale importo del prestito)
- ExtraPayment – C6 (pagamento extra per periodo)
Calcolare un pagamento previsto
a parte le celle di input, uno in più di cella predefiniti è necessario per i nostri ulteriori calcoli – il versamento previsto importo, ossia, l’importo da pagare su un prestito se non vengono effettuati pagamenti extra. Questo importo è calcolato con la seguente formula:
=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")
Si prega di prestare attenzione che abbiamo messo un segno meno prima della funzione PMT per avere il risultato come un numero positivo. Per evitare errori nel caso in cui alcune delle celle di input siano vuote, racchiudiamo la formula PMT all’interno della funzione IFERROR.
Inserisci questa formula in alcune celle (G2 nel nostro caso) e assegna un nome alla cella ScheduledPayment.,
Configura la tabella di ammortamento
Crea una tabella di ammortamento del prestito con le intestazioni mostrate nello screenshot qui sotto. Nella colonna Periodo immettere una serie di numeri che iniziano con zero (è possibile nascondere la riga Periodo 0 in seguito, se necessario).
Se si desidera creare un piano di ammortamento riutilizzabile, immettere il numero massimo possibile di termini di pagamento (da 0 a 360 in questo esempio).
Per il periodo 0 (riga 9 nel nostro caso), tirare il valore del saldo, che è uguale all’importo del prestito originale., Tutte le altre celle di questa riga rimarranno vuote:
Formula in G9:
=LoanAmount
4. Costruire formule per piano di ammortamento con pagamenti extra
Questa è una parte fondamentale del nostro lavoro. Poiché le funzioni integrate di Excel non prevedono pagamenti aggiuntivi, dovremo fare tutti i calcoli da soli.
Immettere le seguenti formule nella riga 10 (Periodo 1), quindi copiarle per tutti i periodi rimanenti.
Pagamento pianificato (B10):
Se l’importo del pagamento pianificato (denominato cella G2) è inferiore o uguale al saldo rimanente (G9), utilizzare il pagamento pianificato. In caso contrario, aggiungere il saldo residuo e gli interessi per il mese precedente.
=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")
Come ulteriore precauzione, avvolgiamo questa e tutte le formule successive nella funzione IFERROR., Ciò impedirà una serie di vari errori se alcune delle celle di input sono vuote o contengono valori non validi.
Pagamento extra (C10):
Utilizzare una formula IF con la seguente logica:
Se l’importo del pagamento extra (denominato cella C6) è inferiore alla differenza tra il saldo rimanente e il capitale di questo periodo (G9-E10), restituire il pagamento extra; altrimenti utilizzare la differenza.
=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")
Pagamento Totale (D10)
Semplicemente aggiungere il versamento previsto (B10) e il pagamento (C10) per il periodo corrente:
=IFERROR(B10+C10, "")
le Principali (E10)
Se il programma di pagamento per un determinato periodo di tempo è maggiore di zero, il ritorno di un minore dei due valori: pagamento previsto meno interesse (B10-F10) o il saldo (G9); in caso contrario, restituisce zero.
=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")
Si prega di notare che il principale include solo la parte del pagamento programmato (non il pagamento extra!) che va verso il capitale di prestito.,
Interesse (F10)
Se il pagamento programmato per un determinato periodo è maggiore di zero, dividere il tasso di interesse annuale (denominato cella C2) per il numero di pagamenti all’anno (denominato cella C4) e moltiplicare il risultato per il saldo rimanente dopo il periodo precedente; altrimenti, restituire 0.
=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")
Saldo (G10)
Se il saldo (G9) è maggiore di zero, sottrarre la parte principale del pagamento (E10) e il pagamento (C10) dal saldo residuo dopo il periodo precedente (G9); altrimenti restituisce 0.,
=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")
Se tutto fatto correttamente, il prestito, piano di ammortamento, a questo punto, dovrebbe essere qualcosa di simile a questo:
Nascondi periodi aggiuntivi
Impostare una regola di formattazione condizionale per nascondere i valori in periodi inutilizzati, come spiegato in questo suggerimento.,pagamenti all’anno:
=LoanTerm*PaymentsPerYear
numero Effettivo dei pagamenti:
Contare le cellule nel Pagamento Totale della colonna che sono maggiori di zero, inizio con un Periodo di 1:
=COUNTIF(D10:D369,">"&0)
Totale pagamenti extra:
Aggiungi su cellule in Pagamento a colonna, inizio con un Periodo di 1:
=SUM(C10:C369)
Totale interessi:
Aggiungere le celle nella colonna di Interesse, inizio con un Periodo di 1:
=SUM(F10:F369)
Opzionalmente, nascondere il Periodo 0 riga, e il prestito, piano di ammortamento con i pagamenti supplementari è fatto!, Lo screenshot qui sotto mostra il risultato finale:
Scarica programma di ammortamento del prestito con pagamenti extra
Piano di ammortamento Modello Excel
Per fare un programma di ammortamento del prestito di prim’ordine in pochissimo tempo, fare uso di modelli incorporati di Excel. Basta andare su File > Nuovo, digitare “piano di ammortamento” nella casella di ricerca e scegliere il modello che ti piace, ad esempio, questo con pagamenti extra:
È così che si crea un programma di ammortamento del prestito o del mutuo in Excel., Vi ringrazio per la lettura e spero di vedervi sul nostro blog la prossima settimana!
Download disponibili
Tabella di ammortamento Esempio Cartella di lavoro
- Come calcolare l’interesse composto in Excel
- Come trovare CAGR (compound annual growth rate) in Excel
- Calcolare la percentuale in Excel con esempi di formule