Hello,
I have a problem is to calculate the interests of a term certificate of deposit, in relation to rate changes, where I have a previous rate and the current rate. To make the calculation with the current rate I have no problem, but I do have a problem in taking the remaining days from the date of change backwards. In other words, the client came and canceled on the 17th of May 5, 2022, he had a rate of (previous rate 4.00) and they changed it for 7.50 (Current Rate).
How would a script calculate the days backwards from January 1 to May 16 with the old rate, since from 17 onwards it would be calculated with the new or current rate?
In the attached excel file there is a date called HIS_FECHA_FECHA, this date is the cancellation date, and the script must behave as follows.
if you enter a date 01/01/2022 it should only take one day
If you enter 02/01/2022 you must take both days and so on until the end of the month.
The client comes and cancels on 02/02/2022, the scripts must take the two days of the month of February and add the 31st of January, if they come on the 3rd of February equally.
If the client comes and cancels on 04/03/2022, the scripts must take the days of April, add the days of February and January and calculate the interest for all months, because I do not know what day the client will come to cancel. I know that you must take the days of the previous months and add the current days of the new date.
ortizmario6025@...
Offline
Last seen: 2 weeks 1 day ago
Joined: 09/03/2021 - 11:54
Can you give me some examples to work with?
YEs
YEs, attach an excel file. As an example, the range taken was from 01/01/2022 to 06/30/2022
I have a few questions, how is the interest calculated, is it compounded yearly, monthly, daily?
I assume the HIS_CAPITAL is the capital amount but what is the ACC_INTDIA, I guess it probably relates to the interest rate.
For the interest calculating what date are you doing it at? Is it always the HIS_FECHA_FECHA or is it another date and you have to do two cacluations for clients that cancel, one with the old rate up to that date and one from that date to the end date?
Have you manually calculated any of them and if so what were your results.
Hi. here the answer
I have a few questions, how is the interest calculated, is it compounded yearly, monthly, daily?
It is calculated daily:
the formula is ACC_INTDIA = (HiS_CAPITAL * HIS_TASANT/100) Number of days /360.
the number of days is the result of the logic of taking the HIS_FECHA_FECHA backwards
you are calculating what date are you doing it at? Is it always the HIS_FECHA_FECHA or is it another date and you have to do two cacluations for clients that cancel, one with the old rate up to that date and one from that date to the end date?
The date HIS_FECHA_FECHA is always taken, which is the date of cancellation from to on the screen of the scripts that I have.
Have you manually calculated any of them and if so what were your results.
for instance;
I cancel the certificate on the 1st day of the 2nd month of 2022, on the 1st of February you must add the 31st of January 2022 and perform the calculation as follows:
in HIS_TASANT it has 2.75, in HIS_CAPITAL it has 3,153,638.59
ACC_INTDIA = (HiS_CAPITAL * HIS_TASANT/100) * number of days /360, the result would be = $7,708.894, which is the interest, the problem is to calculate the days backwards, that is, January 31, there is that I am confused to make those rules, yes There is a cancellation on 3/3/2022, the system must take the 3 days + 28 days of February + 31 January and apply the formula and so on the rest.
I don't know if it's clear in logic,
Hi there, sorry for not getting back sooner. I created this equation in the editor and it gives the amount that you are looking for. Can you have a look at it and see if it works for you? I used the @age function to calculate the number fo days between and added 1 to it.
(HIS_CAPITAL * HIS_TASANT / 100) * ((@Age(HIS_FECHA_FECHA, "20220101") + 1) / 360)
thanks i will try