Vai al contenuto

Calcolo rata mutuo Excel

PIANO_MUTUO_LAMBDA_EXCEL_365

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. Se hai Excel 365, potrai anche ipotizzare un mutuo con un rimborso parziale o totale!

Il file presenta diversi fogli che racchiudono i vari casi - solamente l'ultimo è compatibile con le versioni pre-365!

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.

Questa guida si basa sul meccanismo di ammortamento alla francese, chiamato anche a rate costanti, quindi avrai sempre una rata costante per tutta la durata del mutuo. Per saperne di più, puoi consultare questa voce dedicata proprio all'ammortamento alla francese.

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

Il calcolo di un piano di mutuo può essere fatto in diversi modi su Excel, ma utilizzare le ultime funzioni lo rende decisamente più veloce. Con una sola formula, potremo creare un intero piano di ammortamento, ottenendo una simulazione dinamica a seconda delle informazioni che inseriremo.

Come detto, 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!

Questo va benissimo se pensiamo di mantenere il mutuo a rate costanti per tutta la durata del mutuo - ma come cambierebbe la simulazione se pensassimo di estinguere il mutuo, interamente o parzialmente, in un dato mese?

Come calcolare l'estinzione parziale del mutuo su Excel

CALCOLO_RATA_MUTUO_EXCEL_ESTINZIONE_ANTICIPATA_FORMULA

Vediamo come calcolare l'estinzione parziale del mutuo su Excel. La formula è una nuova LAMBDA chiamata PIANO_MUTUO_RIMBORSO_PARZIALE che sfrutta la LAMBDA precedente. In particolare, ho creato una nuova LAMBDA chiamata PIANO_MUTUO_X, che trovi nel file, molto simile alla LAMBDA per calcolare il mutuo. L'unico elemento che ho eliminato è stato l'inserimento delle intestazioni, poichè in questo caso mi interessa solamente sfruttare i numeri per poi calcolare l'intero ammortamento. Vedrai quindi che il riferimento sarà a questa formula, PIANO_MUTUO_X, e non a PIANO_MUTUO.

Detto ciò, la formula è sicuramente un po' complessa, ma anche in questo caso ci restituisce tutte le informazioni in un colpo solo:

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

= LAMBDA(anni;inizio;ammontare;tasso;rimborso_mese;rimborso;tasso_nuovo;

LET(

    rate_rimanenti; (anni * 12 - rimborso_mese) / 12;

    data_rimborso; DATA.MESE(inizio; rimborso_mese);

    primo_mutuo; INCLUDI(PIANO_MUTUO_X(anni; inizio; ammontare; tasso); rimborso_mese);

    rimborso_new; INCLUDI(INDICE(primo_mutuo; 0; 7); -1) - rimborso;

    SE.ERRORE(

        STACK.VERT(

            primo_mutuo;

            SE(

                rimborso_new <= 0;

                "ESTINTO";

                PIANO_MUTUO_X(rate_rimanenti; data_rimborso; rimborso_new; tasso_nuovo)

            )

        );

        ""

    )

)

Le informazioni da inserire per ottenere il risultato sono queste:

LAMBDA(anni;inizio;ammontare;tasso;rimborso_mese;rimborso;tasso_nuovo;

  • Anni: il numero di anni previsti per il nostro mutuo
  • Inizio: la data di inizio del nostro mutuo
  • Ammontare: l'ammontare totale del nostro mutuo
  • Tasso: il tasso ipotizzato
  • Rimborso_mese: il mese del mutuo al quale ci immaginiamo di fare un rimborso parziale
  • Rimborso: l'ammontare previsto del nostro rimborso parziale
  • Tasso_nuovo: il nuovo tasso ipotizzato. Se è lo stesso mutuo, inserisci pure lo stesso valore precedente

LET(

    rate_rimanenti; (anni * 12 - rimborso_mese) / 12;

    data_rimborso; DATA.MESE(inizio; rimborso_mese);

    primo_mutuo; INCLUDI(PIANO_MUTUO_X(anni; inizio; ammontare; tasso); rimborso_mese);

    rimborso_new; INCLUDI(INDICE(primo_mutuo; 0; 7); -1) - rimborso;

A questo punto, calcoliamo le rate rimanenti moltiplicando il numero di anni del nostro mutuo iniziale per 12. Sottraiamo il mese che immaginiamo sia quello del rimborso e poi dividiamo il risultato per 12. Ad esempio, se il mutuo fosse di 3 anni e facessimo il rimborso parziale dopo 10, la formula sarebbe = (3*12-10)/12 = (26/12). La nostra LAMBDA PIANO_MUTUO_X moltiplica poi questo valore nuovamente per 12, quindi ci restituisce che 10 sono le rate concluse del primo mutuo, mentre 26 saranno quelle post-iniezione di capitale.

La data del rimborso è calcolata come differenza fra il giorno di inizio e il numero di mesi che abbiamo inserito, ovvero rimborso_mese. In questo caso possiamo usare la funzione DATA.MESE che somma un certo numero di mesi a una data specifica. Ad esempio =DATA.MESE(01/01/2024;3) ci restituisce 01/04/2024, perchè si muove di tre mesi in avanti restituendo il primo di Aprile dal primo di Gennaio.

Il primo mutuo si calcola automaticamente con la funzione PIANO_MUTUO_X. In questo caso, però, vogliamo prendere esclusivamente i mesi fino al mese del nostro rimborso. Possiamo quindi utilizzare la funzione INCLUDI, che prende un array e seleziona esclusivamente i primi X o gli ultimi X risultati. In questo caso, INCLUDI pesca i mesi del nostro rimborso_mese, quindi è variabile: se facciamo il rimborso dopo 10 mesi, prenderà esclusivamente i primi 10 mesi e ignorerà quelli successivi.

Ci manca il valore della quota da rimborsare dopo che abbiamo inserito il nostro rimborso parziale. In questo caso possiamo fare INDICE(primo_mutuo;0;7) perchè considera esclusivamente l'ultima colonna del nostro array, ovvero quella dei rimborsi. Mettendo il valore -1, con INCLUDI, significa che vogliamo esclusivamente l'ultimo valore dal basso, mentre 1 vorrebbe dire il primo valore dall'alto. Questa è la quota capitale ancora da rimborsare, cui toglieremo il valore del nostro rimborso (parziale o totale).

SE.ERRORE(

        STACK.VERT(

            primo_mutuo;

            SE(

                rimborso_new <= 0;

                "ESTINTO";

                PIANO_MUTUO_X(rate_rimanenti; data_rimborso; rimborso_new; tasso_nuovo)

            )

        );

        "")

Vediamo ora come si può combinare tutto in un colpo solo. STACK.VERT ci permette di impilare uno o più array, quindi prendiamo prima di tutto il risultato del nostro primo mutuo, dato che vogliamo visualizzare le rate prima della nostra estinzione. A questo punto possiamo inserire una condizione dove, se il rimborso che ipotizziamo è superiore o uguale alla quota capitale rimanente, la funzione faccia apparire "ESTINTO" anzichè effettuare il calcolo, grazie alla funzione SE. Se invece il rimborso non copre completamente la quota capitale, avremo, sotto il nostro primo mutuo, un nuovo secondo mutuo fittizio che prosegue il primo, calcolando però i valori corretti. Avremo infatti il numero di rate rimanenti post estinzione, la data del rimborso come prima data post estinzione, la quota capitale totale sarà quella calcolata in precedenza, togliendo il nostro rimborso e il tasso sarà quello attuale più aggiornato.

CALCOLO_RATA_MUTUO_EXCEL_FORMATTAZIONE_CONDIZIONALE

Ho poi inserito due regole di formattazione condizionale per visualizzare meglio dove finisca un mutuo e dove "cominci" l'altro. Vedrai che l'ultima rata pre-estinzione è con uno sfondo viola, mentre la prima post-estinzione è con uno sfondo giallo. Le regole di formattazione condizionale sono le stesse: devono esserci valori sia nella cella sia in quella successiva, ma la cella precedente (N7) deve essere superiore alla successiva (N8). L'unica cosa che cambia è che una comincia da $N$8 (quella gialla) mentre l'altra comincia da $N$7 (quella viola). In questo modo, la cella di riferimento per la formattazione condizionale varia leggermente

Come scoprire il valore necessario per l'estinzione anticipata del mutuo a un dato mese

CALCOLO_RATA_MUTUO_EXCEL_ESTINZIONE_ANTICIPATA

Potremmo voler sapere a quanto ammonti il valore del rimborso necessario per estinguere il mutuo. Vediamo come fare:

=SE.ERRORE(ARROTONDA.ECCESSO.MAT(INDICE(INDICE(B7#;0;7);N3);1);"")

In questo caso stiamo sempre selezionando il valore finale del nostro mutuo al mese specificato, indicato dalla cella N3. Questo viene calcolato grazie a INDICE(B7#;0;7). Il secondo INDICE ci serve perchè vogliamo solamente il mese che ci interessa, ovvero quello di N3. Così facendo, avremo solamente il valore al mese 10 del nostro intervallo, se facciamo il rimborso al mese 10. Ho inserito poi ARROTONDA.ECCESSO.MAT per visualizzare il valore come intero, in modo da avere una cifra precisa da inserire per l'estinzione totale.

Ringrazio Matteo Zorzoli per avermi supportato nella revisione del file, in particolare nella fase di brainstorming per verificare che tutte le formule fossero corrette!

Come calcolare la rata del mutuo a tasso variabile con Excel

CALCOLO_RATA_MUTUO_EXCEL_TASSO_VARIABILE

Vediamo adesso come calcolare la rata del mutuo a tasso variabile con Excel. Questo caso è particolarmente intricato perchè ci potrebbero essere numerose variazioni durante il periodo considerato, che un utente esperto potrebbe ottenere direttamente da una tabella dedicata con il tasso Euribor mensile. Dato che sono proiezioni nel futuro, risulta comunque complesso effettuare una stima precisa di quanto si andrà effettivamente a spendere, differentemente dal caso del mutuo a tasso fisso.

Nella colonna J potrai inserire i nuovi tassi, per ogni rata che ti interessa. Lo stesso potrai fare nella colonna K, per inserire eventuali rimborsi parziali o totali. Vediamo ora il procedimento più nel dettaglio.

Vediamo come ottenere il numero della rata:

=SEQUENZA(E3*12)

Stiamo creando una sequenza di numeri che moltiplichi gli anni di mutuo per 12.

Per visualizzare l'anno di ogni singola rata il calcolo è più semplice:

=ANNO(D13#)

Avendo la data completa in D13#, prendiamo solamente l'anno.

Più complesso il calcolo della singola rata:

=LET(rata;B13#;

SE.ERRORE(SE(I13#<>"";-RATA(I13#/12;CONTA.NUMERI(rata)-rata+1;SCARTO(rata;-1;6));"");""))

Vediamo i singoli passaggi:

  • Definiamo la rata come B13#, ovvero il numero di rata che ci interessa
  • Se il tasso di interesse (I13#) è diverso da vuoto, allora calcoliamo la rata attuale
  • La rata viene calcolata prendendo il tasso attuale diviso per 12
  • I periodi totali sono le rate che ci rimangono. Se guardiamo al mese 32 di un mutuo da 120 mesi, avremo infatti 120-32+1, cioè 89. Questo perchè CONTA.NUMERI(rata) prende tutte le rate totali (120), toglie il valore attuale (32) e aggiunge 1
  • Il valore attuale viene calcolato cercandolo sul foglio a partire dalla rata. SCARTO(rata;-1;6) significa che ci stiamo spostando sopra di una riga e di sei colonne verso destra - recuperando quindi l'ammontare residuo del mese precedente
  • Se tutto questo calcolo riporta a un errore, inseriamo vuoto

Calcoliamo poi la quota capitale come differenza:

=SE.ERRORE(E13#-G13#;"")

Faremo infatti, semplicemente una differenza fra il valore della rata totale e degli interessi.

Interessi che calcoliamo così:

=SE.ERRORE(PERRIGA(B13#;LAMBDA(x;SCARTO(x;-1;6)))*I13#/12;"")

Per ognuna delle righe di B13#, vogliamo far sì che venga recuperato il valore del capitale residuo precedente, ottenuto con la funzione SCARTO. Questo viene poi moltiplicato per il tasso attuale e diviso per 12.

Vediamo ora come calcolare l'ammontare residuo:

=LET(rata;B13#;ammontare;

PERRIGA(rata;LAMBDA(x;SCARTO(x;-1;6)-SCARTO(x;0;4)-SCARTO(x;0;9)));SE.ERRORE(SE(ammontare<0;0;ammontare);""))

Utilizziamo il numero della rata in B13# come bussola. Praticamente stiamo dicendo che vogliamo il valore dell'ammontare del mese precedente, cui sottrarre, tramite SCARTO(x;0;4), la quota capitale del mese scelto. Potremmo però dover togliere anche la quota inserita nella colonna K a rimborso parziale o totale, indicata con SCARTO(x;0;9), quindi sottraiamo anche questo valore. Se l'ammontare finale è inferiore a 0, inseriamo 0, altrimenti l'ammontare che ci interessa.

Vediamo poi come definire il tasso della rata corrente:

=LET(rata;B13#;tasso;

PERRIGA(rata;LAMBDA(x;LET(tasso_inserito;SCARTO(x;0;8);tasso_precedente;SCARTO(x;-1;7);

SE(tasso_inserito<>"";tasso_inserito;tasso_precedente))));tasso)

Anche qui partiamo dal numero della rata per orientarci sul foglio. Per ognuna delle rate, prenderemo il tasso del mese precedente e quello eventualmente inserito a mano. Se non ci sono nuovi tassi da aggiungere, inseriremo il tasso del mese precedente - altrimenti prenderemo il nuovo tasso.

Con questo abbiamo completato il nostro modello! Ricordati però di tenere a mente questi accorgimenti, quando usi il file:

  • Il calcolo di questo mutuo a tasso variabile richiede che il file abbia il calcolo iterativo attivo, quindi controlla sempre che ci sia altrimenti i risultati saranno imprecisi. Se sei in dubbio, vai su File > Opzioni > Formule > Attiva calcolo iterativo e metti come valore massimo 1000. Questo è fondamentale perchè alcuni elementi vengono utilizzati in maniera ricorsiva, come hai visto dai calcoli precedenti
  • Proprio perchè c'è il calcolo iterativo, se vedi che alcuni risultati sono poco accurati, verifica di avere un numero alto nella parte di calcolo iterativo. Se così non è, puoi anche calcolare due volte di fila il foglio andando su Formule > Calcolo foglio > Calcola
  • Verifica che i dati siano corretti anche quando cambi l'orizzonte temporale, dato che i dati inseriti nelle colonne J e K sono inseriti a mano, quindi vanno cancellati per effettuare altre simulazioni

Nella parte alta del foglio troverai comunque un riassunto delle varie operazioni di cambio dei tassi e dei rimborsi effettuati.

Come inserire le ipotesi per il calcolo del prestito o del finanziamento

Vediamo come arrivare alla creazione della rata del mutuo se non abbiamo Excel 365. 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.

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

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!

21 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?

    1. Ciao Alessandro,

      grazie davvero per lo spunto - ho aggiornato la guida, troverai ora una soluzione che prevede proprio questa casistica!

      Puoi infatti calcolare la restituzione parziale o totale del mutuo, in un foglio dedicato.

      Grazie mille e a presto,

      Marco

      1. Ciao Marco,

        prima di tutto vorrei ringraziarti per questo file per calcolare la rata del mutuo! Mi è stato veramente utile.

        Vedo che nel commento precedente Alessandro ti chiedeva di aggiungere l’opzione per la restituzione anticipata di una quota del capitale in un’unica soluzione. In effetti sarebbe bello poter scrivere la quantità del presunto rimborso anticipato, ed in automatico ottenere il ricalcolo del piano di ammortamento (per quei casi che prevedono la riduzione dell’importo delle rate sucessive).

        Volevo sapere gentilmente, se hai avuto modo per realizzare un file simile? Sarei molto grata!

        Grazie mille!

        1. Ciao Tetiana,

          grazie mille a te per la domanda e per il feedback!

          Ho aggiornato il file quindi ora puoi verificare anch il caso nel quale c'è la restituzione anticipata (totale o parziale) del mutuo, in un colpo solo - come sempre, ti serve Excel 365 ma volendo puoi anche caricare il file nella versione di Excel online che è completamente gratuita.

          Spero che ti sia utile!

          A presto,

          Marco

  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

  3. Strumento molto utile. Sarebbe interessante poter inserire le variazioni del tasso di interesse durante il periodo di ammortamento...cosa che sarebbe utile soprattutto per i mutui a tasso variabile

    1. Ciao Luigi,

      grazie per lo spunto - ho inserito il caso del mutuo a tasso variabile che immagino possa aiutarti, basterebbe inserire una condizione SE nella parte di inserimento per evitare che il tasso superi una certa quota!

      Spero di esserti stato utile, poi se c'è bisogno vediamo più nello specifico, fammi sapere!

      A presto,

      Marco

  4. Complimenti Marco, molto ben fatto!
    Per caso hai considerato di implementare nel file l'opzione di tasso variabile, ovvero che cambiando il tasso a partire di un mese si aggiorna? Ad oggi le formule sono impostate sul valore iniziale e quindi non permette di variare le rate sulla base di nuovi tassi.
    E secondo elemento, hai considerato di implementare nella soluzione l'opzione di inserimento di capitale ad un certo punto del mutuo e riflettere nei mesi seguenti come questo evolve?
    Grazie mille,
    Marco

    1. Ciao Marco,

      grazie per la domanda, prima di tutto!

      Ho aggiornato la guida inserendo entrambi i casi, è un procedimento più complesso che trovi realizzato nel foglio dedicato al mutuo a tasso variabile!

      Spero di esserti stato utile!

      A presto,

      Marco

    1. Ciao Laura,

      grazie per il feedback e per la domanda!

      Sto realizzando un file più complesso per il mutuo a tasso variabile dove è possibile questa integrazione - con il file della guida ti direi di fare così, probabilmente aggiornerò anche la guida:

      - Calcoli il primo mutuo con PIANO_MUTUO_X, che ti restituisce tutti i dati ma senza le intestazioni. Tipo, se pensi che l'iniezione sia dopo 24 mesi, consideri solo questi 24 mesi

      A questo punto prendi la quota capitale che devi restituire al mese 24, cui togli l'ammontare che desideri restituire.

      - Calcoli un secondo mutuo con PIANO_MUTUO_X, considerando come quota capitale quella che hai appena calcolato

      Ripeti poi il procedimento anche per la terza iniezione, la quarta e così via. Magari non è elegantissimo, ma dovresti arrivare alla soluzione.

      Spero di esserti stato utile!

      A presto,

      Marco

Lascia un commento

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