Hello group

I’m redesigning a process to calculate penalty interest.

The old version was based on a loop that summed up, for each day passed, the accumulated interest.

This means that the more days pass the longer the calculation takes.

So I’m planning to move over to calculate a weighted average interest and then use that to calculate the overall interest occurred per "period”.

Calculation periods would be created by “Period delimiters” or “Period breakers"

Period breakers would be events like down payments, occurred cost, the addition of interest to the Capital every twelve months.

===

Rules are:

There are 30 days in a calculation month.

Every 12 months we add the occurred interest to the Capital amount before continuing our calculation.

===

As I haven’t used this method before I was wondering if the following formulas and thoughts are correct or if I need to rethink my approach.

A period is the number of days from one period breaker to the next period breaker.

Period breakers are:

Invoice created

Addition of down-payment

Addition of occurred cost

Addition of occurred interest to the capital amount (happens every 12 months of calculation)

Last day of calculation (Today or day of payment if not today)

===

For each period created by the above breakers I’m thinking to use the following calculation to calculate the occurred interest for any specific period.

Each change in interest during each period would add a new segment to the formula.

I would then sum up all the segments for the periods to calculate the total interest occurred

$wavgint = ( $ppp1 * $dp1 / $dt1 ) + ( $ppp2 * $dp2 / $dt2 ) … ( $pppn * $dpn / $dtn )

$intamPeriod = $bv * $wavgint / 100

$intamtotal = Sum (intamperiod)

$wavgint = Weighed average interest percentage per period

$intamperiod = Amount of interest accumulated per period

$intamtotal = Sum of interest value for all periods calculated

$bv = Base amount

$dp = Days per period

$dt = Days total

$ppp = Penalty percentage per periode

$np = Total number of periods

===

This probably means I need two tables and two list variables:

tTransactions

tInterestPenalty

$PeriodBreakers

$InterestBreakers

I would then loop through the transaction table to extract my Period breakers and add them to my Period list variable in chronological order.

I would then loop through a found set in the interest table to extract my interest values.

I was then thinking of looping through the list variables and add one segment to my weighted percentage calculation each time an interest rate changes within any given period.

And I expect to re-populate the Interest list variable each time a break occurs in the Break table

===

So if anyone has any comments on these thoughts it would be most helpful.

TIA

Hans

I'm sorry, I assumed compounding interest in my first reply, but that's not what your calculations show. Algebraically, the calculation:

((BaseAmount * InterestDay1 / 100) + (BaseAmount * InterestDay2 / 100) + (BaseAmount * InterestDayn / 100))

... reduces to:

BaseAmount * ( InterestDay1 + InterestDay2 + ... + InterestDayN ) / 100

And for date ranges including interest rate changes but not including any compounding:

BaseAmount

* ( DaysAtInterestRate[1] * InterestRate[1] + DaysAtInterestRate[2] * InterestRate[2] + ... + DaysAtInterestRate[n] * InterestRate[n] )

/ ( 100 * TotalNumberOfDaysInRange )