Foglio di calcolo per rendimenti obbligazioni a tasso fisso e StepUp(Btp, Bot e Corporate)

[mauriziomaurizio]

Nella Cartella Emissioni naturalmente( la cartella Prezzi ha già i filtri)........, complimenti per il lavoro veramente Ottimo .......

 

[rodrig11]

che versione di excel stai usando? io ho usato Office -365 , non vorrei che la tua versione non sia compatibile con quella usata da me.

sì è una versione più vecchia. ho ovviato inserendo nella convalida di quella cella la formula più diretta senza passare per R2 di EMISSIONI
=Emissioni!$A$2:A144

Sembra tutto in ordine ora. Grazie.

 

[rodrig11]

@rodrig11 Prova a mettere nella convalida dati della cella F4, in Consenti: "intervallo di celle", e in origine: Emissioni!A2:A200
fammi sapere se cosi funziona,perchè in quel caso la Funzione Indiretto non è compatibile con la tua versione.
Ma se fosse cosi è probabile che ci sia qualche altra cosa che non funzioni correttamente.

Sì, visto ora. Alla fine ho risolto come hai consigliato. Grazie!

 

[fuma57]

Aggiornati i files in prima pagina alla versione 1.5

-Aggiunto Rendimento Lordo e relativo flusso di cassa
-"Spese fisse di Isitituto" spostate a sinistra nei relativi riquadri di acquisto e vendita
- Cambiata gestione macro per Archivio, aggiunto foglio di default "Bond" e scelta della riga per macro "Rimetti nel foglio..." selezionando direttamente la riga o una cella di questa.
-Aggiunta Duration nel "Calcolo Rendimenti" del foglio Emissioni
-Aggiunta nel foglio Emissioni la categoria "### Strip Zero Coupon ###" nella quale non bisogna inserire la data di regolamento ed il prezzo di emissione,ma invece la parola "ACQUISTO", perchè per questa tipologia di Btp la data e il prezzo di emissione corrisponderanno alla data e al prezzo di acquisto
-Sdoppiata macro nel foglio Emissioni per "Quotazione Titolo riga selezionata" ora ce ne sono due , una per il mercato Mot e l'altra per Eurotlx, questo perchè alcuni titoli stanno solo su uno dei due mercati
-Sdoppiata anche la cella per andare alla quotazione del titolo nel foglio Bond,anche qui una per il Mot e una per EuroTlx

Il file "ObbligazioniTF v1.5.txt" è quello contenente le macro anche per Excel e va rinominato in "ObbligazioniTF v1.5.xlsm",questo perchè non è possibile allegare file di tipo .xlsm

 

[Fabio23]

Ciao fuma57,
scusami,ma sul menù a tendina del foglio Bond non si vede il Bot Zc Mg24 perchè non è caricato sul foglio Emissioni. Come devo fare per averlo a scelta nel menù a tendina?
Grazie per la risposta

 

[fuma57]

Ciao fuma57,
scusami,ma sul menù a tendina del foglio Bond non si vede il Bot Zc Mg24 perchè non è caricato sul foglio Emissioni. Come devo fare per averlo a scelta nel menù a tendina?
Grazie per la risposta

Ciao,ti riporto le istruzioni presenti nel foglio ed in prima pagina di questo thread.
Prima cosa devi rimuovere la protezione del foglio Emissioni che è senza password,poi sotto ti ho riportato la riga che ho aggiunto nel mio

- La lista dei Titoli che si trovano nel foglio "Emissioni", può essere modificata, ogni utente può "preparare" la sua lista preferita, aggiungendo/eliminando intere righe o modificando singole voci all'interno di una riga.
L’importante è fare un copia ed incolla dalla riga sopra a quella sotto ,cosi’ da ricopiare anche le formule che servono per recuperare automaticamente le quotazioni, e poi aggiornare i campi con i dati della nuova obbligazione

Bot Zc Mg24 A Eur​

IT0005545469​

0,000%​

0​

12/05/2023​

96,584​

14/05/2024​

100​

96,643​

10.27​

Bot​

12,50%​

 

[Fabio23]

Ciao,ti riporto le istruzioni presenti nel foglio ed in prima pagina di questo thread.
Prima cosa devi rimuovere la protezione del foglio Emissioni che è senza password,poi sotto ti ho riportato la riga che ho aggiunto nel mio

Bot Zc Mg24 A Eur​

IT0005545469​

0,000%​

0​

12/05/2023​

96,584​

14/05/2024​

100​

96,643​

10.27​

Bot​

12,50%​

ok grazie.
Ho caito ma mi manca un'ultimo passaggio.Il TIR lo devo cambiare a mano? Come si calcola?
Grazie per la pazienza

 

[fuma57]

ok grazie.
Ho caito ma mi manca un'ultimo passaggio.Il TIR lo devo cambiare a mano? Come si calcola?
Grazie per la pazienza

in alto a destra del foglio emissioni c'è la macro "calcola rendimenti" clicca su quella,per excel solo se hai la versione con macro,cioè il file .txt rinominato in .xlsm

 

[Fabio23]

in alto a destra del foglio emissioni c'è la macro "calcola rendimenti" clicca su quella,per excel solo se hai la versione con macro,cioè il file .txt rinominato in .xlsm

grazie di tutto.Funziona.Ottimo.Complimenti

 

[fuma57]

In teoria FRAZIONE.ANNO dovrebbe darti più versatilità, ovvero ti permette maggiore precisione per periodi lunghi così da non doverti preoccupare se in mezzo ci sono anni bisestili. Tuttavia provando la funzione ho notato ora un comportamento inaspettato: se la setti con act/act e misuri un intero anno bisestile ti dirà che è 1 anno preciso, stessa cosa per un non bisestile; se metti insieme due interi anni uno bisestile e il successivo non bisestile (ad es. da 1/1/2019 a 1/1/2021) mi aspetterei 2 esatto, invece è 2,000912. Spiegazione?

Ciao @rodrig11 mi trasferisco su questa discussione, per non annoiare i partecipanti dell'altra .
Da prove empiriche che ho fatto, Frazione.anno con base act/act (almeno in LibreOffice) quando ci sono di mezzo anni bisestili e numero totale giorni maggiore di 366, al denominatore userebbe, anzichè 365 o 366 per l'anno bisestile,la seguente regola:
((anno finale-anno iniziale+1)*365))+(1*numero anni bisestili compresi tra data iniziale e data finale)/(anno finale-anno iniziale+1)
ovvero nel tuo caso (((2+1)*365)+1)/3=365,333 periodico , di conseguenza il risultato di frazione.anno è 731/365,333=2,000912

Stamani ho controllato la vendita che ho dovuto fare del marzo24, dal confronto della contabile della banca e le sue minus con quelle del mio foglio, sembrerebbe proprio che la banca usi i giorni effettivi per i conteggi e non utilizzi nemmeno la funzione Frazione.anno, perchè tutti i dati a partire dal disaggio ,al prezzo di carico fiscale e alle minus/plus generate fino al prezzo pagato e rimborsato corrispondo, in questo caso, quasi al centesimo con i conteggi del foglio.Se però per il calcolo lineare invece del conteggio effettivo cioè (data acquisto-data emissione)/(data scadenza -data emissione),uso la funzione Frazione.anno con base act/act allora i conteggi delle minus e dei prezzi di carico e scarico fiscale differiscono leggermente.

A proposito dell'anno bisestile, mentre facevo le prove sopra, visto che nella mia versione provvisoria ho ancora dentro il calcolo del prezzo teorico sia in maniera lineare che esponenziale, ho notato che per il Bot marzo 24 cambiando la convenzione da act/act a act/360, il prezzo teorico esponenziale cambiava,la cosa mi ha abbastanza sorpreso perchè i giorni scorsi avevo fatto tutte le prove che vi ho raccontato con il feb24 ed il prezzo teorico non cambiava mai neanche utilizzando la formula esponenziale; perciò ho rifatto ancora un po di prove con tutti i Bot elencati nel mio foglio e il risultato è: che per tutti quelli con scadenza antecedente alla fine di febbraio 2024 il prezzo teorico cambiando la convenzione non cambia, mentre quelli che scadono dopo il 1 marzo 24 cambiando convenzione cambia anche il prezzo teorico.
In pratica la variante è che il 2024 è anno bisestile , quindi febbraio è più lungo e se la data d'emissione del Bot è prima di fine febbraio 24 e la scadenza è dopo febbraio 24 ecco che allora il comportamento usando la funzione Frazione.anno cambia.

 

[rodrig11]

Ciao @rodrig11 mi trasferisco su questa discussione, per non annoiare i partecipanti dell'altra .
Da prove empiriche che ho fatto, Frazione.anno con base act/act (almeno in LibreOffice) quando ci sono di mezzo anni bisestili e numero totale giorni maggiore di 366, al denominatore userebbe, anzichè 365 o 366 per l'anno bisestile,la seguente regola:
((anno finale-anno iniziale+1)*365))+(1*numero anni bisestili compresi tra data iniziale e data finale)/(anno finale-anno iniziale+1)
ovvero nel tuo caso (((2+1)*365)+1)/3=365,333 periodico , di conseguenza il risultato di frazione.anno è 731/365,333=2,000912

Stamani ho controllato la vendita che ho dovuto fare del marzo24, dal confronto della contabile della banca e le sue minus con quelle del mio foglio, sembrerebbe proprio che la banca usi i giorni effettivi per i conteggi e non utilizzi nemmeno la funzione Frazione.anno, perchè tutti i dati a partire dal disaggio ,al prezzo di carico fiscale e alle minus/plus generate fino al prezzo pagato e rimborsato corrispondo, in questo caso, quasi al centesimo con i conteggi del foglio.Se però per il calcolo lineare invece del conteggio effettivo cioè (data acquisto-data emissione)/(data scadenza -data emissione),uso la funzione Frazione.anno con base act/act allora i conteggi delle minus e dei prezzi di carico e scarico fiscale differiscono leggermente.

A proposito dell'anno bisestile, mentre facevo le prove sopra, visto che nella mia versione provvisoria ho ancora dentro il calcolo del prezzo teorico sia in maniera lineare che esponenziale, ho notato che per il Bot marzo 24 cambiando la convenzione da act/act a act/360, il prezzo teorico esponenziale cambiava,la cosa mi ha abbastanza sorpreso perchè i giorni scorsi avevo fatto tutte le prove che vi ho raccontato con il feb24 ed il prezzo teorico non cambiava mai neanche utilizzando la formula esponenziale; perciò ho rifatto ancora un po di prove con tutti i Bot elencati nel mio foglio e il risultato è: che per tutti quelli con scadenza antecedente alla fine di febbraio 2024 il prezzo teorico cambiando la convenzione non cambia, mentre quelli che scadono dopo il 1 marzo 24 cambiando convenzione cambia anche il prezzo teorico.
In pratica la variante è che il 2024 è anno bisestile , quindi febbraio è più lungo e se la data d'emissione del Bot è prima di fine febbraio 24 e la scadenza è dopo febbraio 24 ecco che allora il comportamento usando la funzione Frazione.anno cambia.

quindi, hai empiricamente notato che la tua banca (Fineco?):
1. non usa affatto il pr teorico esponenziale, ma lineare;
2. nel pr teorico lineare non usa FRAZIONE.ANNO per misurare le durate temporali.

Indagando indagando...
Esistono 3 varianti di convenzione act/act, dalla formula del FRAZIONE.ANNO con act/act che mi hai mandato, LibreOffice (ed excel) usano la AFB. FRAZIONE.ANNO usa denominatore 365 anche per periodi a cavallo di un anno normale e uno bisestile, a patto che non contengano un 29 febbraio, in tal caso usa 366. Per periodi più lungi di un anno, leggiti la regola complessa qui
https://www.isda.org/a/pIJEE/The-Actual-Actual-Day-Count-Fraction-1999.pdf

Detto questo, non so confortare il tuo cruccio, non so perché sul Mg24 il cambio di convenzione fa cambiare il pr teorico, nella formula esponenziale il FRAZIONE.ANNO incide direttamente sull'esponente e indirettamente sulla base... Assumo che sia tutto corretto così e intendo usare FRAZIONE.ANNO nel mio foglio così da non dover cambiare manualmente 365 e 366 a seconda dei casi e non saprei neanche affrontare i casi a cavallo.

 

[Pigi22]

Grazie anzitutto per l'ottimo lavoro.

Premesso che opero via Excel, avrei due domande flash:

1) come si può impostare bollo semestrale?
2) mi confermate che per aggiungere altri TdS esteri, si lavora nel foglio emissioni?

grazie

 

[fuma57]

Grazie anzitutto per l'ottimo lavoro.

Premesso che opero via Excel, avrei due domande flash:

1) come si può impostare bollo semestrale?
2) mi confermate che per aggiungere altri TdS esteri, si lavora nel foglio emissioni?

grazie

il bollo si può impostare trimestrale o annuale tramite l'apposito menu a tendina della cella C5.,semestrale non è possibile.
Per aggiungere nuovi tds o qualsiasi obbligazione a tasso fisso devi inserirle nel foglio Emissioni
Ti riporto le istruzioni presenti nel foglio ed in prima pagina di questo thread .
Prima cosa devi rimuovere la protezione del foglio Emissioni che è senza password

- La lista dei Titoli che si trovano nel foglio "Emissioni", può essere modificata, ogni utente può "preparare" la sua lista preferita, aggiungendo/eliminando intere righe o modificando singole voci all'interno di una riga.
L’importante è fare un copia ed incolla dalla riga sopra a quella sotto ,cosi’ da ricopiare anche le formule che servono per recuperare automaticamente le quotazioni, e poi aggiornare i campi con i dati della nuova obbligazione

nel tuo caso le quotazioni non potranno essere recuperate automaticamente,ma dopo avere inserito la riga con i dati corretti potrai aiutarti con le due macro presenti nel foglio emissioni "quotazione titolo ..." ,basta che selezioni una cella della riga contenete il titolo e ti si aprirà la schermata con la quotazione,Le macro sono 2 perchè una è per i titoli presente nel mercato MOT e l'altra è per Eurotlx

 

[sasadangelo]

Ciao @fuma57,

Visto che te lo stanno chiedendo in molti come aggiungere nuovi titoli al foglio Emissioni. Mi chiedevo: ma questo foglio lo hai compilato a mano la prima volta? Oppure è qualcosa che la prima volta hai fatto scraping da un sito web come per le quotazioni?

 

[fuma57]

Ciao @fuma57,

Visto che te lo stanno chiedendo in molti come aggiungere nuovi titoli al foglio Emissioni. Mi chiedevo: ma questo foglio lo hai compilato a mano la prima volta? Oppure è qualcosa che la prima volta hai fatto scraping da un sito web come per le quotazioni?

Ciao,l'ho copiato da quello di @giorgio1966 , o meglio dal suo foglio Foglio Excel per calcolo rendimenti Btp Tasso Fisso, ci sono alcuni utenti che l'hanno aiutato nella compilazione e li trovi scritti sulla pagina indicata sopra.

 

[sasadangelo]

Quindi si tratta sempre di roba inserita a mano di cui tu hai fatto cut&paste ... non certo di qualcosa importato via URL o marco. Dico bene?

 

[fuma57]

Quindi si tratta sempre di roba inserita a mano di cui tu hai fatto cut&paste ... non certo di qualcosa importato via URL o marco. Dico bene?

si

 

[sasadangelo]

Ciao @fuma57,

Forse sbaglio ma la drop down list tu la crei sui dati di teleborsa qui:
Titoli di Stato - BOT - Borsa Italiana - Finanza

Ho notato che non rimuovono velocemente i BOT scaduti, per cui tu ti ritrovi nella drop down list BOT scaduti. Sei poi costretto a tenerli anche nel foglio di Emissioni. Secondo me forse è meglio tenere pulito il foglio emissioni da BOT scaduti (tanto quel foglio lo gestisci a mano). La drop down la calcoli su quel foglio. Le quotazioni poi le prendi sul foglio che fa scraper da teleborsa, tanto li avere un BOT scaduto lì non è un problema. Dico bene?

 

[fuma57]

Ciao @fuma57,

Forse sbaglio ma la drop down list tu la crei sui dati di teleborsa qui:
Titoli di Stato - BOT - Borsa Italiana - Finanza

Ho notato che non rimuovono velocemente i BOT scaduti, per cui tu ti ritrovi nella drop down list BOT scaduti. Sei poi costretto a tenerli anche nel foglio di Emissioni. Secondo me forse è meglio tenere pulito il foglio emissioni da BOT scaduti (tanto quel foglio lo gestisci a mano). La drop down la calcoli su quel foglio. Le quotazioni poi le prendi sul foglio che fa scraper da teleborsa, tanto li avere un BOT scaduto lì non è un problema. Dico bene?

Caio ,la lista dipende solo dai titoli che hai inserito nel foglio Emissioni,ed ognuno può togliere od aggiungere quelli che preferisce,nel foglio TDScensiti invece ti dice quelli presenti sul sito di teleborsa e che non sono ancora stati inseriti nel foglio Emissioni,cosi che tu volendo li possa aggiungere nel foglio Emissioni.
Poi personalmente i titoli che ho acquistato per ragioni di archivio li tengo nella lista anche se scaduti, ma ognuno può fare come meglio crede

 

[fuma57]

quindi, hai empiricamente notato che la tua banca (Fineco?):
1. non usa affatto il pr teorico esponenziale, ma lineare;
2. nel pr teorico lineare non usa FRAZIONE.ANNO per misurare le durate temporali.

Indagando indagando...
Esistono 3 varianti di convenzione act/act, dalla formula del FRAZIONE.ANNO con act/act che mi hai mandato, LibreOffice (ed excel) usano la AFB. FRAZIONE.ANNO usa denominatore 365 anche per periodi a cavallo di un anno normale e uno bisestile, a patto che non contengano un 29 febbraio, in tal caso usa 366. Per periodi più lungi di un anno, leggiti la regola complessa qui
https://www.isda.org/a/pIJEE/The-Actual-Actual-Day-Count-Fraction-1999.pdf

Detto questo, non so confortare il tuo cruccio, non so perché sul Mg24 il cambio di convenzione fa cambiare il pr teorico, nella formula esponenziale il FRAZIONE.ANNO incide direttamente sull'esponente e indirettamente sulla base... Assumo che sia tutto corretto così e intendo usare FRAZIONE.ANNO nel mio foglio così da non dover cambiare manualmente 365 e 366 a seconda dei casi e non saprei neanche affrontare i casi a cavallo.

Ciao @rodrig11 e @Encadenado a proposito della modalità di calcolo usata dalle banche per i BOT ,mentre stavo cercando altro mi è caduto l'occhio in fondo alla scheda informativa Fineco e ho trovato conferma del calcolo per lo meno del rateo di disaggio fatto per i titoli di durata inferiore ai 18 mesi,che appunto non usano ne formula esponenziale ne funzione FRAZIONE.ANNO, ma i giorni effettivi diviso la durata,ricavata dalla (data scadenza - data emissione).
Non ho ancora trovato conferma da Fineco invece per il calcolo del prezzo teorico.