{"id":1170,"date":"2019-07-26T17:54:04","date_gmt":"2019-07-26T14:54:04","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=1170"},"modified":"2019-07-26T18:10:19","modified_gmt":"2019-07-26T15:10:19","slug":"how-to-use-the-financial-functions-duration-mduration","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-financial-functions-duration-mduration\/","title":{"rendered":"How to use the Financial Functions DURATION, MDURATION"},"content":{"rendered":"\n<p> The Macaulay duration (named after Frederick Macaulay) and its modified version, is a measure of a bond&#8217;s sensitivity to interest rate changes.  <\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/T5yvMWQRHRA?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<p>Technically, duration is the weighted average number of\nyears the investor must hold a bond until the present value of the bond\u2019s cash\nflows equals the amount paid for the bond. Bonds with a higher duration will\ncarry more risk, and hence have a greater volatility in prices, when compared\nto bonds with lower durations.<\/p>\n\n\n\n<p>Excel gives us two function to calculate both versions of Macaulay duration. The DURATION and the MDURATION.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/07\/DURATION.MDURATION.xlsx\">DURATION.MDURATION<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/07\/DURATION.MDURATION.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>In cells B10 and B11 we have already calculated these values\nwhich are represented in years.<\/p>\n\n\n\n<p>Let\u2019s try to calculate them again using a different security\ninvestment.<\/p>\n\n\n\n<p>In cell B10 we will calculate the Macaulay Duration.<\/p>\n\n\n\n<p>We type the name of the function, the settlement date from\ncell B4, maturity date from B5, the coupon rate from B6, the yield from B8 and\nthe frequency of coupon payments from B7.<\/p>\n\n\n\n<p>The last argument, basis, is optional and it helps us choose\nthe type of day count to use. Since we will always use the default 0 value, we\ncan omit it.<\/p>\n\n\n\n<p>In Cell B11 we will use the MDURATION function, which\ncalculates the modified Macaulay duration. The arguments are exactly the same.\nCell B4, cell B5, cell B6, cell B8 and cell B7. We again omit the last\nargument.<\/p>\n\n\n\n<p>If we change the annual coupon rate from 4 to 10 or 1 per\ncent we would observe that the two values are fairly stable. This means that\nthey are not so sensitive to interest rate changes, thus making them a relative\nsafe investment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p> The Macaulay duration (named after Frederick Macaulay) and its modified version, is a measure of a bond&#8217;s sensitivity to interest rate changes.  <\/p>\n","protected":false},"author":2,"featured_media":1172,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[167,115,168],"class_list":["post-1170","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-duration","tag-financial","tag-mduration"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/07\/duration-mduration_tut.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-iS","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/1170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=1170"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/1170\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/1172"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=1170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=1170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=1170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}