100 Best Gemini Excel Data Analysis Prompts for Mortgage Brokers

Direct Answer

The best Gemini prompts for Excel data analysis for mortgage brokers are a curated set of practical, field-ready tasks designed for Excel-based mortgage analytics. They cover data ingestion, cleaning, standardization, risk scoring, portfolio analytics, forecasting, and reporting. Each prompt specifies role, task, placeholders, output format, and constraints for reliable results.

Introduction

This page provides a practical prompt library for Excel data analysis tailored to mortgage brokers. It helps professionals accelerate data workflows, improve accuracy, and generate actionable insights from loan portfolios.

Designed for mortgage brokers, analysts, and operations teams, the prompts guide you from data ingestion to executive-ready dashboards, ensuring consistent outputs and auditable processes.

How to Use These Gemini Prompts

• Replace placeholders in brackets with your actual data sources and fields.
• Add or adjust constraints to fit your data quality and regulatory requirements.
• Request outputs in Excel-friendly formats such as tables, pivot-ready ranges, or CSV to simplify ingestion.
• Verify outputs by cross-checking totals, counts, and key KPIs against known baselines.
• If needed, split large datasets into batches and validate each batch before combining results.
• Version and document your changes for reproducibility and audits.

100 Best Gemini Excel Data Analysis Prompts for Mortgage Brokers

1. Load mortgage dataset: You are a data wrangler for mortgage brokers. Task: ingest [data_source] containing loans with fields such as borrower_id, loan_amount, rate, term, credit_score, property_type, status, and closing_date. Output a clean, deduplicated table in Excel with standardized column names, data types, and a summary row of counts per status. Constraints: maintain original currency units, handle missing values using logical defaults or NA, and preserve timestamps in UTC. Output format: an Excel-ready CSV table with an accompanying schema sheet.
2. Standardize field names: Role: Excel data engineer for mortgage analytics. Task: map and rename columns in [raw_table] to standardized names: borrower_id, loan_amount, rate, term_years, credit_score, property_type, status, closing_date. Provide a mapping table and a versioned list of changes. Output: a two-column mapping sheet and a JSON snippet describing changes.
3. Detect outliers: Role: data analyst for mortgage performance. Task: in [dataset], identify loans where loan_amount is more than three times the median loan_amount or rate exceeds the 95th percentile. Report each outlier with borrower_id, loan_amount, rate, term, and status. Output: a clean Excel sheet of outliers and a summary chart showing distribution of outliers by property_type.
4. Calculate LTV buckets: You are a mortgage analytics assistant. Task: create LTV bucket categories from fields loan_amount and appraised_value in [dataset]. Output: a new column ltv_bucket with categories: under_60, 60_to_80, 80_to_90, over_90. Provide a frequency table and a histogram-ready data series. Constraints: handle missing appraised_value gracefully.
5. Forecast default risk: Role: risk analyst. Task: build a simple Excel-based forecast of default probability using features such as credit_score, DTI, loan_to_value, loan_amount, term_years, and property_type. Output: a worksheet with coefficients, a validation split indicator, and a 12-month forecast chart. Constraints: use logistic-style scoring but present as simple risk tiers.
6. Segment customers: Role: market analyst for mortgage brokers. Task: segment borrowers in [dataset] into three cohorts: high value, mid value, low value based on loan_amount, payment_history, and credit_score. Output: a segment column and a pivot table showing counts and average loan_amount per segment. Output format: Excel table and a summary narrative in a separate sheet.
7. Performance by channel: You are a data analyst. Task: compare loan performance metrics by origination_channel in [dataset], including approval_rate, average_loan_amount, default_rate, and average_term. Output: a pivot table and a bar chart-ready data range. Constraints: color code channels by risk level.
8. Cost-to-serve analysis: Role: financial analyst. Task: compute cost_to_serve per loan using fields such as origination_cost, servicing_cost, and remaining_tenor. Output: a calculated column c2s and a summary by loan_type. Deliver a dashboard-ready dataset and a line chart of c2s over time.
9. Repair missing values: You are a data quality engineer. Task: identify critical missing values in [dataset] across essential fields and apply appropriate imputation: numeric fields with median, categorical with mode, dates with latest plausible date. Output: a cleaned table with a quality score column and a report of changes.
10. Compliance flags: Role: compliance analyst. Task: scan [dataset] for fields that require regulatory flags (e.g., high-price loan, insufficient documentation) and generate a flags column with standardized codes. Output: a flags sheet and a summary of flagged loans by status.
11. Annualized payment schedule: You are a loan operations analyst. Task: convert monthly payments to an annualized schedule for each loan in [dataset], including interest, principal, and remaining balance. Output: a calculation sheet and a printable summary by property_type.
12. Interest rate sensitivity: Role: financial modeler. Task: build a scenario table showing changes in monthly payment when interest rate shifts by +/- 50 basis points for each loan in [dataset]. Output: a scenario matrix suitable for Excel data table tool and a chart showing payment sensitivity.
13. Credit banding: You are a credit risk analyst. Task: assign each borrower to a credit_band based on credit_score ranges: poor, fair, good, very_good, excellent. Output: a new column and a cross-tab showing distribution of loans by credit_band and status.
14. Property type impact: Role: portfolio analyst. Task: analyze performance by property_type in [dataset], including default_rate, average_loan_amount, and median_age of loan. Output: a summary sheet and property_type level charts.
15. Payment delinquency drill: You are an analyst. Task: identify cohorts with delinquency in the last 12 months in [dataset] and produce a drill-down report by month, borrower_segment, and property_type. Output: a drill-down table and a trend chart.
16. Prepayment estimate: Role: product analyst. Task: estimate prepayment rate by loan_type and term in [dataset] using simple heuristics. Output: a prepayment_table with expected monthly prepayments and a visualization-ready dataset.
17. Run rate metrics: You are a performance analyst. Task: compute run rate metrics such as new_inquiries_per_month, approvals_per_month, funded_loans_per_month in [dataset]. Output: a time-series table and a dashboard-ready dataset.
18. Data quality scorecard: Role: data governance. Task: generate a quality score per loan with scoring rules for completeness, accuracy, and consistency. Output: a scorecard sheet and recommended remediations.
19. Cash flow projection: You are a financial planner. Task: produce a 12-month cash flow projection per loan using payment_schedule and servicing_fees. Output: a projection table and a summary dashboard.
20. Portfolio health heatmap: Role: portfolio manager. Task: create a heatmap of risk indicators (default_rate, delinquencies, LTV) by region and property_type. Output: a heatmap-ready data matrix and a legend.
21. Dataset validation: You are a quality auditor. Task: validate [dataset] against a schema with required fields and data types. Output: a validation report with pass/fail per field and suggested fixes.
22. Date normalization: Role: data engineer. Task: normalize date fields closing_date, origination_date to ISO format and extract year, quarter, month. Output: a normalized dataset and a summary of date ranges.
23. Risk-adjusted ROI: You are a portfolio analyst. Task: compute risk-adjusted ROI for each loan using simple risk weightings and derive an aggregated portfolio ROI. Output: a sheet with ROI per loan and a portfolio summary.
24. Loan status funnel: Role: operations analyst. Task: create a funnel from application to funded to current status, with counts and conversion rates. Output: a funnel table and a chart-ready dataset.
25. Data dictionary: You are a data steward. Task: generate a data dictionary for [dataset] listing field names, types, allowed values, and examples. Output: a dictionary sheet and a reference index.
26. Currency normalization: Role: financial data engineer. Task: detect currency fields and normalize to USD using a given rate_sheet. Output: normalized columns and a rate audit log.
27. Servicing performance: You are a servicing analyst. Task: analyze monthly servicing_cost in [dataset] and identify months with spikes. Output: a time series and anomaly flags.
28. Customer lifetime value: Role: marketing analyst. Task: estimate CLV per borrower based on loan_amount, term, rate, and expected churn. Output: CLV column and cohort-level summary.
29. Red flags by broker: You are a broker risk auditor. Task: flag brokers with suspicious patterns in [dataset], such as unusually high inflow of new loans. Output: a broker_flags sheet and a top-risk broker list.
30. Interest rate comparison: Role: scenario analyst. Task: compare rates offered by different lenders for similar profiles in [dataset], and output a recommended rate band and rationale.
31. Loan aging analysis: You are a portfolio analyst. Task: analyze aging of loans in [dataset] by months since origination and status. Output: aging_report and aging_by_status.
32. Documentation completeness: Role: compliance analyst. Task: score documentation completeness per loan and flag gaps. Output: a completeness_score column and a gaps report.
33. Scenario planning: You are a planning analyst. Task: create three scenario plans (base, optimistic, pessimistic) for portfolio performance in [dataset], with key drivers and outcomes. Output: scenario_table and charts.
34. Master pivot: Role: Excel power user. Task: build a master pivot table dashboard linking loan metrics across multiple dimensions in [dataset]. Output: a workbook-ready pivot layout and a narrative sheet.
35. Automated filtering: You are a data ops engineer. Task: create an automated filter set in Excel to segment loans by status, region, and loan_type, with one-click slicers. Output: a template sheet and instructions.
36. Data lineage map: Role: data governance. Task: map data lineage from source to final analytics for [dataset], including transformations. Output: a lineage sheet with steps and responsible owners.
37. Correlation matrix: You are a data scientist. Task: compute a correlation matrix for numeric fields in [dataset], highlight strong correlations above 0.7. Output: matrix and a note on potential red flags.
38. Loan-to-income analysis: Role: credit risk analyst. Task: compute ratios of loan_amount to borrower_income and categorize risk by bins. Output: a ratio column and a distribution chart.
39. Top borrowers: You are a portfolio manager. Task: identify top 10 borrowers by outstanding_balance, weighted by risk score. Output: a ranked list with key metrics.
40. Delinquency hotspots: Role: risk manager. Task: map delinquency rates by region and property_type, creating a regional risk heatmap. Output: heatmap data and a regional summary.
41. Deterministic loan aging: You are a data analyst. Task: compute deterministic aging buckets for loans based on origination_date and current_date, ignoring holidays. Output: aging_bucket column and counts.
42. ARM adjustment tracker: Role: product operations. Task: track adjustable-rate mortgage ARM rate adjustments over time per loan. Output: a tracker sheet and a change log.
43. Pre-qualification checks: You are a loan operations specialist. Task: verify pre-qualification criteria in [dataset] and flag loans that fail criteria. Output: a pass/fail column and a remediation list.
44. Amortization schedule: Role: financial modeler. Task: generate full amortization schedules for each loan and a summary table showing total interest paid. Output: amortization_sheet and a summary chart.
45. Data enrichment: You are a data integrator. Task: enrich [dataset] with external credit bureau scores and property valuations from [source]. Output: enriched_dataset and a mismatch report.
46. Fraud indicators: Role: security analyst. Task: flag loans with patterns of potential fraud in [dataset], such as sudden spikes in applications from new brokers. Output: a fraud_flags sheet and recommended checks.
47. Regulatory flag report: You are a compliance auditor. Task: compile a regulatory_flags worksheet for all loans, noting jurisdictions and required disclosures. Output: a compliance_summary by jurisdiction.
48. MarchAnnualization: Role: financial planner. Task: produce annualized metrics for the last 12 months in [dataset], such as average payment, default_rate, and ROA. Output: a compact annualized_metrics sheet.
49. Sensitivity by term: You are a risk analyst. Task: compare how 15-year vs 30-year terms affect monthly payments in [dataset], with a side-by-side table and chart. Output: a term_sensitivity table.
50. Regional performance: Role: market analyst. Task: summarize loan performance by region in [dataset], including regional default_rate and average loan_amount. Output: regional_report sheet and map-ready data.
51. Portfolio diversification: You are a portfolio strategist. Task: compute diversification metrics by property_type and region in [dataset], and propose rebalancing actions. Output: a diversification_table and a suggested_actions sheet.
52. Narrative insights: Role: analyst. Task: generate a concise executive narrative from the key metrics in [dataset], highlighting risks and opportunities. Output: narrative_sheet with bullet points.
53. Data sampling plan: You are a QA analyst. Task: define a sampling plan to validate the dataset integrity in quarterly extracts of [dataset]. Output: sampling_rules and a sample_report.
54. Loan age distribution: Role: data scientist. Task: create age_distribution buckets for loans and plot a histogram. Output: age_bins column and a histogram-ready dataset.
55. Monthly trend deck: You are a presenter. Task: assemble a monthly trend deck from [dataset], including key metrics and a short narrative. Output: slide_ready_dataset and a text_summary.
56. Risk scoring rubric: Role: risk modeler. Task: define a simple risk scoring rubric with weights for credit_score, loan_amount, LTV, and DTI. Output: a rubric table and per-loan risk_score column.
57. Data validation rules: You are a governance lead. Task: write validation rules for essential fields in [dataset] and attach error messages. Output: rules_sheet and a validation_log.
58. Portfolio churn: Role: operations analyst. Task: measure borrower churn by status_change events in [dataset]. Output: churn_table and retention_chart.
59. Cost per loan: You are a finance analyst. Task: compute total origination and servicing costs per loan and per cohort. Output: cost_per_loan and cohort_costs sheets.
60. Fraud risk scoring: Role: security analyst. Task: assign a fraud_risk_score per loan using criteria like new_broker, rapid_keys, and unusual_geography. Output: risk sheet and top_fraud_loans.
61. Credit utilization: You are a credit risk analyst. Task: calculate credit utilization ratio per borrower from available credit lines and reported balances. Output: utilization column and distribution.
62. Early payoff analysis: Role: product analyst. Task: identify loans likely to be paid off early and estimate impact on revenue. Output: early_payoff_prob and revenue_impact sheets.
63. Cohort retention: You are a marketing data analyst. Task: build a 6-month retention cohort for borrowers and compute retention rate by cohort. Output: cohort_retention sheet and a chart.
64. Data fusion check: Role: data integrator. Task: verify alignment when merging [dataset] with [external_data] on borrower_id, checking for key mismatches. Output: fusion_check sheet and a reconciliation log.
65. Recency analysis: You are a CRM analyst. Task: compute recency of borrower activity and segment by activity window. Output: recency_score column and a distribution chart.
66. Hold vs sell analysis: Role: portfolio manager. Task: simulate hold vs sell decisions for at-risk loans in [dataset], with estimated cash flows. Output: hold_vs_sell_table and a decision_rationale sheet.
67. Payment timing: You are a cash flow analyst. Task: analyze payment timing patterns (on-time, late) in [dataset] and produce a timing_heatmap. Output: timing_data and a heatmap chart.
68. Documentation health: Role: compliance navigator. Task: audit loan doc health across filings and produce a health_score per loan. Output: health_score sheet and remediation_actions.
69. Slicer-ready dashboard: You are a dashboard engineer. Task: create an Excel model with slicers for region, status, and loan_type to explore [dataset]. Output: a dashboard_template workbook.
70. Benchmarking: Role: performance analyst. Task: benchmark metrics in [dataset] against a reference peer group and highlight gaps. Output: benchmarking_sheet and a variance chart.
71. Forecast dashboard: You are a financial analyst. Task: build a 12-month forecast dashboard for portfolio metrics in [dataset] with scenario controls. Output: forecast_dashboard_sheet.
72. Dynamic ranges: Role: Excel expert. Task: define dynamic named ranges for key fields in [dataset] to support robust charts and pivot tables. Output: named_range_table and usage notes.
73. Risk tipping points: You are a risk analyst. Task: identify tipping points where small changes in rate or term cause large shifts in monthly payment. Output: tipping_points sheet and explanation.
74. Customer segmentation map: Role: marketing analyst. Task: map segments to geographic regions with color-coded markers. Output: segment_map sheet and legend.
75. Loan aging by segment: You are a portfolio analyst. Task: analyze loan aging within each customer_segment and report the oldest loans by segment. Output: aging_by_segment sheet.
76. Cross-sell potential: Role: marketing operations. Task: estimate cross-sell potential per borrower based on loan_type and term. Output: cross_sell_potential sheet and recommended offers.
77. Regulatory disclosures: You are a compliance officer. Task: generate required disclosures per loan in [dataset] and a summary by jurisdiction. Output: disclosures_sheet.
78. Data stewardship notes: Role: data steward. Task: document stewardship actions for [dataset], including owners, retention, and access controls. Output: stewardship_log sheet.
79. Loan age heatmap: You are a data viz specialist. Task: create a heatmap of loan age across regions. Output: heatmap_ready_data and a legend.
80. Price-to-rent analysis: Role: market analyst. Task: compare price-to-rent ratios across properties in [dataset] and flag mismatches. Output: pr_to_rent_sheet.
81. Borrower risk profile: You are a risk modeller. Task: generate a risk profile per borrower combining credit_score, DTI, and utilization. Output: risk_profile sheet and a summary narrative.
82. Automation checklist: Role: operations engineer. Task: create a checklist to automate routine Excel data tasks for mortgage analytics in [dataset]. Output: automation_checklist and script plan.
83. Data leakage guard: You are a governance analyst. Task: identify potential data leakage risks in [dataset] when exporting to reports. Output: leakage_risks sheet and mitigations.
84. Term optimization: Role: product analyst. Task: analyze effects of term changes on total interest for loans in [dataset] and propose optimal term bands. Output: term_optimization sheet.
85. Delinquency triggers: You are a risk analyst. Task: define trigger events that predict delinquency within 6 months and assign a trigger_score per loan. Output: trigger_sheet and a monitoring plan.
86. KYC verification check: Role: compliance officer. Task: verify KYC status for borrowers in [dataset] and flag incomplete records. Output: kyc_status sheet and remediation list.
87. Ethical AI prompts: You are a prompt engineer. Task: craft prompts that ensure Gemini outputs are unbiased and auditable for mortgage analytics in [dataset]. Output: ethical_prompts sheet and guidelines.
88. Export-ready reports: Role: reporting specialist. Task: prepare a one-click export template for monthly mortgage analytics in [dataset], including charts and tables. Output: export_template workbook.
89. Audit trail creation: You are a data auditor. Task: create an immutable audit trail for changes made to [dataset], with timestamps and user IDs. Output: audit_trail_sheet and a changelog.
90. Stress test scenarios: Role: risk manager. Task: define three stress test scenarios for mortgage portfolio performance in [dataset], with impact metrics. Output: stress_test_sheet and a results narrative.
91. Data refresh plan: You are a data ops lead. Task: design a quarterly data refresh plan for [dataset], including sources, owners, and validation steps. Output: refresh_plan_sheet.
92. Model explainability: Role: data scientist. Task: provide a plain-language explanation of the risk scoring and forecasts for loans in [dataset]. Output: explainability_sheet and a FAQ.
93. Audit-ready metrics: You are a governance analyst. Task: prepare a metrics pack suitable for external audits with calculations and assumptions for [dataset]. Output: audit_metrics_sheet.
94. Recipient-friendly summaries: Role: client-facing analyst. Task: generate concise, executive-friendly summaries of portfolio performance from [dataset] for reports. Output: summary_paragraphs sheet.
95. Cross-border data: You are a data compliance officer. Task: verify cross-border data usage rights for [dataset] and list relevant controls. Output: cross_border_compliance sheet.
96. Dashboard storytelling: Role: dashboard designer. Task: craft a narrative around a dashboard for mortgage analytics in [dataset], with a recommended slide order. Output: storyboard_sheet.
97. Quality anomaly alerts: You are a data ops engineer. Task: set up anomaly alerts for numeric fields in [dataset] using simple z-score thresholds. Output: alerts_sheet and notification plan.
98. Export format presets: Role: reporting engineer. Task: create presets for CSV, XLSX, and PDF exports of [dataset] dashboards. Output: presets_sheet and usage notes.
99. Pivot template pack: You are an Excel expert. Task: deliver a pack of pivot table templates tailored to mortgage metrics in [dataset], with sample slicers and layouts. Output: pivot_template_pack sheet.
100. Final QA checklist: Role: QA lead. Task: run a final QA on the workbook containing [dataset], ensuring all prompts produce accurate outputs and no errors. Output: qa_checklist and sign-off notes.

Best Practices

• Keep prompts specific to mortgage data fields to avoid ambiguity.
• Use clear output formats like Excel tables, pivot layouts, and charts to prepare dashboard-ready results.
• Test prompts on a small sample before applying to full datasets to catch edge cases.
• Include validation steps and quality checks in every prompt to ensure reliability.
• Restrict outputs to verifiable calculations and avoid speculative or ungrounded results.

Common Mistakes to Avoid

• Using vague terms like analyze instead of specify actions, leading to broad results.
• Forgetting to define placeholders and data formats, causing mismatches.
• Overlooking data privacy and compliance concerns in dataset handling.
• Not including validation or quality checks, leading to undetected errors.
• Ignoring versioning, which makes audits and reproducibility harder.

FAQ

What is the purpose of these Gemini prompts for mortgage data in Excel?

They provide actionable, repeatable instructions to perform precise data tasks in Excel, enabling mortgage brokers to analyze portfolios, assess risk, forecast cash flows, and generate reports.

How should I replace placeholders?

Replace each placeholder like [dataset], [region], or [loan_type] with your actual column names and data sources before running the prompt.

Can I modify prompts for my specific data fields?

Yes. You can adjust field names, risk factors, and output formats to match your data schema while keeping the task structure.

Do prompts include outputs for dashboards?

Many prompts return table data and chart-ready formats suitable for dashboards, enabling quick visualization and storytelling.

How do I ensure compliance when using these prompts?

Incorporate your organization’s data governance and regulatory checks into the prompts, and document data sources, transformations, and access controls.

Are these prompts suitable for beginners?

Yes, they are written with explicit roles, tasks, placeholders, and outputs, making them usable by prompt novices as well as experienced analysts.