LOTUS Spreadsheet Model for

Calculating Interest Rates

Note: Terms at the right are range names and refer to the cell in column A found on the same line. For example, the range for cell A1 is LOANS. The use of range names simplifies the formulas found in cells A6 and A11.

AExplanationRange name1 $1,000.00Loan Amount LOAN 2 10Number of payments in loan agreement TERM 3 52Number of payment periods in a year (eg. weekly=52) 4 24.0%Nominal Annual Interest Rate 5 0.46%Nominal Periodic Interest Rate (A4/A3) =INTEREST 6 $102.56Value of each payment A6=@PAYMNT(LOAN,INTEREST,TERM) 7 8 0.0%Commission deducted from Loan Amount at time of Disbursement =COMMISSION 9 $0.00Charge added to each installment =FEE 10 11 0.46%Periodic Interest Rate A11=@IRATE(TERM,PAYMENT+FEE,LOAN*(1-COMMISSION)) 12 24.0%Annual Percentage Rate (APR) A11*A3 13 27.1%Effective Annual Interest Rate (APR) (1+A11)^A3-1 The two significant formulas are located in cells A6 and A11. For programs using a "flat" interest rate to determine payments, use the formula in cell A11 to convert the "flat" rate to the equivalent APR and effective interest rate.

For Excel users:- the formula for cell A6 is as follows: =PMT(INTEREST,TERM,LOAN*-1)

the formula for cell A11 is: =RATE(TERM,PAYMENT,LOAN*-1)

From: Bill Tucker - TuckerCFI@aol.com

Community Finance Incorporated

Hari Srinivas -hsrinivas@gdrc.org

Return to theCapacity BuildingPage

Return to the Virtual Library on Microcredit