Professional Price and SAC Loan Simulator in Excel
ESCOPO
This prompt turns the real parameters of a credit operation into a complete simulator project in Microsoft Excel. It guides the AI to deliver the workbook architecture, input fields, auditable formulas in Brazilian Portuguese, amortization schedules using the Price and SAC systems, financial comparisons, and executive dashboards.
Ideal for financial analysts, consultants, bank managers, brokers, financial planners, and professionals who need to evaluate loans, mortgage financing, vehicle financing, or business credit. The result is detailed enough to be implemented directly in Excel, including optional VBA suggestions and a slide structure for presentation.
Conteudo
Prompt principal
Act as a senior expert in financial modeling, bank credit, financial mathematics, and Microsoft Excel automation in Brazilian Portuguese. Based on the real data I will provide, design a professional loan and financing simulator that compares the Price and SAC amortization systems in an auditable way. Your goal is to deliver instructions that can be applied immediately in Excel desktop in PT-BR, with formulas, cell references, visual structure, controls, and validations. Before building the solution, use the data below. If any item is missing, assume a conservative premise, explicitly flag it as “PREMISSA A VALIDAR” and keep the solution parameterizable: - Loan purpose and customer profile: [colar] - Financed amount or asset value, down payment, and net amount released: [colar] - Term, payment frequency, and first installment date: [colar] - Stated interest rate, capitalization basis (monthly/annual/daily), and type (fixed/post-fixed): [colar] - APR, IOF, fees, insurance, ancillary costs, and respective charging methods: [colar] - Correction index, if any (IPCA, TR, CDI etc.), and available projection or history: [colar] - System or systems to compare: [Price, SAC or both] - Excel version and preference for formulas, Power Query, PivotTables, or VBA: [colar] - Currency, rounding convention, and internal business rules: [colar] - Desired scenarios (rates, terms, down payment, extra amortization, grace period, or portability): [colar] Follow this framework strictly: 1. Perform a critical review of the data and present a short section called “Premissas e alertas”. Differentiate between nominal, effective, and proportional rates. Check the consistency among term, frequency, and rate. Explain, objectively, whether the rate needs to be converted to the installment frequency. Do not invent regulatory or tax rules: when necessary, indicate that validation must be done with the financial institution or legal department. 2. Propose the file architecture with at least these tabs: “Instruções”, “Parâmetros”, “Price”, “SAC”, “Comparativo”, “Cenários” and “Dashboard”. For each tab, provide the objective, block placement, titles, input cells, calculated cells, and recommended visual style. Define a clear convention: input cells in light blue, formulas in gray, key results in green, and alerts in yellow/red. 3. Detail the “Parâmetros” tab in table format, containing: field, description, suggested cell, data type, example, data validation, and formula when applicable. Include financed amount, down payment, term, periodic rate, start date, fees, IOF, insurance, grace period, indexer, and extra amortizations. Use absolute references when necessary and named ranges only if they bring real readability gains. 4. Build the Price and SAC amortization tables line by line. For both, provide columns, formulas in PT-BR Excel syntax, and an example of a cell reference. Include: installment number, due date, opening balance, payment, interest, amortization, additional charges, total amount paid, closing balance, and extra amortization. In Price, calculate the base payment with PMT and explain the function’s financial signs. In SAC, calculate constant amortization and declining payment. Handle the last installment correctly, avoiding residual balance due to rounding. Use IFERROR and conditional functions when appropriate. 5. Create the “Comparativo” tab with consolidated indicators: initial payment, final payment, lowest and highest payment, total interest, total charges, total paid, term, balance after selected milestones, and absolute/percentage difference between Price and SAC. Include formulas for each indicator and recommend at least three charts: installment progression, interest versus amortization composition, and outstanding balance evolution. 6. Structure a scenario analysis. Create a sensitivity matrix for rate versus term, another for down payment versus total amount paid, and a section for extra amortization in defined months. Explain how to build an Excel Data Table when compatible; if not feasible because it depends on multiple inputs, provide an alternative with helper columns and formulas. 7. Suggest VBA only if it adds concrete value. If you propose a macro, provide complete, commented, and safe code, with installation instructions, an execution button, and error handling. Prioritize solutions without macros and never rely on VBA for essential calculations. 8. If the material is intended for an executive presentation, also provide a 5-slide PowerPoint outline: objective, premises, Price versus SAC comparison, scenarios, and recommendation. Indicate title, key message, suggested visual, and KPI for each slide. Present the final answer exactly in this order: (A) premises and alerts; (B) tab map; (C) parameter table; (D) Price formulas and tables; (E) SAC formulas and tables; (F) comparison, charts, and scenarios; (G) optional VBA; (H) slide outline; (I) audit checklist. Do not use generic formulas without cell addresses. Use the “;” separator in PT-BR Excel formulas, function names in Portuguese when available, and highlight any function whose availability may vary depending on the Excel version. Ensure all calculations are traceable, consistent, and suitable for financial review.
