2
$\begingroup$

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?

$\endgroup$
4
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.