Consumer Loan Pro Forma Financial Modeling Tool

Change your lead cost, your default rate, your conversion rate, your average principal, your payroll. Watch the portfolio and the P&L move before you spend a dollar. An Excel model built by lenders, for payday, installment, and car title loan businesses.

Most Lending Businesses Die in a Spreadsheet That Was Never Built

Here's the truth. The operators who lose money in this business do not lose it on bad loans. They lose it on bad assumptions. They guessed at lead cost. They guessed at conversion. They guessed at first-payment defaults. Then they signed a lease, hired two people, bought leads, and found out six months later that the math never worked.

A bank would never let you do that. A bank wants a pro forma. Investors want a pro forma. Your own gut should want one. The problem is that building a consumer loan financial model from scratch takes someone who understands lending and someone who understands Excel, and most new operators have neither on payroll.

That is the gap this tool closes.

What the Pro Forma Financial Modeling Tool Does

It is a working Excel model of a consumer loan business. Not a template with blank cells. A model with 20+ years of real store and online lending numbers already in it as the starting point. You change the inputs. The portfolio, the cash flow, and the P&L recalculate.

Inputs you control

• Lead cost per lead and leads per day
• Lead-to-funded conversion rate
• Loans funded per day and per month
• Average loan principal
• Fee charged per $100 of principal, and loan term
• Rollover and renewal ("react") rates
• First-payment default rate and overall default rate
• Employee count, hourly rates, and payroll burden
• Rent, software, ACH and processing fees, and the rest of overhead

Outputs you get

• Loan portfolio size, month by month
• Fee revenue, charge-offs, and net revenue
• Profit and loss by month and cumulative
• Cash required to reach break-even, and the month it happens

The What-If Questions It Answers

This is where the tool earns its keep. Every one of these is a decision an operator makes with real money. Run it in the model first.

• Lead cost. Should you buy $2 leads, $10 leads, $50 leads, or $185 leads? Industry-average customer acquisition cost runs near $185. What does your portfolio look like converting 8% of $50 leads versus 3% of $10 leads?
• Volume versus quality. 150 leads a day at $8 converting at 4%, or 100 leads a day at $50 converting at 18%? The model shows which one makes money and how fast.
• Reacts. What happens to revenue if repeat customers rise from 42% to 65%?
• Organic leads. Hire an offshore virtual assistant at $200 a month and push organic leads from 5 a day to 20. What is that worth over 12 months?
• Average principal. Move the average loan from $385 to $425. To $500. To $800. Watch the portfolio and the charge-off line move together.
• First-payment defaults. Cut FPDs from 30% to 12% with tighter underwriting. What does the P&L look like now?
• Payroll. Raise the average hourly rate from $12.50 to $15.25. Bring the call center in-house. See the cost before you make the hire.
• New products. Add car title loans at $1,200 average principal, six-month term, $20 per $100. Or $25 per $100 on 30-day terms. Add a 36% APR unsecured product. An 80% APR. A 120% APR.
• Referral spiffs. Pay employees $25 per funded loan and customers $50 per title loan referral. Does it pay for itself?
• Vendor increases. ACH fees go from $1.50 to $1.75 per transaction. Your loan management software raises its monthly fee from $150 to $200 and adds $1 per transaction. How much does that cost you a year?

And on and on. Only your situation limits what you can test. The purpose of a what-if scenario is not to predict the future. It is to change it before it costs you.

What It Looks Like

Five of the worksheets. The workbook is unprotected, so every formula is yours to inspect and change.

Who This Is For

• The first-time lender with capital and a state picked out, who has never seen what month four looks like on paper.
• The owner adding lending to a pawn shop, check casher, tax office, or auto lot, who needs to know how much cash the loan book will eat before it pays.
• The operator raising money who needs a pro forma an investor or a bank will take seriously.
• The existing lender who wants to test a new product, a new lead source, or a rate change without betting the store on it.

Who it is not for: anyone who wants a number handed to them. This is a model, not a crystal ball. It is only as honest as the assumptions you put in it. Put in real ones.

Why Buy a Lending Model From Jer Ayles

Because I run this spreadsheet before I open a location. I own subprime loan stores. I run a lending operation in Dallas. I have spent more than 20 years in payday, car title, installment, Texas CAB, and tribal lending through Trihouse Consulting. The default values in this workbook are not textbook numbers. They are the numbers my stores and my consulting clients produce.

You cannot buy this from a spreadsheet template site. The people who sell templates have never funded a loan. The people who have funded loans do not sell their model. I do, because an operator who runs the numbers first is an operator who stays in business, and that is good for all of us.

Pairs With

The model tells you whether the business works. The Bible: How to Loan Money to the Masses tells you how to run it: licensing, underwriting, collections, marketing, KPIs, forms, and vendors across 500+ pages. Most buyers own both.

Starting in Texas? Read the Texas CSO/CAB Small-Dollar Loan Model first, then model the CAB fee structure here. Buying an existing book? Run the seller's numbers through this tool before you trust the valuation.

Questions Before You Buy

Educational resource only. Not legal, tax, investment, or regulatory advice. State-specific regulatory information is subject to change. Results depend on your assumptions, your state, and your execution.