8 Replies Latest reply on Dec 1, 2015 12:46 PM by jormond

    Creating an interest calculation table

    she_likes_it!2

      I need to create a complex interest calculation in my solution.  I have decided it would be best to calculate this in a separate related table.  Interest is first calculated as (10% of amount found due) / 365 days in the year = Amount of interest per day.

      then (amount of interest per day X number of days from date of amount found due) = amount of interest for that day if amount paid in full

      Payment Plan - same calculation for first payment.

      second payment - (10% of balance due) / 365 days in the year = Amount of interest per day.

      then (amount of interest per day X number of days from date of last payment) = amount of interest for 2nd payment.

      Interest must be paid from payment first, then balance of payment paid on principle still owing

      My thought is the related table doesn't need to store records, just to the calculations, so the information will come into the Accounts receivable table via a lookup so the information is static in Accounts receivable.

      I have written this out on paper, but I am still struggling on how to automate it.  Any help would be appreciated!

        • 1. Re: Creating an interest calculation table
          jormond

          @she_likes_it!2,

          You may want to move your discussion into the primary Discussions area. This "Community Feedback" section is related to problems and issues with this community website itself, not to FileMaker issues.

           

          It will simply get more attention in the main Discussions area.

           

          Also, try and mark it as a question. Once you get a "correct answer" mark that answer as correct. It will help others that may have the same or similar question.  You can also mark replies as "Helpful".

          • 2. Re: Creating an interest calculation table
            she_likes_it!2

            Thank you, Joshua!  this is my first time posting a question.  I have been going in circles with this!

            • 3. Re: Creating an interest calculation table
              jormond

              No worries. I've been there!!

               

              Just want to make sure you are getting all the help you need.

              • 4. Re: Creating an interest calculation table
                keywords

                Investigate Recursive calculations.

                • 5. Re: Creating an interest calculation table
                  jormond

                  I don't believe we need a recursive calculation to get the numbers. If I remember correctly, and this is off the top of my head, so feel free to correct me...this should work:

                   

                  PrincipalAmount * ( 1 + InterestRate ) ^ NumOfPeriods

                   

                  The thing to keep in mind is that your interest rate time frame and the number of periods has to match. For example, if you are calculating days, you need to break the interest rate into a daily rate. Though you can build it into the calculation to transform the rate for you.

                   

                  On a side note, make sure you check local laws. Most places have a max interest rate you are allowed to charge.

                  • 6. Re: Creating an interest calculation table
                    Extensitech

                    We have a client for whom we built a solution they use to track loans, payments, interest, etc. The requirements you're describing became a significant effort in one of the most complex solutions we've ever developed.

                     

                    Just a few thoughts based on our experience, as a kind of "brain dump":

                     

                    The timing and order of payments becomes pretty important, since a payment (potentially) reduces the principal balance to be used on the "next" payment. Of course, the payment has a payment date, but we found we needed to create an "applied timestamp" field, which used the payment date and the time from the creation timestamp. The creation timestamp isn't enough since, for example, someone might enter a payment on Monday that actually came in late on Friday. Also, when more than one payment is made on the same date, it really doesn't matter which order they are applied, but the order has to be the same regardless of perspective, so that the first payment gets the balance from before the day, but the second gets the balance from the other payment on the same day.

                     

                    Also, I initially tried to make all of this dynamic so that if a date on a payment got changed, or a payment was voided (bounced check) or other changes, the balances and applications would update automatically. This became untenable, and we switched to having a payment look up balances from the most recent prior payment (and the loan, particularly for the first payment). Thereafter, though, if a change was made to anything but the most recent payment, we needed to trigger a relookup on any subsequent payments, so that principal balances and such would update appropriately. This was, frankly, a bit of a disappointment and a bit of a pain to set up, but performance-wise it saved the project, since once the values were set, the payment application could be evaluated based on just this record, and not all prior payments on the loan.

                     

                    You mention having only calculations in your table. I'd recommend not storing the functions in fields (wherever it's avoidable) since changes to field names, revisions to policies, etc. won't update functions stored in fields in a table. As described above, the same sort of problem exists for data that's been looked up/auto-entered, but you can automatically trigger scripts to deal with that. There is no "onFieldNameChange" script trigger, though.

                     

                    Another experience was that state laws, and our client's policies for various divisions and loan programs, changed frequently. Things as seemingly constant as the number of days in a year ended up being settings (in some states, we're required to use a 360-day year). Try to centralize any elements of your functions that could conceivably change (even if it requires a bit of "wild imagination") and preferably put those elements somewhere that an administrative user can edit them.

                     

                    We also ended up creating a table for interest rate period overrides, since "specials" would allow things like "no interest for a year!" or "reduced interest so long as your loan stays current!" and such things. For this, we did end up using recursive custom functions (I guess we could have done this in a script, too) since in the time between payments, the interest could change, even more than once, so we had to evaluate interest in "chunks"... and also have an explanation field to explain the interest, since otherwise the interest would be questioned all the time even though it was correct.

                     

                    I'd also caution you to set this up so that you round only at the last part of your calculation. For example, interest dollars per day may reasonably end up going out several decimal places. Let it, until you get to an "amount due" or "amount applied". If you round too soon (like rounding up interest per day over several weeks) the rounding errors can really add up.

                     

                    I hope this helps, or at least provides some food for thought.

                     

                    Chris Cain

                    Extensitech

                    • 7. Re: Creating an interest calculation table
                      she_likes_it!2

                      Thank you to all for the input.  I have been out for the Thanksgiving Holiday and I plan on testing the information provided in my solution, and see if I can get it to work.  It makes me nervous to hear it was a really hard solution to build but since I couldn't figure it out myself, I knew it would not be easy. 

                      • 8. Re: Creating an interest calculation table
                        jormond

                        Definitely a lot of good info from Extensitech. It can be tricky to implement, there are just a lot of possible ways things that can happen in the course of a payment cycle.

                         

                        Let us know how you make out. And feel free to mark answers as helpful, that way we know we had some idea what you were after.