Il mutuo è probabilmente la decisione finanziaria più importante della vita della maggior parte delle persone, eppure spesso firmiamo un contratto che ci legherà per decenni con la banca, senza capire davvero come funzioni il mutuo e come vengano calcolate le rate.
Il risultato è che facendo un mutuo da 200.000€ per comprare casa, potresti finire per pagare alla banca anche più del 50% di interessi.
Inoltre, ciò che sorprende chi non ha mai guardato un piano di ammortamento è che nei primi anni la maggior parte di ciò che paghi sono proprio interessi.
Se hai già un mutuo o stai per aprirne uno, ecco che capire come funziona l’ammortamento alla francese ti permette di risparmiare migliaia di euro, ad esempio anticipando pagamenti o negoziando meglio con la banca, sapendo già in anticipo quale sara il risultato finale.
Questo è proprio quello che vedremo in questo articolo (e nel relativo video pubblicato su YouTube: solo pochi cenni di teoria per capire come funziona un finanziamento (che abbiamo già visto qui) e poi passiamo alla pratica su Excel, vedendo quanto sia semplice calcolare il piano di pagamento completo.
Mutuo: un esempio
Immaginiamo di fare un mutuo da 200.000€ per comprare la casa dei nostri sogni (o molto più probabilmente un normalissimo trilocale 😅). La banca ci propone un tasso annuo del 3% ed una rata mensile costante, che pagheremo per i prossimi 30 anni, pari a 843,21€ (tra poco vedremo come siamo arrivati a questa cifra):
30 anni di matrimonio (indissolubile?) con la banca, 360 rate tutte uguali tra di loro (che però ci sembreranno essere sempre un po’ più leggere sulle nostre finanze) ma allo stesso tempo sono anche tutte diverse tra di loro 😊. Mi spiego:
- Tutte le rate hanno lo stesso importo: nei prossimi 30 anni pagheremo sempre 843,21€, ogni mese;
- Ogni rata però è fatta da due componenti: una quota che serve a restituire il capitale che abbiamo preso in prestito dalla banca ed una quota di interessi, cioè il guadagno della banca;
- Queste 2 componenti cambiano nel tempo e in ogni rata del mutuo sono sempre diverse tra loro: all’inizio paghi molti interessi e poco capitale; con il passare degli anni invece gli interessi diminuiscono e aumenta la quota di capitale che restituisci;
Ecco che, senza usare nessun tecnicismo, abbiamo già visto tutte le caratteristiche dell’ammortamento alla francese, che è il sistema più usato per i finanziamenti in Italia, come in gran parte del mondo: rata constante e quota capitale crescente.
A questo punto, la ovvia domanda diventa: come si calcola concretamente questo piano di pagamento?
Beh, come si calcola lo abbiamo già visto qui (N.B.: è la terza volta che ti metto questo link, hai letto l’articolo? 😅). Quello che ci resta da fare invece è come creare un piano di ammortamento in Excel.
Come creare un piano di ammortamento per il mutuo in Excel
Il risultato finale che vogliamo raggiungere è un file che si aggiorni da solo e calcoli in automatico l’intero piano di ammortamento, una volta recepiti 3 semplici input:
- il capitale (C) da prendere in prestito
- il tasso di interesse annuo (i) che ci propone la banca
- il numero di anni (n) di durata del mutuo
Per mettere subito mano sul risultato finale, devi semplicemente scorrere verso il basso e scaricare il file Excel che ti ho messo a disposizione. Ma non vorrai mica perderti il resto dell’articolo, grazie al quale scoprirai quali sono le formule Excel necessarie per calcolarti le rate di tutti i finanziamenti della tua vita?
Le formule
Abbiamo già visto nel pluricitato articolo sull’ammortamento francese, come le rate per calcolare la rata e la quota capitale siano rispettivamente
e
Non che siano formule eccessivamente complicate, ma sicuramente noiose e ad alto rischio di errore umano, sì! Per fortuna c’è Excel che ci semplifica la vita.
Per calcolare la rata, la quota capitale e la quota di interessi infatti ci è sufficiente utilizzare rispettivamente:
- le funzioni =PMT(), =PPMT() e =IPMT() se hai Excel in inglese
- oppure le funzioni =RATA(), =P.RATA() e =INTERESSI() se hai Excel in italiano
Vediamo come funzionano.
Come calcolare la rata
Una volta identificati i 3 input di cui sopra (C, i, n), dobbiamo semplicemente “convertirli” in un formato adatto ad Excel per fare tutti i calcoli.
Nello specifico se “i= tasso di interesse annuo” ma le rate del nostro mutuo sono mensili (come avviene nel 99,9% dei casi) allora dobbiamo convertire il tasso annuo in tasso mensile, dividendo il 3% di questo esempio per 12, ottenendo quindi un tasso di interesse mensile (i12) pari a 0,25% (i12 = 3%/12 = 0,25%).
Analogamente se la durata del mutuo è trentennale, significa che il numero complessivo di rate del nostro mutuo n sara pari a 360 (n = 30 * 12 = 360):
Ecco che a questo punto abbiamo tutto quello che ci serve per calcolare la rata. Indichiamo ad Excel che vogliamo utilizzare la funzione PMT (RATA in italiano) la cui sintassi fa riferimento al tasso mensile in C7, al numero complessivo di rate in C8 ed al capitale preso a prestito in C4:
ATTENZIONE: il pagamento di una rata rappresenta una uscita di cassa, pertanto Excel la rappresenterà come un valore negativo. Se nel calcolo del piano di ammortamento vogliamo che tale valora sia positivo non dobbiamo far altro che aggiungere un segno – (meno) davanti alla formula PMT
Come calcolare la quota capitale
In questo caso abbiamo bisogno di 4 input: 3 sono gli stessi visti sopra (C, i, n), l’altro è il numero della rata.
Abbiamo detto prima che l’importo della rata è costante (difatti la rata calcolata al punto precedente sarà valida per tutti i 360 mesi del mutuo) ma le quote capitali ed interesse cambiano sempre.
Di conseguenza nel calcolo di queste 2 componenti sarà fondamentale tenere conto del periodo di riferimento (da 1 a 360 in questo esempio).
La formula da utilizzare per il calcolo della quota capitale della prima rata del mutuo quindi sarà la seguente PPMT (o P.RATA in italiani) con la seguente sintassi:
dove C4, C7 e C8 rappresentano gli stessi valori già visti prima (importo, tasso, numero totale rate) mentre 1 è la “variabile” che fa riferimento al periodo numero 1 del mutuo che stiamo valutando (in altre parole se volessimo conoscere la quota capitale della seconda rata del mutuo inseriremmo il numero 2 e via dicendo fino alla 360).
Attenzione al segno meno (-) davanti a PPMT per lo stesso motivo evidenziato nel calcolo della rata.
Come calcolare la quota interessi
In Excel possiamo calcolare la quota degli interessi seguendo le stesse identiche considerazioni della quota capitale (tranne nell’utilizzo della funzione IPMT, o INTERESSI se Excel è in italiano):
In realtà, conoscendo già l’importo della rata e della quota capitale, possiamo derivare la quota di interessi come differenza tra la rata (che è sempre costante) e la quota capitale del periodo di riferimento:
Ik = R – Ck
Come calcolare il piano di ammortamento
A questo punto abbiamo tutte le formule necessarie per calcolare il mutuo ed il suo piano di ammortamento in Excel.
Ma per avere una vista davvero completa dobbiamo prima conoscere i concetti di “debito residuo” e “debito estinto”. Nulla di più semplice naturalmente ma è bene fare chiarezza.
Con debito residuo (Dk) intendiamo quella quota di capitale che dobbiamo ancora restituire alla banca. Ovviamente questo valore sarà sempre più basso man mano che ripagheremo le rate del mutuo. Nel caso del periodo 1 sarà uguale a C – C1 (cioè 199.656,79€ nel nostro esempio), successivamente per ciascun periodo k, lo calcoleremo come il debito residuo del periodo precedente a cui sottraiamo la quota capitale del periodo in questione, cioè:
Dk = Dk-1 – Ck
Con debito estinto (Ek) invece intendiamo quella quota parte del capitale C che abbiamo ricevuto e abbiamo già restituito alla banca. Nel periodo 1 sarà uguale a C1, cioè la quota capitale del primo mese. Nei periodi successivi invece sarà il debito estinto del periodo precedente a cui sommeremo la quota capitale del periodo in oggetto, ovvero:
Ek = Ek-1 + Ck
Finalmente possiamo completare la tabella Excel con tutto il piano di ammortamento per il nostro mutuo, dal mese 1:
fino al mese 360:
Ecco che la tabella riepilogativa del nostro mutuo è infine completa:
Possiamo poi aggiungere un paio di grafici, ad esempio per confrontare l’impatto degli interessi rispetto al capitale preso a prestito nel corso dei 30 anni di mutuo:
Oppure per valutare la relazione, lungo le 360 mensilità, tra la quota capitale e la quota interessi e l’andamento del debito residuo:
Tutto molto bello e perfettamente funzionante. E puoi iniziare ad usarlo scaricando il file Excel, tramite il link in fondo alla pagina.
Però c’è un problema.
Eggià. Se anziché un mutuo di 30 anni, avessi bisogno di calcolarne uno da 10, 20 o 40, dovresti riaggiornare le tabelle con le formule, perché tutto è tarato su un piano di ammortamento da 360 mesi. Excel ha le istruzioni solo per questa durata!
Ma per te, ho anche la soluzione 😉
Il piano di ammortamento 100% automatico in Excel
Immagina una tabella che aggiunge o elimina righe in automatico, in base agli input che hai inserito.
Ora smetti di immaginare, perché è esattamente quello che troverai nel foglio “automatico” dell’Excel che ti condivido. Mai piu nessun problema di formule rotte o tabelle da sistemare.
Vuoi fare il piano di ammortamento di un mutuo di un solo anno? Nessun problema (nota due finezze: il bordo che viene aggiunto in automatico alla tabella e le righe sotto il mese 12 completamente vuote):
Ma se invece vuoi convertirlo in un mutuo di 20 anni? Tutto ok. Inserisci 20 nel campo “Durata (anni)” ed Excel pensa a tutto il resto:
Non ti spiego tutti i dettagli con cui sono costruite le formule (a prima vista complicatissime, in realtà molto meno!) del foglio “automatico” altrimenti questo articolo, già chilometrico, diventerebbe più lungo dell’autostrada A1.
Scaricalo, inizia a usarlo e fammi sapere cosa ne pensi. Devo però dirti che non è tutta farina del mio sacco. Mi hanno aiutato a realizzarlo due risorse validissime:
- un fantastico tutorial sulla formula Lambda pubblicato sul canale “Utilizzo professionale di Microsoft Office“: se come me sei appassionato di Excel, non puoi non iscriverti!
- il mio inseparabile compagno di avventure, Excel Agent Mode
Conclusioni
Cosa ci resta da fare, a questo punto?
Sicuramente devi usare il file, per scoprire quanto potresti pagare per il tuo prossimo mutuo (o quanto potresti risparmiare negoziando condizioni migliorative). Ma naturalmente per utilizzarlo devi prima scaricarla e quindi si, siamo giunti al momento tanto atteso…il link per scaricarlo!
👉🏼 SCARICA IL FILE EXCEL PER CALCOLARE LE RATE DEL MUTUO 👈🏼
Infine, l’ultima cosa da fare, è guardare il video YouTube collegato a questo articolo, per maggiori dettagli su come funziona il file. Guardalo e fammi sapere cosa ne pensi!
Buona visione! 👋🏼
[…] analisi del settore confermano che con un piano francese su durate medio-lunghe, è comune pagare oltre il 50% degli interessi totali del mutuo già nella prima metà del piano di rimborso, a volte raggiungendo il 60% nei primi 10 […]
Grazie per aver linkato il mio articolo. Buona giornata! 👋🏼
[…] elevato, causando un’impennata significativa della rata. Un esempio concreto rende l’idea: facendo un mutuo da 200.000€ per comprare casa, è realistico che nei primi anni oltre il 70-80% di ogni rata sia costituito da soli interessi. Un […]