Creare un piano di ammortamento del prestito in Excel può essere una competenza preziosa, che consente di visualizzare e gestire i rimborsi del prestito in modo efficace. Un piano di ammortamento è una tabella che descrive in dettaglio ogni pagamento periodico su un prestito ad ammortamento (tipicamente un mutuo o un prestito auto). Suddivide ogni pagamento in componenti di interesse e capitale, mostrando il saldo rimanente dopo ogni pagamento. Immergiamoci in una guida passo passo per creare tale pianificazione in Excel.

Che cos'è un piano di ammortamento?

Creare un piano di ammortamento in Excel

Creare un piano di ammortamento per un numero variabile di periodi

Creare un piano di ammortamento con pagamenti aggiuntivi

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

Che cos'è un piano di ammortamento?

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

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

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

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

Creare un piano di ammortamento in Excel

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

Le formule offrono una comprensione più approfondita dei calcoli sottostanti e offrono flessibilità per personalizzare la pianificazione in base a requisiti specifici. Questo approccio è ideale per coloro che desiderano avere un'esperienza pratica e una visione chiara del modo in cui ciascun pagamento viene suddiviso in componenti di capitale e interessi. Ora analizziamo passo dopo passo il processo di creazione di un piano di ammortamento in Excel:

⭐️ Passaggio 1: imposta le informazioni sul prestito e la tabella di ammortamento

1. Inserisci le informazioni relative al prestito, come tasso di interesse annuo, durata del prestito in anni, numero di pagamenti annuali e importo del prestito nelle celle come mostrato nella seguente schermata:
   
2. Quindi, crea una tabella di ammortamento in Excel con le etichette specificate, come Periodo, Pagamento, Interessi, Capitale, Saldo rimanente nelle celle A7:E7.
3. Nella colonna Periodo immettere i numeri del periodo. Per questo esempio, il numero totale di pagamenti è 24 mesi (2 anni), quindi inserirai i numeri da 1 a 24 nella colonna Periodo. Vedi schermata:
   
4. Dopo aver impostato la tabella con le etichette e i numeri dei periodi, puoi procedere a inserire formule e valori per le colonne Pagamento, Interesse, Capitale e Saldo in base alle specifiche del tuo prestito.

⭐️ Passaggio 2: calcola l'importo totale del pagamento utilizzando la funzione PMT

La sintassi del PMT è:

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

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

⭐️ Passaggio 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, quindi trascina il quadratino 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)

⭐️ Passaggio 4: calcola il capitale utilizzando la funzione PPMT

Dopo aver calcolato gli interessi per ciascun periodo, il passaggio successivo nella creazione di un piano di ammortamento consiste nel calcolare la quota capitale di ciascun pagamento. Ciò viene fatto utilizzando la funzione PPMT, progettata per determinare la parte principale di un pagamento per un periodo specifico, sulla base di pagamenti costanti e di un tasso di interesse costante.

La sintassi dell'IPMT è:

La sintassi e i parametri della formula PPMT sono identici a quelli utilizzati nella formula IPMT discussa in precedenza.

Inserisci la seguente formula nella cella D7, quindi trascina il quadratino di riempimento lungo la colonna per compilare l'entità per ciascun periodo. Vedi schermata:

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

⭐️ Passaggio 5: calcola il saldo rimanente

Dopo aver calcolato sia l'interesse che il capitale di ogni pagamento, il passo successivo nel piano di ammortamento è calcolare il saldo residuo del prestito dopo ogni pagamento. Questa è una parte cruciale del programma in quanto mostra come il saldo del prestito diminuisce nel tempo.

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

   =B4-D7

   
2. Per il secondo e tutti i periodi successivi, calcolare il saldo rimanente sottraendo il pagamento del capitale del periodo corrente dal saldo del periodo precedente. Si prega di applicare la seguente formula nella cella E8:

   =E7-D8

    Note:: il riferimento alla cella del saldo dovrebbe essere relativo, quindi si aggiorna mentre trascini la formula verso il basso.
   
3. Quindi trascina la maniglia di riempimento verso il basso nella colonna. Come puoi vedere, ogni cella si adatterà automaticamente per calcolare il saldo rimanente in base ai pagamenti principali aggiornati.
   

⭐️ Passaggio 6: fai un riepilogo del prestito

Dopo aver impostato il piano di ammortamento dettagliato, la creazione di un riepilogo del prestito può fornire una rapida panoramica degli aspetti chiave del prestito. Questo riepilogo includerà in genere il costo totale del prestito, l'interesse totale pagato.

● Per calcolare i pagamenti totali:

=SUM(B7:B30)

● Per calcolare l'interesse totale:

=SUM(C7:C30)

⭐️ Risultato:

Ora è stato creato con successo un piano di ammortamento del prestito semplice ma completo. vedi schermata:

Creare un piano di ammortamento per un numero variabile di periodi

Nell'esempio precedente creiamo un piano di rimborso del prestito per un numero fisso di pagamenti. Questo approccio è perfetto per gestire un prestito o un mutuo specifico in cui 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 come richiesto per diversi scenari di prestito, dovrai seguire un metodo più dettagliato.

⭐️ Passaggio 1: imposta le informazioni sul prestito e la tabella di ammortamento

1. Inserisci le informazioni relative al prestito, come tasso di interesse annuo, durata del prestito in anni, numero di pagamenti annuali e importo del prestito nelle celle come mostrato nella seguente schermata:
   
2. Quindi, crea una tabella di ammortamento in Excel con le etichette specificate, come Periodo, Pagamento, Interessi, Capitale, Saldo rimanente nelle celle A7:E7.
3. Nella colonna Periodo, inserisci il numero massimo di pagamenti che potresti prendere in considerazione per qualsiasi prestito, ad esempio, inserisci i numeri compresi tra 1 e 360. Questo può coprire un prestito standard di 30 anni se stai effettuando pagamenti mensili.
   

⭐️ Passaggio 2: modifica le formule di pagamento, interessi e capitale con la funzione IF

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

● Formula di pagamento:

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

Quindi la formula è questa:

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

● Formula degli interessi:

La formula della sintassi è:

Quindi la formula è questa:

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

● Formula principale:

La formula della sintassi è:

Quindi la formula è questa:

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

⭐️ Passaggio 3: modifica la formula del saldo rimanente

Per il saldo rimanente, di solito potresti sottrarre il capitale dal saldo precedente. Con un'istruzione IF, modificala come:

● La prima cella di bilancio: (E7)

=B4-D7

● La seconda cella di equilibrio: (E8)

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

⭐️ Passaggio 4: fai un riepilogo del prestito

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

● Per calcolare i pagamenti totali:

=SUM(B7:B366)

● Per calcolare l'interesse totale:

=SUM(C7:C366)

⭐️ Risultato:

Ora disponi di un piano di ammortamento completo e dinamico in Excel, completo di un riepilogo dettagliato del prestito. Ogni volta che si modifica il termine del periodo di pagamento, l'intero piano di ammortamento verrà aggiornato automaticamente per riflettere tali modifiche. Guarda la demo qui sotto:

Creare un piano di ammortamento con pagamenti aggiuntivi

Effettuando pagamenti aggiuntivi rispetto a quelli previsti è possibile estinguere un prestito più rapidamente. Un piano di ammortamento che incorpora pagamenti aggiuntivi, se creato in Excel, dimostra come questi pagamenti aggiuntivi possano accelerare il rimborso del prestito e ridurre gli interessi totali pagati. Ecco come puoi configurarlo:

⭐️ Passaggio 1: imposta le informazioni sul prestito e la tabella di ammortamento

1. Inserisci le informazioni relative al prestito, come tasso di interesse annuo, durata del prestito in anni, numero di pagamenti annuali, importo del prestito e pagamento extra nelle celle come mostrato nella seguente schermata:
   
2. Quindi, calcola il pagamento programmato.
   Oltre alle celle di input, per i nostri calcoli successivi è necessaria un'altra cella predefinita: l'importo del pagamento programmato. Questo è l'importo del pagamento regolare su un prestito presupponendo che non vengano effettuati pagamenti aggiuntivi. Si prega di applicare 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, Interessi, Capitale, Saldo rimanente nelle celle A8:G8;
   • Nella colonna Periodo, inserisci il numero massimo di pagamenti che potresti prendere in considerazione per qualsiasi prestito. Ad esempio, inserisci i numeri compresi tra 0 e 360. Ciò 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 del prestito iniziale. Tutte le altre celle di questa riga devono essere lasciate vuote.
   • 

⭐️ Step 2: crea le formule per il piano di ammortamento con pagamenti extra

Inserisci una per una le seguenti formule nelle celle corrispondenti. Per migliorare la gestione degli errori, racchiudiamo questa e tutte le formule future all'interno della funzione SEERRORE. Questo approccio aiuta a evitare molteplici potenziali errori che potrebbero verificarsi se una qualsiasi delle celle di input viene lasciata vuota o contiene 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, "")

● Calcolare il capitale:

Inserisci la seguente formula nella cella E10:

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

● Calcolare l'interesse:

Inserisci la seguente formula nella cella F10:

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

● Calcolare il saldo rimanente

Inserisci la seguente formula nella cella G10:

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

Una volta completata ciascuna formula, seleziona l'intervallo di celle B10:G10 e utilizza la maniglia di riempimento per trascinare ed estendere queste formule attraverso l'intero set di periodi di pagamento. Per eventuali periodi non utilizzati, le celle mostreranno 0. Vedi schermata:

⭐️ Passaggio 3: fai un riepilogo del prestito

● Ottieni il numero di pagamenti pianificati:

=B2:B3

● Ottieni il numero effettivo di pagamenti:

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

● Ottieni pagamenti extra totali:

=SUM(C10:C369)

● Ottieni l'interesse totale:

=SUM(F10:F369)

⭐️ Risultato:

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

Creare un piano di ammortamento utilizzando il modello Excel

Creare un piano di ammortamento in Excel utilizzando un modello è un metodo semplice ed efficiente in termini di tempo. Excel fornisce modelli incorporati che calcolano automaticamente gli interessi, il capitale e il saldo residuo di ogni pagamento. Ecco come creare un piano di ammortamento utilizzando un modello Excel:

1. Clicchi Compila il > New, nella casella di ricerca, digitare piano di ammortamentoe premere entrare chiave. Successivamente, seleziona il modello più adatto alle tue esigenze facendo clic su di esso. Ad esempio, qui selezionerò il modello di calcolo del prestito semplice. Vedi schermata:
   
2. Una volta selezionato un modello, fare clic su Creare pulsante per aprirlo come una nuova cartella di lavoro.
3. Quindi, inserisci i dettagli del tuo prestito, il modello dovrebbe calcolare e compilare automaticamente il programma in base ai tuoi input.
4. Infine, salva la tua nuova cartella di lavoro del programma di ammortamento.