{"id":52959,"date":"2025-04-01T09:00:00","date_gmt":"2025-04-01T07:00:00","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=52959"},"modified":"2025-06-03T20:48:28","modified_gmt":"2025-06-03T18:48:28","slug":"mdura-formula-gs2","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/es\/blog\/google-sheets-features\/mdura-formula-gs2\/","title":{"rendered":"F\u00f3rmula MDURATION en Google Sheets para un mejor an\u00e1lisis de bonos"},"content":{"rendered":"\n[et_pb_section fb_built=”1″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”][et_pb_column type=”4_4″ _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” min_height=”277.6px” global_colors_info=”{}”]

Introduction<\/b><\/h4>\n

Imagine you’re exploring bond investments and you’re trying to figure out not just when you’ll get your money back, but also how changes in interest rates might affect your returns. This is where the MDURATION formula in Google Sheets shines. It provides a clear picture of the weighted average time until a bond’s cash flows repay the investment, adjusted for interest rate sensitivity. Let\u2019s dive into how this formula works and apply it to real-world scenarios for better financial decisions.<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”]

Understanding MDURATION<\/b><\/h4>\n

MDURATION stands for modified Macaulay duration and is particularly useful for bond investors to measure the time it takes to recover their investment in present value terms, while also considering the bond’s yield and interest rate.<\/p>\n

Syntax Explained<\/b><\/h5>\n

Here’s how the MDURATION formula is structured:<\/p>[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”]

MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”]

    \n
  • settlement<\/b>: The date you start owning the bond.<\/li>\n
  • maturity<\/b>: The date the bond will pay back its principal.<\/li>\n
  • rate<\/b>: The annual coupon rate of the bond.<\/li>\n
  • yield<\/b>: Your expected annual return from the bond.<\/li>\n
  • frequency<\/b>: How many times a year interest is paid (e.g., annually, semiannually).<\/li>\n
  • day_count_convention<\/b>: This optional parameter defines the method for counting days in a year, which influences duration calculations.<\/li>\n<\/ul>\n

    Setting Up Google Sheets<\/b><\/h4>\n

    To utilize this formula effectively, you’ll want to set up a scenario in Google Sheets. Here\u2019s how to organize your data:<\/p>\n

    Sheet Layout:<\/b><\/p>\n

      \n
    • A<\/b>: Description<\/li>\n
    • B<\/b>: Settlement Date<\/li>\n
    • C<\/b>: Maturity Date<\/li>\n
    • D<\/b>: Rate<\/li>\n
    • E<\/b>: Yield<\/li>\n
    • F<\/b>: Frequency<\/li>\n
    • G<\/b>: Day Count Convention<\/li>\n
    • H<\/b>: MDURATION Result<\/li>\n<\/ul>\n

      Input some hypothetical data to analyze a potential bond investment:<\/p>\n

        \n
      • Settlement Date<\/b>: “2023-01-01”<\/li>\n
      • Maturity Date<\/b>: “2028-01-01”<\/li>\n
      • Rate<\/b>: 5%<\/li>\n
      • Yield<\/b>: 4.5%<\/li>\n
      • Frequency<\/b>: 2 (semiannual payments)<\/li>\n<\/ul>\n

        Practical Example<\/b><\/h4>\n

        Calculate the MDURATION for different day count conventions to see the variation in results:<\/p>[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”]

         <\/p>\n\n\n\n\n\n\n
        A<\/b><\/td>\nB<\/b><\/td>\nC<\/b><\/td>\nD<\/b><\/td>\nE<\/b><\/td>\nF<\/b><\/td>\nG<\/b><\/td>\nH<\/b><\/td>\n<\/tr>\n
        Example 1<\/td>\n2023-01-01<\/td>\n2028-01-01<\/td>\n5%<\/td>\n4.5%<\/td>\n2<\/td>\n0<\/td>\n=MDURATION(B2, C2, D2, E2, F2, G2)<\/td>\n<\/tr>\n
        Example 2<\/td>\n2023-01-01<\/td>\n2028-01-01<\/td>\n5%<\/td>\n4.5%<\/td>\n2<\/td>\n1<\/td>\n=MDURATION(B3, C3, D3, E3, F3, G3)<\/td>\n<\/tr>\n
        Example 3<\/td>\n2023-01-01<\/td>\n2028-01-01<\/td>\n5%<\/td>\n4.5%<\/td>\n2<\/td>\n4<\/td>\n=MDURATION(B4, C4, D4, E4, F4, G4)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>[\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}”]

        Observations and Insights<\/b><\/h4>\n
          \n
        • Example 1<\/b>: Uses a 30\/360 day count convention, typical in the U.S., showing a traditional financial calculation approach.<\/li>\n
        • Example 2<\/b>: Uses actual days between dates for more precise calculations.<\/li>\n
        • Example 3<\/b>: Adapts to European conventions, illustrating how financial practices in different regions affect duration.<\/li>\n<\/ul>\n

          Conclusion<\/b><\/h4>\n

          The MDURATION formula in Google Sheets is a powerful tool that enables investors to assess how sensitive their bond investments are to changes in interest rates. By understanding and applying this formula, you can make more informed decisions about your bond purchases, strategically planning your investments to match your financial goals and risk tolerance. Whether you\u2019re a novice investor or an experienced financial analyst, Google Sheets provides the tools you need to analyze and manage your investment portfolio effectively.<\/p>\n

           <\/p>[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]\n","protected":false},"excerpt":{"rendered":"

          Introduction Imagine you’re exploring bond investments and you’re trying to figure out not just when you’ll get your money back, but also how changes in interest rates might affect your returns. This is where the MDURATION formula in Google Sheets shines. It provides a clear picture of the weighted average time until a bond’s cash […]<\/p>\n","protected":false},"author":46,"featured_media":44470,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[37],"tags":[28],"class_list":["post-52959","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-features","tag-spreadsheets"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/52959","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/users\/46"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/comments?post=52959"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/posts\/52959\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media\/44470"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/media?parent=52959"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/categories?post=52959"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/es\/wp-json\/wp\/v2\/tags?post=52959"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}