A savings group loan calculator does three jobs: it works out the monthly instalment, the total interest and the balance after each payment. You can do all three by hand or in a spreadsheet. This article shows the formulas for the two common methods, flat rate and reducing balance, with a worked example for a loan of 1,000,000 at 3% per month.
What you need to know first
- Loan amount (the principal).
- Interest rate per month as a decimal (3% is 0.03).
- Number of months (the term).
- The method: flat or reducing balance. For the difference, see flat vs reducing-balance interest.
Method 1: flat-rate loan calculator
- Total interest = loan x rate x months
- Total repayment = loan + total interest
- Monthly instalment = total repayment / months
Example: loan 1,000,000, rate 3% per month, 12 months.
- Total interest = 1,000,000 x 0.03 x 12 = 360,000
- Total repayment = 1,360,000
- Monthly instalment = 1,360,000 / 12 = 113,333
Method 2: reducing-balance loan calculator
The instalment formula is:
instalment = loan x r / (1 - (1 + r) ^ -n)where r is the monthly rate as a decimal and n is the number of months.
Example: loan 1,000,000, rate 3% per month, 12 months.
- Instalment = 1,000,000 x 0.03 / (1 - 1.03 ^ -12) = about 100,462
- Total repayment = 100,462 x 12 = about 1,205,545
- Total interest = about 205,545
How the schedule works
Each month:
- Interest = remaining balance x monthly rate
- Principal repaid = instalment - interest
- New balance = old balance - principal repaid
For the first month of the example: interest = 1,000,000 x 0.03 = 30,000; principal = 100,462 - 30,000 = 70,462; new balance = 929,538. In month two, interest is 929,538 x 0.03 = 27,886, so more of the instalment goes to principal. The interest falls every month as the balance falls.
Comparing the two results
| Flat | Reducing balance | |
|---|---|---|
| Monthly instalment | 113,333 | about 100,462 |
| Total interest | 360,000 | about 205,545 |
| Total repayment | 1,360,000 | about 1,205,545 |
The same quoted rate of 3% a month produces about 154,000 more interest under the flat method. See APR vs flat rate vs effective interest rate for why.
Using a spreadsheet
If your spreadsheet has a PMT function, the reducing-balance instalment is:
=PMT(0.03, 12, -1000000)For a flat loan, the formulas are:
Total interest = 1000000 * 0.03 * 12
Monthly payment = (1000000 + total interest) / 12To build a full schedule, add one row per month with columns for opening balance, interest (balance x rate), instalment, principal (instalment - interest) and closing balance.
Checks before you approve a loan
- Does the last balance in the schedule reach zero?
- Does the total repaid equal the loan plus the total interest?
- Has the borrower seen the monthly instalment and total cost, in writing?
- Is the method written in the group rules? See the rules template.
- What is the fine for late payment, and when does it apply? See savings group fines.
Common mistakes
- Quoting the monthly rate but not the total cost.
- Mixing methods, for example calculating flat but describing it as reducing.
- Forgetting to recalculate when a borrower makes an early or extra payment.
- Rounding the instalment in a way that leaves a balance at the end. Adjust the last payment instead.
To convert a flat-rate quote into a yearly rate, see how to calculate the APR of a flat-rate loan.