Vai al contenuto

Calcolo rata mutuo Excel

Scopriamo come costruire un file Microsoft Excel per il calcolo della rata di un mutuo con un piano mensile di rientro. Potremo utilizzare il modello per cambiare il numero di mesi, il tasso (fisso) utilizzato, l'ammontare e la data di inizio del rimborso.

Il file presenta 2 fogli: il primo compatibile con Excel 365 mentre il secondo funziona con tutte le versioni di Excel!

Se stai cercando un modo rapido per calcolare la rata del mutuo, puoi utilizzare il file interattivo. Inserendo i mesi, la data di inizio, il tasso fisso e l'ammontare del mutuo totale, potrai vedere velocemente a quanto ammonti la rata!


Calcolare la rata di un mutuo o di un finanziamento è fondamentale per essere certi di essere in grado di sostenere le spese per l'acquisto di una casa, di un'auto o di un'altra spesa (piccola o grande che sia). Con questo file, potrai verificare in autonomia come possa cambiare la rata secondo i diversi parametri illustrati in precedenza.

Puoi cliccare sul link seguente se stai cercando invece un modello per verificare la convenienza della surroga rispetto al mutuo attuale.

Microsoft_Excel_Calcolo_Rata_Mutuo

Se vuoi scoprire come è stato creato il file passo passo, ecco una rapida guida!

Come calcolare la rata del mutuo su Excel 365

PIANO_MUTUO_LAMBDA_EXCEL_365

La creazione delle funzioni LAMBDA ci permette di velocizzare enormemente la creazione di un piano di rientro, dato che potremo ridurre tutto il calcolo a una sola formula (complicata, ma efficace):

=LAMBDA(anni_mutuo;data_inizio;ammontare_totale;tasso; LET(rate_totali;anni_mutuo*12; num_rata;SEQUENZA(rate_totali); ammontare_rata;-RATA(tasso/12;rate_totali;ammontare_totale); date_rata;PERRIGA(num_rata;LAMBDA(x;DATA.MESE(data_inizio;x))); anno_rata;ANNO(date_rata); quota_interessi;PERRIGA(num_rata;LAMBDA(qi;-INTERESSI(tasso/12;qi;rate_totali;ammontare_totale))); quota_capitale;ammontare_rata-quota_interessi; ammontare_residuo;PERRIGA(num_rata;LAMBDA(ar;-VAL.FUT(tasso/12;ar;-ammontare_rata;ammontare_totale))); STACK.VERT(STACK.ORIZ("# Rata";"Anno";"Data";"Rata";"Quota Capitale";"Quota Interessi";"Ammontare Residuo"); STACK.ORIZ(num_rata;anno_rata;date_rata;SE(num_rata;ammontare_rata);quota_capitale;quota_interessi;ammontare_residuo))))

In particolare, la funzione chiede all'utente di inserire:

  • Gli anni del mutuo
  • La data di inizio del mutuo
  • L'ammontare totale del mutuo
  • Il tasso fisso considerato

per poi calcolare, in automatico e in un colpo solo, tutto il piano di rientro.

La funzione andrebbe descritta nel dettaglio, nel file che puoi scaricare in cima all'articolo troverai che si chiama PIANO_MUTUO quindi ti basterà inserire =PIANO_MUTUO per poi testarla!

Proviamo a fare chiarezza con i singoli pezzi della formula:

=LAMBDA(anni_mutuo;data_inizio;ammontare_totale;tasso;

Queste sono le 4 informazioni che l'utente dovrà inserire.

LET(rate_totali;anni_mutuo*12;

num_rata;SEQUENZA(rate_totali);

ammontare_rata;-RATA(tasso/12;rate_totali;ammontare_totale);

La funzione LET ci permette di semplificare i calcoli, quindi dichiariamo che le rate totali sono gli anni del mutuo (inserito dall'utente) moltiplicato per 12, le rate andranno da 1 al numero finale delle rate totali (quindi inseriamo SEQUENZA) mentre l'ammontare della rata sarà calcolato prendendo il tasso diviso per 12 (mensile), le rate totali e l'ammontare totale, che è sempre inserito dall'utente.

date_rata;PERRIGA(num_rata;LAMBDA(x;DATA.MESE(data_inizio;x)));

anno_rata;ANNO(date_rata);

quota_interessi;PERRIGA(num_rata;LAMBDA(qi;-INTERESSI(tasso/12;qi;rate_totali;ammontare_totale)));

quota_capitale;ammontare_rata-quota_interessi;

ammontare_residuo;PERRIGA(num_rata;LAMBDA(ar;-VAL.FUT(tasso/12;ar;-ammontare_rata;ammontare_totale)));

Qui la situazione si complica. PERRIGA ci permette di effettuare una singola operazione per ogni riga di un array, quindi ci aiuta molto dato che il piano di rientro del mutuo sarà composto da numerose rate.

Nel primo caso, PERRIGA ci restituisce le singole date della rata perchè, per ogni riga, sommerà alla data dell'inizio (con la funzione DATA.MESE) il numero di rate corrispondenti. Quindi per il primo pagamento sarà data di inizio + 1 mese, poi per la seconda sarà data di inizio + 2 mesi e così via fino alla conclusione.

Lo stesso succede per la quota interessi, che calcola gli interessi per ogni singola rata, così come per l'ammontare residuo che varierà ogni mese.

La quota capitale viene calcolata come differenza fra ammontare della rata e la quota interessi, mentre l'anno della rata non è altro che l'anno della data della rata considerata.

A questo punto il più è fatto, ma vogliamo visualizzare queste informazioni inserendo anche delle intestazioni per le singole colonne.

STACK.VERT(STACK.ORIZ("# Rata";"Anno";"Data";"Rata";"Quota Capitale";"Quota Interessi";"Ammontare Residuo"); STACK.ORIZ(num_rata;anno_rata;date_rata;SE(num_rata;ammontare_rata);quota_capitale;quota_interessi;ammontare_residuo))))

Le funzioni STACK.VERT e STACK.ORIZ ci aiutano a mettere in fila le informazioni che abbiamo calcolato prima. Avremo quindi prima di tutto le intestazioni e successivamente tutti i calcoli che abbiamo definito nei passaggi precedenti. STACK.VERT ci dice che la prima riga sarà quella delle intestazioni (il primo STACK.ORIZ) cui seguiranno tutti gli altri calcoli (il secondo STACK.ORIZ).

Per scoprire più nel dettaglio come creare una funzione LAMBDA da zero, fai riferimento a questo articolo!

Vediamo invece come si potrebbe fare se non avessimo 365, valutando ogni singolo passaggio singolarmente:

1. Ipotesi per il calcolo del prestito o finanziamento

In questo caso le ipotesi sono:

  • Mesi: durata totale del finanziamento o mutuo (C4)
  • Data di inizio: quando inizia il rimborso (D4)
  • Tasso: considerato costante per tutta la durata (E4)
  • Ammontare: il totale del mutuo richiesto (F4)

Per il momento, non usiamo ancora formule ma è consigliabile inserire la data di inizio come "dd/mm/yyyy" quindi (ad esempio) 01/07/2019 per agevolare i calcoli successivi.

Con queste ipotesi, possiamo iniziare a calcolare due valori importanti.

Data di fine: dobbiamo aggiungere 60 mesi alla data, ma se facessimo =D4+60 avremmo un errore poichè considera 60 giorni e non 60 mesi. Per avere la data di fine corretta dobbiamo utilizzare la funzione DATA.MESE, che aggiunge un numero determinato di mesi ad una data prestabilita. Ad esempio =DATA.MESE("01/02/2019";1) restituirà 01/03/2019 e così via.

H4 è =DATA.MESE(D4;C4)

Rata: per calcolare la rata di un mutuo, esiste una funzione dedicata chiamata RATA. La funzione agisce come segue: RATA(tasso;periodi;ammontare_totale). La funzione restituisce numeri negativi, quindi se li vogliamo positivi dovremo ricordarci di inserire un - (meno) nella formula. Inoltre, dato che parliamo di tasso mensile e abbiamo solo un tasso annuale, dobbiamo ricordarci di dividerlo per 12.

I4 è =-RATA(F4/12;D4;G4)

Le celle K4, L4 e M4 si completeranno quando il modello sarà finito e segnalano un breve spaccato riassuntivo delle spese da sostenere.

2. Calcolo di un piano di ammortamento per il mutuo

Ci sono diversi modi per creare questa parte. In questo caso, il modello prevede la stessa formula dalla prima riga (la 9) che viene poi trascinata nelle altre righe successive. Chiaramente si può anche creare un modello con formule differenziate per la prima riga e poi a seguire dalla seconda riga in poi con altre funzioni.

Iniziamo con la cella B9, che corrisponde al numero di rata.

B9 è =SE.ERRORE(SE(INDICE(B7:B9;1)=B$7;1;SE($D$4>=B8+1;B8+1;""));"").

Quella precedente è una formula apparentemente complessa che ci dice:

  • SE.ERRORE: se c'è un errore, inseriamo il vuoto ("") finale
  • SE (primo): se il primo valore individuato nell'intervallo da B7 a B9 è uguale a B$7, che rimane bloccata (INDICE(B7:B9;1)=B$7) allora inseriamo 1, altrimenti...
  • SE (secondo): se il primo valore non è pari a B$7, partiamo con il secondo SE. In questo caso, se il totale dei mesi ($D$4) è maggiore o uguale a B8+1 (il mese precedente, dato che siamo in B9) allora aggiungiamo un mese (B8+1), altrimenti restituisci vuoto

La cella C9 corrisponde all'anno della rata.

C9 è =SE.ERRORE(ANNO(D9);"")

La funzione ANNO prende una data scritta in formato data (quindi "dd/mm/yyyy" come spiegato prima) e ne restituisce solo l'anno. Se c'è un errore, avremo anche in questo caso vuoto.

La cella D9 corrisponde al mese della rata.

D9 è =SE(B9="";"";DATA.MESE($E$4;B9))

Quindi se B9 è vuota, inseriamo vuoto. Se invece non è vuota, utilizziamo nuovamente la funzione DATA.MESE per aggiungere alla data iniziale ($E$4) il numero di mesi che ci interessa, che è pari al numero di rata (B9).

La cella E9 corrisponde al tasso del nostro finanziamento.

E9 è =SE(B9="";"";$F$4)

Se B9 è vuota, allora restituisce vuoto. Altrimenti, restituisce il valore del nostro tasso ($F$4)

La cella F9 segnala la quota capitale della singola rata.

F9 è =SE.ERRORE(H9-G9;"")

Praticamente questo valore sarà la differenza fra la rata costante e gli interessi (G9)

Microsoft_Excel_Calcolo_Rata_Mutuo_Interessi

Continuando, G9 indica gli interessi della singola rata.

G9 è =SE.ERRORE(-INTERESSI($F$4/12;B9;$D$4;$G$4);"").

Andiamo più nel dettaglio:

  • SE.ERRORE restituisce vuoto se ci sono errori nella formula
  • La formula INTERESSI ci aiuta nel calcolare l'interesse dato che è segnata come INTERESSI(tasso;periodo;periodi;valore_attuale). Quindi in questo caso abbiamo il tasso mensile ($F$4/12), il periodo (il numero di rata, B9), il totale dei mesi (D4) e l'ammontare totale iniziale (G4). Come nel caso della rata, per avere un valore positivo dovremo inserire un - (meno) prima della formula

La cella H9 indica l'ammontare della rata.

H9 è =SE(D9="";"";$I$4).

Se D9 è vuota, allora inseriamo vuota, altrimenti restituiamo il valore della rata costante calcolata in precedenza in I4.

Microsoft_Excel_Calcolo_Rata_Mutuo_Ammontare_Formula

La cella I9 corrisponde a quanto resti da rimborsare sul nostro mutuo.

I9 è =SE.ERRORE(-VAL.FUT($F$4/12;B9;-$I$4;$G$4);"")

Approfondendo il funzionamento della formula:

  • SE.ERRORE è come sopra, se c'è un errore restituisce vuoto
  • VAL.FUT calcola il valore futuro di un finanziamento come VAL.FUT(tasso;periodo;periodi;pagamento;valore_attuale). Inseriamo il tasso mensile ($F4/12), il periodo (il numero di rata, B9), il pagamento (I4, inserito con il -, meno) e l'ammontare totale iniziale (G4).

3. Conclusione del calcolo di rata del mutuo

Inseriamo in K4 il totale di quanto spenderemo (singolo pagamento moltiplicato per numero di rate, D4*I4), in L4 l'ammontare iniziale (pari a G4) e in M4 il totale degli interessi (calcolato come differenza, K4-G4). Ora il file è concluso!

Inoltre, un ottimo strumento online per calcolare il costo di un finanziamento è Prestitionline che segnala già uno spaccato di quanto sia la rata mensile a seconda di vari parametri, nel caso preferissi controllare subito senza usare il file Excel.

9 commenti su “Calcolo rata mutuo Excel”

  1. Ciao Marco, molto interessante e utile.
    Perché non aggiungi l'opzione per la restituzione anticipata di una quota del capitale in un'unica soluzione?

  2. Ottimo file, sarebbe possibile poter scorporare dal mutuo gli interessi relativi solo all'acquisto della prima casa? Mi spiego meglio, la casa costa 50.000 ma il cliente chiede alla banca 58000 e quindi nell'atto di mutuo il notaio scrive che 50.000 sono per l'acquisto prima casa mentre 8.000 per le esigenze personali. Il direttore della banca dice che manderà il solito riepilogo degli interessi sul totale di 58.000 e poi il cliente deve andare dal commercialista per scorporare perché non può portare annualmente in detrazione gli interessi relativi agli 8.000 euro....

    1. Ciao Alexey,

      grazie per il feedback e per la domanda!

      Non sono certo di aver compreso 100% - in questo caso tu chiedi di più rispetto al mutuo totale giusto? Se inserissi 50K nel modello non sarebbe uguale al risultato che stai cercando?

      Attendo tue così nel caso aggiorno il file con quest'altra casistica, se (come hai fatto) mi fai degli esempi con dei numeri posso entrare meglio nel meccanismo pur non essendo un tecnico.

      A presto,

      Marco

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *