Obbligazioni con Excel

Gestire le obbligazioni con Excel permette di calcolare il rendimento effettivo lordo di qualsiasi obbligazione, dai BOT ai CCT e agli Zero Coupon Bond, a tasso fisso e a tasso variabile.

Le obbligazioni sono titoli emessi dallo Stato, da Enti, da Banche oppure da imprese private. In pratica si tratta di operazioni di prestito a lungo termine mediante le quali il risparmiatore che investe percepisce un interesse, detto anche rendimento, e chi emette l'obbligazione (emittente) utilizza il danaro per i propri investimenti.

Le obbligazioni vengono emesse nel mercato primario cui possono accedere soltanto i primary dealers (Stato, Banche, Sim) e collocate per mezzo di consorzi. I titoli dello Stato (BOT, CCT, BTP) vengono emessi tramite il meccanismo dell' Asta.

Le obbligazioni sono contrattate quindi comprate e vendute ogni giorno anche su appositi mercati telematici, principalmente il secondario all'ingrosso, chiamato MTS, e soprattutto il mercato secondario al dettaglio, il MOT, (Mercato Obbligazionario Telematico) al quale possono accedere anche i private tramite i dealers.

Ma veniamo ai 3 esempi pratici con Excel che vi vogliamo mostrare in questo tutorial. Come fonte primaria di informazioni ci siamo basati su una copia del quotidiano economico finanziario Il Sole 24 Ore del 6 settembre 2013.

Le tabelle sono raggiungibili on line a questo indirizzo: http://www.ilsole24ore.com/pdf/indici-e-numeri/obbligazioni_20130907_22.html (Sezione Obbligazioni, data: sabato 7 settembre 2013)

Il primo esempio si riferisce ad un BOT (Buono Ordinario del Tesoro) annuale, il secondo ad un CCT (Certificato di Credito del Tesoro) di durata poliennale e cedole semestrali a rendimento variabile e il terzo si riferisce invece ad una obbligazione UNICREDIT Bancoposta Tasso Fisso con scadenza agosto 2018, i cui rendimenti sono pubblicati dal Sole 24 Ore nella sezione 'Tasso Fisso e Zero Coupon Bond'.

1. BOT scadenza 14/08/2014, Codice ISIN (International Security Identification Number) IT0004954712 quotato sul mercato telematico MOT al prezzo ufficiale di 98,866 il giorno 6/9/2013 (vedi Prezzo Vwap 06.09 quinta colonna in figura 1).

Nella seconda colonna della sezione Titoli di Stato -Bot della pagina finanziaria on line del Sole 24 Ore dedicata ai mercati obbligazionari (pagina nr.1), è indicata la scadenza del BOT, 14-08-14.

Da tenere presente che nel caso dei BOT non è il prezzo di rimborso (sempre 100) a variare ma la quotazione giornaliera.

Infatti, data l'assenza di cedole, il rendimento effettivo, come vedremo tra poco, varia in funzione della quotazione giornaliera e quindi è come si trattasse di una obbligazione indicizzata a tasso variabile.

Gli altri dati significativi pubblicati dal Sole 24 Ore sono: l'imposta sostitutiva (12,50%) il rendimento effettivo lordo, quello netto, la Duration (espressa in anni e in giorni) , la Volatilità, la Quantità (espressa in migliaia di Euro) e il Prezzo medio ponderato sul mercato secondario MTS.

La formula impiegata per il calcolo del Rendimento Effettivo Lordo (che si riferisce sempre ad un tasso annuale) nel caso del BOT è la seguente:

365 x ( 100 - prezzo del BOT)
_______________________________________ X 100

prezzo del BOT x giorni durata residua

In questo caso il prezzo da utilizzare è quello indicato nella quinta colonna (Prezzo ufficiale 6/9), cioè 98,866; i giorni sono quelli espressi dalla Duration (0 anni, 338 giorni), quindi avremo:

365 x ( 100 - 98,866)
________________________________ = 1,23863%

98,866 x 338

che è proprio il dato che compare nella sesta colonna (arrotondato a 1,24), quella del Rendimento lordo calcolato sulla base di 365 giorni per anno.


Apriamo a questo punto un foglio vuoto di Excel e vi riportiamo i dati principali utili al calcolo del rendimento effettivo lordo di questo titolo.


In particolare riserviamo la cella E2 alla data del 6/09/2013, perchè tutti e 3 gli esempi che vedremo si baseranno sulle quotazioni relative a questa data.


(fare riferimento alla figura 2)


Nelle prime 4 colonne della riga 6 indichiamo rispettivamente la Scadenza, il codice ISIN, il valore dell' Imposta sostitutiva e il Prezzo Ufficiale relativi al BOT annuale, così come risultano pubblicati su Il Sole 24 Ore.


In E4 indichiamo sempre 365 e in F4 l'aliquota del 12,50%. Per quanto riguardo i giorni abbiamo scelto di calcolare i rendimenti sulla base di un anno di 365 giorni e non di 360 (anno commerciale, sempre meno utilizzato).


Da notare che la quantità di questo titolo scambiata il 6 settembre 2013 è stata di 1.444 migliaia di Euro, cioè quasi 1 Milione e mezzo di Euro.


Nella cella E6, invece, introduciamo la formula: "=($E$4*(100 - D6))/(D6*G6)*100" la quale esprime esattamente la formula generale già vista più sopra.


Il Rendimento netto è dato dal Rendimento Lordo meno l'imposta sostitutiva del 12,50%.


Nella "Legenda" del Sole 24 Ore si legge che nel Rendimento Netto non è preso in considerazione l'effetto di eventuali plusvalenze o minusvalenze, mentre l'Imposta sostitutiva si ottiene applicando l'aliquota del 12,50% sugli interessi e sull'eventuale "disaggio" di emissione.


Il "disaggio" di emissione è la differenza tra il prezzo di emissione e di rimborso di un titolo.


Poichè il prezzo di rimborso nel caso delle obbligazioni e quindi anche nel caso di questo BOT è sempre 100 e gli interessi sono impliciti (non esistendo cedole), dall'esame del dato pubblicato dal giornale abbiamo dedotto che vi è un "disaggio" implicito per questo BOT di 0,15 punti.


Per giungere ad un Rendimento Netto del 1,10% a partire da un Rendimento Lordo del 1,24% infatti la formula che abbiamo introdotto in F6 è la seguente:


"=+E6-(C6+J6*12,5/100)" (in cella J6, è visibile il disaggio: 0,15)


In pratica abbiamo sommato all'imposta sostitutiva di 0,12224 il 12,5% di 0,15 e sottratto il tutto da 1,24.


Ora avete tutte le informazioni per fare 2 cose: aggiungere una riga per ogni altra obbligazione simile da confrontare, in questo caso Buoni Ordinari del Tesoro, oppure potete seguire l'andamento di quotazioni e rendimenti della stessa obbligazione nel tempo utilizzando una riga per ciascun giorno di quotazione, con la possibilità di ricavare in entrambi i casi dei grafici.


E passiamo ora ad un altro tipo di obbligazione, un CCT (Certificato di Credito del Tesoro).


2. CCT Scadenza 01/03/2017, Codice ISIN: IT0004584204 quotato sul mercato telematico MOT al prezzo ufficiale di 96,452 sempre il giorno 6/9/2013 (vedi figura 3).


Il CCT, a differenza del BOT, ha le cedole, solitamente semestrali oppure annuali. Le cedole non sono altro che gli interessi spettanti all'investitore, cioè a colui che acquista l'obbligazione.


A differenza del BOT, che è sempre emesso ad un prezzo inferiore a 100 per dare comunque un rendimento all'investitore, il CCT può essere emesso ad un prezzo pari a 100 o di poco differente, e cioè ad un prezzo equivalente a quello di rimborso (che sarà sempre 100).


Per il calcolo del rendimento effettivo di un CCT quindi, non si può applicare la formula che abbiamo utilizzato sopra per il BOT, in quanto ci sono da considerare le cedole, la loro periodicità e il fatto che l'interesse è composto, capitalizzabile annualmente.


Questo comporta che in Excel non ci sarà sufficiente un solo rigo per quotazione o per titolo, ma avremo bisogno di un intervallo di celle da dedicare alla distribuzione temporale delle cedole future e all'analisi dei flussi di cassa attualizzati dell'investimento finanziario.


I meno esperti di matematica finanziaria non si preoccupino: gli esempi pratici che seguono conducono passo dopo passo al calcolo del rendimento lordo effettivo senza aver bisogno di complesse formule matematiche.


Iniziamo quindi con il riportare i dati pubblicati dal Sole 24 Ore nella sezione Certificati Credito Tesoro in corrispondenza del titolo selezionato (Pagina 2).


Da notare che la successione delle colonne è diversa dal caso dei BOT, in particolare sono state aggiunte le colonne 'Cedola attuale', 'Cedola futura' e 'Rateo' (figura 4).


La Cedola in corso, dice la legenda del Sole 24 Ore, indica il tasso annuo lordo cedolare per i titoli non indicizzati e il valore lordo della cedola in corso alla valuta per i titoli indicizzati.


In pratica si tratta dei punti di interesse spettanti sul valore nominale del titolo alla prossima data di maturazione della cedola stessa e riferentesi al periodo della cedola, quindi in pratica nel caso in esame avremo che chi ha acquistato questo CCT il giorno 6 settembre 2013, riceverà € 0.59 ogni 100 € di valore nominale acquistato, il giorno 1 marzo 2014 (0.59 è quindi un tasso semestrale, non annuo!)


La Cedola futura rappresenta invece, sempre secondo la legenda, l' ammontare percentuale della cedola successiva a quella corrente.


Se il dato non è certo l'indicazione è data in neretto e si base sull'ipotesi che i tassi sui BOT (o di altro parametro d'indicizzazione) rimangano uguali a quelli dell'ultima asta.


Da rilevare che i CCT hanno durata e quindi scadenza pluriennale, e che si tratta quindi di titoli indicizzati a tasso variabile, ma fisso entro il semestre. In pratica per 6 mesi il tasso resta fisso, come se si fosse acquistato un BOT a 6 mesi a tasso fisso.


Il valore delle cedole future è quindi soltanto stimato ed è legato all'andamento dell'Asta dei BOT semestrali e quindi all'andamento dei tassi di mercato.


La data di godimento, che in questo caso è il 1 marzo, sta a significare che ci sono 2 cedole semestrali per ogni anno, una matura il 1 marzo e l'altra il 1 settembre, perchè la scadenza finale del titolo è il 1/03/2017.


Il rateo è la quota parte della cedola in corso riferita alla data della quotazione e cioè al 6 settembre 2013, ed è calcolata (cella N11) mediante la seguente formula di Excel:


"+G11/(Giorni365("1/09/2013";"1/3/2014"))*M11"


dove in G11 c'è l'importo della Cedola attuale (0,59), in M11 ci sono i giorni trascorsi dalla data di maturazione dell'ultima cedola (1/9/2013) + 5 giorni di valuta che si tengono le banche, per un totale di 10 giorni e la funzione Giorni365 (datamenorecente; datapiurecente) è una funzione personalizzata che calcola la differenza in giorni di calendario tra due date qualsiasi sulla base dell'anno di 365 giorni.


In pratica abbiamo RATEO = (0.59 diviso 181 moltiplicato per 10) = 0.032597 (da notare che nella tabella del Sole 24 Ore è indicato il valore 0,03260)


Ora disponiamo di tutti i dati per sviluppare il piano delle cedole future e dei flussi di cassa da attualizzare per il calcolo del rendimento effettivo lordo di questa obbligazione con excel.


Il rendimento e quindi il tasso ricercato è un tasso annuo, anche se le cedole maturano semestralmente: il rendimento effettivo lordo serve infatti a confrontare la redditività di obbligazioni molto diverse tra di loro e deve quindi essere sempre riferito all'anno.


Tenete presente che il rendimento effettivo a posteriori è sempre un dato incerto e stimato, molto diverso da quello calcolato a priori. In particolare si assume che l'importo delle cedole semestrali future sia sempre uguale alla cedola futura, ma se i tassi cresceranno, le cedole saranno superiori, se invece i tassi scenderanno, le cedole saranno inferiori.


Il prezzo del CCT è naturalmente anch'esso sensibile alle variazioni dei tassi e alla durata del titolo, per cui normalmente a scadenze più lontane dovrebbero corrispondere rendimenti più elevati quando le aspettative sui tassi di interesse sono in crescita e a rendimenti più contenuti se le aspettative sui tassi sono in descrescita.


Per giungere in pratica alla determinazione del Rendimento Effettivo Lordo Annuo, si procede in questo modo.


(riferimento: figura 5)


La prima cosa da fare è determinare il cosiddetto "Corso Tel Quel" al 6 settembre 2013. Il "Corso Tel Quel" è uguale al Prezzo di chiusura + il Rateo.


Il Corso Tel Quel è quindi pari nel nostro caso a 96,452 + 0,032597 = 96,484597 e rappresenta l'esborso iniziale dell'investitore (naturalmente al lordo di commissioni e spese bancarie che andrebbero quindi aggiunte).


Poniamo questo valore in I14 (formula: =+$D$11+N11). Nell'intervallo celle C15 - H21 riportiamo tutte le cedole future a partire da quella del 1/3/2014 e fino a quella finale che maturerà nella stessa data del rimborso, e cioè il 1/03/2017.


La prima cedola (in F15) è pari alla Cedola attuale (+ spread 0.015) meno il rateo (0,59*1,015- 0,032597 = 0,5662253) mentre tutte le altre sono pari alla Cedola futura + spread (0,5583).


L'ultimo flusso di cassa è pari a 100.56 (100 valore di rimborso del titolo al nominale + 0.5583 valore dell'ultima cedola)


La formula utilizzata per attualizzare le cedole e quindi ricavare il rendimento come tasso che mette in equivalenza finanziaria tutti i flussi di cassa relativi all'investimento è la seguente (figura 6).


dove:


Co = valore di mercato del prestito alla data di rilevazione del rendimento (corso tel quel), nel nostro caso è pari a 96.484597;

S1, S2,Sk,Sn = sono le somme da incassare per capitale, interessi e premi (future prestazioni del titolo), nel nostro caso sono gli importi delle cedole e del rimborso finale del capitale al valore nominale (100);


K = 1, 2, n = sono il numero dei periodi calcolati sulla base dei giorni di calendario intercorrenti tra la data di riferimento del corso (data di calcolo del rendimento) e le scadenze delle singole prestazioni Sk.


Nel nostro caso sono i valori successivi, espressi in semestri e cumulati, delle cedole semestrali riferiti a ciascuna data di maturazione (vedi intervallo celle C15-C21), per cui il nostro tasso 'i' sarà necessariamente un tasso semestrale.


'i' = tasso di rendimento effettivo, ovvero il tasso che stiamo cercando.


Procediamo inizialmente per "iterazione" successiva, inserendo nella casella E27 dei valori di tasso arbitrari fino a che i 2 totali in H24 e in I24 non risultino uguali (almeno per uno scarto superiore ai 2 decimali)


Da notare che nell'intervallo H16-H21 ci sono le formule che riproducono in excel la formula generale vista sopra e che servono ad attualizzare, cioè a "scontare" alla data presente le cedole future e il rimborso del capitale finale.


Quindi ad esempio in H15 c'è : "=+F15/((1+$E$24)^0)" (elevato alla 'zero' perchè siamo alla prima rata che non si capitalizza perchè cade nell'anno in corso), mentre in H21 c'è: "=+F21/((1+$E$24)^C21)" (102.05 viene diviso per (1 + i) elevato alla 6,97237569)


6,97237569 è un numero che rappresenta quanti semestri ( e frazione) ci sono tra il 6/9/2013 e il 1/3/2017.


Procedendo per tentativi si trova che l'unico valore di 'i' che pone in equivalenza finanziaria i flussi di cassa, è pari al 2,19% equivalente all'1,09% semestrale.


La formula di Excel che lega il tasso semestrale a quello annuale e che è posta nella cella E24 è: "=NOMINALE(E27;2)/2".


Il tasso del 2,19% corrisponde esattamente a quello della tabella del Sole 24 Ore, cioè il rendimento lordo del CCT calcolato alla data del 6/9/2013.



Non contenti, abbiamo comunque effettuato la prova del 9 mediante la funzione TIR.X (Cella F27), la quale fornisce lo stesso risultato. Notare che ai fini della formula del TIR (Tasso Interno di Rendimento), il primo valore (96,484597) è preso con il segno meno.


Vediamo ora il terzo e ultimo esempio, quello relativo ad una obbligazione a tasso fisso UNICREDIT.


3. UNICREDIT BANCOPOSTA 08-2018, scadenza 24/8/2018, Codice ISIN: IT0004825029 quotata sul mercato telematico MOT al prezzo di chiusura di 107,02 sempre il giorno 6/9/2013 (figura 7).

La cedola a tasso fisso annuo in scadenza 24/8/2014 è 5,65 e il rateo risulta pari a 0.27863 (5.65 diviso 365 x 18 = 0.27863)

Attenzione, in questo caso la periodicità della cedola è annuale e quindi non ci sarà più bisogno di convertire un tasso semestrale in uno annuale equivalente: il tasso che pone in equivalenza i flussi di cassa è già il tasso ricercato, ovvero il Rendimento Effettivo Lordo Annuo di questa obbligazione.

Qui sotto vediamo il foglio di Excel in cui sono stati sviluppati i calcoli per giungere alla determinazione del Rendimento Effettivo del 4,05%.


Il procedimento che porta alla determinazione del Rendimento Effettivo Lordo di questa obbligazione (vedi figura 8) è lo stesso utilizzato nell'esempio precedente.


Le differenze sostanziali sono 2: le cedole sono annuali anzichè semestrali e il loro ammontare è predeterminato scadenza per scadenza, anzichè essere legato alle Aste dei Bot semestrali, come nel caso dei CCT.


Da notare infine come, nel caso di tassi di interesse di mercato bassi o decrescenti, come si verifica in questo anno 2013, il prezzo dell'obbligazione tenda a salire ben al di sopra del valore nominale, questo spiega perchè il rendimento effettivo è inferiore rispetto al tasso nominale, che resta il 5.65%







I Tutorial di Microsoft Excel


Home page MC2 E-Learning



Dr.Maurizio Cucchiara

Amministratore unico
MC2 E-Learning S.a r.l.
Formazione per il Web
autore del corso:
"Excel Corso Base"
disponibile on line su questo sito
http://www.mc2.it/html/excel.html

Condividi sui social network:

Social buttons



Iscriviti al nostro
nuovissimo
canale Youtube!


Iscrivi al nostro canale Youtube



Potrai restare aggiornato
sui nostri pi recenti
video-tutorial su:
Microsoft Access ed Excel
e molto altro...



Cerca sul web con Google:




Guarda il video-tutorial
sul nostro canale Youtube:


Obbligazioni con Excel

NOVITA'!

'VIDEO TUTORIAL
DOWNLOAD AREA'

(Fai clic sull'immagine
qui sotto per vedere
una anteprima della
pagina di accesso)



L'accesso riservato agli iscritti ai corsi on line www.mc2.it

Acquista anche uno solo
dei nostri corsi
per avere immediatamente
accesso alla
'Videotutorial Download Area': facile, basta un clic:

http://www.mc2.it



Iscriviti alla newsletter: E' GRATIS! (?)

 Nome: (?)

 (?)

e-mail: (?)

 (?)

Newsletter:

formato preferito:

Microsoft Access (?)

Impariamo Excel e il VBA(?)

Internet "da zero"! (?)

Html (?)

Consiglio

(?) = Aiuto

Leggi qui la Informativa completa sulla privacy.