oh i should mention this is just a table I am using, i dont need to save these records to anything, consider this just a form outside of my database. It doesnt need to be related to anything in my main database
The structure of your table is important here.
I assume there could be multiple loans being paid off here so it looks like you need two tables: Loans, Payments.
Your Loan amount and interest would be stored in Loans with Payments functioning as a Ledger that records each payment recording the resulting changes in principal and interest. In any case, your relationship between tables should be:
Loans::LoanID = Payments::PaymentID
Before I can go further, (and I may not be able to supply the full solution), this question needs to be answered:
Is this simple or compound interest?
I think this is over thinking it. This is not a database really, it is just a report that you can manually enter things in. It will never be saved, just printed. I am doing just math between cells. No multiple loans, no interest to calculate this is simply ok how do I do a what if calculation. Such as
If Interest > Payment then Interest Payment = Payment and Principal Payment= 0
and if interest < payment then Principal Payment = Payment - Interest and Payment - Principal Payment = Interest Payment
Can I do IF statemeents
Wouldn't an Excel or other spreadsheet be much simpler?
I think a spreadsheet would be simpler unless you are planning on linking this into a more complex database system.
"It will never be saved"--That seems a very odd way to do business. Most businesses want an electronic copy of everying thing they do in case a dispute arises. They may keep paper copies, but they'll want the electronic copy so that they can quickly find the original data. FileMaker automatically saves everything. If you do this in FileMaker and don't want it saved, you'll have to delete the records afterwards.
I really don't think that I'm overthinking this. How you compute the interest amount for each payment is different depending on whether the interest compounds with each payment period or if the interest amount is constan throughout the life of the loan. From your Posts, I'm guessing that this is simple interest.
Keeping to the "all in one table" approach that you have indicated that you prefer:
You'll need these fields:
gInterest (global field for recording current simple interest amount)--making this global means you only have to enter it once.
gInitialPrincipal (Global Field)
InterestBalance ( calculation )
PrincipalBalance ( Calculation )
Your InterestBalance calculation might be this:
Let ( [ r = Get ( RecordNumber ) ; int = If ( r = 1 ; 0 ; GetNthRecord ( InterestBalance ; r - 1 ) ) + gInterest ] ;
If ( Payment < int ; int - payment ; 0 )
Let ( [ r = Get ( RecordNumber ) ;
prin = If ( r = 1 ; gInitialPrincipal ; GetNthRecord ( PrincipalBalance ; r - 1 ) ) ;
int = If ( r = 1 ; 0 ; GetNthRecord ( InterestBalance ; r - 1 ) ) + gInterest )
Prin + int - Payment )
Got it, it took a while, math drives me nuts at times