July 29, 2026 · ViaSheet Team
The Mortgage Broker CRM Spreadsheet: Loan Pipeline & Rate Lock Guide
Discover how independent mortgage brokers and loan officers manage borrower files, lender panels, rate lock expirations, and commission payouts in Google Sheets.
For independent mortgage brokers, loan officers (LOs), and mortgage brokerage teams, running a high-volume loan business requires navigating a multi-stage underwriting pipeline. Whether originating conventional residential mortgages, FHA/VA loans, jumbo mortgages, or commercial real estate financing, your success depends on managing borrower file intake, lender turnaround times, rate lock expirations, appraisal deadlines, and Realtor referral relationships.
According to a benchmark report by the National Association of Mortgage Brokers (NAMB), independent mortgage brokers lose up to 20% of active loan applications due to stalled files in underwriting, expired lender rate locks, and delayed document collection. More critically, failing to cultivate Realtor and financial planner referral partners cuts annual loan volume by over 40%.
While proprietary loan origination CRMs like BNTouch, Surefire, or Sales-Boom exist, their per-user subscription fees ($1,000 to $2,500 per loan officer per year) represent an expensive overhead burden for solo brokers and small mortgage shops.
In this ultimate guide, we will show you step-by-step how to build a production-ready Mortgage Broker CRM Spreadsheet in Google Sheets or Microsoft Excel. You will learn how to track multi-stage loan pipelines, automate rate lock expiration warnings, audit lender turnaround times, manage Realtor referral sources, and forecast monthly commission income without software fees.
Why Independent Loan Officers Need a Spreadsheet CRM
Mortgage origination is a complex financial transaction involving borrowers, real estate agents, title companies, appraising firms, and wholesale lenders.
Here is why independent mortgage brokers choose Google Sheets or Excel:
- Zero Seat Subscription Overhead: Mortgage commission income fluctuates with interest rate cycles. Eliminating $150/month per user software fees protects your brokerage cash flow.
- Instant Mobile File Status Access: Storing loan amounts, LTV ratios, lender lock dates, and underwriting statuses in a cloud Google Sheet allows loan officers to update borrowers and realtors instantly from smartphones or tablets while attending open houses or closing tables.
- Custom Commission Split Math: Wholesale lender payouts vary based on loan type and compensation agreements (e.g., Lender-Paid vs. Borrower-Paid compensation, broker split %). Spreadsheets allow custom formula ledgers for every loan origin.
- Data Ownership & Borrower Confidentiality: Keeping borrower contact records and pre-approval details in your private Google Workspace account ensures enterprise cloud encryption without uploading confidential financial data to third-party SaaS cloud databases.
Architecture of a Mortgage Broker CRM Spreadsheet
To keep your loan origination business organized and compliant, structure your spreadsheet into six core tabs:
[1. Borrower Lead Pipeline] ➔ [2. Master Loan Register] ➔ [3. Deadline & Rate Lock Radar] ➔ [4. Wholesale Lender Panel] ➔ [5. Commission Ledger] ➔ [6. Mortgage Dashboard]Tab 1: Borrower Lead Pipeline
Tracks incoming borrower leads from initial pre-qualification inquiry to credit pull, pre-approval letter issued, property under contract, and loan application submitted.
Tab 2: Master Loan Register
Indexes active loan files: Borrower Name, Loan Type (Conventional, FHA, VA, Jumbo, USDA), Purchase Price, Loan Amount, Loan-to-Value (LTV %), Interest Rate, Wholesale Lender, and Underwriting Status.
Tab 3: Deadline & Rate Lock Radar
An automated schedule tracking critical loan milestones: Rate Lock Expiration Date, Appraisal Ordering & Delivery Date, Conditional Approval Date, Clear to Close (CTC) Date, and Scheduled Closing Date.
Tab 4: Wholesale Lender Panel
Logs wholesale lender contacts, current interest rate pricing sheets, underwriting turnaround times (e.g. 24-hr initial turn), and niche product guidelines (e.g. Bank Statement Loans, DSCR).
Tab 5: Commission Ledger
Calculates funded loan amounts, gross commission points (e.g. 1.75% or 2.00%), referral fee split deductions, team desk splits, and net broker payouts.
Tab 6: Mortgage Dashboard
Displays high-level executive analytics: total active pipeline volume ($), funded volume YTD, average days to close per file, and revenue by referral partner.
Step-by-Step: Building Your Mortgage CRM Spreadsheet
Let’s build the Master Loan Register and Rate Lock Radar tabs in Google Sheets or Excel.
1. Structure the Master Loan Register Tab
Create a tab named Loan Register and set up the following headers in Row 1:
| Column | Header Name | Data Type | Description / Formula |
|---|---|---|---|
| A | Loan ID | Formula | =IF(ISBLANK(B2), "", "LOAN-" & TEXT(ROW()-1, "0000")) |
| B | Borrower Full Name | Text | Primary borrower legal name |
| C | Loan Type | Dropdown | Conventional, FHA, VA, Jumbo, USDA, DSCR Investment |
| D | Purchase Price ($) | Currency | Agreed property purchase price |
| E | Loan Amount ($) | Currency | Total mortgage loan amount |
| F | LTV (%) | Formula | =IF(ISBLANK(D2), 0, E2 / D2) |
| G | Wholesale Lender | Dropdown | UWM, Rocket Pro, Pennymac, Homepoint, Freedom Mortgage |
| H | Pipeline Stage | Dropdown | 1. Pre-Approved, 2. App Submitted, 3. Processing, 4. Underwriting, 5. Clear to Close, 6. Funded |
| I | Rate Lock Exp Date | Date | Lender rate lock expiration date |
| J | Lock Status Alert | Formula | Visual alert checking rate lock urgency |
2. Automating Rate Lock & Underwriting Alerts
Allowing a borrower’s rate lock to expire before closing can cost thousands of dollars in extension fees or higher interest rates. Automate date alerts:
A. Automated LTV Calculation (Column F)
In cell F2, calculate Loan-to-Value ratio by dividing Loan Amount by Purchase Price:
=IF(ISBLANK(D2), 0, E2 / D2)Format cell as Percentage (%).
B. Rate Lock Expiration Alert Formula (Column J)
In cell J2, write a conditional logic check classifying rate lock urgency:
=IF(H2="6. Funded", "✅ Closed & Funded", IF(ISBLANK(I2), "Floating (Unlocked)", IF(TODAY() > I2, "🚨 LOCK EXPIRED", IF((I2 - TODAY()) <= 5, "⚠️ LOCK EXPIRING (5 Days)", "✅ Lock Active"))))Explanation:
- If today’s date has passed
I2, flags as🚨 LOCK EXPIRED(Request lender extension immediately). - If within 5 days of
I2, flags as⚠️ LOCK EXPIRING (5 Days). - Otherwise, displays
✅ Lock Active.
Apply Conditional Formatting to highlight 🚨 LOCK EXPIRED in bright red (#FEE2E2).
3. Calculating Mortgage Commission Payouts
On your Commission Ledger tab, calculate net broker commissions per funded file:
| Borrower Name | Funded Loan ($) | Comp Points (%) | Gross Commission ($) | Referral Split ($) | Net Broker Payout ($) |
|---|---|---|---|---|---|
| David Miller | $400,000.00 | 2.00% | =B2 * C2 ($8,000.00) | $0.00 | $8,000.00 |
| Elena Rostova | $650,000.00 | 1.75% | $11,375.00 | $2,500.00 | $8,875.00 |
Gross Commission Formula (Column D):
=IF(OR(ISBLANK(B2), ISBLANK(C2)), 0, B2 * C2)Net Payout Formula (Column F):
=IF(ISBLANK(D2), 0, D2 - E2)5 Referral Growth Hacks for Mortgage Brokers
- Track Realtor Referral Attribution: On your
Borrowerstab, log the referring real estate agent’s name in Column M. Generate a quarterly summary showing total funded volume generated by each Realtor partner. - Send Tuesday Pipeline Status Updates: Filter your loan pipeline every Tuesday morning and text standardized status updates to the listing agent, buyer’s agent, and borrower simultaneously.
- Log Annual Mortgage Review Dates: On your
Past Borrowerstab, set an automated notification 11 months after funding. Contact past borrowers to review refinancing opportunities if interest rates have dropped! - Attach Secured Borrower Document Links: Store direct Google Drive links for borrower W-2s, tax returns, and bank statements in Column K for one-click access during underwriting condition clears.
- Monitor Days-in-Stage Stalls: Highlight any loan file that has been stuck in
3. Processingor4. Underwritingfor over 7 business days to resolve underwriting conditions promptly.
Frequently Asked Questions (FAQ)
Can loan officers and processors update file statuses simultaneously?
Yes! Google Sheets allows real-time simultaneous editing across multiple computers in your office or remote work locations.
How do I handle floating versus locked interest rates?
In your Master Loan Register tab, Column I records the Rate Lock Expiration Date. If the rate is floating, leave Column I blank; the spreadsheet displays Floating (Unlocked) until a formal rate lock confirmation is issued by the wholesale lender.
Is Google Sheets secure for storing borrower financial info?
Yes. Google Sheets uses enterprise Google Cloud encryption. Ensure your Google account uses Two-Factor Authentication (2FA), restrict edit permissions to authorized loan officers and processors, and avoid storing raw Social Security numbers in plain text.
Upgrade to the ViaSheet Mortgage Broker CRM Spreadsheet
Building a custom mortgage database with underwriting pipelines, rate lock radars, lender panel logs, and Realtor referral attribution tables requires hours of formula design.
If you want a pre-built, battle-tested spreadsheet engineered specifically for independent mortgage brokers and loan officers, explore our Mortgage Broker CRM Spreadsheet.
The ViaSheet Mortgage Broker CRM features:
- Executive Mortgage Dashboard: Real-time stats on active pipeline volume ($), funded volume YTD, commission forecasts, and rate lock expiration alerts.
- Borrower Lead Pipeline: Track prospects from initial pre-qual credit pull to pre-approval letter and contract signed.
- Master Loan Register: Index files by loan type (Conventional, FHA, VA, Jumbo), purchase price, loan amount, and automated LTV %.
- Rate Lock & Deadline Radar: Color-coded alerts for expiring rate locks, appraisal dates, and clear-to-close milestones.
- Commission Ledger: Calculate gross commission points, referral partner splits, and net broker payouts.
- Realtor Referral Matrix: Track top referral sources and automate past-borrower annual mortgage review check-ins.
- One-Time Purchase: Pay just €39 once for lifetime access in Google Sheets and Microsoft Excel—no monthly fees or seat subscriptions ever.
Fund more loans, protect rate locks, and scale your mortgage brokerage. Download the ViaSheet Mortgage Broker CRM today!