HORNEKS ANS

ACF Library

CalcAnnuityInterestRate

Back to List

Description: Calculate effective interest rate for annuity loan

FileMaker Prototype: 

Set Variable [$res; ACF_Run("CalcAnnuityInterestRate"; float_LoanSum;  float_Payment;  int_Years;  int_termsYear)]

Category: FINANCE

Function source:

    /*
       AnnuityLoanPayment: 
       Calculate the Payment amounth for an annuity loan : 
       PV = Present Value
       r  = Interest rate
       n  = number of payments
    */ 


function AnnuityLoanPayment ( float PV, float r, int n)
    float P = r*PV/(1-(1+r)^(-n));  
    return P; 
end


    /*
       CalcAnnuityInterestRate: 
       Calculate the Interest rate for an annuity loan by simulation : 
       LoanSum = Present Value
       P  = Payment amounth
       Y  = number of years
       nY  = number of payments pr year. 
    */ 

function CalcAnnuityInterestRate ( float LoanSum, float P, int Y, int nY)

   float r; 
   float res; 
   // We start with High Interest rate. 
   float rY = 100.0;  
   float step = rY/2; 
   float usedrY; 
   if (P*Y*nY < LoanSum) then
           throw "\nNot enough payment - Payment starts at : " + LoanSum / (nY*Y); 
   else
       repeat
           usedrY = rY; 
           r = rY/100/nY; 
           res = AnnuityLoanPayment ( LoanSum, r, Y*nY); 
           print "\nInterest: " + rY + "% - Payment: " + res;
           if ((res-P)>0.0) then
               print " diff(+) " + (res-P); 
               rY = rY - step; 
           else
               print " diff(-) " + (res-P); 
               rY = rY + step; 
           end if
           step = step / 2; 
        until ((abs(res-P)<0.0001) || (step < 0.000001));
   end if
   return usedrY; 
end

The CalcAnnuityInterestRate function calculates the effective interest rate for an annuity loan with fixed payments. While a basic loan formula can determine the actual payment based on a given interest rate, additional bank fees often increase the effective interest rate. Since there’s no direct formula to calculate this rate, the function uses a binary search (trial and error) algorithm to find the interest rate that matches the payment amount.

It relies on the AnnuityLoanPayment function (also in the code) to compute the payment for each interest rate it tests.

Example

Let's say we have these parameters:

  • Loan amount: 100,000
  • Loan term: 5 years, with 12 monthly payments per year
  • Monthly payment calculated by the bank: 2,000 (including bank fees and establishment fee)

To find the effective interest rate, use the following:

Set Variable [$res; ACF_Run("CalcAnnuityInterestRate"; 100000; 2000; 5; 12)]

The console output logs the calculation process:

Interest: 100.000000% - Payment: 8402.305216 diff(+) 6402.305216
Interest: 50.000000% - Payment: 4560.474166 diff(+) 2560.474166
Interest: 25.000000% - Payment: 2935.132338 diff(+) 935.132338
Interest: 12.500000% - Payment: 2249.793823 diff(+) 249.793823
Interest: 6.250000% - Payment: 1944.926168 diff(-) -55.073832
... (10 more iterations)...
Interest: 7.420099% - Payment: 2000.000142 diff(+) 0.000142
Interest: 7.420093% - Payment: 1999.999859 diff(-) -0.000141
Interest: 7.420096% - Payment: 2000.000000 diff(+) 0.000000

The resulting effective interest rate for this loan is 7.42% per annum.

Back to List