What is MDURATION in Google Sheets?
The MDURATION function in Google Sheets calculates the modified duration of a bond or fixed-income security. Calculating the modified duration helps investors understand how the price of a bond changes in response to fluctuations in the interest rate. The formula builds on the Macaulay duration but adjusts according to the interest rate compounding. It is commonly used in bond risk analysis to estimate the percentage price change for a 1% shift in yield. Thus, it is very useful for portfolio and investment planning.
In the example below, we have entered the values required in Google Sheets and applied the formula as shown below to calculate the modified duration.
=MDURATION(B1, B2, B3, B4, B5, B6). Press Enter.
The result shows how much the bond’s price is expected to change in response to a 1% change in interest rates. This helps investors evaluate and manage interest rate risk.
Key Takeaways
- The MDURATION in Google Sheets calculates the modified duration of a bond, which indicates how much the price of a bond will change with changes in interest rates.
- The syntax for the function is:
=MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention]) - MDURATION is different from DURATION because it adjusts the value based on the bond’s yield. Therefore, it is more accurate for estimating interest rate risk.
- The rate and yield must be provided in decimal form, and the frequency must be 1 (annual), 2 (semi-annual), or 4 (quarterly).
Syntax
=MDURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
1. settlement – is the date when the security is traded to the buyer after issuance. It marks the start of ownership and cash flow for the investor.
2. maturity – the end date or maturity of the security when the issuer repays the face or par value to the buyer.
3. rate – is the annualized coupon interest rate paid by the security. It represents the return provided to the investor through periodic payments.
4. yield – is the annual yield or return that the investor expects from the security, often based on market interest rates.
5. frequency – is the number of coupon payments made per year. Common values include:
- 1 for annual payments
- 2 for semi-annual payments
- 4 for quarterly payments
6. day_count_convention (optional) – [0 by default] – defines the day count method used to calculate the duration. Google Sheets accepts five different values:
- 0 – US (NASD) 30/360: Assumes 30-day months and 360-day years, with adjustments for end-of-month dates.
- 1 – Actual/actual: Uses the actual number of days between dates and in the year.
- 2 – Actual/360: Counts actual days but assumes a 360-day year.
- 3 – Actual/365: Counts actual days with a 365-day year.
- 4 – European 30/360: Similar to option 0 but follows European end-of-month conventions.
How To Use MDURATION Function in Google Sheets?
The MDURATION function can be manually typed into Google Sheets as a formula, or it can be selected from the Google Sheets menu. First, let’s look at how to manually use the MDURATION function with an example.
Entering MDURATION Manually
Let us look at an example. We have the bond details entered in a spreadsheet. The bond is purchased on 01-Jan-2020, with a maturity date of 01-Jan-2030. It has an annual coupon rate of 7%, and the expected yield is 5%. Interest is paid semi-annually.
Step 1: Enter the following details in a Google Sheet as shown:
Step 2: In cell B8, let us type the MDURATION formula, as shown below.
=MDURATION(
Step 3: Enter each parameter in the correct order:
=MDURATION(B1, B2, B3, B4, B5, B6)
Close the parentheses and ensure the correct cell references are used for the inputs.
Step 4: Press Enter. You will now see the modified duration value displayed. In this example, it is 7.3 years, which reflects the bond’s sensitivity to changes in interest rates.
Inserting MDURATION through the Google Sheets Menu
Click the cell where you want to display the result.
- Go to the Insert tab on the Google Sheets toolbar.
- Choose Function, then go to Financial.
- From the list of financial functions, select MDURATION.
- Fill in the required parameters.
- Google Sheets returns the modified duration based on the inputs.
Examples
We use the MDURATION formula in Google Sheets to calculate the modified duration of a fixed-income security, which reflects its price sensitivity to changes in interest rates. This function is particularly useful in bond portfolio management and interest rate risk analysis. Let us explore some key scenarios where the MDURATION function is very useful in finding the duration.
Example #1 – Calculate the modified duration for a bond bought on January 1, 2025, maturing on January 1, 2028, with an annual coupon rate of 6% and a yield of 3%, with quarterly payments
An investor purchases a bond on January 1, 2025, that matures on January 1, 2028. The bond has a face value of $5000, an annual coupon rate of 6%, and a yield of 3%. The bond pays interest quarterly. Let us calculate the modified duration using the MDURATION function in Google Sheets.
Step 1: Enter the bond details into the sheet as shown below:
Step 2: Use the MDURATION formula as shown below:
=MDURATION(B1, B2, B3, B4, B5, B6)
Step 3: Alternatively, you can directly enter the formula using the arguments in another cell (e.g., B9).
=MDURATION(DATE(2025, 1, 1), DATE(2028, 1, 1), 0.06, 0.03, 4, 0)
Step 4: Press Enter. You will get a modified duration value of around 2.77 years, which reflects the bond’s sensitivity to changes in interest rates.
Example #2 – Calculate the modified duration for a bond bought on January 1, 2025, maturing on January 1, 2030, with an annual coupon rate of 3% and a yield of 5%, with semi-annual payments
An investor purchases a bond on January 1, 2025, which matures on January 1, 2030. The bond offers an annual coupon rate of 3% and the expected yield is 5%. Interest payments are made semi-annually. We will calculate the modified duration for these values.
Step 1: Enter the bond details into a spreadsheet, as shown below:
Step 2: In cell B8, type the following MDURATION formula:
=MDURATION(B1, B2, B3, B4, B5, B6)
Step 3: You can also enter the values directly, as we did in the previous example. Let us check the result both ways.
=MDURATION(DATE(2025, 1, 1), DATE(2030, 1, 1), 0.03, 0.05, 2, 0)
Step 4: Press Enter. We get the modified duration value as approximately 4.38 years. This result helps the investor understand how much the bond’s price may change in response to a 1% change in interest rates.
Example #3 – Calculate the modified duration for a bond bought on January 1, 2025, maturing on January 1, 2032, with an annual coupon rate of 5% and a yield of 4%, with annual payments
A person purchased a bond on January 1, 2025, which will reach maturity on January 1, 2032. The bond offers an annual interest rate of 5%, and the investor anticipates a 4% yield. Since there are payments0 once per year, the payment frequency varies accordingly. Let us use the MDURATION function in Google Sheets to determine the bond’s modified duration.
Step 1: Let us fill in the bond data in a spreadsheet.
Step 2: In cell B8, insert the following formula to calculate modified duration:
=MDURATION(B1, B2, B3, B4, B5, B6)
Step 3: Press Enter. You get the bond’s modified duration, which is around 5.87 years. This means the bond’s price is expected to decrease by around 5.87% for every 1% increase in interest rates.
Important Things to Note
- The MDURATION in Google Sheets returns the modified duration, which adjusts for yield and is more accurate for interest rate sensitivity.
- Frequency must be 1 (annual), 2 (semi-annual), or 4 (quarterly). Use only these three values.
- MDURATION uses the Macaulay duration internally but adjusts it to reflect price sensitivity using the yield.
- One must enter the coupon rate and yield as percentages or decimal values (e.g., 6% as 0.06.
- If the rate, yield, or frequency values are invalid, we get an #NUM error.
Frequently Asked Questions (FAQs)
1. We get an #VALUE! error when a date is invalid or there is incorrect format.
2. A #NUM! error often occurs when frequency is not 1, 2, or 4, or when yield/rate values are out of range.
3. Also ensure that the settlement date is before the maturity date to avoid potential errors. The day count basis should also be within the accepted range (0 to 4).
In case of an error, review each argument carefully to fix the error.
We use both MDURATION and DURATION in Google Sheets to calculate the duration, but they differ in what they measure.
DURATION returns the Macaulay duration, which is the weighted average time to receive cash flows.
MDURATION in Google Sheets adjusts for yield and gives the modified duration, which reflects price sensitivity. Modified duration is more accurate when interest rates change.
MDURATION is used for interest rate risk, while DURATION is used for time-weighted analysis.
Dates should be entered using the DATE function or as formatted date cells in Google Sheets.
Download Template
This article must help understand MDURATION Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is MDURATION function in Google Sheets. We learn its syntax and how to use it to find the modified duration of a bond with examples. You can learn more from the following articles. –
Leave a Reply