Why Excel’s US 30/360 Date Differences Can Be Asymmetric
Summary
The document addresses why Excel’s US 30/360 day-count calculation can give different magnitudes when date order is reversed, using a leap-year February month-end and the following March date as its example. It reports that the result depends on the convention selected in Excel’s DAYS360 function: the US/NASD basis adjusts dates differently from other bases, while the cited Actual/Actual option gives a different count.
The answer attributes the apparent asymmetry to Excel’s implementation and notes that its calculation can count both endpoints as whole days in the reversed case. It suggests YEARFRAC as an alternative when trying to avoid the issue and lists Excel’s available day-count bases. This is a narrow software-specific explanation rather than a full derivation of the US 30/360 rules; the excerpt does not establish that YEARFRAC resolves all edge cases or implementation differences, so users should verify conventions for their financial application.
Key ideas
- Excel’s US 30/360 day count can produce different magnitudes when the start and end dates are reversed.
- Excel’s DAYS360 results depend on the selected basis, which changes date-adjustment rules.
- The example focuses on a February month-end in a leap year and the following March date.
- YEARFRAC is suggested as an alternative, but the document does not verify its behavior for every edge case.
Tags
Full text
# discrepancy in calculating 30/360 day count # discrepancy in calculating 30/360 day count I'm trying to calculate 30/360 (US) date differences, but I'm confused by the differences between calculating dates in one order vs the other. When I calculated the difference between date1=2/29/2016 and date2=3/1/16 in Excel (using Days360(date1,date2)), I get one day difference. When I reverse the dates and calculate then I get -2 days difference instead of -1. Why the discrepancy? I thought the numbers would be consistent either direction. I read the page here: Day_count_convention which talks about changing day values, but it doesn't go into detail on how to calculate differences in dates or why calculations are different in reverse. Another confusing point is that the convention tells you to change the day to 30 if you're end of month in February. However, you can't have Feb 30 with any date software. What is the proper way of calculating the difference between two dates using 30/360 US? ## Answer by Martin (score 4) https://quant.stackexchange.com/a/30121 I think your discrepancy is a problem in Excel: https://support.office.com/en-us/article/DAYS360-function-b9a509fd-49ef-407e-94df-0cbda5718c2a > =DAYS360(29/02/2016,01/03/2016,0) = 1 (suitable for 30/360 US) =DAYS360(29/02/2016,01/03/2016,1) = 2 And reverse calculated the different is 2, because Excel count both days as whole days: > =DAYS360(01/03/2016,29/02/2016,0) = 2 (suitable for 30/360 US) =DAYS360(01/03/2016,29/02/2016,1) = 2 If you want to avoid this try Yearfrac http://www.excelfunctions.net/Excel-Yearfrac-Function.html > =YEARFRAC(01/03/2016,29/02/2016,0) is the same as > =YEARFRAC(01/03/2016,29/02/2016,0) Day count basis 0 or omitted = US (NASD) 30/360 1 = Actual/actual 2 = Actual/360 3 = Actual/365 4 = European 30/360
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.