8 Replies Latest reply on Apr 15, 2015 1:20 PM by hjgunn@mmedia.is

    [XPOST]Calculating penalty interest using weighted averages

    hjgunn@mmedia.is

      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

        • 1. Re: [XPOST]Calculating penalty interest using weighted averages
          thurmes

          I'm confused by several of your rules and procedures, but maybe it's just that I didn't understand what you're doing.

          My questions:

          Is this compounded interest? (from what you say above, it sounds like the answer is that it's compounded not on a daily basis but on a 'yearly' basis, where 1 year = 360 days)

          How often does the interest rate change?  (from what you say, it sounds like potentially daily?)

          Why the two rules? (but never mind, we'll work within them)

          What rules govern the change in the interest rate (if it's set by external interest rates, no rules, and a table of dates and interest rates would work well; if the interest rate goes up because of a late payment, then there are rules and the table of interest rates would be a starting point)

          How often do you round the number to reflect the smallest unit of currency?

           

          If the interest were compounded on a daily basis, you'd calculate it for each period by $bv * ((1 + $dpr ) ^ $nd - 1 ) [$dpr = daily percentage rate, $nd = number of days]

          If the interest is not compounded, you're still going to have to do a calculation for each period (here, the start/endpoint of a period might also include dates the interest rate changes), either to calculate the average percentage rate or to calculate the interest. If it weren't for the additions and subtractions that affect the principal, it wouldn't make much difference which way you did the calculation (assuming you only rounded at the end of the calculation). I think you'll still have to do a loop for each period. However, to simplify error-checking I would suggest calculating the interest amount for every period, tracking the accumulated interest for the year, making changes to the principal, etc. That way, with FMP Advanced (or even with some finagling with the regular version), you'll be able to check that the script works correctly, and quickly find any errors.

          • 2. Re: [XPOST]Calculating penalty interest using weighted averages
            hjgunn@mmedia.is

            Let's try again...

             

            I need to calculate accrued interest over a period of time.

            The period can vary from days to months to years.

            Using a process that does not need to loop through every day of said period to calculate the interest.

            A month is 30 days and I need to add the calculated interest to the capita every 12 months before continuing to calculate interest,

             

            ===

             

            Interest can change monthly or every few months depending on the "mood" of our central bank.

            And the total period may be split into smaller periods by the breaker events

             

            ===

             

            Calculating the interest happens like this:

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

            Then every twelve months I add the accumulated interest to the BaseAmount, reset the accumulated interest to 0 and start over accumulating interest.

            The rounding only happens once, at the end when the process is finished

             

            ===

             

            I suspect my main question, is how to calculate the interest per period, using a weighted interest average instead of the above.

             

            My thought was to use the formula I posted and add a segment to it every time the interest rate changes effectively creating a weighted interest I could multiply the base amount with to calculate the interest for the period.

             

            ===

             

            The talk about about the interest and transaction tables is about how to get the start and end points for the periods to calculate.

             

            I must admit I haven't seen the version of the formula, you posted, before.

            • 3. Re: [XPOST]Calculating penalty interest using weighted averages
              jbante

              For each contiguous date range with the same interest rate, the total amount is:

               

              BaseAmount * ( 1 + InterestPercentagePerDay / 100 ) ^ NumberOfDaysAtSameDailyInterestRate

               

              And the interest by itself is Total - BaseAmount, or 1 - ( 1 + InterestPercentagePerDay / 100 ) ^ NumberOfDaysAtSameDailyInterestRate.

               

              The total amount for multiple ranges is a product of the interest rates, not a mean:

               

              BaseAmount

              * ( 1 + InterestPercentagePerDay[1] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[1]

              * ( 1 + InterestPercentagePerDay[2] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[2]

              * ...

              * ( 1 + InterestPercentagePerDay[n] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[n]

               

              And just the effective interest is:

               

              1

              - ( 1 + InterestPercentagePerDay[1] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[1]

              * ( 1 + InterestPercentagePerDay[2] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[2]

              * ...

              * ( 1 + InterestPercentagePerDay[n] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[n]

               

              Then the effective overall daily interest rate is:

               

              1

              - (

              ( 1 + InterestPercentagePerDay[1] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[1]

              * ( 1 + InterestPercentagePerDay[2] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[2]

              * ...

              * ( 1 + InterestPercentagePerDay[n] / 100 ) ^ NumberOfDaysAtSameDailyInterestRate[n]

              ) ^ ( 1 / TotalNumberOfDays )

              • 4. Re: [XPOST]Calculating penalty interest using weighted averages
                jbante

                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 )

                • 5. Re: [XPOST]Calculating penalty interest using weighted averages
                  hjgunn@mmedia.is

                  Thank you for your input thurmes.

                  This thing needs a button for "helpful answers"

                   

                  Kind regards

                  Hans

                  • 6. Re: [XPOST]Calculating penalty interest using weighted averages
                    hjgunn@mmedia.is

                    Thank you for your reply.

                     

                    As far as I see yet this formula seems to be working fine.

                     

                    ===

                     

                    I have an additional question...

                     

                    Interest rate = ( DaysAtInterestRate[1] * InterestRate[1] + DaysAtInterestRate[2] * InterestRate[2] + ... + DaysAtInterestRate[n] * InterestRate[n] ) / ( TotalNumberOfDaysInRange )

                     

                    Is it correctly understood that the formula above gives the average interest rate for an entire year.

                    So if I wanted to calculate the interest for 5 days I would need to do a:

                     

                    BaseAmount * InterestRate * DaysCalculated / TotalNumberOfDaysInOneYear

                     

                    ===

                     

                    And does "^" mean "power of"?

                    • 7. Re: [XPOST]Calculating penalty interest using weighted averages
                      thurmes

                      You're welcome.

                       

                      Yes, "^" means "to the power of", and you can use it just that way in FileMaker calculations.

                       

                      Looking at my reply, it wasn't as clear as it might have been - I said a lot of things with only a few words. jbante was right on how to calculate the average interest rate. I was suggesting that calculating the average interest rate doesn't simplify things much, and perhaps another approach might be more useful. Let me demonstrate:

                       

                      The base amount (BA) will change for each period breaker, so at least yearly, but maybe more often; let's give BA subscripts a, b, ...

                      You will only use a given average interest rate for one BA

                      Let's say you have periods 1, 2, ..., n within one BA, so we'll have a1, a2, ... an, then b1, b2, ... bn

                      For these there's a daily interest rate Ra1, ...    [InterestRate / (100 * 365.25) ]

                      And a number of days NDa1, ...

                       

                      (The periods will change from customer to customer, because they'll have different starting points, different payment points, etc, so you can't save any calculations that way; you'll have to do the calculation fresh for each customer.)

                       

                      The average interest rate (AIR) for BAa will be

                      AIRa = ( Ra1 * NDa1 + Ra2 * NDa2 + ... + Ran * NDan ) / n   [you'll do the part inside ( ) with a loop]

                      Then you'll get an Interest Amount IAa = BAa * AIRa * n

                       

                      Then you'll do this again for BAb, ...   [so that's another loop outside the first loop]

                      And Accumulated Interest will be AI = IAa + IAb + ... + IAn

                       

                      So for each Base Amount, you'll do n + 3 calculations, but for most of them the numbers will be very small, and it will be hard to intuitively look at each result and say "yeh, that looks right". That won't happen until you do the last calculation.

                       

                      ******

                      The other way to do this by not averaging the interest rate, but just calculating the interest for each period.

                       

                      IAa1 = BAa * Ra1 * NDa1

                      and AI = sum ( IAa1 ... IAan & IAb1 ... IAbn & ... & IAn1 ...IAnn )

                      All of this will be done in two nested loops, you'll do about the same number of calculations, and at each step you can look at the result (the amount of interest for this period) and get a good idea of whether the numbers look right.

                       

                      In neither case will you make calculations on a day-by-day-by-day basis; one potential problem with the second method is that you'll have to decide when to Round the numbers, because rounding for each step could give a significantly different answer than just rounding at the very end.

                       

                      I hope that's a bit clearer.

                       

                      --Bill

                      • 8. Re: [XPOST]Calculating penalty interest using weighted averages
                        hjgunn@mmedia.is

                        Thank you for your comments

                         

                        I'll need a bit more time to look at your method, but I had been thinking of something in this direction:

                         

                        Populate a list variable with Transaction dates

                        $i = 1

                        $IntTotal = 0

                        Open Loop

                          Place values $i and $i+1 from the list of transaction dates into two separate variables

                          Open Loop

                            Populate a list variable with each date and interest rate pair where the interest changes, that fall within a period delimited

                            by the two dates from the transaction list

                          End Loop

                          Open Loop

                            Create the weighted average for the current set of Interest periods

                            Calculate the interest for the current set of Interest periods

                            $IntTotal = $IntTotal + Calculated Interest

                          End Loop

                          $i = $i + 1

                        End Loop

                        Set field InterestTotal to $IntTotal