Microfinance financial model: portfolio, costs and cash

A microfinance financial model connects loan portfolio movements to interest income, funding costs, operating expenses and cash. Start with the lender's servicing and finance exports, then keep earned income separate from principal collections. A growing portfolio can require more cash even when the earnings trend improves.

Start with separate portfolio, funding and cash records

Use a monthly workbook with a common reporting date and currency. Keep one portfolio record per loan and month, identified by a business reference rather than borrower names. Bring opening principal, new disbursements, principal collected, interest earned, interest collected, charge-offs and closing principal. Preserve lending program and branch fields for later comparisons.

Use a separate funding export for opening debt, draws, repayments, interest and closing debt. Add operating expense actuals and a dated payment schedule. The CDFI Fund's program description identifies several forms of financial assistance, including loans, grants and equity investments. Keep those funding types distinct; the example below assumes borrowing and no grant receipts.

Freeze the approved plan before bringing in actuals. Label each row with period, scenario and source reference, so a revised assumption cannot overwrite the baseline. The broader startup financial model covers company planning; this workbook focuses on lender portfolio movements.

Build the loan portfolio roll-forward

Closing principal = Opening principal + Disbursements − Principal collected − Charge-offs

An illustrative US community lender prepares the following quarter. All dollar amounts are USD. Principal collected excludes interest, and charge-offs are reductions in principal rather than cash payments.

Place these columns in A:G, with October in row 2. Enter the following and copy down:

F2=B2+C2-D2-E2
G2=(B2+F2)/2
B3=F2

The quarter check is $1,000,000 + $540,000 − $420,000 − $30,000 = $1,090,000. Also compare each closing balance with the servicing control total. A correct movement formula does not prove that every loan was included.

For actual receipts, aggregate only matching period and program rows. If a Receipts sheet has month in A, program in B and principal collected in C, this formula selects the month in A2 and program in K1:

=SUMIFS(Receipts!$C$2:$C$5000,Receipts!$A$2:$A$5000,A2,Receipts!$B$2:$B$5000,$K$1)

The vendor's SUMIFS documentation explains how the criteria select corresponding amounts. Covirage is not affiliated with Microsoft.

Connect balances to income and funding costs

Assume an annual portfolio yield of 18% and annual borrowing cost of 6%, each divided by twelve for this planning example. Monthly average balances use the opening and closing balances. These are supplied assumptions, not market benchmarks; use prepared accrual schedules when actual payment dates or nonaccrual loans make averages unsuitable.

On a Funding sheet with those columns in A:G:

E2=B2+C2-D2
F2=(B2+E2)/2
G2=F2*6%/12

Portfolio interest equals average principal times 18% divided by twelve. Add the supplied operating expense and credit-loss expense assumptions:

With the portfolio table on a Portfolio sheet, the result table uses:

B2=Portfolio!G2*18%/12
C2=Funding!G2
F2=B2-SUM(C2:E2)

This is a management planning result before taxes and any omitted items. The credit-loss expense is a separate finance assumption, not automatically the period's charge-offs. Have finance reconcile the allowance schedule rather than treating those two amounts as interchangeable.

Calculate cash from receipts and payments

The FDIC's Money Smart for Small Business curriculum treats managing cash flow as a distinct business competency. Keep a cash schedule alongside earnings, particularly when lending disbursements exceed collections.

Opening unrestricted cash is $100,000. Assume funding interest and operating expenses are paid in the month incurred, with no other cash movements.

| Month | Opening cash (USD) | Principal receipts (USD) | Interest receipts (USD) | Debt draws (USD) | Disbursements (USD) | Debt repaid (USD) | Interest paid (USD) | Operations paid (USD) | Closing cash (USD) | |---|---:|---:|---:|---:|---:|---:|---:|---:|---:|---:| | October | 100,000 | 130,000 | 14,000 | 50,000 | 180,000 | 20,000 | 3,575 | 9,000 | 81,425 | | November | 81,425 | 140,000 | 15,000 | 50,000 | 200,000 | 20,000 | 3,725 | 9,000 | 53,700 | | December | 53,700 | 150,000 | 16,000 | 0 | 160,000 | 20,000 | 3,750 | 9,000 | 26,950 |

With the table in A:J:

J2=B2+SUM(C2:E2)-SUM(F2:I2)
B3=J2

Prove the $73,050 cash reduction independently. Start with the $2,425 planning loss, add back $12,000 of noncash credit-loss expense, and subtract $2,625 of interest earned but not collected: $47,625 − $45,000. Then add $420,000 of principal receipts, subtract $540,000 of disbursements, add $100,000 of borrowing and subtract $60,000 of debt repayments. The result is −$73,050; $100,000 − $73,050 equals $26,950.

Figures retain full precision; these assumptions produce whole-dollar results, so no rounding adjustment is needed. For more on the distinction behind this check, see cash flow vs profit.

Compare prepared scenarios with actuals

Change collections before changing lending volume. If $30,000 of December principal receipts arrives after the quarter, December closing cash falls from $26,950 to −$3,050, holding the cash schedule's other inputs constant. Against a supplied minimum cash target of $50,000, the gap is $53,050. Recompute interest accruals separately if the delayed balance changes earned income.

Finance owns the reconciliations; servicing owns repayment inputs; treasury owns available funding and draw dates. Compare actual collections, disbursements and operating payments against the frozen plan, then review the largest differences. The FP&A guide covers the wider reporting workflow.

Where it goes wrong

• Principal called income. Separate repayment principal from interest and fees before aggregation.
• Closing balances added across months. Sum movements; carry closing balances forward.
• Charge-offs deducted twice from earnings. Follow the approved credit-loss and allowance schedules.
• Uncommitted funding treated as cash. Distinguish an assumed draw from funding available on the required date.
• Restricted cash included in lending capacity. Reconcile usable cash to restrictions and the bank balances.

Compare your lender workbook in Covirage

Bring a prepared portfolio workbook, funding schedule and operating actuals, or a Google Sheet, using business references. Covirage maps the columns; its tools compute and check the supplied balances, variances and cash movements, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See Covirage for financial services for the operating-finance context.