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 14 marzo 2007.
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 il terzo si riferisce invece ad una obbligazione UNICREDIT Multicall con scadenza maggio 2015, i cui rendimenti sono pubblicati dal Sole 24 Ore nella sezione 'Tasso Fisso e Zero Coupon Bond'.
1. BOT scadenza 14/03/2008, Codice ISIN (International Security Identification Number) IT0004200587 quotato sul mercato telematico MOT al prezzo ufficiale di 96,170 il giorno 14/3/2007 (vedi figura 1 ).
Nella seconda colonna della sezione Buoni Ordinari Tesoro della pagina finanziaria del Sole 24 Ore dedicata ai mercati obbligazionari, è indicata la scadenza del BOT, 14-03-08.
La 'P' accanto sta ad indicare che si tratta di un titolo indicizzato sui prezzi di rimborso. In realtà, 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), il prezzo medio ponderato sul mercato secondario MTS e il prezzo di chiusura sul TLX.
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 14/3), cioè 96,170; i giorni sono quelli espressi dalla Duration (0 anni, 364 giorni), quindi avremo:
|
365 x ( 100 - 96,170) ________________________________ = 3,99347 % 96,170 x 364 |
che è proprio il dato che compare nella sesta colonna, 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 14/03/2007, 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 14 marzo 2007 è stata di ben 40.489 migliaia di Euro, cioè di più di 40 Milioni 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,25 punti.
Per giungere ad un Rendimento Netto del 3,48% a partire da un Rendimento Lordo del 3,95% infatti la formula che abbiamo introdotto in F6 è la seguente:
"=+E6-(C6+J6*12,5/100)" (IN CELLA J6, NON VISIBILE, C'E: 0,25)
In pratica abbiamo sommato all'imposta sostitutiva di 0,48018 il 12,5% di 0,25 e sottratto il tutto da 3,99.
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 1/12/2010, Codice ISIN: IT0003605380 quotato sul mercato telematico MOT al prezzo ufficiale di 100,59 (100,55 sul TLX) sempre il giorno 14/3/2007 (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.
Da notare che la successione delle colonne è diversa dal caso dei BOT, in particolare sono state aggiunte le colonne 'Cedola attuale', 'Cedola futura', 'Data godimento' e infine 'Rateo' (figura 4).
La Cedola in corso, dice la legenda, 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 riferentisi al periodo della cedola, quindi in pratica nel caso in esame avremo che chi ha acquistato questo CCT il giorno 14 marzo 2007, riceverà € 1.95 ogni 100 € di valore nominale acquistato, il giorno 1 giugno 2007 (1.95 è 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.
Occorre tenere in considerazione questo fatto, soprattutto quando i tassi si muovono, come in questo periodo.
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 giugno, sta a significare che ci sono 2 cedole semestrali per ogni anno, una matura il 1 giugno e l'altra il 1 dicembre, perchè la scadenza finale del titolo è il 1/12/2010.
Il rateo è la quota parte della cedola in corso riferita alla data della quotazione e cioè al 14 marzo 2007, ed è calcolata (cella N11) mediante la seguente formula di Excel:
"+G11/(PERSONAL.XLS!Giorni365("1/12/2006";"1/6/2007"))*M11"
dove in G11 c'è l'importo della Cedola attuale (1,95), in M11 (nascosta) ci sono i giorni trascorsi dalla data di maturazione dell'ultima cedola (1/12/2006) + 5 giorni di valuta che si tengono le banche, per un totale di 108 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 = (1.95 diviso 182 moltiplicato per 108) = 1.157143
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 effettivo a posteriori. 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.
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 14 marzo 2007. Il "Corso Tel Quel" è uguale al Prezzo di chiusura (abbiamo preso quello del mercato TLX) + il Rateo.
Il Corso Tel Quel è quindi pari nel nostro caso a 100,55 + 1,157143 = 101,707143 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 - H22 riportiamo tutte le cedole future a partire da quella del 1/6/2007 e fino a quella finale che maturerà nella stessa data del rimborso, e cioè il 1/12/2010.
La prima cedola (in F15) è pari alla Cedola attuale (1.95) mentre tutte le altre sono pari alla Cedola futura.
L'ultimo flusso di cassa è pari a 102.05 (100 valore di rimborso del titolo al nominale + 2.05 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 101.707143;
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 degli anni (o frazioni di anno calcolate 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-C22), per cui il nostro tasso 'i' sarà necessariamente un tasso semestrale.
'i' = tasso di rendimento effettivo, ovvero il tasso che stiamo cercando.
Non c'è altro modo che procedere per "iterazione" successiva, cioè occorre porre 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 3 decimali)
Da notare che nell'intervallo H16-H22 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 H22 c'è: "=+F22/((1+$E$24)^C22)" (102.05 viene diviso per (1 + i) elevato alla 7,434065)
7,434065 è un numero che rappresenta quanti semestri ( e frazione) ci sono tra il 14/3/2007 e il 1/12/2010.
Procedendo per tentativi si trova che l'unico valore di 'i' che pone in equivalenza finanziaria i flussi di cassa, è pari al 3,95319% equivalente all'1,95744% semestrale.
La formula di Excel che lega il tasso semestrale a quello annuale e che è posta nella cella E24 è: "=NOMINALE(E27;2)/2" dove la funzione NOMINALE è disponibile se facendo clic sul menù Strumenti, quindi Strumenti aggiuntivi, è stata spuntata la casella relativa alla voce 'Strumenti di analisi' e si è quindi installata la relativa libreria di funzioni finanziarie aggiuntive.
Vediamo ora il terzo e ultimo esempio, quello relativo ad una obbligazione decennale UNICREDITO.
3. UNICREDITO 05-15 SU MULTICALL, scadenza 30/5/2015, Codice ISIN: IT0003855779 quotata sui mercati telematici TLX e MOT al prezzo di chiusura di 92,77 sempre il giorno 14/3/2007 (figura 7).
La cedola attuale, in scadenza 30/5/2007, è 2.60, quella futura è 2.80 e il Rateo è pari a 2.08712 (2.60 diviso 365 x (288 + 5 = 293))
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,58%.
Altre informazioni su questo titolo sono state ottenute a questo indirizzo:
http://www.unicreditbanca.it/it/imprese/investimenti/prodotti/obbligazioni/?idc=561
(cercare la riga corrispondente al codice ISIN IT0003855779 e cliccare sul codice stesso, quello in arancione)
La particolarità di questa obbligazione è quella di avere una CEDOLA STEP UP, cioè che si incrementa progressivamente anno dopo anno.
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 crescenti, come si verifica in questo anno 2007, il prezzo dell'obbligazione tenda a scendere, compensando il rendimento e anzi diminuendolo, infatti, se diamo un'occhiata alla quotazione di UNICREDIT 05-2015 al 14 giugno 2007 (data di ultimazione di questo tutorial), ci accorgiamo che il prezzo è sceso da 92,77 a 89,96 e il rendimento effettivo è sceso dal 4,58% al 4,44% annuo dopo soli 3 mesi!
I Tutorial di Microsoft Excel
Home page MC2 E-Learning
Dr.Maurizio Cucchiara
autore del corso:
"Corso Base di Microsoft Access"
disponibile on line su questo sito
MC2 E-Learning S.a r.l.
Formazione per il Web
http://www.mc2.it/html/access.html
Cerca sul web con Google: