4 Replies Latest reply on May 13, 2016 4:06 AM by hjgunn@mmedia.is

    [XPost] How to calculate APR?


      Hello all


      I've been working on a loan schedule application


      I’ve finished the scripts needed to create a loan schedule for the four types of loans we use over here.

      Well five types it, if one includes bubble loans.




      Now I need to calculate APR for these different types of loans


      I know that APR equals the cost of the loan divided by its original payment value.

      Not sure i’m wording this correctly but at least its: APR = cost / netto loan amount, probably calculated at the present value of each payment involved.


      I have looked at the formulas for APR as depicted on https://en.wikipedia.org/wiki/Annual_percentage_rate and in current EU regulations




      As usual I had no idea what I was doing when I started out on this and thought I could just turn the formula for APR.

      However it seems it's not possible to turn the formula for APR so people are using Excel and Goal seek to solve the equations involved.


      The EU provides an Excel based calculator which uses password protected scripts and has no intention of providing an open version. To protect the calculators integrity. Also it is up to the member nations to provide the details needed to use the formulas.




      Can anyone here explain to me what is involved if I want to write a script to solve the needed calculations in Filemaker?




        • 1. Re: [XPost] How to calculate APR?

          There are different methods of computing and most states have a banking department which regulate all loans, so I would recommend contacting your states banking department, some provide this type information on their website.  

          • 2. Re: [XPost] How to calculate APR?

            I am in the process of retrieving this information from the regulatory body over here.


            However the formula seems a bit complex and solving it for X (APR) is even more complex as it involves using Goal seek functionality.


            Screen Shot 2015-10-27 at 14.51.56.png

            We also have a rewritten form of the above using the concept of "flows"


            Screen Shot 2015-10-27 at 15.02.09.png

            Where S is the current balance of the flow as stated in the EU directive 2008/48/EC


            At this time the regulatory body does not possess information on how to calculate this without using Excel and its Goal seek function. My goal is to do this in Filemaker hence my question.


            Also after reading how this is calculated in the States it seems that Europe is using somewhat different methods and/or naming conventions.


            Some pages on the Internet are putting APR equal to nominal interest and then calculating effective APR as effective interest. Over here APR is calculated by dividing the total cost of the loan with the amount paid to the debtor at the beginning of the loan. This works for a bubble loan with a life cycle of one year and one lump payment at the end of the cycle.


            However when it comes to mortgages the above formula seems to be what is needed and I'm looking for information on how to implement it using Filemaker.




            • 3. Re: [XPost] How to calculate APR?

              I found this PDF file from 2001 on the net:



              In annex 2 "Calculating APRs with computers" there is mention of two methods to calculate APR.


              Bisection method

              Newton-Rapheson method


              Is there anyone here that can verify that I have found the methods to use or should I look for a different method?




              • 4. Re: [XPost] How to calculate APR?

                As it turns out the bisection and Newton Rapes methods are but two of a family of root solving methods that can be used to solve this issue. To solve these types of calculations one needs to re-create the goal seek functionality as offered by Excel.