Foglio Excel per calcolo rendimenti Btp Tasso Fisso

[giorgio1966]

O si corregge a mano o bisogna infilarsi nel ginepraio della funzione SE nidificata di Excel.

Ciao.

mau——

Se so cosa fare, a sistemare le formule ci penso io, non è un problema. Però ripeto, mi sembra strano che due Excel su due per anni hanno funzionato in un modo e nessuno abbia mai avuto problemi...

 

[giorgio1966]

Scadenze rettificate (spero).

Ho sostituito il file nel primo messaggio con il tuo con i dati aggiornati.
Grazie mille per il supporto.

 

[totore]

Ho sostituito il file nel primo messaggio con il tuo con i dati aggiornati.
Grazie mille per il supporto.

Grazie di tutto. Il file non si aggiorna in automatico.

 

[giorgio1966]

Grazie di tutto. Il file non si aggiorna in automatico.

No, devi riscaricarlo e sostituirlo al precedente.

 

[totore]

No, devi riscaricarlo e sostituirlo al precedente.

Provato, non si aggiorna ci sono dati di ieri.

 

[Charly1]

Provato, non si aggiorna ci sono dati di ieri.

Nella formula della ricerca della data di scadenza nel foglio emissioni ho trovato un errore. La formula =DATA(DESTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4);STRINGA.ESTRAI(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4;2);SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;2;FALSO);2))

nell'estrazione del giorno è errata e va messo SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);2))

quindi la formula giusta completa è la seguente:

=DATA(DESTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4);STRINGA.ESTRAI(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4;2);SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);2))

 

[totore]

Nella formula della ricerca della data di scadenza nel foglio emissioni ho trovato un errore. La formula =DATA(DESTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4);STRINGA.ESTRAI(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4;2);SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;2;FALSO);2))

nell'estrazione del giorno è errata e va messo SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);2))

quindi la formula giusta completa è la seguente:

=DATA(DESTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4);STRINGA.ESTRAI(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4;2);SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);2))

Hai aggiornato il file in prima pagina? Grazie.

 

[giorgio1966]

Provato, non si aggiorna ci sono dati di ieri.

Nel file postato da @feurio si era persa la connessione web, riprova ora.

 

[Charly1]

Hai aggiornato il file in prima pagina? Grazie.

Vedo che nella nuova versione in prima pagina la data di scadenza non viene più cercata con formula ma è stata aggiornata manualmente

 

[giorgio1966]

Nella formula della ricerca della data di scadenza nel foglio emissioni ho trovato un errore. La formula =DATA(DESTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4);STRINGA.ESTRAI(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4;2);SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;2;FALSO);2))

nell'estrazione del giorno è errata e va messo SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);2))

quindi la formula giusta completa è la seguente:

=DATA(DESTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4);STRINGA.ESTRAI(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);4;2);SINISTRA(CERCA.VERT($A61;Prezzi!$B:$G;3;FALSO);2))

E' vero, ed era un errore grave, praticamente i giorni di tutte le scadenze erano errati.
Comunque quella formula ora è stata sostituita dalle date dirette, aggiornate dal nuovo file.
Motivo in più per riscaricarlo.

 

[feurio]

Scusate, ma vedo solo ora: si potevano anche semplicemente copiare le date di scadenza con un copia e incolla sul vecchio foglio. Io ho modificato solo quelle nel foglio fornito da @BtpItaliaOt27Eur con le emissioni corrette.

 

[feurio]

Un'altra cosa, s e i Tassi nel foglio Emissioni sono esatti (e dovrebbero esserlo) è inutile prenderli ogni volta dal foglio Prezzi perche' quelli non cambiano nel tempo.

 

[fuma57]

Per LibreOffice purtroppo ci sono anche qui problemi con i Collegamenti Esterni,
ho provato anche sull'ultimo file scaricato ora, ho notato che spesso quando scarico il file e lo eseguo non ci sono i collegamenti esterni , in pratica non aggiornerà mai il foglio prezzi.
Ma la cosa non è sistematica a volte funziona altre volte no , non solo, se inserisco a manina il collegamento al sito sembrerebbe funzionare ma se lo salvo nel formato originale ".xlsx" e poi lo ricarico il collegamento esterno risparisce.Se però lo salvo nel formato di LiibreOffice ".ods" il tutto sembrerebbe funzionare.
Quindi perchè funzioni bisogna andare nel foglio Prezzi e cancellare tutto il suo contenuto e
poi nella cella A1 inserire il collegamento al sito "BTP Rendimenti netti".
Nel dettaglio:
1)Se il foglio fosse protetto per prima cosa sproteggerlo dal menu Strument->Proteggi foglio
2)Menu Modifica->"seleziona tutto" e poi comando "cancella contenuto" (tasto Canc)
3)selezionare cella A1 e dal menu Foglio->"Collegamenti esterni "
inserire Url "BTP Rendimenti netti"
digitare "Invio" (tasto return) quindi quando si presenta la finestrella dare "OK" e poi sulla
successiva finestrella selezionare l'ultima tabella "HTML_1-GridView1"
e dare "OK"
3)Dal menu File->"Salva con nome" e salvare come "BTp TF.ods"
4)Chiudete il file e riapritelo per verificare che adesso chieda di aggiornare i collegamenti esterni

 

[mauriga]

Se so cosa fare, a sistemare le formule ci penso io, non è un problema. Però ripeto, mi sembra strano che due Excel su due per anni hanno funzionato in un modo e nessuno abbia mai avuto problemi...

Coi bond a cedole corte o lunghe il problema si poneva anche col master6.5.

Ciao.

mau——

 

[francs]

Visto che alcuni utenti mi hanno chiesto, per i Btp a tasso fisso, un lavoro simile a quello fatto sul foglio di giamyx per i Btp Italia, condivido questa mia versione del foglio Master6.5 di Maino, con layout rivisto e semplificato per l'uso esclusivo dei Btp TF.
Anche in questo caso, la gran parte del merito va all'autore originale del foglio, io e i miei soliti consulenti/collaboratori (@altor12 , @PazzoperlaDea e @Sa10 ) abbiamo quasi esclusivamente cercato di semplificare e riordinare le varie informazioni presenti.

Qui sotto l'elenco delle funzionalità aggiunte.
- aggiunta dello sheet "Emissioni", in cui sono stati elencati tutti i Btp a tasso fisso.
- Importazione dal sito BTP Rendimenti netti di prezzi, scadenze e tassi di tutti i Btp della lista
- compilazione manuale delle altre informazioni necessarie al calcolo per tutti i btp (lavoro immane ed impeccabile eseguito da @Sa10 )
- selezione del Btp tramite una tendina
- compilazione automatica di tutti i campi relativi alle informazioni del titolo selezionato
- stima a priori dell'imposta di bollo, e possibilità di correggere a posteriori con il valore esatto
- aggiunta dello sheet "Storico" in cui è possibile replicare (va fatto manualmente) in una sola riga un ordine eseguito sullo sheet principale. Questo permette, volendo, di tenere uno storico di tutti gli ordini eseguiti senza mantenere una copia del foglio principale per ogni ordine. L'unica limitazione di questo storico è la mancanza, per ovvi motivi, dei flussi di cassa e quindi del TIR, il rendimento riportato è quello relativo al calcolo con l'interesse semplice

Un paio di note.
- L'imposta di bollo è applicata tutta alla scadenza, questo comporta un calcolo del TIR non preciso. Tuttavia, prove empiriche mi hanno persuaso che la differenza è nell'ordine di centesimi di percentuale, spesso anche meno. Questo, unito alla difficoltà di intervenire sulle formule per ottenere le date esatte per i pagamenti delle imposte, mi ha convinto a non procedere oltre nella ricerca di un calcolo preciso.
- La lista dei Btp che si trova nel foglio "Emissioni", in teoria, può essere modificata. I titoli presenti attualmente sono quelli importati dal sito BTP Rendimenti netti cosi come le informazioni delle prime colonne. Ma ogni utente può "preparare" la sua lista preferita, aggiungendo/eliminando intere righe o modificando singole voci all'interno di una riga. Lo sheet è protetto per evitare di perdere le formule che accedono ai dati importati da web, ma la protezione può essere rimossa senza digitare nessuna password. Se aggiungete dei titoli oltre a quelli già presenti, affinché questi appaiano nella tendina del campo "Nome", dovete anche procedere alla modifica della validazione di questo campo. Menù "Dati" "Convalida Dati" e nella formula per l'origine dell'elenco aggiungete la nuova riga.
- Nello sheet "Storico" sono previste fino a 60 righe di ordini. Se è necessario crearne di nuove, è sufficiente copiare e incollare una riga intera, tra quelle già esistenti. Fate attenzione a fare copia dell'intera riga, in modo da duplicare anche le formule presenti nelle colonne nascoste.

Anche di questo foglio ho preparato una versione per GoogleSheet, necessaria per la differente modalità di accesso ai dati via web.

EDIT:
Aggiornato il file con le Date Emissione, Date Scadenza e Prezzi Emissione per tutti i Btp in lista, rivisti e corretti dall'utente @feurio.
Chi ha già scaricato il file precedente è pregato di sostituirlo con quello attuale.

Se interessa potete inserire il mio (l'ho realizzato per l'altro thread) in qualche modo nel vostro, ma solo ovviamente la parte delle proiezioni, cioè il calcolo del fair value del BTP in base al TIR (tabella) e in base alla duration in caso di aumento dei tassi. Cioè i contenuti della tabella e della cella B33. Tralasciando tutto il resto. Il rendimento netto, il TIR e la duration sono allineati con con quelli di rendimentibtp e borsa italiana.

 

[giorgio1966]

Se non mi mandi al diavolo, volevo segnalare un probabile refuso.
Ho simulato un acquisto in emissione e ho notato che mi viene addebitato del rateo, cosa che ovviamente è impossibile.
Secondo me il problema è la cella “cedola precedente” che indica una data anteriore all’emissione.

Grazie e ciao.

mau——

Tra parentesi é stata corretta la data di emissione al 17/05 , questo è un btp con prima cedola corta, quindi il problema potrebbe ripetersi ogni volta si abbia questa situazione? Se volete posto l'algoritmo per individuare cedole corte, non saprei proprio come implementarlo su un foglio di calcolo

Ho fatto un po' di indagini. Ho trovato il seguente documento che, per quanto sembri una bozza senza nessuna ufficialità, vista la provenienza ritengo abbastanza credibile.

https://www.dt.mef.gov.it/modules/d...ietimi_e_delle_cedole_dei_titoli_di_Stato.pdf

Da questo documento ho compreso che il problema non era solo sul rateo, ma anche sulla prima cedola pagata (cella K4).
In base agli esempi riportati, ho provato a correggere i calcoli effettuati nel foglio Excel.
Ci sono però veramente una infinità di combinazioni e di casi possibili, quindi prima di ufficializzare le modifiche, chiederei a @mauriga e @BtpItaliaOt27Eur in particolare, ma anche a chiunque altro sia interessato o voglia darmi una mano, di provare ad effettuare quante più prove possibili (anche di non regressione, cioè devono continuare a essere corretti anche i casi che lo erano già prima).
Nel foglio che allego qui sotto, oltre alle correzioni da testare, ho creato uno strumento (PROVA) in fondo alla lista, con le caratteristiche di quello utilizzato per gli esempi del documento sopra.
Grazie a tutti.

Coi bond a cedole corte o lunghe il problema si poneva anche col master6.5.

Ciao.

mau——

E come era stato risolto?
Cedole lunghe che significa? Che l'ultima cedola è più lunga di sei mesi?

 

[BtpItaliaOt27Eur]

Ho fatto un po' di indagini. Ho trovato il seguente documento che, per quanto sembri una bozza senza nessuna ufficialità, vista la provenienza ritengo abbastanza credibile.

https://www.dt.mef.gov.it/modules/d...ietimi_e_delle_cedole_dei_titoli_di_Stato.pdf

Da questo documento ho compreso che il problema non era solo sul rateo, ma anche sulla prima cedola pagata (cella K4).
In base agli esempi riportati, ho provato a correggere i calcoli effettuati nel foglio Excel.
Ci sono però veramente una infinità di combinazioni e di casi possibili, quindi prima di ufficializzare le modifiche, chiederei a @mauriga e @BtpItaliaOt27Eur in particolare, ma anche a chiunque altro sia interessato o voglia darmi una mano, di provare ad effettuare quante più prove possibili (anche di non regressione, cioè devono continuare a essere corretti anche i casi che lo erano già prima).
Nel foglio che allego qui sotto, oltre alle correzioni da testare, ho creato uno strumento (PROVA) in fondo alla lista, con le caratteristiche di quello utilizzato per gli esempi del documento sopra.
Grazie a tutti.


E come era stato risolto?
Cedole lunghe che significa? Che l'ultima cedola è più lunga di sei mesi?

Prima discrepanza tra il primo file ( o anche quello con le scadenze rettificate che calcola uguale) è che il file Prova calcola il rateo netto uguale a quello lordo ed è quindi la prima cosa da correggere. Cedole lunghe ce ne sono per generiche obbligazioni per es annuali

 

[giorgio1966]

Prima discrepanza tra il primo file ( o anche quello con le scadenze rettificate che calcola uguale) è che il file Prova calcola il rateo netto uguale a quello lordo ed è quindi la prima cosa da correggereVedi l'allegato 2873119

Scusa, mi ero dimenticato di dire che avevo messo l'imposta a zero per semplificare il confronto con gli esempi del documento.
Se preferisci avere anche il netto rimettila pure a 12.5

 

[BtpItaliaOt27Eur]

Ho trovato due contabili con cui fare il confronto con quello che calcola il foglio Prova, istruttive
perché a cavallo dello stacco, quindi con durate delle cedole diverse e con tassi calcolati in proporzione.
Inizio con l'acquisto, la prima cosa che si nota è che il foglio considera il btp avente una sola cedola e lo fa scadere il 15/01, per i dati di
acquisto è segnata una Data Ultima Cedola che in realtà non esiste, poi l'importo lordo del disaggio è calcolato
a 0,44€ , che è uguale a quello che mi sono calcolato io, mentre sulla contabile è segnato 0,48 e questo potrebbe
derivare dal fatto che per il calcolo occorra la data di regolamento della prima tranche e non la data di emissione, però
direi che va bene lo stesso. Per la ritenuta il foglio fa una cosa che non ho capito , la scompone in due parti, comunque
quello che importa è che la loro somma è uguale a quello che ho calcolato io ed è uguale a quella sulla contabile,
(ovviamente se il disaggio lordo dovesse essere molto più elevato ci sarà una piccola differenza). Per il rateo c'è
la differenza di 1 centesimo tra quanto calcolato dal foglio e la contabile e questo è sicuramente da attribuire ad
arrotondamento. Tra un po' posto anche la vendita

 

[BtpItaliaOt27Eur]

Nella vendita il foglio calcola dei dati che sembrano in contraddizione : nel quadro Vendita vengono riportati

Disaggio Lordo​	0,007677​
Imposta sul Disaggio​	0,00096​

mentre nel quadro sottostante (Scadenza) che viene usato per il riepilogo della vendita vengono riportati

Scarto d'emissione Lordo​	€ 0,46​
​ Imposta sul Disaggio	-0,06​

Sulla contabile viene, come nell'acquisto, 0,48 per il disaggio e -0,06 per la ritenuta, quindi all'atto pratico ci troviamo, il foglio è passato da 0,44 a 0,46 coerentemente (ma la ritenuta , che è piccola, non è cambiata) mentre quello che è strano è che l'intermediario insiste su 0,48 . Per il rateo le cose sono un po' diverse, viene riportato

Importo Rateo Lordo​	€ 0,58​
Imposta sul Rateo​	-€ 0,07​

mentre l'intermediario riporta 0,14 per il rateo e -0,02 per la ritenuta , e questo forse è un segnale che qualcosa non quadra

Per la cedola netta incassata ci ritroviamo esattamente da quello riportato dal foglio 44,94€