Calcolatore rendimento obbligazioni su excel

In questo articolo imparerai come creare il tuo personalecalcolatore di rendimento delle obbligazioni.

Vedremo come calcolare il rendimento delle obbligazioni zero coupon come i BOT (buono ordinario del tesoro) e i CTZ (certificato del tesoro zero-coupon) e delle obbligazioni a tasso fisso come i BTP (buono del tesoro poliennale).

In fondo all’articolo troverai anche come calcolare la duration di un’obbligazione e come calcolare l’oscillazione di prezzo in base alla variazione dei tassi.

Calcolatore rendimento obbligazioni zero coupon

Le obbligazioni zero coupon sono obbligazioni prive di cedola e il cui rendimentoviene determinato sulla base della differenza tra il valore di rimborso pari al 100% del valore nominale e prezzo di emissione (sotto la pari).

Quali dati servono per calcolare il rendimento di un’obbligazione zero coupon?

• data di oggi: =OGGI()
• data di rimborso: bisogna cercarla sul sito di Borsa italiana
• prezzo di oggi: bisogna cercarlo sul sito di Borsa italiana (aggiungere il segno (-) davanti)
• prezzo di rimborso: 100 (salvo casi eccezionali)

Come si calcola il rendimento di un’obbligazione zero coupon?

Rendimento: =TIR:X(prezzo di oggi : prezzo di rimborso ; data di oggi : data di rimborso)

Facciamo un esempio:

• Data di oggi: 03/04/2023
• Data di rimborso: 01/05/2024
• Prezzo di oggi: -97,85
• Prezzo di rimborso: 100

Rendimento: =TIR.X(-97,85:100;31/03/2023:01/05/2024) = 2,03%

*in arancione i dati che si trovano sul sito di Borsa Italiana

Calcolatore rendimento obbligazioni tasso fisso

Il rendimento delle obbligazioni a tasso fisso deriva in parte dal flusso cedolare e in parte dalla differenza tra il prezzo di sottoscrizione o di acquisto e il valore nominale (pari a 100) che viene rimborsato alla scadenza.

Esistono 2 metodi per calcolare il rendimento delle obbligazioni a tasso fisso:

• formula TIR:X, più precisa ma meno facile da usare (obbligatoria per obbligazioni step up e step down);
• formula REND, meno precisa ma più facile da usare.

Come si calcola il rendimento di un’obbligazione tasso fisso con la formula TIR:X?

Dati per formula TIR.X:

• data di oggi (=oggi)
• data rateo (=oggi)
• date incasso cedole: bisogna cercarla sul sito di Borsa italiana
• data di rimborso: bisogna cercarla sul sito di Borsa italiana
• prezzo di oggi: bisogna cercarla sul sito di Borsa italiana (aggiungere il segno (-) davanti)
• prezzo di rimborso: 100 (salvo eccezioni)
• ultima data di stacco cedola: bisogna cercarla sul sito di Borsa italiana
• conteggio giorni rateo: formula
• cedola: bisogna cercarla sul sito di Borsa italiana
• frequenza cedola: 1=annuale, 2=semestrale, 4=trimestrale
• cedola periodica: formula
• base di calcolo: 365
• rateo: : formula (aggiungere il segno (-) davanti)

Calcolatore rendimento obbligazioni tasso fisso con la formula TIR:X

• cedola periodica: =cedola / frequenza cedola
• conteggio giorni rateo: =data di oggi – data ultimo stacco
• rateo = [(cedola/base di calcolo)*conteggio giorni rateo]

Rendimento =TIR.X(prezzo di oggi: prezzo di rimborso ; data di oggi : data di rimborso)

*al contrario delle obbligazioni zero coupon bisogna inserire anche la data del rateo e di tutte le date di stacco delle cedole e i relativi controvalori che si possono trovare sul sito di Borsa Italiana)

Facciamo un esempio:

• Data di oggi = 03/04/2023
• Data rateo =03/04/2023
• Data cedola = 01/09/2023
• Data cedola = 01/03/2024
• Data cedola = 01/09/2024
• Data di rimborso = 01/09/2024
• Presso di oggi = -100,91
• Prezzo di rimborso = 100
• Cedola = 3,75
• Frequenza cedola = 2
• Cedola periodica = 3,75 / 2 = 1,88
• Ultima data di stacco cedola = 01/03/2023
• Conteggio giorni rateo = 03/04/2023 – 01/03/2023 = 33
• Rateo = [(3,75/365)*33] = -0,34

Rendimento =TIR:X(-100,91:100 ; 03/04/2023:01/09/2024) = 3,09%

*in arancione i dati che si trovano sul sito di Borsa Italiana

Come si calcola il rendimento di un’obbligazione tasso fisso con la formula REND?

Dati per formula REND:

• data di oggi (=oggi)
• data di rimborso
• prezzo di oggi
• prezzo di rimborso
• cedola
• frequenza cedola (1= annuale, 2=semestrale, 3=annuale)
• base di calcolo = 365

Calcolatore rendimento obbligazioni tasso fisso con la formula REND

Rendimento = REND($data di oggi; $data di rimborso; $cedola; $prezzo di oggi; $prezzo di rimborso $frequenza cedola; $base)

Facciamo un esempio:

• Data di oggi = 03/04/2023
• Data di rimborso = 01/09/2024
• Prezzo di oggi = 100,91
• Prezzo di rimborso = 100
• Cedola = 3,75
• Frequenza cedola = 2
• Base di calcolo = 365

Rendimento =REND($03/04/2023;$01/09/2024; $3,75: $100,91; $100; $2; $365) = 3,08%

*in arancione i dati che si trovano sul sito di Borsa Italiana

Come calcolare la duration di un’obbligazione

• durata (vita residua) =(data di rimborso- data di oggi) /365

• duration =DURATA(data di oggi; data di rimborso; cedola; rendimento; frequenza cedola)

• duration modificata =duration / (1+rendimento)

Facciamo un esempio:

Useremo i dati dell’esempio precedente

• Durata = (01/09/204 – 03/04/2023) / 365 =1,42

• Duration =DURATA(03/04/2023; 01/09/2024; 3,75; 3,08%; 2) =1,38

• Duration modificata = 1,38 / (1+3,08%) = 1,34

Come calcolare l’oscillazione di prezzo in base alla variazione dei tassi

• variazione tassi =+/- tasso%
• oscillazione prezzo = – (duration modificata * variazione tassi * prezzo di oggi)

Facciamo un esempio:

Useremo i dati dell’esempio precedente

• Variazione tassi =+0,50%
• Oscillazione prezzo = – (1,34 * 0,50% * 100,91) = -0,68

Se i tassi di interesse aumentano di 0,50% il prezzo dell’obbligazione scenderà di -0,68.