DURATION Excel Formula

Author Zaheer    Category Formulas     Tags ,

In Excel there are a lot of Functions; one of this is the Excel Financial Function. This function will help you analyze and compute yields, interest fees, and other related financial values.

The Excel DURATION is one of the Financial Function in Excel. This function computes in an assumed $100 average value of a security which pays interests in a cycle.

Moreover, the syntax of the DURATION function is DURATION(settlement, maturity, coupon, yld, frequency, [basis]). The settlement in the argument means the security date that is the purchasing date of the coupon. The argument maturity in the function is the date by which the security will mature or the date when the coupon will expire. Moreover, the yld argument is the yearly yield of the security.

The frequency argument refers to how frequent the payment for the coupon is done yearly. These values will be represented by 1 as annually, 2 as semi0annually, and 4 as quarterly.

The basis on the function is an optional value. This integer is the day which is the basis for the computation. There are likely a lot of basis such as 0 with the day count basis of 30/60, 1 basis has a day count of the actual days. For the 2 basis the day count basis will be the actual days divided by 360. The 3 basis has a day count basis of the actual days divided by 365 days. Lastly, the 4 basis in European format has a day count basis of 30 divided by 360.

On the other hand, the date argument on the function can be imputed through a reference cell which contains the date. Or it can be that the given dates which are returned by the Excel functions or formulas. Also, if you input the dates in text format these might be interpreted in a different way which will depend on the setting or system of your computer. More so, inputting the dates in a manner of serial numbers is not recommended because the serial numbering varies from one computer to another.

In using the DURATION function errors can occur such as the #VALUE!, #NUM!, and #NAME?. The #VALUE! error occurs when the arguments used are non-numeric and if the given dates are invalid Excel dates.

The #NUM! error will occur if the given date of settlement is greater than the given maturity date. This will also occur if the given values for the yld, coupon, basis, and frequency are invalid values.

For the #NAME? error will occur if the Analysis ToolPak Add-Ins were not enabled. To enable the Add-Ins in Excel 2003 hit the Tool and on the drop-down list select the Add-Ins. On the Add-Ins dialogue box select the Analysis ToolPak and hit OK.

Post comment