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).
