6 Replies Latest reply on Dec 15, 2010 11:20 AM by JohnAnthony

    OK Last calculation help needed

    JohnAnthony

      Title

      OK Last calculation help needed

      Post

      i am building a table for a form I have, and I am racking my brain on this one

      lets say i have a Principal amount and an Interest Amount  (For example we'll say $5000 Principal and $100 Interest)  I received a payment of $300.   My chart has to show how the payment splits.  In my case all payments go to interest first so I need to have two fields outputted here...one will be for $200 (towards the principal) the other will be for $100 (towards principal).  Its easy to do when the Interest is higher then the payment, but its not simple when the payment needs to be shown as split between the two

      example

      5000princ  100int

      200          100        =300

      -----------------------------------

      4800        0

      and 

      5000princ  450

      0               300       =300

      -----------------------------------

      5000         150

        • 1. Re: OK Last calculation help needed
          JohnAnthony

          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

          • 2. Re: OK Last calculation help needed
            philmodjunk

            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?

            • 3. Re: OK Last calculation help needed
              JohnAnthony

              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 

              • 4. Re: OK Last calculation help needed
                rgnant_1

                Wouldn't an Excel or other spreadsheet be much simpler?

                • 5. Re: OK Last calculation help needed
                  philmodjunk

                  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)
                  Payment
                  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 )
                         )

                  Principal Balance

                  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 )
                         )

                  • 6. Re: OK Last calculation help needed
                    JohnAnthony

                    Got it, it took a while, math drives me nuts at times