What is DURATION Function in Google Sheets?
The DURATION function in Google Sheets returns the number of compounding periods an investment requires to achieve a target value. It is very useful for investors and financial managers to calculate the time until we receive the cash flow of an investment.
It measures the volatility of a bond to changes in interest rates. It shows how long a bond takes to pay back its invested principal through cash flows. The DURATION function returns the Macaulay duration of a security, commonly used by financial analysts. The Macaulay duration is a concept commonly used by portfolio managers. It measures the time taken for the principal of a bond to be repaid from its internal cash flows. In the simple example below, we have entered the necessary details and obtained the duration in cell B6 in years, as shown below.
Key Takeaways
- The DURATION function is used to calculate the Macaulay duration of a bond, which shows the bond’s sensitivity to interest rates. The function returns the number of compounding periods required by an investment to achieve a target value.
- The syntax of DURATION in Google Sheets is as follows:
=DURATION(settlementdate, maturitydate, couponrate, yield, frequencyofpayment, [day_count_convention])
- By calculating the duration for different bonds, you can compare them and find which bond is more sensitive to changes in the interest rate.
- A bond with a longer duration is more sensitive to changes in interest rates.
Syntax
=DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
- settlement – is the date after issuing the security, when it is delivered to the buyer.
- maturity – is the end or maturity date of the security, when the buyer can redeem it at face or par value.
- rate – is the annualized interest rate at which the investment appreciates.
- yield- is the annual yield that the buyer expects for the security.
- frequency – is the number of interest payments a buyer can pay annually.
- day_count_convention – [OPTIONAL parameter – 0 by default ] – it is an indicator of the day count method that Google Sheets should consider. There are five different possible values for this parameter.
- 0 – assumes that there are 30-day months and 360-day years (US (NASD) 30/360). Using this value ensures there are specific adjustments to the entered dates at the end of the month.
- 1 – calculates based on the number of days between the specified dates and the number of days in the intervening years.
- 2 – calculates based on the number of days between the specified dates but assumes a 360-day year.
- 3 – evaluates the DURATION formula in Google Sheets based on the actual number of days between the specified dates but assumes a 365-day year.
- 4 – very similar to the first option 0, except it adjusts end-of-month dates according to European financial conventions.
How to Use DURATION Function in Google Sheets?
The DURATION can be manually entered as a formula in Google Sheets. It can also be entered through the Google Menu bar. First, let us look at how we can enter the function manually.
In the example below, we have all the necessary coupon details entered in a spreadsheet. The coupon is purchased on 01-Jan-2015, with maturity on 1-Jun-2025. The coupon rate of 8%, and the yield is 6%. The payments are made quarterly.
Step 1: Enter the above details in a Google sheet, as shown below.
Step 2: Now, apply the formula for the DURATION in cell B8, as shown below.
First, enter the following:
=DURATION(
Step 3: Enter the parameters in the required order one by one and close the parentheses.
Step 4: Press Enter. Now you get the duration value as 7.09 years.
Entering the DURATION Through the Menu Bar.
Place the cursor where you want the formula to be entered.
Go to the Insert tab in Google Sheets.
Select the option Function and then Financial.
From the list of functions, select the DURATION function.
Examples
We use the DURATION formula in Google Sheets to calculate the count of the compounding periods of an investment to reach a target value. It is primarily used in fixed-income securities analysis. Let us look at the other areas where this function is very useful.
Example #1 – Calculate the duration of a bond given a yield of 5%
A businessman has a 5-year bond with a $5000 face value. The annual coupon rate is 6%, and the yield is 5%.
The other details, including the settlement date, are 01/01/2015, and the maturity date is 1/01/2023. Find the Macaulay duration of the bond.
Step 1: Let us calculate the duration using DURATION in Google Sheets. We have entered the bond details in a sheet as shown below.
Step 2: Enter the following function as shown below in B6.
=DURATION(B1,B2,B3,B4,B5).
It can also be entered directly with the arguments, as shown in cell B8.
=DURATION(DATE(2015, 1, 1), DATE(2023, 1, 6), 0.06, 0.05, 1)
Step 4: Press Enter. You get a similar value of 6.29 years, as seen below.
Example #2 – Combining DURATION with IF function
We can also use the DURATION function with other functions like IF. Let us implement the DURATION function with the IF function in Google Sheets. IF is a logical function that can be applied to check for certain conditions before calculating the duration. In this example, let us calculate the bond duration only if the yield rate meets a specific threshold, say 6%.
Step 1: Enter all the details as shown below in a sheet. Here, we entered the details as a header and two sets of details of two coupons. Let us use the IF with the DURATION formula for both options.
Step 2: Enter the following formula in cell F2.
=IF(D2 > 0.05, DURATION(A2, B2, C3, D2, E2), “Coupon rate is less”)
Step 3: Press enter and observe the result.
Step 4: Now, drag the formula to cell F3. Observe the result.
The IF function checks if the yield rate is greater than 0.05 (i.e., 5%). If so, it will calculate the bond duration. Otherwise, you get the message “Coupon rate is less.”
Example #3 – Using DURATION with Conditional Formatting
We can use DURATION with conditional formatting based on our requirements to highlight specific cells. We can enter different formulas and conditions, as shown below.
Step 1: We have calculated the duration using the formula for the following details.
Step 2: Enter the following formula in cell B6 to find the duration.
=DURATION(B1,B2,B3,B4,B5)
Step 3: Drag the formula across the row until E6 to get the coupons’ durations.
Step 4: Now, we wish to apply conditional formatting for coupons that have a duration of less than 5 years. To conditionally format the duration in Google Sheets, follow these steps.
Select the range from B1:E6. Go to “Format” > “Conditional formatting.”
Step 5: In the “Conditional format rules” pane, under “Format cells if…,” choose “Custom formula is.” Enter the following formula: B$6 < 5. Here, we enter the column as an absolute reference ($) as we want the entire column to be shaded if the condition is TRUE. Select the required background color and press Done,
Step 6: It applies the formatting to all columns with less than 5 years of duration.
Important Things to Note
- The DURATION function is important as it helps determine if you want to buy or sell bonds.
- If the date is invalid, you get a #VALUE! Error.
- The settlement and maturity date arguments should be entered using the function DATE or other date parsing functions to avoid being entered as text.
- To find the modified duration, we use the MDURATION function, which has the same arguments as the DURATION function.
Frequently Asked Questions (FAQs)
The DURATION function has five mandatory and one optional argument. Some of the errors you may encounter include:
a. The settlement and maturity dates should be in the date format, or you will get a #VALUE! Error.
b. If the settlement date is greater or equal to the maturity date, you get a #NUM! error
c. You get a #NUM! Error if the coupon or yield is less than zero.
d. The #NUM! error occurs when the basis is less than zero or more than four.
e. If the frequency is not 1,2 or 4(annual, semi-annual, quarterly), you get an #NUM! Error.
1. The calculation of DURATION is important for bond investors and portfolio managers as it helps them assess risks before making investment decisions.
2. We can use the DURATION in Google Sheets to compare the durations of different bonds which have different coupon rates and maturity dates. This helps identify which bond is more sensitive to changes in the interest rate.
3. It helps enhance your financial analysis capabilities when you are analyzing bonds and managing fixed income portfolios.
4. You can also integrate the DURATION function with other financial models.
Macaulay duration measures the bondholder’s average time to receive the bond’s cash flows. The present value of the cash flows weights it. It means measuring the weighted average time until the bond’s cash flows are received. It reflects the bond’s price sensitivity to interest rate changes.
Download Template
This article must help understand DURATION 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 DURATION Function in Google Sheets. We learn its syntax & how to use it to find the compounding period with examples. You can learn more from the following articles. –
Leave a Reply