Calculating Macauley, Modified, and Effective Bond Durations in Excel

[@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 👍

[@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

[@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!

[@MrsGG-id1os]

@@RyanOConnellCFA Modified Duration: “This formula is used to determine the effect that a 100-basis-point (1%) change in interest rates will have on the price of a bond” can it be used in cases when the interest rate changes by less than 1% ?

[@RyanOConnellCFA]

@@MrsGG-id1os Yes, Modified Duration can be used for interest rate changes smaller than 1%. It provides an approximation of price sensitivity for small changes in yield, typically up to about 0.5%. And actually, the smaller the change in rates the more accurate duration will be in predicting the price change. For larger changes or more precise calculations, especially with bonds that have embedded options, more advanced methods may be needed.

[@malakia_m]

Thanks Ryan

[@RyanOConnellCFA]

My pleasure Devin!

[@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.)

[@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)

[@okkysaputra9441]

sir can u create video how to calculate convexity?

[@RyanOConnellCFA]

I will look into this in the near future!

[@TesttestTesttest-h2k]

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

[@coomlord5360]

@@RyanOConnellCFA 1:32 , you are discounting a semi annual pmt on a yearly basis when you multiply the t by 2

[@FARISHAFADZIL-k8p]

this is so helpful thank you

[@RyanOConnellCFA]

You're welcome!

[@draco6061]

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

[@rohitsurve426]

He is just taking one example to show what would be the effective duration if the change in yield is 0.25%. You can assume any other number for change in yield. You may also notice that in the end he says the higher the change in yield you are assuming, the less accurate the Effective duration is going to be.

[@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