Calculate the Z-Spread of coupon bond with yield curve - Use Solve

Ocean_my — Mon, 23 Nov 2020 15:41:58 GMT

Hi, Please i need help, i would like to calculate the Z-spread(zero volatilité spread) of coupon bond.
Input(known variable) :

Price of the Bond = 102.3

Coupon = 3%

Frequency : Annuel

Date of maturity = 15/12/2024.

Yield Curve: Year 1 =0.1, Year 2 =0.2, Year 3 =0.4, Year 4 = 0.5.

The formula is : 102.3= 3/(1+0.1+SP)^1+3/(1+0.2+SP)^2+3/(1+0.4+SP)^3+3/(1+0.5+SP)^4+100/(1+0.5+SP)^4.

With the function Solve, i would like to find SP(Z-spread).

Thank you for your help.

Samy

Re: Calculate the Z-Spread of coupon bond with yield curve - Use Solve

Ocean_my — Mon, 23 Nov 2020 16:06:06 GMT

Price Bond = Coupon/(1+Rate(1)+Spread)^1+ Coupon/(1+Rate(2)+Spread)^2+ Coupon/(1+Rate(N)+Spread)^N+ Principal/(1+Rate(N)+Spread)^N