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 - hsrinivas@gdrc.org
Return to the Capacity Building Page
Return to the Virtual Library on Microcredit