Calculating Macauley, Modified, and Effective Bond Durations in Excel

Join Ryan O'Connell, CFA, FRM, as he dives deep into Calculating Macauley, Modified, and Effective Bond Durations using Excel. Starting with the basics of calculating the present value of a semi-annual bond, this tutorial will guide you step-by-step through the intricacies of bond duration calculations. Bond duration is a key concept for understanding interest rate risk. Whether you're a finance professional or just keen to learn, this video offers valuable insights and hands-on techniques. Don't forget to like, share, and subscribe for more expert finance tutorials!
💾 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 - Intro to Calculating Bond Durations in Excel
0:13 - Calculating the Present Value of Semi-Annual Bond
2:37 - Calculate Macauley Duration
4:05 - Calculate Modified Duration
5:21 - Calculate Effective Duration
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.

[@RyanOConnellCFA]

💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/excel-bond-duration-calculator-macauley-modified-and-effective/

[@alexandrehamel6062]

Much easier than reading through Fabozzi's book in order to get a good initial understanding of the concept. Great job!

[@RyanOConnellCFA]

Glad it was helpful, and thank you for your comment! I try to simplify these complex concepts as best I can 👍

[@malakia_m]

Thanks Ryan

[@RyanOConnellCFA]

My pleasure Devin!

[@tcc447]

Enjoyed the video. Have you tested the DURATION function in Excel? It's supposed to calculate the Macauley Duration but I don't think it works perfectly. Also, towards the end when you are calculating the various prices, you used the PV function. I just want to note you could have simplified and used the NPV function which only requires the future stream of cashflows and the discount rate as inputs.

[@RyanOConnellCFA]

I have not used the duration function in Excel! Thanking you for pointing that out to me. I will check it out now. And also, thanks for the time saving tip on the NPV function!

[@robertocortesi4236]

Good Video ! Could you make a video that would also integrate the calculation of the convexity into the sheet presented? This way it would be more complete, even if the convexity doesn't have much impact in "normal" rate cases! Thanks in advance if you do!!!!

[@RyanOConnellCFA]

Thank you! Could you check out this video and let me know if it answers your question: kzbin.info/www/bejne/g6utmpunfJd3e6M

[@elektramagic4266]

Hi Ryan, next year I'll be completing my Master's in Finance and I'd love to work in the investment field. My preference is to perform portfolio analyses and be capable of managing risks (which are interconnected). I'm torn between CFA and FRM since both seem very intriguing to me. Your videos are so engaging that my sleep hours have reduced a bit these last few days 😄 What would you recommend?

[@RyanOConnellCFA]

Hey! I'm surprised to hear you are losing sleep watching my videos as I always thought these videos would be quite boring for people 😂 I made a video answering this exact question of CFA vs FRM. Please check it out and let me know if it answers your question or if you have more! kzbin.info/www/bejne/d57adnibfsZrsMk

[@elektramagic4266]

@@RyanOConnellCFA I've decided to go for the CFA. I purchased the course and started right away. I've noticed that many topics are ones I've already learned in my master's, but other subjects do go into more depth. Over the next few months, I'm planning to study this thoroughly and then consider taking the exam. Do you think that if I apply with a Master's in Finance on my resume and a CFA Level 1 certificate, I'll have a good chance of getting opportunities to work at an investment bank/fund/... or do they expect more? (This is separate from your personal profile.)

[@okkysaputra9441]

sir can u create video how to calculate convexity?

[@RyanOConnellCFA]

I will look into this in the near future!

[@draco6061]

where does the change in YTM of .25% at mm 6:00 come from?

[@serenegohsj]

I'm confused. Is mod d actually units in terms of additional YEARS one needs to hold the bond for a 1% change in ytm?? The % change in bond price is = (-modD) x (change in ytm)

[@RyanOConnellCFA]

No, you do not need to hold the bond for that many years to experience the 1% change in YTM. If YTM changes by 1% today, the bond should change by -modD * change in YTM today (assuming 0 convexity)

[@theilsguy]

You say in the video that Modified Duration is 'The sensitivity in bond price changes to changes in Interest Rates '. But I thought that definition corresponded to the Macaulay Duration ?? What is the difference between Macaulay and Modified duration then ?

[@RyanOConnellCFA]

Great question! Macaulay Duration measures the weighted average time until a bond's cash flows are received, while Modified Duration adjusts Macaulay Duration to directly measure the bond's price sensitivity to interest rate changes. Essentially, Modified Duration provides a more direct and practical measure of interest rate risk

[@theilsguy]

Wow I wasn't expecting an answer that fast. Thank a lot ! :)@@RyanOConnellCFA

[@RyanOConnellCFA]

@@theilsguy It is my pleasure!

[@user-ls1hp3dz7f]

Why time is ×2 when calculate macdur it a semi annual why ×2

[@RyanOConnellCFA]

Hello, could you let me know the timestamp for what part of the video you are referring to?

[@danielloza4710]

@@RyanOConnellCFA 1:37, I have the same doubt

[@MrDeadlyCrow]

EffDur is not correct

[@RyanOConnellCFA]

What is incorrect about it?

[@MrDeadlyCrow]

@@RyanOConnellCFA Where is your par and spot curve/rates? Did you assume flat curve?

[@RyanOConnellCFA]

We do not need to know anything about the par curve or spot curve to calculate effective duration. All we need to know is the overall yield to maturity (discount rate) for our bond