Crea un piano di ammortamento del prestito in Excel – Una guida passo-passo

Creare un piano di ammortamento del prestito in Excel può essere una competenza preziosa, che ti permette di visualizzare e gestire efficacemente i tuoi pagamenti di rimborso. Un piano di ammortamento è una tabella che dettaglia ogni pagamento periodico su un prestito in ammortamento (tipicamente un mutuo o un prestito auto). Suddivide ciascun pagamento in componenti di interesse e capitale, mostrando il saldo residuo dopo ogni pagamento. Immergiamoci in una guida passo-passo per creare un tale piano in Excel.

Cos'è un piano di ammortamento?

Crea un piano di ammortamento in Excel

Crea un piano di ammortamento per un numero variabile di periodi

Crea un piano di ammortamento con pagamenti extra

Crea un piano di ammortamento (con pagamenti extra) utilizzando un modello Excel

Cos'è un piano di ammortamento?

Un piano di ammortamento è una tabella dettagliata utilizzata nei calcoli dei prestiti che mostra il processo di estinzione di un prestito nel tempo. I piani di ammortamento sono comunemente usati per prestiti a tasso fisso come mutui, prestiti auto e prestiti personali, dove l'importo della rata rimane costante per tutta la durata del prestito, ma la proporzione del pagamento verso interessi rispetto al capitale cambia nel tempo.

Per creare un piano di ammortamento del prestito in Excel, le funzioni integrate PMT, PPMT e IPMT sono fondamentali. Vediamo cosa fa ciascuna funzione:

• Funzione PMT: Questa funzione viene utilizzata per calcolare il pagamento totale per periodo di un prestito basato su pagamenti costanti e un tasso di interesse costante.
• Funzione IPMT: Questa funzione calcola la parte degli interessi di un pagamento per un determinato periodo.
• Funzione PPMT: Questa funzione viene utilizzata per calcolare la parte del capitale di un pagamento per un periodo specifico.

Utilizzando queste funzioni in Excel, puoi creare un piano di ammortamento dettagliato che mostra le componenti di interesse e capitale di ciascun pagamento, insieme al saldo residuo del prestito dopo ogni pagamento.

Crea un piano di ammortamento in Excel

In questa sezione, introdurremo due metodi distinti per creare un piano di ammortamento in Excel. Questi metodi soddisfano diverse preferenze e livelli di abilità degli utenti, garantendo che chiunque, indipendentemente dalla sua competenza con Excel, possa costruire con successo un piano di ammortamento dettagliato e accurato per il proprio prestito.

Le formule offrono una comprensione più profonda dei calcoli sottostanti e forniscono flessibilità per personalizzare il piano secondo requisiti specifici. Questo approccio è ideale per coloro che desiderano avere un'esperienza pratica e una chiara visione di come ogni pagamento viene suddiviso in componenti di capitale e interessi. Ora, suddividiamo il processo di creazione di un piano di ammortamento in Excel passo dopo passo:

⭐️ Passo 1: Imposta le informazioni sul prestito e la tabella di ammortamento

1. Inserisci le relative informazioni sul prestito, come il tasso di interesse annuale, la durata del prestito in anni, il numero di pagamenti all'anno e l'importo del prestito nelle celle come mostrato nello screenshot seguente:
   
2. Quindi, crea una tabella di ammortamento in Excel con le etichette specificate, come Periodo, Pagamento, Interesse, Capitale, Saldo Residuo nelle celle A7:E7.
3. Nella colonna Periodo, inserisci i numeri dei periodi. Per questo esempio, il numero totale di pagamenti è di 24 mesi (2 anni), quindi inserirai i numeri da 1 a 24 nella colonna Periodo. Vedi screenshot:
   
4. Una volta impostata la tabella con le etichette e i numeri dei periodi, puoi procedere ad inserire formule e valori per le colonne Pagamento, Interesse, Capitale e Saldo in base alle specifiche del tuo prestito.

⭐️ Passo 2: Calcola l'importo totale del pagamento utilizzando la funzione PMT

La sintassi della funzione PMT è:

Inserisci la seguente formula nella cella B7, e poi trascina la maniglia di riempimento verso il basso per riempire questa formula in altre celle, e vedrai un importo di pagamento costante per tutti i periodi. Vedi screenshot:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Passo 3: Calcola gli interessi utilizzando la funzione IPMT

In questo passaggio, calcolerai gli interessi per ciascun periodo di pagamento utilizzando la funzione IPMT di Excel.

Inserisci la seguente formula nella cella C7, e poi trascina la maniglia di riempimento lungo la colonna per riempire questa formula e ottenere gli interessi per ciascun periodo.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Passo 4: Calcola il capitale utilizzando la funzione PPMT

Dopo aver calcolato gli interessi per ciascun periodo, il prossimo passo nella creazione di un piano di ammortamento è calcolare la parte del capitale di ciascun pagamento. Questo viene fatto utilizzando la funzione PPMT, che è progettata per determinare la parte del capitale di un pagamento per un periodo specifico, basandosi su pagamenti costanti e un tasso di interesse costante.

La sintassi della funzione IPMT è:

La sintassi e i parametri per la formula PPMT sono identici a quelli utilizzati nella formula IPMT precedentemente discussa.

Inserisci la seguente formula nella cella D7, e poi trascina la maniglia di riempimento lungo la colonna per inserire il capitale per ciascun periodo. Vedi screenshot:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Passo 5: Calcola il saldo residuo

Dopo aver calcolato sia gli interessi che il capitale di ciascun pagamento, il prossimo passo nel tuo piano di ammortamento è calcolare il saldo residuo del prestito dopo ciascun pagamento. Questa è una parte cruciale del piano poiché mostra come il saldo del prestito diminuisce nel tempo.

1. Nella prima cella della tua colonna saldo – E7, inserisci la seguente formula, che significa che il saldo residuo sarà l'importo originale del prestito meno la parte del capitale del primo pagamento:

   =B4-D7

   
2. Per il secondo e tutti i periodi successivi, calcola il saldo residuo sottraendo il pagamento del capitale del periodo corrente dal saldo del periodo precedente. Applica la seguente formula nella cella E8:

   =E7-D8

    Nota: Il riferimento alla cella del saldo dovrebbe essere relativo, in modo che si aggiorni mentre trascini la formula verso il basso.
   
3. E poi trascina la maniglia di riempimento verso il basso nella colonna. Come puoi vedere, ogni cella si adatterà automaticamente per calcolare il saldo residuo in base ai pagamenti del capitale aggiornati.
   

⭐️ Passo 6: Crea un riepilogo del prestito

Dopo aver impostato il tuo piano di ammortamento dettagliato, creare un riepilogo del prestito può fornire una panoramica rapida degli aspetti chiave del tuo prestito. Questo riepilogo includerà generalmente il costo totale del prestito, gli interessi totali pagati.

● Per calcolare i pagamenti totali:

=SUM(B7:B30)

● Per calcolare gli interessi totali:

=SUM(C7:C30)

⭐️ Risultato:

Ora, un semplice ma completo piano di ammortamento del prestito è stato creato con successo. Vedi screenshot:

Sblocca la Magia di Excel con Kutools AI

• Esecuzione Intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici — tutto guidato da semplici comandi.
• Formule Personalizzate: Genera formule su misura per ottimizzare i tuoi flussi di lavoro.
• Codifica VBA: Scrivi e implementa codice VBA senza sforzo.
• Interpretazione delle Formule: Comprendi facilmente formule complesse.
• Traduzione del Testo: Superare le barriere linguistiche all'interno dei tuoi fogli di calcolo.

Potenzia le tue capacità di Excel con strumenti alimentati dall'IA. Scarica Ora ed esperisci un'efficienza mai vista prima!

Crea un piano di ammortamento per un numero variabile di periodi

Nell'esempio precedente, abbiamo creato un piano di rimborso del prestito per un numero fisso di pagamenti. Questo approccio è perfetto per gestire un prestito o mutuo specifico dove i termini non cambiano.

Ma, se stai cercando di creare un piano di ammortamento flessibile che possa essere utilizzato ripetutamente per prestiti con periodi variabili, permettendoti di modificare il numero di pagamenti secondo necessità per diversi scenari di prestito, dovrai seguire un metodo più dettagliato.

⭐️ Passo 1: Imposta le informazioni sul prestito e la tabella di ammortamento

1. Inserisci le relative informazioni sul prestito, come il tasso di interesse annuale, la durata del prestito in anni, il numero di pagamenti all'anno e l'importo del prestito nelle celle come mostrato nello screenshot seguente:
   
2. Quindi, crea una tabella di ammortamento in Excel con le etichette specificate, come Periodo, Pagamento, Interesse, Capitale, Saldo Residuo nelle celle A7:E7.
3. Nella colonna Periodo, inserisci il numero massimo di pagamenti che potresti considerare per qualsiasi prestito, ad esempio, riempi i numeri da 1 a 360. Questo può coprire un prestito standard di 30 anni se effettui pagamenti mensili.
   

⭐️ Passo 2: Modifica le formule di pagamento, interesse e capitale con la funzione IF

Inserisci le seguenti formule nelle celle corrispondenti, e poi trascina la maniglia di riempimento per estendere queste formule fino al numero massimo di periodi di pagamento che hai impostato.

● Formula del pagamento:

Normalmente, usi la funzione PMT per calcolare il pagamento. Per incorporare un'istruzione IF, la formula di sintassi è:

Quindi le formule sono queste:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Formula dell'interesse:

La formula di sintassi è:

Quindi le formule sono queste:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Formula del capitale:

La formula di sintassi è:

Quindi le formule sono queste:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Passo 3: Regola la formula del saldo residuo

Per il saldo residuo, di solito sottrarresti il capitale dal saldo precedente. Con un'istruzione IF, modificala come segue:

● La prima cella del saldo:(E7)

=B4-D7

● La seconda cella del saldo:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Passo 4: Crea un riepilogo del prestito

Dopo aver impostato il piano di ammortamento con le formule modificate, il prossimo passo è creare un riepilogo del prestito.

● Per calcolare i pagamenti totali:

=SUM(B7:B366)

● Per calcolare gli interessi totali:

=SUM(C7:C366)

⭐️ Risultato:

Ora, hai un piano di ammortamento completo e dinamico in Excel, completo di un dettagliato riepilogo del prestito. Ogni volta che regoli il termine del periodo di pagamento, l'intero piano di ammortamento si aggiornerà automaticamente per riflettere queste modifiche. Vedi la demo qui sotto:

Crea un piano di ammortamento con pagamenti extra

Effettuando pagamenti aggiuntivi oltre a quelli programmati, un prestito può essere estinto più rapidamente. Un piano di ammortamento che incorpora pagamenti extra, quando creato in Excel, dimostra come questi pagamenti aggiuntivi possono accelerare il rimborso del prestito e ridurre gli interessi totali pagati. Ecco come puoi impostarlo:

⭐️ Passo 1: Imposta le informazioni sul prestito e la tabella di ammortamento

1. Inserisci le relative informazioni sul prestito, come il tasso di interesse annuale, la durata del prestito in anni, il numero di pagamenti all'anno, l'importo del prestito e il pagamento extra nelle celle come mostrato nello screenshot seguente:
   
2. Quindi, calcola il pagamento programmato.
   Oltre alle celle di input, è necessaria un'altra cella predefinita per i nostri calcoli successivi - l'importo del pagamento programmato. Questo è l'importo regolare del pagamento del prestito supponendo che non vengano effettuati pagamenti aggiuntivi. Applica la seguente formula nella cella B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Quindi, crea una tabella di ammortamento in Excel:
   • Imposta le etichette specificate, come Periodo, Pagamento Programmato, Pagamento Extra, Pagamento Totale, Interesse, Capitale, Saldo Residuo nelle celle A8:G8;
   • Nella colonna Periodo, inserisci il numero massimo di pagamenti che potresti considerare per qualsiasi prestito. Ad esempio, riempi i numeri da 0 a 360. Questo può coprire un prestito standard di 30 anni se effettui pagamenti mensili;
   • Per il Periodo 0 (riga 9 nel nostro caso), recupera il valore del saldo con questa formula =B4, che corrisponde all'importo iniziale del prestito. Tutte le altre celle in questa riga dovrebbero essere lasciate vuote.
   • 

⭐️ Passo 2: Crea le formule per il piano di ammortamento con pagamenti extra

Inserisci le seguenti formule nelle celle corrispondenti una per una. Per migliorare la gestione degli errori, racchiudiamo questa e tutte le formule future all'interno della funzione SE.ERRORE. Questo approccio aiuta ad evitare potenziali errori multipli che potrebbero sorgere se alcune delle celle di input sono lasciate vuote o contengono valori errati.

● Calcola il pagamento programmato:

Inserisci la seguente formula nella cella B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Calcola il pagamento extra:

Inserisci la seguente formula nella cella C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Calcola il pagamento totale:

Inserisci la seguente formula nella cella D10:

=IFERROR(B10+C10, "")

● Calcola il capitale:

Inserisci la seguente formula nella cella E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Calcola l'interesse:

Inserisci la seguente formula nella cella F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Calcola il saldo residuo

Inserisci la seguente formula nella cella G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Una volta completate tutte le formule, seleziona l'intervallo di celle B10:G10, e usa la maniglia di riempimento per trascinare ed estendere queste formule attraverso l'intero set di periodi di pagamento. Per qualsiasi periodo non utilizzato, le celle mostreranno 0. Vedi screenshot:

⭐️ Passo 3: Crea un riepilogo del prestito

● Ottieni il numero di pagamenti programmato:

=B2:B3

● Ottieni il numero di pagamenti effettivo:

=COUNTIF(D10:D369,">"&0)

● Ottieni i pagamenti extra totali:

=SUM(C10:C369)

● Ottieni gli interessi totali:

=SUM(F10:F369)

⭐️ Risultato:

Seguendo questi passaggi, crei un piano di ammortamento dinamico in Excel che tiene conto dei pagamenti extra.

Crea un piano di ammortamento utilizzando un modello Excel

Creare un piano di ammortamento in Excel utilizzando un modello è un metodo semplice ed efficiente in termini di tempo. Excel fornisce modelli integrati che calcolano automaticamente l'interesse, il capitale e il saldo residuo di ciascun pagamento. Ecco come creare un piano di ammortamento utilizzando un modello Excel:

1. Clicca File > Nuovo, nella casella di ricerca, digita piano di ammortamento, e premi Invio tasto. Poi, seleziona il modello che meglio si adatta alle tue esigenze cliccandoci sopra. Ad esempio, qui, selezionerò il modello Simple loan calculator. Vedi screenshot:
   
2. Una volta selezionato un modello, clicca sul pulsante Crea per aprirlo come una nuova cartella di lavoro.
3. Poi, inserisci i dettagli del tuo prestito, il modello dovrebbe calcolare automaticamente e popolare il piano in base ai tuoi input.
4. Infine, salva la tua nuova cartella di lavoro del piano di ammortamento.