Calcolo rata mutuo Excel

In questo articolo, vedremo come costruire un file Microsoft Excel in grado di dirci l’ammontare della rata con un piano mensile. Potremo utilizzare il modello per cambiare il numero di mesi, il tasso (fisso) utilizzato, l’ammontare e la data di inizio del rimborso.

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.

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

Prima parte: le ipotesi per il 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.

In questo caso, nella cella H4 inseriamo =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.

In questo caso, nella cella I4 inseriamo =-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.

Seconda parte: come creare un piano mensile di rimborso

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. Detto ciò:

La cella B9 corrisponde al numero di rata. Inseriamo =SE.ERRORE(SE(INDICE(B7:B9;1)=B$7;1;SE($D$4>=B8+1;B8+1;””));””). E’ 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. Inseriamo =SE.ERRORE(ANNO(D9);””) per restituire l’anno della rata che stiamo considerando. 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. Inseriamo =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. Inseriamo =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. Inseriamo =SE.ERRORE(H9-G9;””). Praticamente sarà la differenza fra la rata costante e gli interessi (G9)

Microsoft_Excel_Calcolo_Rata_Mutuo_Interessi

La cella G9 indica gli interessi della singola rata. Inseriamo =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. Inseriamo =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. Inseriamo =SE.ERRORE(-VAL.FUT($F$4/12;B9;-$I$4;$G$4);””). Approfondendo:

  • 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).

Terza parte: riepilogo del finanziamento

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.

1 commento su “Calcolo rata mutuo Excel

Lascia un commento

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