DAYS360()

Syntax. DAYS360(start_date,end_date,method)

Definition. This function calculates the number of days between the start and end dates based on a year with 360 days.

Arguments

• start_date (required). The start date of the interval

• end_date (required). The end date of the interval

• method (optional). A logical value indicating whether the U.S. or European method is used for the calculation:

  • FALSE or Omitted. U.S. (NASD) method. If the starting date is the last day of a month, the starting date becomes equal to the thirtieth day of the same month. If the ending date is the last day of a month and the starting date is earlier than the thirtieth day of a month, the ending date becomes equal to the first day of the next month; otherwise, the ending date becomes equal to the thirtieth day of the same month.

  • TRUE. European method. Starting dates and ending dates that occur on the thirty-first day of a month become equal to the thirtieth day of the same month.

Background. If the start date is after the end date, the DAYS360() function returns a negative number and causes an incorrect result in interest calculations. Also, make sure to enter the interest rate in the Excel percent format.

To calculate interest, do the following: First, multiply the capital by the interest rate to calculate the interest amount for a year. Divide the result by 360 to determine the interest for one day. Multiply the result by the number of days you calculate with the DAYS360() function. This results in the following formula:

interest =capital x interest rate x DAYS360(start_date,end_date,TRUE)/360

If necessary, you can round the result to the second decimal place by using the ROUND() function (see Chapter 15).

See Also

For more information about saving dates in Excel, see the section titled The Excel Date System in Chapter 2.

Example. Assume that you want to calculate the interest to date from November 22, 2010, (with the date in cell C12) through May 31, 2011 (in E12), for $250,000 (in C11), based on an interest rate of 5.25 percent (in E11). Make sure that you enter the percentage for the interest rate using the Excel format. You use the DAYS360() function to calculate the interest days.

=C11*E11*DAYS360(C12,E12,TRUE)/360

The result is $6,854.17. The partial calculation DAYS360(C12;E12;TRUE) returns 188 interest days (see Figure 7-2).

Figure 7-2. Calculating the interest to date with the DAYS360() function.

See Also

DATEDIF(), EDATE(), EOMONTH(), NETWORKDAYS(), NETWORKDAYS.INTL()

Sample Files

Use the DAYS360 worksheet in the Date_Time.xls or Date_Time.xlsx sample file. The sample files are found in the Chapter07 folder. For more information about the sample files, see the section titled Using the Sample Files.