Foglio Excel per calcolo rendimenti Btp Italia

giorgio1966

Se non puoi convincerli, confondili.
Registrato
4/1/19
Messaggi
676
Punti reazioni
698
Apro questa discussione per condividere la versione che ho preparato del foglio di @giamyx per il calcolo del rendimento dei Btp Italia.
Se in futuro giamyx sarà d'accordo le due discussioni potranno venire unite.
Tengo a precisare che, in gran parte, ho semplicemente rivisto il layout del foglio originale (al cui autore restano quindi la quasi totalità dei meriti), disponendo le informazioni secondo un criterio, a mio giudizio, più ordinato (il che non significa che sia così anche per altri utenti).

Qui di seguito, riporto le nuove funzionalità, aggiunte in collaborazione con gli utenti @altor12 , @PazzoperlaDea e @Sa10 (elencati in ordine alfabetico).
- Elenco dei Btp Italia con nome più chiaro
- Gestione del pagamento dell'imposta di bollo (con la possibilità di specificare la periodicità della rendicontazione)
- Gestione del pagamento dell'imposta sul Capital Gain
- Gestione di eventuali minusvalenze preesistenti in portafoglio
- Gestione del premio fedeltà
- Calcolo del rendimento netto annuo (ottenuto con l'applicazione sia dell'interesse semplice sia del TIR), tenendo conto dei fattori dei punti precedenti
- Importazione dei prezzi dei Btp dal sito teleborsa.it e "suggerimento" degli stessi di fianco ai relativi campi di input per l'acquisto e per la vendita.
- Possibilità di specificare la modalità di gestione della fiscalità del rateo rivalutazione capitale (disaggio), tra le quali è stata aggiunta la modalità che non considera mai tale rateo
- Possibilità di previsione degli indici FOI, per ogni singolo mese, valida per tutti i mesi successivi a quello specificato

Ho preparato anche una versione dello stesso foglio da utilizzare con GoogleSheet (a causa dell'importazione dei prezzi non è stato possibile mantenere una versione singola).

EDIT 24/01/2023
Aggiornate entrambi gli Excel alla versione 1.1
Di seguito le modifiche
- Eliminato il riferimento errato nel tootip del campo "Data Acquisto".
- Ridotto il font nello sheet "Istruzioni".
- Aggiunto il campo "Duration" nella sezione rendimenti.
- Modificato da Effettivo/360 a Effettivo/365 il metodo di calcolo della funzione FRAZIONE.ANNO nella durata dell'investimento.
- Corretti gli ISIN dei titoli nello sheet "Emissioni". Ora sono presenti solo i titoli acquistabili sul mercato secondario.
- Sistemate alcune celle che visualizzavano errore o zero in caso di dati non completi.

EDIT 25/01/2023
Aggiornata la versione per GoogleSheet alla 1.1.1
- Corretto errore nella formula del campo "Prezzo", introdotto nella versione 1.1

EDIT 26/01/2023
Aggiornate entrambe le versioni alla 1.2
- Cambiata la dicitura da "Duration" a "Duration Modificata"
- Corretta la validazione sul campo "Data Vendita"
- Gestione del premio fedeltà antecedente alla scadenza

EDIT 11/2/2023
Aggiornate i file Excel alla versione 1.3
- Rinominata la sezione "INTERMEDIARIO" in "TASSAZIONI"
- Aggiunta la cella per specificare il valore attuale della imposta di bollo (cella B6)
- Le imposte sulle cedole e sul capital gain sono state unificate in un unica voce "Imposta Sostitutiva" e sono state spostate nella sezione "TASSAZIONI" (cella B4)
- Aggiunta la voce "Riduzione Imponibile per Titoli di Stato" (cella B5), pari a 12,5%/26%. In questo modo spero di avere reso più chiara la gestione della imposta sul capital gain (26% su un imponibile ridotto del 12,5%/26%), che ha causato disorientamento in alcuni utenti. I calcoli nella sezione rendimenti sono rimasti identici (o meglio, è rimasto identico il risultato, i calcoli rendono trasparente la modifica)
- Bloccate tutte le celle relative alle tassazioni
- Inserita una riga nella sezione "Rendimenti" (riga 2, in arancione), che mostra la media dell'inflazione prevista specificata nel foglio FOI
- Modifiche estetiche (diciture, font, colori di sfondo)

EDIT 17/2/2023
Aggiornati i due file alla versione 1.4
- Eliminato il totale senza imposte dei flussi di cassa
- Spostato il totale dei flussi di cassa in cima alla lista degli importi
- Aggiunta una colonna nei flussi di cassa per indicare la composizione dell'importo (legenda nel tooltip della cella H2)
- Eliminate nella sezione "Rendimenti" le celle "Rendita Globale al lordo di Bolli e CG", "Rendimento Globale al lordo di Bolli e CG", "Rendimento Annuo al lordo di Bolli e CG"
- Aggiunta la scelta per il calcolo degli importi lordi o netti (cella B2)
- Aggiunta nella sezione "Rendimenti" la cella con il valore della plus/minusvalenza prima della riduzione
- Aggiunto il commento alle celle "Prezzo fiscale di carico" e "Prezzo fiscale di scarico"

EDIT 3/3/2023
Aggiornati i file alla versione 1.5
- Aggiunto il nuovo Btp MAR28 2.00% (Il prezzo non è al momento disponibile, se ho azzeccato il nome con cui sarà quotato non ci sarà da fare nulla, altrimenti andrà modificata la stringa da ricercare)
- Aggiunta la versione per LibreOffice (la versione per Excel in LO aveva problemi con l'importazione dei prezzi)
- Modificata la gestione della tendina dei nomi (ora si adegua automaticamente alla lista nel foglio "Emissioni", quindi è possibile aggiungere i nuovi titoli (o togliere i vecchi) modificando semplicemente questa lista)

EDIT 29/3/2023
Aggiornati i file alla versione 2.0
- Invertito l'ordine dei fogli FOI ed Emissioni
- Aggiunta l'opzione di calcolo "Al Lordo del Bollo" nella tendina della cella B2
- Aumentate a tre le cifre decimali per i prezzi di acquisto e di vendita
- Tolto l'indice "Duration Modificata" nella sezione "Rendimenti" (l'analisi condotta da @altor12 ha mostrato che questo indice per i Btp Italia non è significativo)
- Invertite le righe del titolo e dell'avviso nella sezione "Vendita"
- Corretta la gestione dei flussi di cassa, nel caso di vendita del titolo nel giorno del bollo, il bollo non viene addebitato
- Corretta la gestione dei flussi di cassa, nel caso di acquisto del titolo nel giorno del bollo, il bollo viene addebitato
- Nell'avviso della sezione "Rendimenti" il valore dell'inflazione media si riferisce al periodo corrispondente alla durata dell'investimento. Siccome il valore non è più riferito esclusivamente alla inflazione prevista, sono stati modificati di conseguenza la dicitura e il commento della cella E2.
- Eliminato BTP Apr23 dalla lista delle emissioni
- Aggiornato il foglio istruzioni
- Restyling del foglio "FOI"
+Eliminati i valori dell'anno 2015
+Default della previsione impostato a 0% nella cella D2
+Bloccata la prima riga
+Aggiunto filtro alla colonna "A", in questo modo è possibile visualizzare solo gli anni/mesi desiderati
+Formattazione condizionale della colonna "B" per evidenziare i valori già comunicati da Istat rispetto a quelli frutto di previsione (riguardo a questo punto va fatta una precisazione, in Excel fino alla versione 2013 o senza utilizzare una macro è impossibile stabilire se una cella contiene una formula, quindi la distinzione è stata fatta in maniera "approssimativa" basandosi sulla data del valore FOI. Su GoogleSheet e LibreOffice è invece possibile stabilire la presenza di una formula, quindi la distinzione è stata fatta in modo preciso tramite questa differenza)

EDIT 29/6/2023
Aggiornati i file alla versione 2.1
- Introdotta l'acquisizione automatica degli indici del FOI.
Un ringraziamento a @Pedrag che mi ha segnalato il sito da cui prelevare i dati.
Nel foglio FOI gli indici non devono più essere aggiornati manualmente quando escono sul sito ISTAT, ma vengono recuperati automaticamente tramite una query web che punta a "Dati inflazione Italia, Area euro, US, Foi e IPCA".
Le formule sono fatte in modo che, se i dati non vengono trovati, è possibile procedere alla previsione sugli indici futuri nel consueto modo (colonna D).
La colorazione in arancione delle celle della colonna B è ora determinata, per tutti e tre gli ambienti, dal ritrovamento del dato da web.
La ricerca è impostata per essere eseguita ad ogni apertura del file. In questo modo il ritrovamento è del tutto automatico. Va detto però che la query richiede un minimo tempo di esecuzione (circa un secondo) che, sommato a quello già presente per il ritrovamento dei prezzi, può risultare fastidioso. Visto che l'aggiornamento degli indici del FOI avviene una volta al mese, chi preferisce può disabilitare l'esecuzione automatica e procedere al lancio della query a comando, tramite "Dati/Connessioni/Aggiorna tutti" (non so se sia possibile anche con Libre Office o Google Sheet).

EDIT 26/12/2023
Aggiornati i file alla versione 2.2
- Eliminato il Btp Nov23, ormai scaduto
- Unificate le colonne "Totale Lordo" e "Totale Netto" nella tabella "Flusso Cedolare Previsto" (Colonne V e W). Come in tutto il resto del foglio, la distinzione tra netto e lordo avviene tramite la selezione della cella B2, non aveva senso mostrare entrambi i valori in questo caso (infatti se si selezionava "Lordo", il totale netto era sempre lordo).
- Migliorato il calcolo dell'inflazione media nel periodo dell'investimento (cella E2). Mentre prima si prendeva, come intervallo per il calcolo, i mesi compresi tra la data di acquisto e quella di fine investimento, ora sono presi in considerazione i "Numeri Indice" di acquisto e di fine investimento. Questo comporta in modo automatico due vantaggi: vengono considerati i 2 mesi precedenti di inflazione, e vengono effettuati i calcoli sul giorno preciso. In questo modo, l'intervallo su cui viene calcolata l'inflazione media è identico a quello su cui viene calcolato il rendimento.
 

Allegati

  • Btp Italia v2.2.xlsx
    155,9 KB · Visite: 1.918
  • Btp Italia v2.2.ods
    160,8 KB · Visite: 334
  • Btp Italia Google v2.2.xlsx
    324,2 KB · Visite: 764
Ultima modifica:
E' stato ed è un'onore far parte della squadra,esser stato tra i primi ad utilizzare la tua versione del foglio e aver condiviso con Voi tempo,letture,informazioni. A Voi il mio ringraziamento :bow:
 
Grande lavoro di squadra! anche da parte mia un ringraziamento a @giorgio1966 per il grande lavora di automatizzazione e ad @altor12, per la parte "finanziaria", senza dimenticare l'imprescindibile base di @giamyx senza cui nulla sarebbe stato possibile.

PS: vale la pena dire che tra le varie, schede è stata compilata un'ottima guida all'utilizzo da parte del grande @Sa10. Buon utilizzo!
 
Ultima modifica:
Ottimo strumento, complimenti.
Poso chiedere se l'importazione da teleborsa.it è automatica o bisogna farla manualmente?
 
Mi corre l'obbligo di aggiungere che abbiamo ovviamente cercato un contatto con @giamyx per condivisione e per avere una sua opinione sull'iniziativa, dato che senza il suo "motore excel" non si può andare da nessuna parte.
 
Ultima modifica:
È possibile avere la versione non protetta?
Io uso solo ed esclusivamente Excel mobile per iPad e alcune funzioni sono disabilitate (es l’importazione dati).

Grazie.

mau——
 
È possibile avere la versione non protetta?
Io uso solo ed esclusivamente Excel mobile per iPad e alcune funzioni sono disabilitate (es l’importazione dati).

Grazie.

mau——
Puoi togliere direttamente tu la protezione, non c'è password. Vai sul foglio che ti interessa e selezioni il menu "revisione - togli protezione foglio"
 
Ottimo lavoro ! Mi accingo ad usarlo. Grazie
Mi permetto di chiedere se c'è una motivazione per cui non è stato inserito in tabella l'ISIN della emissione (con CUM )
ISIN_CUM.jpg
 
Molto interessante, grazie a tutti per il lavoro! Lo sto usando e funziona perfettamente, molto chiaro e preciso. Aspetto con ansia la versione per i Bundei tedeschi! :flower:
 
Ultima modifica:
Grazie!
Molto apprezzato.

Piccola precisazione su un commento

Vedi l'allegato 2871347

è la cella B6, sottigliezza lo so.
Hai fatto benissimo a segnalarlo, ti ringrazio.
Adesso li controllo tutti, probabilmente ce ne sono altri, perchè nei vari spostamenti (e sono stati tanti ;) ) ad un certo punto mi sono dimenticato di aggiornare quei commenti.
Grazie ancora.
 
Ottimo lavoro ! Mi accingo ad usarlo. Grazie
Mi permetto di chiedere se c'è una motivazione per cui non è stato inserito in tabella l'ISIN della emissione (con CUM )
Vedi l'allegato 2871322
Esatto. Tanto, inserendo come data di acquisto la data di inizio vita del prodotto (per esempio 22/11/2022 per il NV28), il sistema tiene già conto a scadenza del premio fedeltà nel rendimento, trasformando di fatto nel prodotto CUM, anche se l'ISIN è quello del secondario
 
Domanda.
Avendo acquistato lo stesso btpi in 3 date diverse, c'è la possibilità di avere nello stesso foglio i vari dettagli di acquisto e un riepilogo unico, tipo il totale della cedola senza avere un foglio per ogni acquisto?
 
Domanda.
Avendo acquistato lo stesso btpi in 3 date diverse, c'è la possibilità di avere nello stesso foglio i vari dettagli di acquisto e un riepilogo unico, tipo il totale della cedola senza avere un foglio per ogni acquisto?
Purtroppo al momento no. Devi creare 3 schede differenti
 
Domanda.
Avendo acquistato lo stesso btpi in 3 date diverse, c'è la possibilità di avere nello stesso foglio i vari dettagli di acquisto e un riepilogo unico, tipo il totale della cedola senza avere un foglio per ogni acquisto?
Per fare quello che chiedi, l'unico modo è procedere come hai già pensato tu (un foglio per ogni acquisto).
Ma per foglio intendo solo lo sheet principale, non tutto il file Excel.
 
Ultima modifica:
Scusami ma ho notato che selezinando il BTP si aggiornano tutti i parametri escluso il valore della quotazione.
Infatti questo un dato da inserire a mano come il foglio originale ma mi chiedo se non sarebbe utile che venga inserito automaticamente con la funzione cerxa come per tutti gli altri dati.
 
Scusami ma ho notato che selezinando il BTP si aggiornano tutti i parametri escluso il valore della quotazione.
Infatti questo un dato da inserire a mano come il foglio originale ma mi chiedo se non sarebbe utile che venga inserito automaticamente con la funzione cerxa come per tutti gli altri dati.
Non saprei, il prezzo aggiornato sul mercato viene mostrato come suggerimento dopo la scritta "Prezzo" nella cella a fianco.
Ma non è detto che poi l'utente voglia inserire esattamente quel valore, ho preferito lasciare il campo libero.
 
Indietro