Use Excel Copilot to Build and Verify Income Calculation Worksheets

What This Does

Excel Copilot helps you build income calculation worksheets, write formulas using plain English, and check your existing calculations for errors — reducing the risk of income miscalculations that can lead to loan repurchases.

Before You Start

• Microsoft 365 Business Standard or higher with Copilot enabled
• Excel desktop app or Excel on the web
• A loan file with income documentation to analyze (W-2, Schedule C, K-1, etc.)

Steps

1. Open Excel and your income data

1. Open Excel and either start a new blank workbook or open your existing income calculation spreadsheet
2. Enter your raw data in columns:

   • Column A: Income category (W-2 wages, self-employment income, rental income, etc.)
   • Column B: Year 1 amount
   • Column C: Year 2 amount
3. Click the Copilot button in the Home ribbon (it looks like the Copilot icon)

What you should see: A Copilot panel opens on the right side of Excel.

2. Ask Copilot to build a calculation formula

1. In the Copilot panel, describe what formula you need in plain language:

   • "Calculate the 24-month average of Column B and Column C and put the result in D2"
   • "Calculate qualifying income as the lower of the two-year average or the most recent year amount"
   • "Add a formula that applies a 25% reduction to rental income if this is a single-family investment property"
2. Copilot writes the formula and explains what it does
3. Click Insert to add it to your spreadsheet

What you should see: The formula appears in the correct cell, and Copilot explains the logic in plain language.

3. Verify an existing income calculation

1. If you already have calculations built, describe what you want Copilot to check:

   • "Review my income calculation in rows 2-15 and tell me if there are any formula errors"
   • "Check if my Schedule C income calculation is using the correct FNMA method — add-backs should be depreciation, depletion, and non-recurring losses"
2. Copilot will analyze your spreadsheet and flag issues

What you should see: A list of identified issues or a confirmation that your formulas are consistent, with explanations.

4. Create a new income calculator from scratch

1. Start a blank spreadsheet
2. Tell Copilot: "Create a self-employed income calculator for a sole proprietor using Schedule C income. Include rows for gross income, business expenses, depreciation add-back, depletion add-back, and calculate the 24-month average qualifying income."
3. Copilot builds the full worksheet structure with headers, formulas, and even color-coding

What you should see: A complete income calculation template, ready for you to enter data.

Troubleshooting: If Copilot can't find specific guideline formulas (e.g., "FNMA method for S-corp income"), describe the calculation method yourself ("add W-2 wages + K-1 business income, then divide by 24 for monthly qualifying income") — Copilot will build the formula without needing to know the guideline source.

Real Example

Scenario: You have a self-employed borrower with two years of Schedule C returns. You need to calculate qualifying income including depreciation and mileage add-backs per FNMA guidelines.

What you type in Copilot: "Create a Schedule C income worksheet. Rows: (1) Net profit from Schedule C line 31, (2) add Depreciation from 4562, (3) add Business use of home if any, (4) subtract non-recurring income, (5) sum = adjusted income Year 1 and Year 2, (6) two-year average monthly qualifying income. I'll enter the actual dollar amounts."

What you get: A clean, organized calculation worksheet with formulas already built — you just fill in the numbers from the tax return.

Time saved: 20–30 minutes of manual spreadsheet building → 5 minutes.

Tips

• Save your Copilot-built income calculators as templates so you don't rebuild them each time — one session creates a permanent tool
• After Copilot generates a formula, always verify the result against a manual spot-check — a $1,000/month error compounds to $360K+ over a 30-year loan
• Ask Copilot to "add input validation so cells only accept numbers" — this prevents data entry errors from corrupting calculations

Tool interfaces change — Excel Copilot requires M365 Copilot license from your employer. Features are updated frequently.