Calcolare surroga mutuo Excel

    Microsoft_Excel_Calcolare_Surroga_Mutuo_Report

    In questa guida scopriremo come funziona la surroga del mutuo e come confrontare una eventuale surroga e l'attuale mutuo su un file Excel.

    Microsoft_Excel_Calcolare_Surroga_Mutuo_Report

    Puoi scaricare il file al seguente link per utilizzarlo anche terminata la lettura (versione Excel 365 o Excel 2019 / versione per Excel precedenti a 2019).

    Attualmente i tassi sono piuttosto bassi e potrebbe risultare conveniente cambiare banca facendo la cosiddetta surroga del mutuo. La surroga significa che un finanziamento esistente viene trasferito a un altro istituto, che può scegliere quali condizioni offrire alla clientela. Bisogna quindi determinare se il tasso offerto dalla nuova banca sia inferiore a quello attuale, come vedremo in questo caso pratico. Pur essendo un modello accurato, l'obiettivo è di dare una stima qualificata relativamente alla convenienza della surroga rispetto al mutuo esistente. Il suggerimento è di controllare attentamente le condizioni prima di prendere una decisione definitiva.

    Puoi informarti su come creare un piano di ammortamento al seguente link, scoprendo come calcolare la rata di un mutuo su Excel.

    In questo articolo, non tratteremo casi particolari collegati con polizze o simili per evitare di rendere il modello più complesso. Tendenzialmente la banca dovrebbe dare un valore definito che potremo utilizzare per i nostri calcoli.

    Calcolare surroga mutuo Excel: piano ammortamento mutuo esistente

    Microsoft_Excel_Calcolare_Surroga_Mutuo_Piano_Ammortamento

    Utilizzeremo una struttura molto simile a quella dell'articolo per il calcolo della rata del mutuo, recuperando alcune informazioni chiave quali:

    Mesi: durata totale del finanziamento o mutuo (D7). In questo caso, ipotizziamo 10 anni con 120 mesi di mutuo

    Data di inizio mutuo: quando inizia il rimborso (E7). La data è fissata per il 07/07/2019

    Tasso: considerato costante per tutta la durata (F7) e pari a 3,00%

    Ammontare: il totale del mutuo richiesto (G7), in questo caso pari a € 100.000

    Con queste informazioni, possiamo calcolare l'intero piano di ammortamento che sul file può essere consultato dalla riga 23 alla riga 330. Se vi sono dei passaggi delle formule che non sono chiari, si può sempre fare riferimento al file dedicato sul calcolo del piano di ammortamento.

    Fermo restando che abbiamo calcolato un piano di ammortamento per il mutuo già in essere, ora dobbiamo verificare se e quanto ci possa convenire la surroga con il secondo istituto di credito che stiamo valutando.

    Calcolare surroga mutuo Excel: piano ammortamento surroga

    Microsoft_Excel_Calcolare_Surroga_Mutuo_Cambiamento

    Relativamente alla surroga, ci interessano solo due valori fondamentali.

    Data di inizio: quando pensiamo avvenga il passaggio del mutuo verso la seconda banca (L7). In questo caso, 01/12/2019

    Tasso: il nuovo tasso offerto dalla banca (M7). Consideriamo 1,50%

    Ora possiamo cominciare con i calcoli per verificare la convenienza della surroga e creare un piano di ammortamento dedicato.

    Data di inizio della surroga del mutuo

    Surroga attiva da: per calcolare il nuovo finanziamento, dobbiamo sapere quale sarà la data dell'ultima rata nel primo istituto. Questo determinerà il nostro ammontare residuo che sarà calcolato per l'intera surroga. La cella di riferimento sarà la P25.

    Dividiamo la formula in P25 per evidenziare i singoli componenti*:

    =PIÙ.SE(

    Per evitare di fare errori, dobbiamo verificare due casi.

    Caso 1. Se la data di inizio fosse fissata per il giorno 07/07/2019 e avessimo il subentro al giorno 13/11/2019, l'ultima rata della prima banca originaria del mutuo sarebbe con scadenza al 07/11/2019.

    Caso 2. Se la data di inizio fosse fissata per il giorno 07/07/2019 e il subentro fosse il giorno 01/11/2019, allora l'ultima rata che dovremmo considerare nella prima banca sarebbe fissata al 07/10/2019.

    GIORNO(L7)>GIORNO(E7);

    Se il giorno del mese della surroga è superiore al giorno del mese della prima rata, allora possiamo procedere con il primo caso.

    L7-GIORNO(L7)+GIORNO(E7);

    Come funziona la formula? In questo caso prendiamo la data della surroga (L7) cui togliamo il giorno del mese della stessa data. In questo modo avremo l'ultimo giorno del mese precedente, cui aggiungiamo il giorno del mese con la scadenza delle rate del mutuo in E7. Avendo fissato la data di inizio al 7 di un determinato mese, avremo quindi la rata fissata correttamente al giorno 7 mantenendo coerenza con il piano di ammortamento precedente.

    GIORNO(L7)<=GIORNO(E7);

    Caso numero due. Dobbiamo eseguire un altro calcolo per prendere l'ultima rata del mese precedente, dato che in questo caso il giorno della surroga è inferiore o uguale alla data del mutuo.

    DATA.MESE(L7-GIORNO(L7)+GIORNO(E7);-1))

    La formula è leggermente più complessa in questo caso. Inserita nella funzione DATA.MESE vediamo la stessa operazione precedente del caso 1, ma con la funzione DATA.MESE possiamo rimuovere un mese (-1) in modo da ottenere l'ultima rata del mese precedente e fissare la formula corretta.

    * Nel caso avessi una versione di Excel meno recente e non disponessi di PIÙ.SE, puoi utilizzare dei SE concatenati per arrivare allo stesso risultato.

    Ammontare totale della surroga del mutuo

    Ammontare: dobbiamo determinare quale sia l'ammontare restante per calcolare la surroga. Cella di riferimento: Q25.

    =INDICE($I$31:$I$330;CONFRONTA(P25;$D$31:$D$330;0))

    Con un semplice INDICE + CONFRONTA andiamo a recuperare il valore dell'ammontare corrispondente a P25, ovvero l'ultima rata del mutuo con la prima banca.

    Numero di rate della surroga

    Mesi surroga: dobbiamo anche calcolare quante rate siano ancora parte del finanziamento, prendendo i valori dal mutuo precedente. Stavolta andremo a inserire la formula in O25.

    =D7-INDICE($B$31:$B$330;CONFRONTA(Q25;$I$31:$I$330;0))

    Come funziona la formula? Prendiamo il valore delle rate totali (D7) cui andiamo a sottrarre, tramite INDICE + CONFRONTA, il numero di rate che sono state già saldate con la prima banca.

    L'intero piano di ammortamento viene calcolato come in precedenza per il singolo mutuo, senza necessità di formule differenziate.

    Convenienza del mutuo rispetto alla surroga

    Microsoft_Excel_Calcolare_Surroga_Mutuo_Convenienza

    Abbiamo terminato la parte di definizione del modello. Dobbiamo però offrire uno specchietto riassuntivo per determinare se sia più conveniente rimanere con la banca attuale o passare al nuovo istituto di credito. Come regola generale, sappiamo che un tasso inferiore nel secondo istituto ci porterà a godere di un risparmio nel caso della surroga. Viceversa, un tasso superiore ci porterà a non cambiare istituto. Vediamo come quale sia il risparmio per singola rata e sul totale del finanziamento.

    Inseriamo queste considerazioni con una formula nella cella C10 evidenziandone le singole parti:

    =PIÙ.SE(

    Come dicevamo, abbiamo due casi. Un caso nel quale la surroga è conveniente e uno nel quale è preferibile rimanere con lo stesso istituto.

    M7<F7;

    Il tasso della surroga è inferiore al tasso attuale. Quindi possiamo risparmiare e il suggerimento è quello di effettuare la surroga.

    =CONCAT("In questo caso, la surroga è conveniente e permette di risparmiare ";TESTO(D25-K25;"€ #,##");" per mese.";CODICE.CARATT(10);CODICE.CARATT(10);" In particolare, il totale di risparmio sulle ";O25;" rate rimanenti sarà pari a circa ";TESTO((D25-K25)*O25;"€ #.###,#0");".");

    Il commento è creato con la funzione CONCAT che unisce diverse parti di testo, avremmo potuto utilizzare anche CONCATENA che è la versione precedente della stessa funzione. Iniziamo dicendo che la surroga è conveniente, inserendo il valore di risparmio sulla singola rata. Questo viene calcolato facendo la differenza fra le due rate (D25-K25) e viene formattato con la funzione TESTO per restituire la valuta per rendere il report riassuntivo più leggibile. CODICE.CARATT aggiunge due righe vuote per migliorare la visualizzazione, ma non è fondamentale.

    Nelle righe finali, evidenziamo come inseriamo il numero totale di rate restanti (O25) e restituiamo nuovamente un valore formattato con TESTO, in questo caso ottenuto moltiplicando il risparmio per la singola rata (D25-K25) moltiplicato per le rate restanti (O25). Il punto finale è solo per una questione estetica, in modo da chiudere il report.

    M7>=F7;"La surroga non è conveniente ed è consigliabile rimanere con il mutuo attuale.")

    Come abbiamo visto, nel caso abbiamo un valore equivalente o superiore con la surroga rispetto al mutuo iniziale, non è conveniente fare cambiamenti e questo viene evidenziato dal report riassuntivo.

    Formattazione condizionale per il mutuo

    Microsoft_Excel_Calcolare_Surroga_Mutuo_Formattazione_Condizionale

    Per rendere il file più leggibile, è anche presente una regola di formattazione condizionale sul piano del mutuo che rende più visibili le rate effettivamente pagate e oscura quelle successive poichè saranno parte della surroga. Per questo motivo alcune saranno in grigio e altre con sfondo bianco a seconda dei casi utilizzati.

    =$D31>=$M$31

    In questo caso, consideriamo solamente le celle per le singole righe che abbiano una data maggiore o uguale a quella di inizio surroga. In questo modo, possiamo aiutare la visualizzazione.

    Conclusione e download file per calcolare la surroga

    Per calcolare se convenga la surroga oppure il mutuo, puoi scaricare il file cliccando sul pulsante e troverai il file esattamente come è stato descritto nell'articolo.

    Lascia un commento

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