Come creare un piano di ammortamento in Excel: i passaggi spiegati

Un piano di ammortamento offre una chiara mappa finanziaria, mostrando come ogni rata del prestito si suddivide tra interessi e capitale fino all'estinzione completa del debito.

Con le funzioni integrate di Excel, puoi creare piani di ammortamento dettagliati per visualizzare il ciclo di vita del tuo prestito, prendere decisioni finanziarie informate e persino esplorare opzioni di estinzione anticipata.

In questo articolo imparerai a costruire da zero un piano di ammortamento completo in Excel. Tratteremo funzioni essenziali come PMT(), IPMT() e PPMT(), ti guideremo passo dopo passo e analizzeremo scenari pratici per arricchire la tua cassetta degli attrezzi per la pianificazione finanziaria.

Che cos'è esattamente un piano di ammortamento?

Un piano di ammortamento è una tabella completa dei pagamenti periodici di un prestito, che mostra la quota di capitale e di interessi che compone ciascuna rata fino all'estinzione alla fine del termine.

Quando contrai un prestito, diversi elementi chiave determinano come funzionano le tue rate:

• Capitale: L'importo originario preso a prestito che devi rimborsare
• Interessi: Il costo del denaro preso a prestito, calcolato come percentuale del capitale
• Periodicità dei pagamenti: Quanto spesso paghi (mensile, bisettimanale, ecc.)
• Durata del prestito: Il periodo totale entro cui rimborserai il prestito

Per la maggior parte dei prestiti standard — come mutui, prestiti auto e personali — le rate restano costanti per tutta la durata. Ciò che cambia con ogni pagamento è la proporzione destinata a interessi rispetto al capitale. Nelle fasi iniziali, una percentuale maggiore di ogni rata copre gli interessi. Con l'avanzare della durata, una quota crescente di ogni rata riduce il capitale residuo.

Lo scopo principale di creare un piano di ammortamento è rendere trasparente il tuo prestito. Ti permette di vedere esattamente quanti interessi pagherai nel tempo, quanto velocemente costruisci equity e come i pagamenti aggiuntivi possono influire sulla tempistica del prestito. Per le aziende, questi piani servono anche per la contabilità e la pianificazione fiscale.

Le funzioni e formule di Excel che devi conoscere

Excel non ha una singola funzione integrata "di ammortamento". Invece, in genere si costruisce un piano di ammortamento con funzioni finanziarie specifiche che lavorano insieme per calcolare i diversi aspetti delle rate del prestito. Ecco le funzioni chiave di cui avrai bisogno:

Funzione PMT()

La funzione PMT() calcola la rata di un prestito basata su pagamenti costanti e un tasso d'interesse costante:

=PMT(rate, nper, pv, [fv], [type])

• rate: Il tasso d'interesse per periodo (tasso annuo diviso per la frequenza dei pagamenti)

• nper: Il numero totale di periodi di pagamento

• pv: Il valore attuale (importo del prestito)

• [fv]: Valore futuro facoltativo (tipicamente 0 per prestiti estinti)

• [type]: Facoltativo; 0 per pagamenti a fine periodo, 1 per inizio periodo

Per calcolare la rata mensile di un mutuo da $250.000 al 4,5% annuo per 30 anni: 

=PMT(4.5%/12, 30*12, 250000)

Questo restituisce -$1.266,71, che rappresenta l'uscita di cassa per il mutuatario.

Per una spiegazione dettagliata della funzione PMT(), inclusi errori comuni ed esempi avanzati, puoi consultare la nostra guida completa a PMT() in Excel.

Funzione IPMT()

La funzione IPMT() calcola la quota interessi di un pagamento specifico:

=IPMT(rate, per, nper, pv, [fv], [type])

• per: Il periodo di pagamento specifico che stai calcolando

Il parametro per è essenziale perché determina per quale periodo di pagamento stai calcolando l'importo degli interessi: a differenza della funzione PMT(), che restituisce la stessa rata per tutti i periodi, i risultati di IPMT() variano sensibilmente in base al periodo indicato, poiché la quota interessi diminuisce nel tempo.

Questo mostra esattamente quanta parte di una determinata rata va a coprire gli interessi.

Funzione PPMT()

La funzione PPMT() calcola la quota capitale di un pagamento specifico:

=PPMT(rate, per, nper, pv, [fv], [type])

Analogamente a IPMT(), l'importo di capitale varia per periodo: i primi pagamenti hanno quote capitale più piccole, quelli successivi più grandi. Questo mostra quanta parte di una determinata rata riduce il saldo del prestito.

Formule personalizzate

Per ciascun periodo di pagamento, vorrai tracciare:

Saldo residuo: L'importo del prestito ancora dovuto dopo ogni pagamento

Remaining balance = Previous balance - Current principal payment

Capitale cumulato: Capitale totale pagato fino a oggi

Cumulative principal = Previous cumulative principal + current principal payment

Interessi cumulati: Interessi totali pagati fino a oggi

Formula: Previous cumulative interest + current interest payment

Funzione Round()

Anche piccoli errori di arrotondamento possono accumularsi in un piano di ammortamento:

=ROUND(value, 2)

Questo garantisce che tutti i valori monetari siano arrotondati al centesimo più vicino.

Riferimenti assoluti vs. relativi

Quando costruisci la tua tabella di ammortamento, dovrai:

• Usare riferimenti assoluti ($) per valori fissi come tasso d'interesse e importo del prestito

• Usare riferimenti relativi per valori che cambiano a ogni periodo

La formula seguente, ad esempio, usa riferimenti assoluti per tasso, durata e importo del prestito, ma un riferimento relativo per il periodo di pagamento:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

Guida passo passo per creare il piano di ammortamento

Ora che conosci le funzioni chiave, costruiamo da zero un piano di ammortamento. Questa procedura guidata creerà una tabella completa che traccia ogni pagamento lungo tutta la vita del prestito.

1. Inserimento dei dati del prestito

Inserisci i dettagli del prestito nelle celle di input dedicate come mostrato nell'esempio sotto:

• Importo del prestito: $250.000 (cella B1)
• Tasso d'interesse annuo: 4,5% (cella B2)
• Durata del prestito: 30 anni (cella B3)
• Frequenza dei pagamenti: Mensile (cella B4)

Impostazione dei parametri di input del prestito. Immagine dell'autore. 

2. Calcolo della rata mensile

Nella cella B5, calcola la rata mensile usando la funzione PMT: =PMT(B2/12, B3*12, B1)

Questa formula restituisce -$1.266,71, che rappresenta la tua rata mensile. Il segno negativo indica un pagamento (uscita di cassa).

Impostazione delle intestazioni di colonna e delle formule. Immagine dell'autore.

3. Creazione della prima riga del piano

Ora possiamo creare le seguenti intestazioni e compilare la prima riga di dati (righe 7 e 8):  

• Periodo: Inserisci 1 nella cella A8

• Importo rata: =$B$5 nella cella B8 (riferimento alla rata mensile calcolata)

• Quota interessi: =IPMT($B$2/12, A8, $B$3*12, $B$1) nella cella C8

• Quota capitale: =PPMT($B$2/12, A8, $B$3*12, $B$1) nella cella D8

• Saldo residuo: =$B$1+D8 nella cella E8 (prestito originario più quota capitale, funziona perché D8 restituisce un valore negativo, sottraendo di fatto il capitale dal prestito)

• Capitale cumulato: =D8 nella cella F8 (uguale alla quota capitale per la prima riga)

• Interessi cumulati: =C8 nella cella G8 (uguale alla quota interessi per la prima riga)

Dettaglio della prima rata. Immagine dell'autore.

4. Completamento del piano

Per la seconda riga (riga 9):

• Periodo: =A8+1 nella cella A9 (incremento di 1)

• Importo rata: Copia la formula da B8 a B9

• Quota interessi: =IPMT($B$2/12, A9, $B$3*12, $B$1) nella cella C9

• Quota capitale: =PPMT($B$2/12, A9, $B$3*12, $B$1) nella cella D9

• Saldo residuo: =E8+D9 nella cella E9 (saldo precedente più capitale corrente)

• Capitale cumulato: =F8+D9 nella cella F9 (totale precedente più capitale corrente)

• Interessi cumulati: =G8+C9 nella cella G9 (totale precedente più interessi correnti)

Nota che nelle funzioni IPMT() e PPMT(), A9 è usato come riferimento relativo (senza simboli del dollaro), il che significa che quando copi la formula verso il basso si aggiornerà automaticamente ad A10, A11, ecc., calcolando ogni volta il periodo corretto.

Impostazione di formule copiabili verso il basso. Immagine dell'autore.

Le prime rate nel piano di ammortamento. Immagine dell'autore.

Seleziona tutte le celle della riga 9 e trascina fino alla riga 367 (per un prestito mensile di 30 anni con 360 pagamenti).

Con l'avanzare della durata, una quota maggiore di ogni rata va a capitale anziché interessi. Immagine dell'autore.

5. Verifica dell'ultima rata

In un piano di ammortamento costruito correttamente, l'ultima rata deve portare il saldo residuo esattamente a zero e il capitale cumulato deve eguagliare l'importo del prestito originario.

L'ultima rata porta il saldo residuo esattamente a zero. Immagine dell'autore.

Come si vede nell'ultima riga, il saldo residuo è $0,00 (evidenziato) e il capitale cumulato è pari a $250.000, in linea con l'importo del prestito originario. Questo conferma che il nostro piano di ammortamento è accurato.

Nel verificare il saldo finale, può essere utile applicare la funzione ROUND() ai calcoli chiave. Piccole discrepanze decimali dovute alla precisione di calcolo di Excel possono causare un lieve importo residuo anziché zero tondo. Usare =ROUND(value, 2) assicura che tutti i valori monetari restino coerenti con gli standard tipici di rendicontazione.

Ulteriori aspetti da considerare

Un piano standard è il punto di partenza. Considera questi elementi per rendere il tuo modello più flessibile:

Gestione delle variazioni di pagamento

I piani di ammortamento diventano ancora più utili quando li personalizzi per esplorare scenari di pagamento diversi. Per modellare pagamenti extra, crea una cella di input aggiuntiva per i versamenti supplementari e modifica la formula della quota capitale per includere tale importo. Questo approccio aiuta a visualizzare come i pagamenti aggiuntivi riducono sia la durata del prestito sia gli interessi totali pagati. Per prestiti con balloon payment o tassi variabili, valuta di creare sezioni separate nel piano che riflettano i cambi di tasso in momenti specifici. Puoi usare lo Scenario Manager di Excel per confrontare diversi contesti di tasso e il loro impatto sui costi totali.

Migliorare leggibilità e analisi

Una formattazione curata trasforma il piano di ammortamento da una semplice tabella a uno strumento finanziario informativo. Applica il formato valuta con decimali coerenti a tutti i valori monetari e usa la formattazione condizionale per evidenziare traguardi chiave — ad esempio quando hai rimborsato metà del capitale o raggiunto specifiche soglie di equity. È anche importante capire come i diversi metodi di maturazione degli interessi influenzano le rate. La maturazione giornaliera (comune nei prestiti studenteschi) calcola gli interessi su un anno di 365 giorni, mentre molti mutui usano una maturazione mensile. Documentare quale metodo utilizza il tuo piano garantisce accuratezza quando lo confronti con gli estratti conto reali del prestito.

Personalizzare per tipologie di prestito specifiche

Prestiti diversi hanno caratteristiche uniche che possono richiedere adattamenti al tuo modello base. Per i mutui con conto di escrow, aggiungi colonne per tracciare assicurazione e imposte insieme a capitale e interessi. Per prestiti con ammortamento negativo (in cui le rate non coprono gli interessi maturati), modifica il calcolo del saldo residuo per aggiungere gli interessi non pagati al capitale. L'opzione sulla tempistica dei pagamenti nelle funzioni finanziarie di Excel (parametro type) ti permette di modellare pagamenti a inizio o fine periodo, utile in particolare per i leasing e alcuni prestiti commerciali con convenzioni diverse da quelle dei prestiti ai consumatori.

Conclusione

Creare un piano di ammortamento in Excel ti offre una visione preziosa dei tuoi prestiti, oltre ciò che la maggior parte dei finanziatori fornisce. Costruendo il tuo piano, puoi vedere esattamente come ogni rata incide sul saldo, comprendere il vero costo del denaro nel tempo e prendere decisioni informate sulle opzioni di rimborso anticipato. Le competenze sviluppate in questo processo — usare le funzioni finanziarie di Excel, creare formule dinamiche e progettare tabelle informative — vanno ben oltre l'analisi dei prestiti e si applicano a molti aspetti della pianificazione finanziaria personale e aziendale.

Se vuoi andare oltre l'ammortamento e costruire modelli finanziari più completi — tra cui previsione dei ricavi, valutazione degli investimenti ed esercizi di what-if — dai un'occhiata al nostro corso Financial Modeling in Excel. È un ottimo passo successivo per rafforzare le tue abilità con i fogli di calcolo per l'analisi finanziaria. E ricorda: più ti senti a tuo agio con questi calcoli, meglio sarai posizionato per prendere il controllo del tuo futuro finanziario, che si tratti di gestire debiti personali, pianificare investimenti aziendali o consigliare i clienti sulle loro opzioni finanziarie.