Mortgage Sales Tool - Dynamic Excel Sheet

Proposal and Proof of Work Requirements for Dynamic Excel Sheet Development We are seeking an experienced Excel developer to create a dynamic, visually appealing, and proprietary tool for our loan officers. This tool will enable our team to present purchase and refinance scenarios to clients, offering detailed calculations, comparisons, and insights. It should reflect Cohen Mortgage branding, include precise mortgage insurance premium calculations for different loan programs, and be protected to maintain its proprietary nature. Project Scope and Features: 1. User Data Entry Inputs: - Property address - Purchase price - Down payment percentage - Loan amount (calculated automatically) - Interest rate (fixed or adjustable options) - Loan term (e.g., 15, 20, 30 years) - Loan Program (Conventional, FHA, VA, USDA, Jumbo) - Annual property taxes - Annual homeowner’s insurance premiums - Annual homeowner's association dues; if applicable - Mortgage insurance premiums (calculated based on loan program) 2. Loan Program Customization: - FHA Loans: UFMIP (1.75%), MIP based on LTV and down payment. - VA Loans: Funding fees based on down payment; exemptions for eligible borrowers. - USDA Loans: Upfront and annual guarantee fees. - Conventional Loans: User-defined PMI rates, with auto-termination at 78% LTV. - Jumbo Loans: No MI but accommodate higher loan limits. 3. Key Functionalities: - Monthly payment breakdown with charts and visuals. - Closing costs estimator. - Amortization schedules with early payoff options. - Future refinance scenarios. - Rent vs. own analysis. - Side-by-side comparison of 3 scenarios. 4. Branding: - Include Cohen Mortgage logos, colors, and fonts. - Professional header and footer with contact info and disclaimers. - Consistent visual style across outputs. 5. Protection and Security: - Lock all formulas, calculations, and branding elements. - Password-protect the sheet to restrict editing to user input fields. - Include a disclaimer asserting proprietary ownership. Proof of Concept Requirement: To ensure alignment with our expectations for the consumer-facing portion of the tool, we request a mockup or proof-of-concept report. This will help us evaluate the visual appeal and layout before committing to the full project. 1. Visual Appeal: - Clean, professional layout incorporating Cohen Mortgage branding. - Charts, graphs, or tables summarizing key loan information. - Space for payment breakdowns, closing costs, and scenario comparisons. 2. Key Elements to Include in the Mockup: - A breakdown of monthly payments (PITIA) in a clear format. - Estimated amounts due at closing, including down payment and program fees. - Comparison of scenarios (e.g., loan options with different terms or rates). - Optional elements: amortization summaries, rent vs. own analysis, refinance benefits. 3. Format: - Provide the mockup as a PDF, screenshot, or Excel file demonstrating only the consumer-facing report portion. - The mockup does not need to be functional at this stage. 4. Evaluation Criteria: - Alignment with Cohen Mortgage branding. (See Instagram page @cohenmortgage & visit website [login to view URL]; logo and branding guidelines attached) - Clarity and professionalism of the layout. - Ability to present complex information in a simple, easy-to-understand format.