KutoolsforOffice — Un'unica soluzione, cinque potenti strumenti.Ottenere di più con meno sforzo.

Crea un piano di ammortamento per prestiti in Excel – Una guida passo passo

AutoreXiaoyang Data di modifica

Creare un piano di ammortamento di un prestito in Excel è un’abilità estremamente utile, che ti permette di visualizzare e gestire in modo efficace i tuoi rimborsi. Un piano di ammortamento è una tabella che dettaglia ogni pagamento periodico di un prestito ammortizzato – come un mutuo o un finanziamento auto – suddividendolo in quota interessi e quota capitale, e mostrando il saldo residuo dopo ciascun versamento. Ecco una guida passo passo per realizzarlo in Excel.

Crea un piano di ammortamento del prestito

Cos’è un piano di ammortamento?

Crea un piano di ammortamento in Excel

Crea un piano di ammortamento con un numero variabile di periodi

Crea un piano di ammortamento con pagamenti aggiuntivi

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

Scarica il file di esempio

Creare un piano di ammortamento in Excel

Cos’è un piano di ammortamento?

Un piano di ammortamento è una tabella dettagliata utilizzata nei calcoli dei prestiti che illustra il processo di rimborso di un prestito nel tempo. I piani di ammortamento sono comunemente usati per prestiti a tasso fisso, come mutui, finanziamenti auto e prestiti personali, in cui l’importo della rata rimane costante per tutta la durata del prestito, ma la ripartizione tra interessi e capitale varia nel tempo.

Per creare un piano di ammortamento di un prestito in Excel, le funzioni integrate RATA, P.RATA e INTERESSE sono essenziali. Ecco cosa fa ciascuna di esse:

  • Funzione RATA: Questa funzione calcola il pagamento periodico di un prestito, basato su rate costanti e un tasso di interesse fisso.
  • Funzione INTERESSE: Calcola la quota interessi di una rata relativa a un periodo specifico.
  • Funzione P.RATA: Questa funzione calcola la quota capitale del pagamento relativa a un periodo specifico.

Grazie a queste funzioni di Excel, puoi creare un piano di ammortamento dettagliato che mostra la quota interessi e la quota capitale di ogni pagamento, nonché il saldo residuo del prestito dopo ciascun versamento.

Crea un piano di ammortamento in Excel

In questa sezione ti presentiamo due metodi distinti per creare un piano di ammortamento in Excel, pensati per soddisfare preferenze e livelli di competenza diversi: così, chiunque — indipendentemente dalla propria familiarità con Excel — potrà costruire con successo un piano di ammortamento dettagliato e accurato per il proprio prestito.

Le formule offrono una visione più approfondita dei calcoli alla base del piano e permettono la flessibilità necessaria per adattarlo alle proprie esigenze specifiche. Questo approccio è perfetto per chi cerca un’esperienza pratica e desidera comprendere chiaramente come ogni rata viene suddivisa tra quota capitale e quota interessi. Ora vediamo passo dopo passo come creare un piano di ammortamento in Excel:

⭐️ Passo 1: Impostare le informazioni del prestito e la tabella di ammortamento

  1. Inserire le informazioni relative al prestito, come il tasso di interesse annuo, la durata del prestito in anni, il numero di pagamenti all’anno e l’importo del prestito nelle celle come mostrato nello screenshot seguente:
    Inserisci le informazioni relative al prestito
  2. Successivamente, crea una tabella di ammortamento in Excel con le etichette specificate—ad esempio Periodo, Pagamento, Interessi, Capitale e Saldo residuo—nelle celle A7:E7.
  3. Nella colonna **Periodo**, inserisci i numeri progressivi dei periodi. In questo esempio, il totale dei pagamenti è di 24 mesi (2 anni), quindi dovrai inserire i numeri da 1 a 24 nella colonna Periodo. Vedi lo screenshot:
    inserisci i numeri del periodo
  4. Una volta impostata la tabella con le etichette e i numeri dei periodi, puoi procedere all’inserimento di formule e valori nelle colonne Pagamento, Interessi, Capitale e Saldo, in base alle caratteristiche specifiche del tuo prestito.

⭐️ Passo 2: Calcolare l’importo totale del pagamento utilizzando la funzione RATA

La sintassi della funzione RATA è:

=-RATA()tasso di interesse per periodo,numero totale di pagamenti, importo del prestito)
  • tasso di interesse per periodo: Se il tasso di interesse del tuo prestito è annuo, dividilo per il numero di pagamenti all’anno. Ad esempio, con un tasso annuo del 5 % e pagamenti mensili, il tasso per periodo sarà del 5 %/12. In questo caso, il tasso verrà visualizzato come B1/B3.
  • Numero totale di pagamenti: moltiplica la durata del prestito in anni per il numero di pagamenti all’anno. In questo esempio, verrà visualizzato come B2*B3.
  • importo del prestito: È il capitale che hai preso in prestito. In questo esempio, corrisponde alla cella B4.
  • Segno negativo (-): La funzione RATA restituisce un valore negativo perché rappresenta un pagamento in uscita. Aggiungi un segno meno davanti alla funzione RATA per visualizzarlo come numero positivo.

Inserisci la seguente formula nella cella B7, quindi trascina il quadratino di riempimento verso il basso per applicarla alle altre celle: otterrai un importo di pagamento costante per tutti i periodi. Vedere lo screenshot:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)
Nota: Qui, utilizzare riferimenti assolutinella formula in modo che, quando la si copia nelle celle sottostanti, rimanga invariata.

Calcola l'importo totale del pagamento utilizzando la funzione RATA

⭐️ Passo 3: Calcolare gli interessi utilizzando la funzione INTERESSE

In questo passo si calcoleranno gli interessi per ciascun periodo di pagamento mediante la funzione INTERESSE di Excel.

=-INT.PER()tasso di interesse per periodo,periodo specifico,numero totale di pagamenti, importo del prestito)
  • Tasso di interesse per periodo: Se il tasso di interesse del tuo prestito è annuo, dividilo per il numero di pagamenti all’anno. Ad esempio, se il tasso annuo è del 5 % e i pagamenti sono mensili, il tasso per periodo sarà del 5 %/12. In questo caso, il tasso verrà visualizzato come B1/B3.
  • periodo specifico: Il periodo specifico per cui desideri calcolare gli interessi. Di solito inizia da 1 nella prima riga del tuo piano di ammortamento e aumenta progressivamente di 1 in ogni riga successiva. In questo esempio, il periodo inizia dalla cella A7.
  • Numero totale di pagamenti: moltiplica la durata del prestito in anni per il numero di pagamenti all’anno. In questo esempio, verrà visualizzato come B2*B3.
  • importo del prestito: È il capitale che hai preso in prestito. In questo esempio, corrisponde alla cella B4.
  • Segno negativo (-): La funzione RATA restituisce un valore negativo perché rappresenta un pagamento in uscita. Aggiungi un segno meno davanti alla funzione RATA per visualizzarlo come numero positivo.

Inserisci la seguente formula nella cella C7, quindi trascina il quadratino di riempimento lungo la colonna per applicare la formula e calcolare gli interessi di ogni periodo.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)
Nota: Nella formula sopra, A7 è impostato come riferimento relativo, garantendo così un adattamento dinamico alla riga specifica a cui viene estesa la formula.

Calcola gli interessi utilizzando la funzione INTERESSI

⭐️ Passo 4: Calcolare il capitale utilizzando la funzione P.RATA

Dopo aver calcolato gli interessi per ciascun periodo, il passo successivo nella creazione di un piano di ammortamento è determinare la quota capitale di ogni pagamento. Questo si ottiene utilizzando la funzione P.RATA, appositamente progettata per calcolare la porzione in conto capitale di un pagamento relativo a un periodo specifico, assumendo pagamenti e tasso di interesse costanti.

La sintassi della funzione INTERESSE è:

=-P.RATA()tasso di interesse per periodo,periodo specifico,numero totale di pagamenti, importo del prestito)

La sintassi e i parametri della formula P.RATA sono identici a quelli della formula INTERESSE illustrata in precedenza.

Inserisci la seguente formula nella cella D7, quindi trascina il quadratino di riempimento lungo la colonna per compilare automaticamente la quota capitale di ciascun periodo. Guarda lo screenshot:

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

Calcola il capitale utilizzando la funzione PPMT

⭐️ Passo 5: Calcolare il saldo residuo

Dopo aver calcolato sia gli interessi che il capitale di ogni pagamento, il passo successivo nel piano di ammortamento consiste nel calcolare il saldo residuo del prestito dopo ogni versamento. Questa è una parte fondamentale del piano, poiché mostra come il saldo del prestito diminuisce nel tempo.

  1. Nella prima cella della colonna del saldo – E7, inserire la seguente formula, che indica che il saldo residuo sarà l’importo originale del prestito meno la quota capitale del primo pagamento:
    =B4-D7
    Calcola il saldo residuo
  2. Per il secondo e tutti i periodi successivi, calcola il saldo residuo sottraendo il pagamento in conto 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 deve essere relativo, in modo che si aggiorni automaticamente quando trascini la formula verso il basso.
    Calcola il saldo residuo
  3. Trascina quindi il quadratino di riempimento lungo la colonna. Noterai che ogni cella si adatterà automaticamente per calcolare il saldo residuo in base ai pagamenti in conto capitale aggiornati.
    trascina il quadratino di riempimento verso il basso nella colonna

⭐️ Passo 6: Creare un riepilogo del prestito

Dopo aver impostato il piano di ammortamento dettagliato, la creazione di un riepilogo del prestito offre una panoramica immediata degli aspetti chiave del finanziamento, inclusi il costo totale del prestito e gli interessi complessivi pagati.

● Per calcolare i pagamenti totali:

=SUM(B7:B30)

● Per calcolare gli interessi totali:

=SUM(C7:C30)

Crea un riepilogo del prestito

⭐️ Risultato:

È stato così creato con successo un piano di ammortamento semplice ma completo. Consulta lo screenshot:

è stato creato un semplice piano di ammortamento del prestito

uno screenshot di kutools for excel ai

Scopri la magia di Excel con KUTOOLS AI

  • Esecuzione intelligente: Esegui operazioni sulle celle, analizza i dati e crea grafici, il tutto guidato da comandi semplici.
  • Formule personalizzate: crea formule su misura per ottimizzare i tuoi flussi di lavoro.
  • Programmazione VBA: Scrivi e implementa codice VBA in modo semplice e intuitivo.
  • Interpretazione delle formule: Comprendi con facilità anche le formule più complesse.
  • Traduzione del testo: Elimina le barriere linguistiche nei tuoi fogli di calcolo.
Potenzia le tue capacità in Excel con strumenti basati sull’intelligenza artificiale.Scarica orae scopri un’efficienza mai vista prima!

Crea un piano di ammortamento con un numero variabile di periodi

Nell’esempio precedente abbiamo creato un piano di rimborso con un numero fisso di rate. Questo approccio è ideale per gestire prestiti o mutui con termini predeterminati e invariabili.

Tuttavia, se desideri creare un piano di ammortamento flessibile, riutilizzabile per prestiti con durate variabili e adattabile al numero di rate in base a diversi scenari di prestito, dovrai seguire un metodo più dettagliato.

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

  1. Inserire le informazioni relative al prestito, come il tasso di interesse annuo, la durata del prestito in anni, il numero di pagamenti all’anno e l’importo del prestito nelle celle come mostrato nello screenshot seguente:
    Inserisci le informazioni relative al prestito
  2. Successivamente, crea una tabella di ammortamento in Excel inserendo le etichette specificate — ad esempio Periodo, Pagamento, Interessi, Capitale e Saldo residuo — nelle celle A7:E7.
  3. Nella colonna Periodo, inserisci il numero massimo di pagamenti che potresti prendere in considerazione per qualsiasi prestito; ad esempio, compila con i numeri da 1 a 360. In questo modo coprirai un prestito standard trentennale con pagamenti mensili.
    inserisci il numero massimo di rate

⭐️ Passaggio 2: Modifica le formule per rata, interessi e capitale con la funzione SE

Inserisci le seguenti formule nelle celle corrispondenti, quindi trascina il quadratino di riempimento per estenderle fino al numero massimo di periodi di pagamento impostato.

● Formula della rata:

Di solito, per calcolare la rata si utilizza la funzione RATA. Integrando un’istruzione SE, la formula diventa:

=SE()periodo corrente<=numero totale di periodi, -RATA()tasso di interesse per periodo,numero totale di periodi, importo del prestito), «» )

La formula risulta quindi:

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

● Formula degli interessi:

La formula è:

=SE()periodo corrente<=numero totale di periodi, -INT.PER()tasso di interesse per periodo,periodo corrente,numero totale di periodi, importo del prestito), «» )

La formula risulta quindi:

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

● Formula del capitale:

La formula è:

=SE()periodo corrente<=numero totale di periodi, -P.RATA()tasso di interesse per periodo,periodo corrente,numero totale di periodi, importo del prestito), «» )

La formula risulta quindi:

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

Modifica le formule di pagamento, interessi e capitale con la funzione SE

⭐️ Passaggio 3: Modifica la formula del saldo residuo

Per calcolare il saldo residuo, si sottrae normalmente il capitale dal saldo precedente. Utilizzando un’istruzione SE, la formula diventa:

● Prima cella del saldo: (E7)

=B4-D7

● Seconda cella del saldo: (E8)

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

Aggiusta il saldo residuo

⭐️ Passaggio 4: Crea 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 gli interessi totali:

=SUM(C7:C366)

Crea un riepilogo del prestito

⭐️ Risultato:

Ora hai a disposizione un piano di ammortamento completo e dinamico in Excel, accompagnato da un riepilogo dettagliato del prestito. Ogni volta che modifichi la durata del periodo di rimborso, l’intero piano di ammortamento si aggiorna automaticamente per riflettere i nuovi parametri. Guarda la demo qui sotto:

Crea un piano di ammortamento con pagamenti aggiuntivi

Effettuando pagamenti aggiuntivi rispetto a quelli previsti, puoi estinguere il prestito più rapidamente. Un piano di ammortamento con pagamenti extra, realizzato in Excel, ti mostra esattamente come questi versamenti anticipati accelerino l’estinzione del debito e riducano gli interessi totali da pagare. Ecco come impostarlo:

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

  1. Inserire le informazioni relative al prestito, come il tasso di interesse annuo, 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:
    Inserisci le informazioni relative al prestito
  2. Calcola quindi il pagamento programmato.
    Oltre alle celle di input, è necessaria un’altra cella predefinita per i calcoli successivi: l’importo del pagamento programmato. Si tratta dell’importo regolare del pagamento di un prestito, ipotizzando 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),"")

    calcola la rata programmata
  3. Successivamente, creare una tabella di ammortamento in Excel:
    • Imposta le etichette specificate, ad esempio Periodo, Pagamento programmato, Pagamento aggiuntivo, Pagamento totale, Interessi, Capitale, Saldo residuo nelle celle A8:G8;
    • Nella colonna Periodo, inserisci il numero massimo di pagamenti che potresti prendere in considerazione per qualsiasi prestito — ad esempio, un valore compreso tra 0 e 360, sufficiente a coprire un prestito standard di 30 anni con pagamenti mensili.
    • Per il Periodo 0 (riga 9 nel nostro caso), inserisci il valore del Saldo con questa formula =B4, che corrisponde all’importo iniziale del prestito. Tutte le altre celle di questa riga devono rimanere vuote.
    • crea una tabella di ammortamento

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

Inserisci le seguenti formule una alla volta nelle celle corrispondenti. Per migliorare la gestione degli errori, racchiudile — insieme a tutte le formule successive — nella funzione SE.ERRORE. Questo approccio previene efficacemente numerosi errori potenziali che potrebbero verificarsi se alcune celle di input risultassero vuote o contenessero valori non validi.

● Calcola la rata programmata:

Inserisci la seguente formula nella cella B10:

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

Calcola la rata programmata

● Calcola il pagamento extra:

Inserisci la seguente formula nella cella C10:

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

Calcola il pagamento aggiuntivo

● Calcola il pagamento totale:

Inserisci la seguente formula nella cella D10:

=IFERROR(B10+C10, "")

Calcola il pagamento totale

● Calcola il capitale:

Inserisci la seguente formula nella cella E10:

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

Calcola il capitale

● Calcola gli interessi:

Inserisci la seguente formula nella cella F10:

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

Calcola gli interessi

● Calcola il saldo residuo

Inserisci la seguente formula nella cella G10:

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

Calcola il saldo residuo

Una volta completate tutte le formule, seleziona l’intervallo di celle B10:G10 e usa il quadratino di riempimento per estenderle a tutti i periodi di pagamento. Nei periodi non utilizzati, le celle visualizzeranno automaticamente 0. Vedi lo screenshot:
usa il quadratino di riempimento per trascinare e estendere queste formule verso il basso

⭐️ Passaggio 3: Crea un riepilogo del prestito

● Ottieni il numero programmato di pagamenti:

=B2:B3

● Ottieni il numero effettivo di pagamenti:

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

● Ottieni il totale dei pagamenti extra:

=SUM(C10:C369)

● Ottieni il totale degli interessi:

=SUM(F10:F369)

Crea un riepilogo del prestito

⭐️ Risultato:

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

Crea un piano di ammortamento utilizzando un modello di Excel

Creare un piano di ammortamento in Excel utilizzando un modello è un metodo semplice e veloce. Excel mette a disposizione modelli predefiniti che calcolano automaticamente interessi, quota capitale e saldo residuo per ogni rata. Ecco come creare un piano di ammortamento sfruttando un modello di Excel:

  1. Fai clic su File > Nuovo, digita piano di ammortamento nella casella di ricerca e premi il tasto Invio. A questo punto, seleziona il modello più adatto alle tue esigenze facendo clic su di esso. Ad esempio, qui sceglierò il modello Calcolatore semplice di prestito. Guarda lo screenshot:
    Crea un piano di ammortamento tramite modello
  2. Una volta selezionato un modello, fai clic sul pulsante Crea per aprirlo come nuovo workbook.
  3. Inserisca quindi i propri dati relativi al prestito: il modello calcolerà e compilerà automaticamente il piano in base agli input forniti.
  4. Infine, salva la nuova cartella di lavoro del piano di ammortamento.