Calculate interests with interest calculation numerators(Excel, XP, 2003, 2007)

               You have found a weird entry in the bank statements; the overdraft interest was calculated on the basis of an interest calculation numerator.
               Excel has the ‘ZINS’ formula for annual interest calculation with a fixed capital value, but the calculation for constantly changing capital values is a lot more tedious. Every partial sum must be individually calculated and then added up for the relevant number of days. Here, the interest in the commercial calculation is calculated by means of the quotient of capital, multiplied by days, multiplied by interest rate, divided by 100 percent and 360 days.

               This calculation is simplified by adding the fi xed part of the formula to ‘Interest divisor=Interest rate/360 days’. The remaining variable parts together form the ‘Interest rate=Capital*Days/100’. This procedure simplifies the interest calculation. First calculate and add the individual interest calculation numerators. Then multiply the sum with the interest divisor and obtain the total interest using the formula ‘Total interest=SUM(Interest calculation numerator)*Interest divisor’. Note that in case of capital, cents are not included and rounded. Moreover, interest calculation numerators are always rounded off to whole numbers before using. In order to understand the calculation, enter the validation date in column ‘A’ and the relevant account status in column ‘B’ for a calculation period one below the other. Then start with the determination of the past days in cell ‘C2’ using the formula ‘=A2-A1’. Then enter the formula for the interest calculation numerator in cell ‘D2’.
ROUND(B1*C2/100, 0)
 If credit as well as debit interest with diff erent rates gets accumulated in an account, distribute the interest calculation numerators in columns ‘D’ and ‘E’. You can also use the ‘IF’ function to see whether the accounting balances are positive or negative. The formula for the interest calculation numerator in credit would then be
IF(B1>0, ROUND(B1*C2/100, 0), 0) 
Similarly, you can determine the interest calculation numerators for negative accounting balances in column ‘E’ with
IF(B1<0, ROUND(B1*C2/100, 0), 0) 
Now select the required fi eld in column ‘C’ of ‘C2’ below and apply the formula with ‘Edit | Fill | Below’. Repeat this step in the columns of interest calculation numerators. You can now add the interest calculation numerators in the column and multiply the sum with the interest divisor to calculate the interest. The interest amount of the period is then
TOTAL(D2:D20)*Interest rate/360