100 Best ChatGPT Excel Data Analysis Prompts for Mortgage Brokers

Direct Answer

The best ChatGPT prompts for Excel data analysis for mortgage brokers are designed to transform raw loan data into clear insights, dashboards, and actionable steps. Use them to identify bottlenecks, measure performance, and guide decisions across pipelines, rates, and regions.

Introduction

This prompt library provides practical, copy-ready Excel data analysis prompts tailored for mortgage brokers. Each prompt includes role, task, context, output format, and constraints to minimize guesswork.

Whether you are an individual broker or a regional operations head, these prompts help you extract meaningful metrics from mortgage data and turn them into concrete actions.

How to Use These ChatGPT Prompts

• Replace placeholders like [DateRange], [Region], and [Product] with your actual data scope.
• Add any constraints such as time window, currency, or segment definitions to tailor outputs.
• Request outputs in specific formats (CSV, table, bullet summaries) to simplify reporting.
• Validate outputs by cross-checking totals and ensuring column names match your dataset.
• Save prompts as templates to reuse across monthly closes and quarterly reviews.

100 Best ChatGPT Excel Data Analysis Prompts for Mortgage Brokers

1. Pipeline stage breakdown: You are a data analyst for a mortgage brokerage. Given a dataset with columns [LoanID], [Applicant], [Stage], [Amount], [Rate], [CloseDate], [OriginDate], [Status], [Branch], [Broker], produce a tabular summary showing counts and total amounts by Stage, Month, and Branch. Output as a CSV with headers Stage, Month, Branch, Count, TotalAmount. Include a brief interpretation section in 2 bullet points.
2. Rate impact comparison: As an Excel analyst for a mortgage brokerage, comparing impact of rate changes on monthly funding. Use dataset columns [Month], [Rate], [Applications], [Closings], [ClosedAmount], [Product], [Broker]. Create a pivot-like report that shows correlation between Rate and Closings, with a scatter plot description and a summary recommendation in bullet form.
3. Top brokers by volume: Create a ranked list of top brokers by loan volume over [DateRange]. Dataset includes [Broker], [LoanID], [Amount], [CloseDate], [Status]. Output a table with columns Broker, TotalVolume, Closings, AvgLoan, and a 2-sentence takeaway for leadership.
4. Applicant segment profiling: Segment applicants into three profiles: First-time Buyer, Refinancer, and Investor. Use dataset fields [ApplicantID], [CreditScore], [DebtToIncome], [LoanAmount], [PropertyType], [Region], [Approval]. Produce a 1-page profile sheet with key metrics and a color-coded risk tag per segment.
5. Pipeline aging dashboard: From dataset with [Stage], [DateEntered], [ExpectedCloseDate], [Broker], [Amount], generate an aging dashboard showing days in stage, with color bands for risk (green, yellow, red). Output a printable table and a 5-line executive summary.
6. Close rate trend: Analyze monthly close rate from fields [Month], [Applications], [Closings], [CloseRate]. Provide a line chart description (no images) and an accompanying interpretation: which months underperformed and potential causes.
7. Product mix analysis: Given [Product], [Applications], [Closings], [Amount], analyze how product mix affects close rate and total volume. Output a table by Product with CloseRate, TotalAmount, and a 3-sentence recommendation.
8. Seasonality adjustments: Using [Month], [Applications], [Closings], [Rate], adjust performance metrics for seasonality. Produce an adjusted CloseRate and a 1-page seasonal note for leadership.
9. Lead-to-application funnel: From dataset with [LeadID], [Source], [ApplicationDate], [Status], [ConvertedToApplication], [Denied], build a funnel: Leads, Applications, Approvals, Closings. Output conversion rates and bottlenecks in a bullet list.
10. Credit policy delta: Assess impact of a hypothetical credit policy change using fields [CreditScoreBand], [Income], [DTI], [Approved], [Denied], [Reason]. Produce a sensitivity table with estimated change in approvals across bands and a one-paragraph impact note.
11. Geographic performance: Create per-region performance metrics from fields [Region], [Branch], [Applications], [Closings], [Amount]. Output a regional table and a 2-sentence geographic recommendation.
12. Loan-to-value insights: Analyze LTV distribution and its relationship with approval rate using [LTV], [Approval], [Region], [Product]. Produce quartile breakdown by LTV and a concise takeaway.
13. Investor loan patterns: Filter dataset for Investor loans and compare volume, rate, and duration against owner-occupied loans. Output a side-by-side table plus 2 actionable insights.
14. Refinance window analysis: From [DateRange], compare refinance volume versus purchase loans. Output a two-column table with GrowthRate and a 1-sentence note per column.
15. Origin channel efficiency: Evaluate efficiency of channels in converting leads to closings. Use fields [Channel], [Leads], [Applications], [Closings]. Provide a channel-by-channel efficiency score and a brief recommendation.
16. Credit score bands: Split applicants into credit score bands (Very Good, Good, Fair, Poor). For each band, compute approval rate, average loan amount, and average rate. Output a compact table and 2-line interpretation.
17. Underwriting time: From [StageDate], [Underwriter], [TurnaroundDays], [Status], analyze average time to approval and identify longest delays. Output a ranked list of top delayers with recommended process changes.
18. Fraud risk flags: Flag potential fraud indicators using fields [ApplicantID], [IP], [ApplicationDate], [LoanAmount], [Flag]. Produce a risk score per loan and a 1-page alert summary.
19. Compliance checklist: Create a compliance checklist from dataset attributes [Product], [Region], [Broker], [RegulationTag], [Status]. Output a 1-page Excel-friendly checklist with 20 items and status column.
20. Batch loan status update: Draft a batch update template to reconcile statuses across [LoanID], [CurrentStatus], [TargetStatus], [Reason]. Include a CSV-ready update file and a 1-line summary per loan.
21. A/B test results: Compare two policy interventions using fields [Variant], [Applications], [Closings], [Conversion], [Revenue]. Produce a verdict with effect size and a 2-sentence conclusion.
22. Data quality score: Compute data quality score across key fields [LoanID], [Applicant], [Status], [CloseDate]. Output a quality score per row and a 1-page data quality improvement plan.
23. Forecast revenue: Forecast next quarter revenue using time series of [Month], [Closings], [AverageClose], [Rate], [Product]. Produce a forecast table and a 2-line note on assumptions.
24. Rate sensitivity map: Create a map-like table showing how rate changes affect monthly closings by product. Output a grid with Rate bands, Product, Closings, and TotalAmount.
25. Portfolio risk heatmap: From fields [Region], [Product], [LoanStatus], [CreditScore], generate a risk heatmap indicating concentration risk. Provide a summary interpretation in two bullets.
26. Turnaround time leaders: Identify branches with fastest underwriting by analyzing [Branch], [TurnaroundDays], [Applications], [Closings]. Output a leaderboard and two improvement recommendations.
27. Client retention signals: Analyze repeat borrowers using [ClientID], [FirstLoanDate], [LastLoanDate], [TotalLoans], [Status]. Output retention rate by year and a 1-page retention brief.
28. Delinquency watchlist: Flag loans nearing delinquency using [DaysPastDue], [Status], [PaymentHistory], [LoanAmount]. Produce a watchlist CSV and a 1-page risk note.
29. Automated report template: Generate an Excel-ready monthly report template covering key KPIs: [Month], [Applications], [Closings], [Revenue], [AvgRate], [Product]. Provide formula placeholders and a one-page guide.
30. Scenario planning kit: Create a scenario planner for macro factors affecting mortgage volumes: unemployment, rates, housing starts. Output a scenario table with assumptions and impact on Closings.
31. Product profitability: Compute profit per loan by product using [Product], [Revenue], [Cost], [Volume]. Output a per-product profitability table and a 2-sentence strategic note.
32. Compliance risk scoring: Score loans for compliance risk using [Region], [Product], [Broker], [Flag]. Provide a risk score and recommended action per loan.
33. On-time funding rate: Measure percentage of loans funded on or before target date using [LoanID], [TargetDate], [FundDate], [Status]. Output a rate and list of late loans.
34. Customer lifetime value: Estimate LTV for repeat clients using [ClientID], [TotalLoans], [TotalRevenue], [RetentionYears]. Output a lifetime value per client and a 1-line takeaway.
35. Loan purpose distribution: Break down loans by purpose (Purchase, Refinance, Equity), using [LoanID], [Purpose], [Amount]. Produce a table and a brief executive note.
36. Deal floor plan: Create a floor plan view for active loans by broker with columns [Broker], [LoanID], [Stage], [Amount], [DaysInStage]. Output a sortable table and a 1-line summary.
37. Delinquency projection: Project delinquency risk for the next quarter using historical [DelinquencyRate], [LoanAmount], [LTV], [CreditScore]. Provide a projection table and a mitigation note.
38. Adverse scenarios: Model worst-case scenarios for rate increases and job loss with fields [Scenario], [RateIncrease], [Unemployment], [ClosingsImpact]. Output a table and recommended actions.
39. Data cleansing plan: Develop a 1-page cleansing plan targeting missing values in [Field], with steps, owners, and a CSV of corrected rows.
40. Automation checklist: List 20 automation opportunities in Excel for mortgage data workflows: imports, validations, and reporting. For each, provide a brief benefit and required data fields.
41. KPI benchmark by region: Benchmark regional KPIs: application rate, conversion, and average loan size by region. Output a table and a 2-line regional insight.
42. Fee impact analysis: Analyze how origination fees affect net revenue across products using [Product], [Fee], [Volume], [NetRevenue]. Provide a table and a 2-line insight.
43. Turnover correlation: Explore correlation between broker turnover and volume using [Broker], [Tenure], [Volume]. Output a correlation coefficient and 1-line interpretation.
44. Documentation completeness: Score document completeness for each loan using [DocsPresent], [DocType]. Output a completeness score per loan and a 1-page checklist to improve it.
45. Pricing parity check: Check pricing parity across channels for [Product], [Rate], [Channel], [LOB]. Output any disparities and a recommended adjustment plan.
46. Referral source analysis: Compare performance by referral source using [Source], [Applications], [Closings], [Revenue]. Provide a source-by-source table and 2 actionable recommendations.
47. Interest rate outlook: Summarize current rate environment and forecast impact on close rates using [Rate], [Forecast], [Product]. Provide a short bulleted forecast and a 1-line conclusion.
48. Batch anomaly detection: Run anomaly detection on daily loan data using fields [Date], [Value], [AnomalyFlag]. Output a list of anomalies with their metric and suggested next steps.
49. Customer segmentation: Cluster borrowers into segments based on [CreditScore], [Income], [LoanAmount], [Region]. Output 4 segments with summary profiles and recommended actions.
50. Export-ready dashboards: Prepare an export-ready dataset and 1-page dashboard spec with charts for key metrics: [Month], [Applications], [Closings], [Amount]. Include column names and formatting rules.
51. Data dictionary draft: Create a data dictionary for the mortgage dataset covering [Field], [Definition], [AllowedValues], [Source]. Output a clean, copy-ready dictionary.
52. Historic rate comparison: Compare current rates with a baseline period using [Product], [CurrentRate], [BaselineRate], [Delta]. Provide a table and a 2-line interpretation.
53. Loan aging by broker: Show aging of loans by broker with fields [Broker], [LoanID], [Stage], [DaysInStage], [Amount]. Output a broker table and a 2-line insight.
54. Data import template: Provide a robust Excel import template for new mortgage data with required columns [LoanID], [Applicant], [Product], [Rate], [CloseDate]. Include validation rules.
55. Expense vs revenue: Analyze relationship between origination expenses and revenue by product. Output a table with Expense, Revenue, Margin and a short action note.
56. Portfolio diversification: Assess concentration by product and region. Output a diversification score and recommended adjustments in 2 bullets.
57. Credit utilization trend: Track average debt-to-income across time and correlate with approvals. Provide a trend table and 2-line insights.
58. Scenario variance report: Produce a variance report between planned and actual closings by product and region. Output a table and a 1-sentence takeaway.
59. Broker performance forecast: Forecast broker velocity and volume for next quarter using [Broker], [HistoricalVolume], [Seasonality]. Output per-broker forecast table and two-line note.
60. Policy change impact: Simulate impact of a policy change on approvals by region. Provide a table of approvals with and without policy and a brief recommendation.
61. Rate cap analysis: Analyze how rate caps affect borrower choices and closings across products using [Cap], [Product], [Closings]. Output a table and 1-line guidance.
62. Product profitability heatmap: Create a heatmap-like table for profitability by product and region. Output a grid and a 2-sentence interpretation.
63. Automation risk review: List the top three automation risks in Excel data processes and propose mitigations with a 1-line justification per item.
64. Client onboarding times: Track onboarding time from lead to first payment using [LeadDate], [OnboardDate], [Product]. Output a table and a 2-line optimization note.
65. Turnaround time by broker: Rank brokers by average underwriting turnaround and suggest improvements. Output a leaderboard and 2 actionable tips.
66. Depot of anomalies: Compile a daily anomalies depot from [Date], [Metric], [Value], [Flag]. Provide a list with suspected causes and recommended checks.
67. Historical performance snapshot: Create a quarterly performance snapshot with [Quarter], [Applications], [Closings], [AverageRate], [Volume]. Output a compact summary.
68. Data retention plan: Outline a data retention plan for mortgage data including retention periods and archiving steps. Provide a 1-page plan.
69. Audit-ready reports: Build an audit-ready Excel workbook structure with sheets for data, calculations, and notes. Include naming conventions and protection tips.
70. Turnover risk map: Map broker turnover risk by tenure and volume. Output a small risk map and 2-line mitigation notes.
71. Client journey map: Outline the typical client journey from lead to close with touchpoints and data signals. Produce a 1-page journey map and data needs list.
72. Refund and returns: Analyze refunds or paybacks by product and reason. Output a table and 2-line suggestions to reduce refunds.
73. Cross-sell opportunities: Identify cross-sell opportunities from existing clients using [ClientID], [Product], [LoanCount]. Output a list of top opportunities with rationale.
74. Data normalization plan: Propose a normalization scheme for heterogeneous data sources including field mappings and normalization rules. Output a concise plan.
75. Pivot table templates: Provide ready-to-use pivot table configurations for common mortgage metrics: by broker, by product, by region. Include row/column fields and filters.
76. Validation rules: List 15 data validation rules for Excel mortgage data to prevent common errors. Include example formulas and error messages.
77. Rate calibration: Calibrate rate calculations across products to ensure consistency. Output a calibration checklist and a sample corrected calculation.
78. Forecast accuracy check: Compare forecasted closings to actuals for the last four quarters. Output a toggle table and a 2-line accuracy note.
79. Data storytelling: Create a concise narrative from a data slice: region X, product Y, date Z. Output a short executive summary with 3 supporting bullets.
80. Compliance checklists by product: Generate product-specific compliance checklists with 15 items each. Output as 2 blocks for two products and a master index.
81. Expense-to-revenue map: Map expenses to revenue by month and product. Output a table and a 1-line optimization note.
82. Data extraction prompts: Draft prompts to extract clean data from raw loan files into structured columns: LoanID, Applicant, Status, Amount, Rate, CloseDate. Include field mappings.
83. Credit policy dashboards: Build a dashboard spec showing policy thresholds and impact on approvals. Include charts, filters, and an export-ready layout.
84. Market share analysis: Estimate market share by region using your loan counts vs a provided market total. Output a table and 2-line insights.
85. Open loans aging: Track open loans by days in pipeline. Output a table with buckets (0-30, 31-60, 61+), counts, and totals.
86. Rate escalation rules: Define rate escalation rules based on product, term, and credit score. Provide a table of rules and a sample calculation.
87. Lead scoring model: Create a simple lead scoring model using fields [Source], [Budget], [Timeline], [Interest]. Output a score for each lead and recommended actions.
88. Documented decisions log: Maintain a decisions log for loan approvals with fields [LoanID], [Decision], [Reason], [Date]. Output a ready-to-share log template.
89. Automation error guard: Propose error handling steps for automated data workflows, including retries and alerts. Provide a 1-page guardrail plan.
90. Portfolio diversification rules: Define rules to diversify mortgage portfolio by region and product. Output a policy checklist and a 1-line rationale.
91. Credit risk sensitivity: Compute sensitivity of approval rates to changes in average credit score. Output a table and a 1-line directive.
92. Date normalization: Normalize date fields into ISO format and extract Year and Quarter. Provide a small function snippet and expected results.
93. Client value map: Map high-value clients by lifetime revenue and loyalty. Output a ranked list with notes on engagement strategies.
94. Benchmark against peers: Compare your region's metrics to peer regions. Output a table with gaps and two improvement ideas.
95. Loan modification analysis: Analyze how modification terms influence closings. Output a table with term changes and a 2-sentence conclusion.
96. Automation handoff doc: Prepare a handoff document for IT to maintain Excel dashboards, covering data sources, refresh cadence, and owners.
97. Audit preparedness checklist: Create an audit readiness checklist covering data lineage, access controls, and change logs. Output a 1-page checklist.
98. Scenario workshop prompts: Provide prompts to run a scenario workshop with stakeholders focusing on rate, demand, and capacity. Output a facilitator guide and 3 prompts.
99. Export format presets: Define presets for exporting data to analysts, lenders, and management with specific column orders and formats. Output a 1-page spec.
100. Executive briefing pack: Assemble a compact executive briefing pack covering 5 key metrics, 2 trends, and 2 recommendations. Output a ready-to-share outline.

Best Practices

• Keep datasets clean and well-labeled with consistent column names.
• Label worksheets and outputs clearly for handoffs to managers and lenders.
• Use stable references for data sources to ensure repeatable results.
• Limit outputs to the required scope to reduce noise and speed up delivery.
• Lock or document model assumptions when sharing with teams.

Common Mistakes to Avoid

• Using placeholders without replacing them leads to nonspecific results.
• Asking for outputs in vague formats like 'summary' without concrete fields.
• Overloading a single prompt with too many tasks, reducing accuracy.
• Not validating results against actual data, causing misinterpretation.
• Ignoring data quality issues that skew analyses.

FAQ

What is the purpose of these prompts for mortgage brokers?

They provide ready-to-use Excel analysis prompts to derive actionable insights from mortgage data, improving pipeline management, pricing decisions, and client targeting.

How should I adapt prompts to my data?

Replace placeholders with your actual column names, date ranges, and product definitions. Keep data in a clean, consistent format.

Can prompts generate charts or dashboards?

Prompts describe outputs and can guide the creation of charts, pivot tables, and dashboards; they specify formats and summaries but do not render visuals themselves.

Are there prompts for compliance and risk?

Yes, several prompts cover compliance checks, risk scoring, anomaly detection, and fraud flags to support governance and control.

How do I reuse prompts over time?

Save prompts as templates and adjust date ranges, regions, and products for monthly or quarterly reports to preserve consistency.

What are recommended outputs to export?

CSV, Excel tables, and one-page summaries are common outputs to feed analysts, leaders, and lenders.