• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

formula needed to calculate interest

C

chaptzem1

New Member
Hi all nice to be here my question is

i have a statement of my account going back to 2005 i get billed almost every month but not on regular dates it could be between 10 to 60 days between 1 bill to the next, i am getting charged interest of 18% per year but its being charged daily my problem is that on this year 2013 my interest calculation works but on the years before i am always off, the reason is that 2013 is a regular year and the formula divides the 18% by 365 but last year was a leap year and it should be 18% divided by 366 so how do i make 1 formula that would divide the 18% by year automatically if its a regular year it will get divided by 365 and if its a leap year it will get divided by 366
TY
 
Hi chaptzem1,

Welcome to the forum.

Just check if this formula is of any use.

=IF(OR(MOD(E8,400)=0,AND(MOD(E8,4)=0,MOD(E8,100)<>0)),C8/366, C8/365)

Your percentage value in C8 and year in E8.

Just advise if any issue.

Regards!
 
Try this formula to get 366 for leap years and 365 for others, it assumes the date is in A1

=IF(MOD((YEAR(A1)),4)>0,365,366)

regards

kanti
 
thanks somendra
my sheet has on top the todays date and than on every line i have "a due date and a amount and the number of days till today and the total interest to date" in the bottom i have the total capital and the total interest
for the bills of 2012 the interest for the year 2012 is 18/366 and for the year 2013 for the same bill the interest changes to 18/365
 
here is my actual sheet
the calculations of the interest for 2013 is true but the years before that is false because 2012,2008, was a leap year
if i want to ck my new statement if its correct i change the date in $H$1 to the statement date and it should match penny for penny
 

Attachments

  • Statement 6.xlsx
    18.7 KB · Views: 8
Hi Chaptzem1,

Have you checked with the bank on how they charge the interest, to my knowledge interest when charged is added to the account and this should occur at least once per year, or 12 times or 4 time etc. Once the interest is charged it is added to the capital and then interest is charged on the original capital plus the debited interest.

I do not think that you can have interest for more than 365 days, another question is whether the rate is constant throughout the period?
 
they always charge interest as of the due date
after the due date the interest keeps on going till its paid off
they always divide the 18% by the number of days in the year, in a leap year the rate per day is a bit less than in a straight year since the same 18% gets divided in 366 and not 365
 
Hi Chaptzem1,

I need to understand your worksheet, do you have the actual amounts charged by the bank? it would be good to know what the correct result should be.
 
Hi,

Please check out the attached file, you should have adapted the formula I posted earlier and applied it to your problem!
 

Attachments

  • Statement 6_1.xlsx
    20.1 KB · Views: 5
if you ck in colum G all the amounts for the year 2013 are correct all the amounts before that are off with a bit
if you change the date in H1 to DEC/20/2013 the total in C122 should be 2,549,156.37
 
That does not help, we need to know what the numbers are that are not the same, anyway look at the file I posted
 
Where does this number come from, how is it calculated and PLEASE do not tell me it is a bit off, it means nothing
 
all the numbers from J4 to J98 are off because there is like to diffrent rates charged per day for the regler years the rate is.18/365=
0.000493151
and for the leap year the rate is
.18/366=
0.000491803
 
That has been accounted for in my calculation, but let us have the charges as per the bank otherwise I cannot give you an answer.

Have you confirmed with the bank the calculation, I also note that there are blank for certain Credit entries and not for others, so without a full picture, I cannot help you.
 
this not a bank its a like tax bill and thats how they charge, in a leap year the rate per day is not the same as in a regulare year, the blank lines is because once a year i get a credit and they dont give me no interest on the credits also the lines that have a total dose not have a date or interest the gray shaded lines is total per year
 
The amounts that you are calculating the interest on, is that a transaction or is that a balance? If it is a balance then the calculations are correct.

Does the interest kick-in from the day of the transaction or after it becomes overdue? So you may have incurred the debit today, but no interest until it is due, so is there someway of identifying the grace period
 
the amounts in colum A is a transaction and i pay interest on from the date in colum B till the date in colum H1
for example
A4 the amount is $12,642.85 i pay interest from OCT/10/2005 till Dec/31/2011 "0.000493151" per day, and from JAN/1/2012 till DEC/31/2012 i pay "0.000491803" per day and from JAN/12013 till DEC/20/2013 i pay "0.000493151" per day
so the total interest as DEC/20/2013 on that line should be $18,440.18
and in my calculation and your calculation it comes up to $18,660.385
 
you are right its my mistake
for 2008 i am also paying the same rate as 2012 since its a leap year too
sorry for my typo
 
CAPITAL 12,642.85 INTEREST RATE

10/10/2005 812 $5,062.69 0.000493151
12/31/2007


12/31/2007 366 $2,275.71 0.000491803
12/31/2008

12/31/2008 1095 $6,827.14 0.000493151
12/31/2011

12/31/2011 366 $2,275.71 0.000491803
12/31/2012


12/31/2012 354 $2,207.13 0.000493151
12/20/2013

2993

$18,648.38
 
@chaptzem1

Just check in you message above. I think there are many faults. Like dates are included in two cases. than 2008 till 2011. Rather it should be 2008 & 2010-2011.

Just work out again.

Regards!
 
You must log in or register to reply here.
Back
Top