Modello Calcolo Tasso Interno Rendimento

Con questo mio primo articolo su bpexcel voglio introdurre il concetto del T.I.R. (Tasso Interno di Rendimento) e la sua applicazione per la valutazione del costo dell’indebitamento in caso sia previsto un piano d’ammortamento con rientro pluriennale (ad es. un mutuo).
Allegato al presente articolo ho caricato un piccolo tool in grado di generare automaticamente piani di ammortamento da pochi input e valutare il tasso effettivo dell’operazione, integrando anche la possibilità di verificare come tale tasso cambi in funzione di alcune ipotesi di estinzione.
Si tratta di una versione beta messa a disposizione di tutti gratuitamente con lo scopo di creare insieme una risorsa utili a semplificare la valutazione del costo reale di un finanziamento, quindi invito tutti a testarlo e farmi avere le vostre opinioni a leoneadvisor@gmail.com in modo da perfezionarlo e ampliarlo. Ovviamente condividerò con i lettori di bpexcel.it la versione completa ed aggiornata.

IL PIANO DI AMMORTAMENTO ALLA FRANCESE
Finanziamenti come i mutui sono rapporti caratterizzati da condizioni e flussi sostanzialmente predeterminati già al momento della stipula.
La maggior parte di questi contratti prevede un’operatività basata su un unico flusso in entrata, alla data dell’erogazione del finanziamento, ed una serie di flussi in uscita, ovvero le rate che nella stragrande maggioranza dei casi saranno tutte di pari importo e quindi basate su di un piano di ammortamento alla francese.
In un mutuo con ammortamento alla francese la rata viene determinata nel seguente modo:
R= (C*i)/(1-(1+i)⁻ⁿ)
R= rata
C= il capitale erogato dalla banca
n= il numero complessivo delle rate
i= tasso interesse applicato. Se il tasso applicato viene espresso su base annuale bisogna trasformarlo in base mensile, se il tasso su base annuale è 6%, su base mensile sarà 0,5%.
La rata è composta da una quota capitale, che va a ridurre il debito residuo, mentre la quota interesse costituisce il corrispettivo richiesto dalla banca per il godimento del capitale non ancora rimborsato.
Di seguito vi riporto uno schema semplificativo di come ricreare un piano di ammortamento.
R= Costante
QI (Quota Interessi)= Per la prima rata C*i, poi DR*i.
QC (Quota Capitale)= R-QI
DE (Debito Estinto)= QC la prima rata, poi sarà dato da DEn-1+QCn fino a coincidere nell’ultima rata con il capitale erogato C.
DR (Debito Residuo)= C – QC per la prima rata, poi sarà dato da C – DE
QI (Quota Interessi)= C*i per la prima rata, poi DR*i

L’importo rata è fisso, come già detto, e ricavabile dalla formula sopra citata, ma si può anche generare utilizzando la FUNZIONE RATA di Excel inserendo come input il capitale erogato, il tasso contrattuale e il numero di rate.
Ovviamente, nel caso cambiasse il tasso nel corso nella vita del finanziamento bisognerà aggiornare il piano di ammortamento in funzione dei nuovi parametri.
Abbiamo ora il nostro bel piano di ammortamento, però è necessario integrarlo inserendo tutti gli eventuali oneri che l’istituto addebita oltre agli interessi passivi, come: commissioni d’incasso, penali, spese di istruttoria, ecc..
Così facendo avremo i reali flussi in entrata (il capitale erogato) ed in uscita (le rate con annesse spese correlate all’erogazione del finanziamento) propri del contratto di finanziamento oggetto della nostra analisi.

IL TASSO INTERNO DI RENDIMENTO
Adesso passiamo a determinare l’effettivo tasso che viene applicato e per farlo si utilizza il metodo del T.I.R., come detto il Tasso Interno di Rendimento.
Il TIR (o IRR, acronimo dall’inglese Internal Rate of Return) è un indice di redditività finanziaria di un flusso monetario, si tratta di una metodologia che si fonda sul contrapporre il valore attuale di tutti i flussi in entrata con il valore attuale dei flussi in uscita, dei quali si conoscono importi e rispettive scadenze. Viene utilizzata per stimare il rendimento di un investimento, ma trova applicazione anche nei finanziamenti, come di seguito illustrato.
Nel calcolo dell’attualizzazione di un capitale la formula è la seguente:
M(t) = Va = M*(1+i)⁻ᵗ

Legenda:
• Va: Valore attuale
• M: montante (capitale + interessi)
• i: tasso di interesse
• t: tempo di attualizzazione

In concreto si tratta di risolvere un’equazione nella quale si conoscono i valori di tutti i termini ad eccezione di uno, ovverosia il TIR che nel caso dei finanziamenti coincide con il T.E.G. (Tasso Effettivo Globale).

La risoluzione dell’equazione è spesso di difficile soluzione, in quanto si basa su metodi di calcolo iterativo, ma ci viene in aiuto excel, il quale grazie attraverso la funzione TIR.X ci restituisce il valore del TIR in pochi istanti.
E’ necessario creare una colonna con i flussi (ovviamente i flussi in entrata devono avere segno diverso da quelli in uscita) ed una colonna con le date dei relativi addebiti o accrediti, a questo si può accedere alla funzione TIR.X e si dovrà indicare come “valori” la colonna dei flussi, e come “date_pagamenti” le scadenze in colonna DATA, poi vi chiederà di inserire un valore “ipotesi” che Excel prendere come riferimento per il proprio calcolo iterativo, solitamente utilizzo 0 o 1, ma è ininfluente in flussi propri di un piano d’ammortamento alla francese.

 

 

A questo punto vi invito a testare il programma in allegato al presente articolo e verificare come excel può aiutarci a semplificare i calcoli sopra enunciati.

Download modello Excel

 

Nel foglio INPUT dovrete semplicemente indicare i dati nelle celle celesti e il programma restituirà nei successi fogli di calcolo il piano di ammortamento e valuterà il tasso effettivo del piano.
È possibile anche verificare il tasso effettivo in caso di estinzione anticipata, compilando l’apposito cruscotto sempre nel foglio INPUT e verificandone i risultati nei fogli di calcolo dedicato all’estinzione anticipata.
Si tratta di una versione beta del tool, l’obiettivo è valutare il tasso effettivo di un finanziamento a rate costanti con ammortamento alla francese.
Il programma consente anche di inserire le eventuali spese annuali e in automatico riproporrà tali importi nel corso del piano di ammortamento con cadenza annuale.
Spero che questo mio primo articolo sia stato utile almeno a comprendere le potenzialità di excel nel campo della valutazione di un finanziamento e magari aiutarci a ridurre quell’asimmetria informativa propria del mondo bancario.
Attendo riscontri e critiche costruttive in modo da migliorare il tool e renderlo più efficiente per tutti coloro che condividono il progetto bpexcel.it.
Nei prossimi articoli pubblicherò integrazioni contenenti aggiornamenti, in particolare per il calcolo della mora e la valutazione del relativo tasso effettivo in caso di insolvenza.
Grazie a tutti.

 

Il software è stato aggiornato con l’articolo del 18 dicembre

 

Dott. Gabriele Leone
Dottore in Intermediazione, Finanza Internazionale e Risk Management con specializzazione in Corporate Finance, si occupa di progettazione ed implementazione di modelli per analisi e valutazioni finanziarie a supporto di privati, PMI e professionisti.

leoneadvisor@gmail.com
www.leoneadvisor.it
it.linkedin.com/in/leonegabriele

3.9/5 (8 Reviews)
Torna in alto