Microfinance & Small-Loan Math: Flat vs Declining Rates, APR & Portfolio Quality

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Price one small loan of Lumen Microfinance two ways, flat and on the declining balance, build the repayment schedule and measure the true annual rate of the flat loan.
• Find out what a Lumen loan with a flat rate, an upfront fee and a compulsory savings deposit really costs, as an APR with RATE and as an effective rate with EFFECT.
• Build a 12-week repayment schedule with due dates for a Lumen market-vendor loan and compare its interest cost with monthly repayments.
• Measure how well 8 Lumen borrower groups repaid in September 2026: on-time rate per group, follow-up flags and the portfolio repayment rates.
• Sort a Lumen loan officer's 12 loans into ageing buckets with VLOOKUP, total each bucket with SUMIF and calculate PAR30 and PAR90.
• Calculate Lumen's required loan loss reserve from the ageing table, the provision expense after this year's write-offs, the risk coverage ratio and the write-off ratio.
• Compare Lumen's 2025 and 2026 results: operational self-sufficiency, operating expense ratio, cost per borrower and borrowers per loan officer.
• Build the interest rate Lumen needs from its costs, compare it with the portfolio yield it actually earns, measure the yield gap and show the build-up in a waterfall chart.