Piano di ammortamento di un mutuo variabile: il modello Excel

di Gianfranco Budano

scritto il

Come calcolare il piano di ammortamento di un mutuo a tasso variabile legato all'Euribor prelevando i dati automaticamente dal Web: modello Excel da personalizzare e scaricare.

Realizzare piani di ammortamento per prestiti è un’operazione semplice: Microsoft Excel dispone di un modello deputato a tale scopo, utilizzato anche dalle aziende. Tuttavia, quando si tratta di mutui variabili (il tasso è dato dalla somma tra indice Euribor nel periodo di riferimento + spread determinato in fase di contrattazione) la cosa si complica, perché il calcolo della rata deve essere aggiornato di volta in volta al periodo.

=> Come scegliere il mutuo: fisso, variabile e surroga

Ammortamento rate variabili

In questo tutorial vediamo come applicare degli automatismi al modello per il piano di ammortamento di un finanziamento: in sostanza si personalizza in modo tale da ottenere l’aggiornamento del tasso – e di conseguenza della rata – tramite una connessione a Internet e un pulsante di refreshRealizzeremo il piano supponendo, come esempio, di aver sottoscritto un contratto di mutuo di 10mila euro in 10 anni, con rata semestrale a interesse variabile legato all’Euribor e spread del 2% annuo (che equivale all’1% semestrale).

=> Scarica il modello Excel per il Piano di ammortamento

Lanciamo Excel e, dal bottone Office, creiamo un nuovo file da “Modelli installati”; selezionindo “Ammortamento prestito” e clicchiamo su “Crea”. Verrà generato un foglio contenente il piano di ammortamento di un prestito a tasso fisso, con alcune celle bloccate per impedire che vengano alterate. L’utente ha a disposizione solo la possibilità di inserire i dati nei campi vuoti, mentre il foglio genererà in automatico le rate. All’inizio il nostro piano di ammortamento è vuoto e possiamo inserire campi come l’importo del mutuo, la durata, la frequenza della rata e il tasso di interesse.

Figura 1. Inserimento campi principali

Inserimento campi principali

Per il calcolo del tasso, che è fondamentale per realizzare l’ammortamento, possiamo prelevare il parametro Euribor direttamente dal Web, così da non dover effettuare ogni volta l’aggiornamento.

Posizioniamoci nella cella F4, andiamo nella scheda “Dati” e clicchiamo su “Da Web“; nella finestra che si aprirà digitiamo o copiamo l’indirizzo web dal quale attingere i dati relativi all’Euribor. Dopo aver premuto “Vai”, viene fatta l’anteprima del sito; selezioniamo dalla freccetta gialla l’area che deve essere importata, nel nostro caso si tratta dell’Euribor ed in particolare di quello a 6 mesi, e clicchiamo su “Importa”. Quindi nella successiva finestra, spuntiamo l’opzione “In un nuovo foglio di lavoro”, così da non intaccare la tabella del piano di ammortamento e clicchiamo su “Proprietà”. Nella finestra “Proprietà intervallo dati esterno” che si aprirà, spuntiamo l’opzione “Aggiorna dati all’apertura del file e diamo l’Ok due volte”.

=> Confronta: ammortamento di un prestito a rate costanti

Figura 2. Importare dati dal Web

Importare dati dal Web

Figura 3. Impostare l’aggiornamento dei dati

Impostare l'aggiornamento dei dati

In un foglio a parte verrà importata la tabella, che abbiamo selezionato dal Web, della quale però ci interessano in verità solo le colonna A e C, ed in particolare la riga 12, cioè quella che riporta l’Euribor a 6 mesi in un anno di 365 giorni (anno solare). Inoltre le celle con i valori devono essere formattate come percentuale, altrimenti non è possibile utilizzare il contenuto in calcoli matematici, giacché di default risulterebbero in formato testuale.

Figura 4. Formattare i valori come percentuali

Formattare i valori come percentuali

Procediamo ora ad aggiungere lo spread all’Euribor: nello stesso “Foglio1” aggiungiamo nella colonna D il calcolo dell’Euribor sommato allo spread dell’1% semestrale, come appare in figura:

Figura 5. Calcolare lo split

Calcolare lo split

Rinominiamo questo foglio come “Euribor+split” e torniamo al foglio “Piano ammortamento prestito”. Qui ci posizioneremo nella cella D6 per inserire il tasso di interesse. Ovviamente non lo faremo a mano ogni volta, ma inseriremo una formula di equivalenza, nella quale diremo di copiare il valore della cella D12 del foglio “Euribor+split”.

Nella riga della formula, o direttamente nella cella D6 citata, inseriamo la seguente formula e diamo l’invio:

=’Euribor+split’!D12

A questo punto il valore della cella D12 del foglio “Euribor+split” verrà ripetuto automaticamente nella cella D6 del foglio “Piano ammortamento prestito”, dopo ogni aggiornamento dell’Euribor e verrà generato il piano di ammortamento.

Figura 6. Il piano di ammortamento generato

Il piano di ammortamento generato

L’aggiornamento dei dati avviene – secondo quanto da noi disposto in precedenza- ogni volta che il file viene riaperto, sempre che sia disponibile la connessione ad Internet. Ma possiamo anche aggiornare il file manualmente da Dati>Aggiorna tutti.

Per concludere in maniera ordinata il lavoro, provvediamo a nascondere il foglio “Euribor+split”, così non potrà essere erroneamente alterato, essendo fondamentale per il calcolo dell’interesse. Clicchiamo con il tasto destro del mouse sulla linguetta “Euribor+split” e selezioniamo “Nascondi”.