Calculate Bond Convexity and Duration in Excel | Interest Rate Risk
Вставка
- Опубліковано 22 тра 2024
- In this insightful tutorial, Ryan O'Connell, CFA, FRM delves deep into the concepts of "Bond Convexity and Duration in Excel" to give you a robust understanding of these critical interest rate risk parameters. Whether you are a seasoned investor or just starting your journey in the financial markets, this tutorial is designed to equip you with the skills to analyze bonds like a pro using Excel. You will see how to calculate both bond duration and bond convexity using Excel. Join as we unpack the intricate dynamics of bond investments, one formula at a time.
💾 Purchase the file created in this video here: ryanoconnellfinance.com/produ...
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
📚 CFA Exam Prep Discount - AnalystPrep:
► Get 20% off CFA Level 1, 2, and 3 complete courses with promo code "RYAN20". Explore here: analystprep.com/shop/all-3-levels-of-the-cfa-exam-complete-course-by-analystprep/?ref=mgmymmr
📘 FRM Exam Prep Discount - AnalystPrep:
► Get 20% off FRM Part 1 and Part 2 complete courses with promo code "RYAN20". Explore here: analystprep.com/shop/frm-part-1-and-part-2-complete-course-by-analystprep/?ref=mgmymmr
Chapters:
0:00 - How to Calculate Bond Duration in Excel
2:21 - What Bond Duration Means for Bond Prices
2:56 - How to Calculate Bond Convexity in Excel
6:27 - Calculate Changes in Bond Prices Based on Convexity
9:33 - Convexity Changes at Different Interest Rates
Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC. Some of the links above are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/bond-convexity-duration-calculator-in-excel/
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
👨💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
Thank you very much for your videos! It s really hard to get that kind of content around
It is my pleasure! I really appreciate your feedback Pablo
This is awesome!
Thank you, and found your channel Norman!
Your videos have been very helpful!!! Thank you for your hard work!
Thank you and you're certainly welcome!
Amazing content again! I guess dv01 and pv01 are the next? 😊
Thank you! DV01 is the synonymous with Dollar Duration which I discuss in this video @4:45
I may make a DV01 specific video in the future but that won't be for a while as I have a large backlog
Keep up the great work with these videos! I am reviewing the Fixed Income Valuation & Risk/Return readings - this is a great help.
Thank you, I appreciate the feedback and have no plans on stopping! 💪
I have a doubt regarding the formula for the duration implied price and duration + convexity implied price for YTM larger than 5%. Why in one we use the 5% (base) minus 5.01% and in the other use the 5.01% minus the 5% (base)? Because my numbers only matched yours with this difference in formulas, when they should be the same I guess.
Hi, thank you for the video.
Had a doubt. Doesnt the formula for change in rice have modified duration instead of MacAuley duration?
You are correct that modified duration is more appropriate to use for measuring changes in bond prices based on changes in interest rates. I go into detail on the differences between the two (as well as effective duration here: ua-cam.com/video/2tXjJR1W0YU/v-deo.html
Shouldn’t you take modified duration to compute the change in price ?
Hey! What a great video, it has been very useful! I just have one quick question, why when you calculated the estimated bond price using duration, you used MacDuration instead of Modified Duration. I though that MacD is the weighted average time it takes to get all the cashflows and Modified Duration is the % change that the bond price will have by 1% change in YieldToMat, if you can help me to clarify this, I will appreciate it, thanks!
You are correct Sebastian! Modified Duration is more appropriate for estimating changes in bond prices based on changes in interest rates. I have a video here that breaks down the different duration metrics in detail here: ua-cam.com/video/2tXjJR1W0YU/v-deo.html
Isn't the interpretation you give for the Macauly Duration actually the Modified Duration?
The interpretation can be used for both but you are right that you will see that interpretation used more often with the modified duration. I have a video breaking down Macaulay duration, modified duration, and effective duration here: ua-cam.com/video/MzJihqG2DEA/v-deo.html
To think I actually read the garp FRM books when i could've done my eyes/brain a favor by watching thrse videos
Hahaha much appreciated! Those were some rough books, I just went straight to the Schweser Notes when I was taking the tests
Do you have a video that explains the difference between positive and negative convexity? What does negative convexity mean?
I don't currently have a video on that topic yet, but I appreciate the suggestion and will consider exploring it in a future video.
Positive convexity in bonds means that the bond's duration increases as interest rates fall, typically leading to larger price increases and smaller price decreases. Negative convexity, on the other hand, occurs when a bond's duration decreases as interest rates fall, often seen in bonds with features like prepayment options, resulting in limited price appreciation and greater price sensitivity to rising rates.
In general which one would you recommend more: masters in finance or cfa?
Hey! It depends on your situation and preferences. I addressed this question exactly in this video here: ua-cam.com/video/nJ-PNKbIMD8/v-deo.html
Thanks for the video, it made it very clear how the math works. However, I'm unclear on why someone would go thru the effort to calculate new Bond Price when interest rates change using the Macaulay/Convexity method. Why isn't it sufficient to just use the PV function in Excel to find the new Bond Price as interest rates change? This would be your Column C in the table beginning on row23. Your Column D is the Bond Price as a result of using the Macaulay/Convexity method, and it is close to Column C. I believe your explaining that the slight difference between Col C and Col D is due to the fact that Convexity changes depending on the interest rate.
You're right, I was using that as an example to show convexity changes at different interest rates for a particular bond. It would be easier to calculate with the PV function, this was a bit more of a thought experiment than anything else
I used your Convexity Formula but it is not matching with Bloomberg. Any thoughts ?
Bloomberg has much lower convexity numbers than using the methodology I've found in text books. You can ask Bloomberg helpdesk for the methodology by which they calculate convexity to see if you can back engineer how they calculate it
Would you recommend cfa or FRM or mba for senior folks in risk management who are looking to ipskill outside of their primary domains (eg: operational risk).
Hey! If you are looking to upskill outside of your primary domain then I would say probably CFA or MBA. I would lean CFA if you want to work in more technical financial analysis roles or MBA if you want to push for middle management. In my opinion the CFA has more prestige than almost all MBA programs though
I don't know if I did this right but the dollar convexity and convexity from YTM of 4.99 and 5.01 is giving me 146710.09 for $ Conv/1588.6 for Conv. and 3649.68 for $Conv/39.58 for Conv? What is the reason for this.
This seems a bit high to me but it is hard for me to tell where you may have gone wrong based on this comment alone. You may need to walk back through each step exactly to make sure nothing has been missed or skipped
How did you find the initial convexity
You can see when I calculate convexity in the video by looking at the chapter time stamps