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".
Thank you, Joshua! this is my first time posting a question. I have been going in circles with this!
No worries. I've been there!!
Just want to make sure you are getting all the help you need.
Investigate Recursive calculations.
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.
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.
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.
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.