Excel Act/365 and Act/360 conventions

[dkralj]

Hello everyone, first time posting here.
We have started testing Strata Opengamma for your future needs and have stumble upon getting different values for same conventions inside Excel and inside Strata. Those two conventions are Act/365 ( for which we are unsure which convention is it inside Strata, we supose its Act/365 Actual, and for Act/360.

For example: having two dates, 1.3.2020. and 1.9.2020.
When we put those two dates into Excel result is: Act/360 = 180 day and Act/365 = 183 days.
When we test those dates with Strata result is: Act/360 = 184 days and Act/365 Actual = 184 days.

Code of java class that calculates that is as follows:

private LocalDate datumDospijecaObveznice;
private LocalDate datumNamireObveznice;
private Map<String, String> listaKonvencija;
private String konvencija;
private int brojDana;

private static Logger log = LoggerFactory.getLogger(VrijednosniPapiriIzracun.class);

@PostConstruct
public void init() {
	
	listaKonvencija = new TreeMap<String, String>();
	listaKonvencija.put("Act/360", "ACT_360");
	listaKonvencija.put("Act/364", "ACT_364");
	listaKonvencija.put("Act/365.25", "ACT_365_25");
	listaKonvencija.put("Act/365 Actual", "ACT_365_ACTUAL");
	listaKonvencija.put("Act/365F", "ACT_365F");
	listaKonvencija.put("Act/365L", "ACT_365L");
	listaKonvencija.put("Act/Act AFB", "ACT_ACT_AFB");
	listaKonvencija.put("Act/Act ICMA", "ACT_ACT_ICMA");
	listaKonvencija.put("Act/Act ISDA", "ACT_ACT_ISDA");
	listaKonvencija.put("Act/Act Year", "ACT_ACT_YEAR");
	listaKonvencija.put("NL/365", "NL_365");
	listaKonvencija.put("1/1", "ONE_ONE");
	listaKonvencija.put("30/360 ISDA", "THIRTY_360_ISDA");
	listaKonvencija.put("30/360 PSA", "THIRTY_360_PS");
	listaKonvencija.put("30E/360", "THIRTY_E_360");
	listaKonvencija.put("30E/360 ISDA", "THIRTY_E_360_ISDA");
	listaKonvencija.put("30E+/360", "THIRTY_EPLUS_360");
	listaKonvencija.put("30U/360", "THIRTY_U_360");
	listaKonvencija.put("30U/360 EOM", "THIRTY_U_360_EOM");
}

public void submit() {
	brojDana = DayCount.of(konvencija).days(datumDospijecaObveznice, datumNamireObveznice);
	log.debug("Računa se broj dana od datuma " + datumDospijecaObveznice + " do datuma " + datumNamireObveznice);
	log.debug("Odabrana konvencija je {}", konvencija);
	
	for (String konv : listaKonvencija.keySet()) {
		brojDana = DayCount.of(konv).days(datumDospijecaObveznice, datumNamireObveznice);
		log.debug("Računa se broj dana od datuma " + datumDospijecaObveznice + " do datuma " + datumNamireObveznice);
		log.debug("Konvencija {} {} dana za {} - {}", konv, brojDana, datumDospijecaObveznice, datumNamireObveznice);
		
	}
	
	log.debug("Stvarni broj dana je {}", ChronoUnit.DAYS.between(datumDospijecaObveznice, datumNamireObveznice));
	
}

When trying other 3 Excel conventions we get same number of days.

My question is where is the problem here and why are those values different?

[stephen]

Excel’s Act/365 corresponds to Strata’s Act/365F.

The DayCount classis intended primarily to calculate the yearFraction(). The days() method should return the same value for Act/360 and Act/365F as it is implemented in the same way (see the source code). It returns the actual number of days between the two dates because the name of the day count is “Act”. ie. it returns the numerator of the day count fraction.

• Act/360: Actual difference in days = 184, Year Fraction = 184/360
• Act/365: Actual difference in days = 184, Year Fraction = 184/365

I’m not sure what excel function you are using to get 180 and 183, but I suspect there is no corresponding equivalent in Strata.

[dkralj]

Thanks for fast response and thank you for telling us to which Act/365 in Excel corresponds to in Strata.
Below are two pictures for Excel table that calculates periods and two day counts.

Four values that are calculated are last four columns. Functions that are used for their calculation are: 1st COUPPCD, 2nd COUPNCD, 3rd COUPDAYS, 4th COUPDAYBS

[dkralj]

Act/365 picture since I couldn’t post two images on a post as a new user.