How to Use the DURATION and MDURATION Functions in Google Sheets for Bond Sensitivity Analysis

Evaluating interest rate risk in fixed-income investments requires understanding how sensitive a bond portfolio is to fluctuations in market yields. Rather than relying on simple maturity dates, financial analysts calculate duration to measure the weighted average time until all coupon and principal cash flows are received. In Google Sheets, the native DURATION and MDURATION functions deliver automated, institution-grade calculations for Macaulay duration and modified duration without requiring custom discounting matrices.

Understanding Macaulay Duration and Modified Duration

While both metrics quantify bond behaviour, they answer distinct analytical questions:

• Macaulay Duration (DURATION): Measures the effective weighted average maturity of the bond’s cash flows, expressed in years. It reveals the exact point in time at which an investor recoups half the present value of the bond’s cash flows.
• Modified Duration (MDURATION): Quantifies the percentage price sensitivity of a bond relative to a 100-basis-point (1%) change in yield to maturity. It adjusts Macaulay duration by dividing it by (1 + Yield / Frequency), providing a direct metric for portfolio volatility.

Syntax and Parameters for DURATION and MDURATION

Both functions share identical parameter signatures across financial models:

=DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
=MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])

Every argument must follow standard Google Sheets financial conventions:

Parameter	Required/Optional	Data Type	Description
settlement	Required	Date	The date after issuance when the security is delivered to the buyer. Must be wrapped in DATE().
maturity	Required	Date	The final expiration date when the principal face value is repaid. Must fall strictly after settlement.
rate	Required	Percentage / Number	The annual coupon rate stated on the bond indenture (e.g. 5.5% or 0.055).
yield	Required	Percentage / Number	The current annualised yield to maturity (YTM) demanded by the secondary market.
frequency	Required	Integer	Coupon payments per calendar year: 1 (Annual), 2 (Semi-annual), or 4 (Quarterly).
day_count_convention	Optional	Integer	Financial basis: 0 (US 30/360, default), 1 (Actual/Actual), 2 (Actual/360), 3 (Actual/365), 4 (European 30/360).

Step-by-Step Implementation in Google Sheets

To construct an automated bond risk evaluation model, follow these implementation steps:

1. Open your spreadsheet in Google Sheets and populate a designated input block with bond indenture details:
   • Cell B2: Settlement Date (=DATE(2026, 7, 1))
   • Cell B3: Maturity Date (=DATE(2036, 7, 1))
   • Cell B4: Annual Coupon Rate (6.00%)
   • Cell B5: Market Yield to Maturity (4.50%)
   • Cell B6: Payment Frequency (2 for semi-annual)
   • Cell B7: Day Count Convention (1 for Actual/Actual US Treasury)
2. To compute the Macaulay duration in years, enter the following formula in cell B9:

   =DURATION(B2, B3, B4, B5, B6, B7)

3. To compute the modified duration for percentage price sensitivity, enter this formula in cell B10:

   =MDURATION(B2, B3, B4, B5, B6, B7)

Translating Modified Duration into Bond Price Changes

The primary utility of MDURATION lies in estimating capital gain or loss when interest rates shift. The foundational approximation formula is:

Estimated Percentage Price Change = -1 * Modified Duration * Change in Yield

For instance, if your 10-year Treasury note yields a modified duration of 7.42 years and market benchmark rates rise by 50 basis points (+0.50%):

Estimated Price Change = -1 * 7.42 * 0.0050 = -3.71%

Conversely, if central banks cut interest rates by 25 basis points (-0.25%), the estimated capital appreciation equals +1.855%.

Comparing Zero-Coupon, Discount, and Premium Bonds

Bond structure fundamentally influences cash-flow timing. Google Sheets models reflect these critical financial relationships:

Bond Structure	Coupon vs Yield	Macaulay Duration Relation	Interest Rate Sensitivity
Zero-Coupon Bond	Coupon = 0%	Macaulay Duration exactly equals Years to Maturity	Maximum potential volatility for a given maturity
Discount Bond	Coupon < Yield	Duration remains higher due to heavy weighting of final par value	High price volatility
Premium Bond	Coupon > Yield	Duration is lower because large coupon flows front-load cash receipts	Lower price volatility relative to maturity

Handling Common Financial Errors

When working with financial formulas in Google Sheets, watch out for these syntax anomalies:

• #NUM!: Occurs if the settlement date is later than or equal to the maturity date, if coupon rates or yields are negative, or if frequency is set to an unsupported integer such as 3 or 12.
• #VALUE!: Triggered when date arguments are entered as plain unparsed strings (such as "01/07/2026") that Google Sheets cannot interpret. Always supply dates via the DATE(year, month, day) function.