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.
A Explanation Range name 1 $1,000.00 Loan Amount LOAN 2 10 Number of payments in loan agreement TERM 3 52 Number 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.56 Value of each payment A6=@PAYMNT(LOAN,INTEREST,TERM) 7 8 0.0% Commission deducted from Loan Amount at time of Disbursement =COMMISSION 9 $0.00 Charge 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 - firstname.lastname@example.org
Return to the Capacity Building Page
Return to the Virtual Library on Microcredit