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!