Days count calculation for loan
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Labels:
- Database Connection
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi
it is clear that i need to calculate based on the actual days count basis mentioned in the table
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
