giorgio1966
Se non puoi convincerli, confondili.
- Registrato
- 4/1/19
- Messaggi
- 661
- Punti reazioni
- 691
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.
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
Ultima modifica:
) ad un certo punto mi sono dimenticato di aggiornare quei commenti.