Days count calculation for loan

[Aqureshi025]

Hi Everyone,

I am struggling to write the formula for the days count basis. 

Below is my data. Based on the calculation of date basis, my interest income / expense should be calculated on that basis. Means if the date basis is actual / 365 then the formulae should calculate the actual number of days in 201X year divide by 365

 But if it is Actual / Actual, then it should calculate the actual number of days of loan in that year / actual number of days of that 201X year. for e.g 2015 has 364 days then 2014 it should be 364 days of 2015 has 365 days then divide by 365. Also 30/360 factor.

Can any one help. Below is the extract from my original data

Number	Currency	Amount	Nominal Rate	Start date of loan	Maturity Date of loan	Date Basis	YearEnd
A	GBP	50000000	0.75	27/10/2020	25/01/2021	Actual/365	31/12/2020
B	CHF	10000000	1.35	22/09/2017	14/03/2020	30E/360 EOM	31/12/2017
C	MXN	100000000	1.95	21/12/2018	21/12/2021	30E/360	31/12/2018
D	USD	20000000	2.55	16/03/2020	14/04/2020	Actual/360	31/12/2020
E	EUR	-50000000	3.15	19/01/2017	19/01/2032	Actual/Actual (1)	31/12/2017
F	EUR	-123000000	3.75	12/06/2019	30/06/2029	30/360	31/12/2019
G	AUD	-140000000	4.35	04/02/2016	04/02/2026	Actual/Actual (2)	31/12/2016
H	CNY	1000000000	4.95	16/07/2020	28/06/2021	Actual+/Actual ISDA	31/12/2020
I	CNY	1000000000	5.55	04/03/2020	03/03/2021	Actual/Actual ISDA	31/12/2020

[AngelosPachis]

Hi @Aqureshi025 ,

I've seen a couple of questions in the community similar to this one but unfortunately, most of them have gone unanswered because the outcome is not very clear.

Could you help us assist you by giving a more representative example of the calculations needed? The 2014-2015 example seems a bit confusing to me.

[Aqureshi025]

Hi

it is clear that i need to calculate based on the actual days count basis mentioned in the table

[jdunkerley79 · Accepted solution]

You would need to build something like:

The easy one ACT/<constant> is correct at the top.

The 30/360 is nearly correct but you will need to handle the 30E where last day in Feb moves to 30th

Actual/Actual is the most painful as you need to model the payment and fit it in but the attached is a reasonable approximation where 12 months is added.

If these are something people need I am happy to build them out fully as part of the Abacus function I support

The above workflow is attached as a starting point

[Aqureshi025]

Hi

Many thanks. It almost resolve the problem, but there is one small thing to resolve which I am trying to do.

See in Actual / 365

The years column 2 row is 1.78082 means it accumulate the days of the prior year. Each year day count should be independent from the prior year. Same error is in all three buckets

See the year column it adding 1 each year before decimal

[jdunkerley79 · Accepted solution]

Have attached a new version.

Was measuring from Start of Loan until YearEnd

Have now added an extra formula which computes YearStart as max of Start and 1st Jan in YearEnd

And YearEnd will be truncated to Maturity date if in it.