excel yield curve_current yield是什么_用excel计算yield

There are many available definitions of Z (zero volatility) spread e.g.:

Quote [Investopedia]:

"The constant spread that will make the price of a security equal to the present value of its cash flows when added to the yield at each point on the spot rate Treasury curve where a cash flow is received . In other words, each cash flow is discounted at the appropriate Treasury spot rate plus the Z-spread."

Quote [Wikipedia]:

"The Z-spread, ZSPRD, Zero-volatility spread or Yield curve spread on a mortgage-backed security (MBS) is the flat spread over the treasury yield curve required in discounting a pre-determined coupon schedule to arrive at its present market price."

The purpose of this article is give a visual (graphical) representation of a z-spread. I will go over the procedure of building a graph in excel. This should help your understanding of the z-spread. Towards the end of this article you should see a graph that is similar to:

Before getting into the steps of building the graph, I want to quickly point out what the z-spread is in figure above. Notice that, at any point in the graph, the vertical distance between the green and blue line is exactly the same. This constant distance is really the z-spread. In other words: z-spread is the constant vertical shift of each point on the base curve.

Z-Spread using excel

In this section, I will show a step-by-step procedure on building a graphical representation of a z-spread as well as finding the a bond price given z-spread and vice-versa. I want to point out that I will be using some fixed-income/mathematical concepts (e.g. zero coupon bond, coupon bond, bond pricing using discounting cash flows, yield to maturity, treasury yield curve, interpolation) without going into much details of them. I will be using an example to explain z-spread. In this example we will be pricing a bond that pays 5% coupon annually and has a face value of $1000. The bond matures in 30 years. We will use discounted cash flow mechanism to price the bond. In order to get a discounted value for each cash flow (coupon payment/principal), we need yield (rate) values. We will use a hypothetical yield curve (along with interpolation). We can add a constant value (z-spread) to each cash flow yield see how that affects the price of the bond.

To make it more interesting, I will work on two objectives:

Bond price changes due to change in z-spread

Finding a z-spread for a bond price

Lets extend our excel model to now find the z-spread given that we have a bond price, e.g. I want to find the z-spread for a bond price of $1105.30. In order to do this, we will have to goal-seek (or trial-error approach). This really means that we keep changing the value of the z-spread until we see the the bond price equal to $1105.30. Luckily, excel provides a tool that does these iterations of goal-seeking for us. The tool is called solver. In the following steps, I will show to get up solver so that we find the z-spread for the price of $1105.30.

To bring up the solver click on Data -> Solver (Note: if solver is not available in the Data tab then you will have to install it. It is free to install solver in excel). Once the solver screen comes up, set it up as the following:

Now, if you click 'Solve' the solver will come up with a solution of z-spread of 1.63%. This means that if we apply a z-spread of 1.63% on top of our yield curve, we will get a price of $1105.30 for our bond.

I hope this helps in your understanding of the z-spread. Please post your questions in the comments section. You can post comments only if you sign up for an account on this website.

Click here to download the excel file used in this article.

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/tongxinshuyu/article-51685-1.html