DURATION in Excel

Excel has a variety of financial functions that are useful ways to analyze investments. One of these is the DURATION function. DURATION is a function-driven version of the common Macauley duration formula. With a few simple inputs, you can use it to determine the duration of a bond security. Let’s learn how to use DURATION in Microsoft Excel.

How to Use DURATION in Excel

DURATION in Excel is a powerful function. It performs a rather complex financial analysis based on a few simple inputs. This ensures speedy and accurate analysis. To use the DURATION formula, you’ll need the following inputs:

• The settlement date of the security.
• The maturity date of the security.
• The percent coupon rate of the security.
• The percent yield of the security.
• The frequency (annual, semiannual, etc).
• The basis (optional).

For simplicity, your best option is to list each of these values in its own cell. For example, you can place the settlement date in cell A1, the maturity date in cell A2, and so on. By placing the values into cells like this, you can streamline and simplify your formula.

Now, it’s time to build the DURATION formula. To do it, begin by clicking into any blank cell in your workbook. Type =DURATION(. Your formula will appear as:

=DURATION(

Then, you’ll need to select each of the required inputs. In this example, you’ll click cell A1, type a comma, click cell A2, and so forth. Your completed DURATION formula is:

=DURATION(A1,A2,A3,A4,A5,A6)

Hit Enter on your keyboard and Excel returns the DURATION solution: 12.408.

As you can see, DURATION is a quick way to analyze the duration of an investment security. It removes the difficulty by performing all of the math for you. All you need are the basic inputs outlined above, and you’re all set.