Key rate calculations
- Thread starter panweiyou
- Start date
Wei You,
Here is the underlying spreadsheet for this key rate model. Please note: I made a speaking mistake over this slide, although the numbers are correct. I happen to be modeling a fully amortizing (mortgage) bond. As such, the initial mortgage bond price is:
60 N
2.5 I/Y
3000 PMT
0 FV (i.e., zero instead of par!)
CPT PV = -92,725.97
You'll see the answer to your question in the sheet, to the right side. And why it's non-trivial to share the calcs on the slide. But the concept is easier than it looks. The initial bond has a 5% YTM; i.e., all of the bond's cash flows are discounted at 5%.
The 2-year key rate, for example, simply shocks the 2 year rate and its NEIGHBORING RATES to re-price (neighbor are shocked by by linear interpolation). So, the 2-year re-pricing changes from discounting all cash flows at same rate to discounting the near term cash flows (0 to 5 years) by slightly higher rates (and the rest at 5%). I arbitrarily picked 4 "key" rates so I shock (re-price) four times and that gives a VECTOR of key rate 01s and key rate durations. That's the point of the exercise: the term structure shift is *approximated* by the sum of these key rate durations (that constitute the vector).
David
Here is the underlying spreadsheet for this key rate model. Please note: I made a speaking mistake over this slide, although the numbers are correct. I happen to be modeling a fully amortizing (mortgage) bond. As such, the initial mortgage bond price is:
60 N
2.5 I/Y
3000 PMT
0 FV (i.e., zero instead of par!)
CPT PV = -92,725.97
You'll see the answer to your question in the sheet, to the right side. And why it's non-trivial to share the calcs on the slide. But the concept is easier than it looks. The initial bond has a 5% YTM; i.e., all of the bond's cash flows are discounted at 5%.
The 2-year key rate, for example, simply shocks the 2 year rate and its NEIGHBORING RATES to re-price (neighbor are shocked by by linear interpolation). So, the 2-year re-pricing changes from discounting all cash flows at same rate to discounting the near term cash flows (0 to 5 years) by slightly higher rates (and the rest at 5%). I arbitrarily picked 4 "key" rates so I shock (re-price) four times and that gives a VECTOR of key rate 01s and key rate durations. That's the point of the exercise: the term structure shift is *approximated* by the sum of these key rate durations (that constitute the vector).
David
David,
Thanks for the prompt clarification. I see now why it is not easy using the slide to explain the calculation. Thanks for the Excel.
One suggestion - as the key rate shift is small - with the difference after the 2 decimal places, the differences after the shock is not apparent from the Excel. This is because the rate cell is formatted with percentage of 2 decimal places. This gives an initial impression that the rate shocked are still the same, which is slightly misleading. After i changed to 4 decimal places, I found it easier to understand.
Another suggestion - To make the Excel a little more idiot proof for poor souls like me, perhaps you will want to add some comments to the section which you shock and linear interpolate the results. Being an Excel idiot and mathematically rusty, it took me a while before figuring out that you are using a linear interpolation formula
"
Regards
Wei You
Thanks for the prompt clarification. I see now why it is not easy using the slide to explain the calculation. Thanks for the Excel.
One suggestion - as the key rate shift is small - with the difference after the 2 decimal places, the differences after the shock is not apparent from the Excel. This is because the rate cell is formatted with percentage of 2 decimal places. This gives an initial impression that the rate shocked are still the same, which is slightly misleading. After i changed to 4 decimal places, I found it easier to understand.
Another suggestion - To make the Excel a little more idiot proof for poor souls like me, perhaps you will want to add some comments to the section which you shock and linear interpolate the results. Being an Excel idiot and mathematically rusty, it took me a while before figuring out that you are using a linear interpolation formula
"Regards
Wei You
Wei You,
Thank you for suggestion. I agree. I shared this with you raw. We didn't share this XLS last year, but for the 2008 exam I plan to share most/all of the key spreadsheets. So, I am really glad you find it helpful (I think it is the best way to understand many concepts like key rates)...so definitely I'll make this more user friendly per your suggestion!
David
Thank you for suggestion. I agree. I shared this with you raw. We didn't share this XLS last year, but for the 2008 exam I plan to share most/all of the key spreadsheets. So, I am really glad you find it helpful (I think it is the best way to understand many concepts like key rates)...so definitely I'll make this more user friendly per your suggestion!
David
Hi David,
I must say after viewing your excel spreadsheet on key rates shifts, i could understand it very much better.
1.Could you provide a spreadsheet for the key rates exposures for hedging instruments similar to table 7.2 of Tuckman's? i have trouble understanding how these key rates exposures are derived.
2. In Tuckman example (Table 7.2), there were 3 bonds (2,5,30 year) selling at par and 1 bond(10 year) selling at premium. I am not clear why the 2, 5 and 30 year par bonds are only exposed to their respective 2,5 and 30 key rates shifts but the 10 year bond selling at premium are exposed to all 2,5,10 and 30 key rate shifts?
Thank you in advance.
Peggy
I must say after viewing your excel spreadsheet on key rates shifts, i could understand it very much better.
1.Could you provide a spreadsheet for the key rates exposures for hedging instruments similar to table 7.2 of Tuckman's? i have trouble understanding how these key rates exposures are derived.
2. In Tuckman example (Table 7.2), there were 3 bonds (2,5,30 year) selling at par and 1 bond(10 year) selling at premium. I am not clear why the 2, 5 and 30 year par bonds are only exposed to their respective 2,5 and 30 key rates shifts but the 10 year bond selling at premium are exposed to all 2,5,10 and 30 key rate shifts?
Thank you in advance.
Peggy
Hi Peggy,
It's really great the XLS helps!
1. Yes, understood, I am working on an improved (more friendly) key rate, will upload that ASAP.
2. Right, me too. I actually don't agree with Tuckman on this, I agree with you. Or, I do not understand it even after trying many times. If his hedge instruments were zeros, it would make sense. He seems to be depending on: if bond trades @ par, then yield must equal coupon. But it doesn't help me: if you take the 5% coupon 5-year bond trading at par and then shock the 2% key rate, the price changes, so I don't agree. I either just don't get it or he meant to use zeros. (ironically, it is not the point of the text, so it would be simpler to forgo this "simplifying" assumption).
But i will say, i don't think it matters from a conceptual point. Other texts I have seen populate the entire matrix of 7.2. So, frankly, until somebody explains this i am going to tend to disagree with him here....
thanks,
David
It's really great the XLS helps!
1. Yes, understood, I am working on an improved (more friendly) key rate, will upload that ASAP.
2. Right, me too. I actually don't agree with Tuckman on this, I agree with you. Or, I do not understand it even after trying many times. If his hedge instruments were zeros, it would make sense. He seems to be depending on: if bond trades @ par, then yield must equal coupon. But it doesn't help me: if you take the 5% coupon 5-year bond trading at par and then shock the 2% key rate, the price changes, so I don't agree. I either just don't get it or he meant to use zeros. (ironically, it is not the point of the text, so it would be simpler to forgo this "simplifying" assumption).
But i will say, i don't think it matters from a conceptual point. Other texts I have seen populate the entire matrix of 7.2. So, frankly, until somebody explains this i am going to tend to disagree with him here....
thanks,
David
Hi David,
Thanks for your reply.
Could you also have a spreadsheet out to illustrate how the bucket analysis work and compare and contrast it with the key rate analysis?
I find of all the Tuckman chapters on Fixed Income, Chapter 7 is the most difficult to understand.
Kind Regards,
Peggy
Thanks for your reply.
Could you also have a spreadsheet out to illustrate how the bucket analysis work and compare and contrast it with the key rate analysis?
I find of all the Tuckman chapters on Fixed Income, Chapter 7 is the most difficult to understand.
Kind Regards,
Peggy
Similar threads
