Quando un’impresa chiede un prestito in banca può essere utile prima calcolare la rata media, per capire se sarà in linea con la propria capacità di spesa e in questo tutorial spieghiamo come farlo con Excel, stabilendo i costi dei singoli pagamenti secondo le due variabili chiave: durata del finanziamento e tasso di interesse.
=> Scarica il modello di calcolo rate
Durata del finanziamento
Apriamo una nuova cartella di lavoro Excel. Nel Foglio 1 impostiamo la tabella di calcolo partendo dalle etichette di testo, a tale scopo scriviamo sulla cella:
- A1 “Durata in anni”;
- B1 “Termine pagamenti”;
- C1 “N° Rate”;
- D1 “Importo Rata”;
- E1 “Tot. Pagato”;
- F1 “Interessi pagati”.
I valori da considerare
Poi, rispettivamente, su H1, H2 e H3 digitiamo “Finanziamento”, “Tasso di interesse” e ” N. rate”. Tutte le celle potranno essere formattate a piacimento. Di seguito i valori fissi su cui calcolare le rate:
- I1 importo del finanziamento (ad esempio “€. 44000”)
- I2 tasso d’interesse (ad esempio “8,7%”)
- I3 numero di rate da rimborsare all’anno (se mensili “12”).
Compilazione dei campi in colonna
- Nella colonna A riportiamo gli anni di durata del finanziamento digitando, a partire dalla cella A2, i valori partendo dall’uno fino ad arrivare ad un numero più consistente. Nel nostro esempio siamo arrivati fino a 15 anni.
- Impostiamo poi nella colonna B la data presunta di termine dei pagamenti, assumendo che la data iniziale sia quella odierna. A tale scopo selezioniamo B2 e riportiamo la seguente formula =OGGI()+(A2*365) composta da due addendi: data corrente e durata del finanziamento in giorni. Utilizzando il quadratino di riempimento trasciniamo (fino a B16 nel nostro esempio) per ricopiare la formula nelle celle sottostanti.
- Nella colonna C dobbiamo impostare il numero delle rate che varierà in base alla durata del finanziamento. A tale scopo scriviamo su C2 la seguente formula =$i$3*A2 e ricopiamo la formula fino alla cella C16.
- Nella colonna D andiamo a calcolare l’importo della rata utilizzando l’apposita formula che Excel ci mette a disposizione: RATA = (TassoInteresse/N°Rate annuali;N°Rate Complessive;Finanziamento). Riportiamo la formula nella cella D2 della nostra tabella, così: =RATA($i$2/$i$3;C2;$i$1). La funzione “Rata”, poiché rappresenta un valore in uscita, genera un numero negativo. Per ovviare utilizziamo la funzione ASS(), inserendo tra le due parentesi la formula precedente: =ASS(RATA($i$2/$i$3;C2;$i$1)) e trasciniamo fino alla cella D16.
- Passiamo alla colonna E dove calcoleremo il totale da rimborsare, moltiplicando l’ammontare delle rate per il loro numero. Nella colonna F estrapoleremo dal valore appena calcolato gli interessi da pagare. A tale scopo scriviamo su E2 ed F2 rispettivamente =D2*C2 e =E2-$I$1. Ricopiamo fino alla riga 16 le due formule. Con una tabella così costruita, possiamo (anche variando i valori contenuti sulla colonna i) facilmente verificare i costi relativi al nostro finanziamento al variare della sua durata.
=> Ammortamento di un prestito a rate costanti
Tasso di interesse
Proviamo ora a creare nel Foglio 2 una tabella che ci consenta di valutare uno scenario di previsione di costo rata al variare del tasso di interesse. Scriviamo sulla cella:
- B1 g “Tasso”
- C1 g “Importo rata”
- D1 g “Totale pagato”
- E1 g “Interessi pagati”
Nella colonna G ricopiamo la tabella mostrata in figura:
Digitiamo ora i parametri su cui calcolare il costo della Rata. Posizioniamoci su H1 e scriviamo l’ammontare del finanziamento da richiedere, lasciamo “€.44000” come in precedenza. Su H3 riportiamo il tasso di interesse (“7,44%” nel nostro esempio). Su H4 digitiamo di quanto devono variare i tassi su cui calcolare la rata: noi abbiamo utilizzato il valore “0,3%”. Infine su H5 e H6 scriviamo in quanti anni intendiamo saldare il finanziamento e quante rate intendiamo pagare all’anno, in entrambe le celle abbiamo utilizzato il valore “12”.
Calcoliamo ora la rata utilizzando la formula già analizzata in precedenza, per cui sulla cella H7 scriviamo =ASS(RATA(H3/H6;H6*H5;H1)).
Su H8 calcoliamo quanto complessivamente avremo pagato al termine del periodo indicato (nel nostro esempio 12 anni). A questo scopo utilizziamo la formula=H7*H6*H5.
Su H9 scriviamo =H8-H1 in modo da ottenere l’ammontare complessivo degli interessi da pagare.
Infine su H10 reimpostiamo la formula già vista, per calcolare il giorno di conclusione dei pagamenti, ovvero =OGGI()+(H5*365).
Eseguiamo ora i punti seguenti in modo che nella tabella possa essere completata l’analisi di simulazione.
- Nelle celle C2, D2, E2, riportiamo i valori corrispondenti già calcolati nella colonna H scrivendo rispettivamente “=H7”, “=H8” e “=H9”.
- Nella colonna A riportiamo alcuni valori cosiddetti “di servizio”, cioè numeri che presi singolarmente non sono riconducibili ad alcuna informazione comprensibile, ma di cui in realtà ci serviremo per automatizzare la trascrizione dei vari tassi di interesse (indispensabili per eseguire i calcoli successivi). A tale scopo scriviamo su A3 ”-4”, poi procedendo verso il basso, facciamoli crescere digitando su A4 “-3”, su A5 “-2”… fino ad arrivare alla cella A11 in cui sarà presente “4”.
Nella colonna B predisponiamo adesso i tassi di riferimento su cui calcolare in seguito il valore delle rate. Posizioniamoci su B3 e digitiamo =$H$3+($H$4*A3) , dopodiché utilizzando il quadratino di riempimento copiamo la formula fino alla cella B11.
Eseguiamo adesso il calcolo delle rate, utilizziamo però un metodo diverso rispetto a quello visto in precedenza.
- Selezioniamo l’intervallo di celle B2:E11
- Posizioniamoci nella barra “Dati”, poi clicchiamo nel pulsante “Analisi di simulazione” e infine (nel menù) su “Tabella dati”
Nella finestra omonima dobbiamo indicare quante e quali sono le variabili di cui tenere conto per la nostra analisi di previsione dei costi. Ovviamente, come già più volte detto noi intendiamo effettuare il calcolo al variare di un unico valore, ovvero il tasso d’interesse, quindi nel campo contrassegnato con la dicitura “Cella di input per colonna” digitiamo “$H$3”, dopodiché confermiamo con OK.
Abbiamo adesso una tabella a una variabile che ci consente di schematizzare il costo di una rata in funzione della modifica del tasso di interesse.
Per rendere meglio leggibile il nostro foglio nascondiamo quelle informazioni che possono generare confusione, a tale scopo clicchiamo col tasto destro sulle intestazioni “A” e “2” e poi su “Nascondi”.
La nostra tabella ci apparirà ora come mostrato in figura.
Da notare che modificando i valori della colonna A, (per esempio il finanziamento da richiedere, piuttosto che il tasso, la varianza ecc) i valori della tabella si aggiornano, così facendo in qualsiasi momento potremo verificare la situazione per noi più vantaggiosa.
