Calcolo rata mutuo Excel

In questo articolo, vedremo come costruire un file Microsoft Excel per il calcolo della rata di un mutuo 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.

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!

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

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.

1 commento su “Calcolo rata mutuo Excel”

Lascia un commento

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