Excel for mortgage loan officers on Mac: pipelines, PMT payments, and voice help

What loan officers keep in Excel on a Mac

Most loan officers use Excel as a side tool for work the loan system does not do well. A typical workbook holds the pipeline, a contact list, a document checklist, payment math, and a weekly update for realtor partners. One practitioner guide describes exactly this setup, with a column for each loan stage and one for each required document. That guide comes from a company that sells a mortgage CRM, so read it as one view, not a survey.

I could not find a survey that measures how much time loan officers spend in spreadsheets. If you want a real number for your office, time one week of copy and paste work. Your own figure will tell you more than any industry average.

• Pipeline: borrower, loan officer, stage, and stage date, one row per loan
• Document checklist: one column per required document
• Payment math: PMT for quick quotes, and an amortization schedule for comparisons
• Partner updates: a short weekly summary you paste into an email

Excel can hold all of this on a Mac, and the limits sit far above what one office needs. A worksheet holds 1,048,576 rows and 16,384 columns. A single cell holds 32,767 characters. Those limits come from Microsoft's own specifications page.

Build a mortgage pipeline tracker in Excel on Mac

Start with one flat table, not a set of linked sheets. Select your headers and the first row of loans, then press ⌘T. You can also choose Insert > Table. Excel turns the range into a table with filter buttons. New rows join the table when you type under the last row.

Name the table before you build anything else. Microsoft's October 2026 update says naming a table as you create it is now available to Excel for Mac users. Click inside the table, open Table Design, and type a name in the Table Name box. Call it Pipeline, not Table1. Then a formula like =COUNTIF(Pipeline[Stage],"Docs Out") keeps working when rows move.

Set up the columns before you enter data. Use a drop-down for Stage, so every row uses the same words. Select the Stage column, then go to Data > Data Validation, choose List, and type your stage names separated by commas. A single typo in a stage name breaks every count you build later.

Watch for two mistakes. Dates typed as text sort in the wrong order. If a date sits on the left side of its cell, it is probably text. Merged cells stop sorting and filtering, so never merge a cell inside the table. Menus also move between Excel builds, so check the path on your version.

Document checklists in Excel on Mac

A document checklist works best with one column per document and a fixed list of answers. Use Received, Requested, and N/A. Then count what is still missing with a formula, not by eye. Eyeballing a long list is how a file stalls for a week.

Suppose the document columns run from H to M. The formula =COUNTIF(H2:M2,"Received") counts the received items on row 2. Divide that by =COLUMNS(H2:M2) and format the result as a percent to get percent complete. If you mark items N/A, subtract them from the total first.

Conditional formatting makes gaps easy to see. Select the checklist, then go to Home > Conditional Formatting > Highlight Cells Rules > Equal To. Set the rule to highlight Requested. Open items show in color before a processor asks about them.

Excel does not send anything. It will not email a realtor or remind a borrower when a document is late. Practitioner guidance says manual entry is prone to errors, and that Excel cannot automate these tasks. The weekly update still leaves the sheet by hand.

PMT formula for mortgage payments on Mac

The PMT function returns the payment on a loan. Its syntax is =PMT(rate, nper, pv). The rate is the rate per period, nper is the number of payments, and pv is the loan amount. For a monthly mortgage, divide the annual rate by 12 and multiply the years by 12.

Here is a layout that works. Put the loan amount in B1, the annual rate in B2, and the term in years in B3. In B5, type =PMT(B2/12,B3*12,-B1). Excel returns the monthly principal and interest as a positive number. PMT returns payments as negative when the loan is positive, so the minus sign on B1 flips the result.

Format B2 as a percent before you type into it. If a rate goes into a General cell as a plain whole number, the formula treats it as a far larger annual rate. The payment then comes out far too high, and the error is easy to miss in a long list.

PMT gives principal and interest only. Taxes, insurance, and mortgage insurance are not in the formula. Add those in a separate column when a borrower asks for a total housing payment. Then compare your result with the Loan Estimate your lender issued.

Loan amortization schedule in Excel on Mac

An amortization schedule shows how each payment splits between interest and principal. Build one with six columns: payment number, beginning balance, payment, interest, principal, and ending balance. Each row starts from the balance on the row above it.

Suppose the beginning balance sits in C6 and the annual rate sits in B2. Then the interest in D6 is =C6*$B$2/12. The dollar signs lock the rate cell, so the formula copies down the column. Principal is the payment minus the interest. Ending balance is the beginning balance minus principal.

Round each payment to cents with ROUND, or the last row carries a stray fraction of a cent. For interest across a range of payments, CUMIPMT adds it up for you. That helps when a borrower asks how much interest falls in the first year.

Microsoft's mortgage calculator page lists five templates. They include a Loan amortization schedule and a Simple loan calculator and amortization table. The templates also compare extra repayments and shorter terms. The page sends you to Excel for the web, so open one in your Mac app before you build a client file around it.

Fix lookups and #N/A errors in borrower data

Lookups pull a rate, a fee, or a status from one sheet into another. If your Microsoft 365 version has XLOOKUP, it is the simplest choice. The shape is =XLOOKUP(lookup value, lookup array, return array, "Not found"). The fourth argument replaces #N/A with text you can filter on.

Suppose column A on the Pipeline table holds borrower names. The formula =XLOOKUP(A2,Rates!A:A,Rates!B:B,"Not found") finds the name in the Rates sheet and returns the matching rate from column B. Blank results point to the source list, not the formula.

Most #N/A errors come from text that looks the same and is not. A name with a trailing space, a loan number stored as text on one sheet and as a number on the other, or a copied name with a hidden character will all fail. Wrap the lookup value in TRIM, or paste values from the source to clean it.

Our step by step guide covers the classic VLOOKUP #N/A fixes for Mac. Read the VLOOKUP #N/A error fix steps if your file still uses that formula.

Pivot tables for pipeline reports on Mac

A pivot table is the fastest way to count loans by stage or total loan amounts by loan officer. Click any cell in the table, then go to Insert > PivotTable. Drag Stage to Rows and Loan Amount to Values. Excel groups the counts for you.

Keep the pivot on one table. Microsoft's help says Data Models are not supported on Excel for Mac. Its help also says that building a PivotTable from several tables in Oracle, Access, IBM DB2, or MySQL is not supported on Excel for Mac. A Windows report that joins a loan table to a rate table will not build on a Mac. Use a lookup column to bring the rate into the main table first.

After you add new rows, click the pivot and go to Data > Refresh All. A pivot that looks stale is usually a refresh issue, or a source range that stops above the new rows. The step by step pivot table guide covers the source range fix.

Copilot in Excel on Mac: license, calculation, and SharePoint limits

Copilot in Excel is available on Mac. Microsoft's FAQ says it runs across Windows, Mac, the web, iPad, iPhone, and Android. Edit, chat, and plan modes are available on Windows, Mac, and the web.

Three conditions catch people out. First, the license must qualify. Microsoft lists Microsoft 365 Personal or Family with an AI credits plan, Microsoft 365 Premium, a commercial Microsoft Copilot subscription, and eligible business or enterprise plans. Second, Copilot editing works only when Calculation Options are set to Automatic. Go to Formulas > Calculation Options > Automatic.

Third, if your organization requires check-out in SharePoint, Copilot in Excel will not work on Mac for files from that site. Excel for the web still opens those files. Ask your IT team which files live in a check-out library before you plan a workflow around Copilot.

Microsoft's October 2026 post says the clean-data skill is rolling out to general availability on all platforms. The skill recorder is for Insiders of Excel for Mac. Check which features your build offers before you rely on any of them.

Protect borrower data in a shared spreadsheet

A loan sheet holds Social Security numbers, bank balances, and income. Keep those out of the pipeline. Use a loan number instead of a name where you can. Store the sensitive file in the system your lender approves. A copy on your desktop is one more copy to lose.

Lazur listens and looks at the screen only while you hold the shortcut. Audio and the screenshot go to AI providers to answer, and they are not stored. Content is never used to train AI models. Still, anything open on screen when you hold ⌥⌘ is part of that request. Close files you do not need first.

Your lender's policy wins over any tip on this page. Ask your compliance team before you paste borrower data into any AI tool, including Copilot in Excel.

Common mistakes in Excel on Mac for loan officers

• Dates typed as text, so sorting by stage date puts the wrong months first
• Stage names spelled three ways, so counts quietly miss loans
• Totals typed by hand over formulas, which go stale after one edit
• Several copies named final, final_v3, and final_new, with no clear current file
• Rates typed without the percent format, which breaks every payment cell
• Hidden columns with live data inside, which show up later as missing numbers

Fix the file name habit first. Keep one master workbook. Save a dated copy each time you send an update to a partner. OneDrive and SharePoint version history cover the rest, so you do not need a new file for every change.

Why spreadsheet time matters to lender costs

Freddie Mac's 2024 Cost to Originate Study says loan origination costs have risen 35%, or $3,000, over three years. The same study says an average retail-only lender loses about $600 per loan. Those figures describe lender costs, not Excel time. Do not read them as a measure of spreadsheet work.

The study also says lenders using LPA digital capabilities at high rates produce loans that cost $1,500 less to make. Process tools change cost, and a spreadsheet is a process tool too. It helps most when it replaces re-keying. It helps least when it becomes a second copy of the loan system.

A weekly routine for the pipeline

Pick one morning each week for a pipeline review. Sort the Pipeline table by Rate lock expires. Then filter Days in stage above the limit you set. Work the top rows first. Those are the loans most likely to slip past a lock date.

Once a week, make the partner update. Copy the summary from the pivot or a small filtered view, paste it into the email, and check each name against the table. The copy step is where errors slip in. Compare the totals before you send.

Track how long the routine takes for two weeks. If the copy step eats most of the time, that is the first place to look for a faster approach. Repetitive work is where most of the minutes disappear, and it is the part worth shrinking.

Where Lazur helps inside Excel, and where it stops

Lazur is a voice assistant for the Mac that lives in the notch. Hold ⌥⌘ and ask about what is on screen. Lazur answers out loud. Ask where something is, and Lazur draws a circle and a label around it. Ask how to do something, and Lazur guides you one click at a time, waiting for each click.

Lazur does not click buttons, edit files, or type into your cells. It is not an add-in for Excel, and no app maker partners with it. The formulas and the entries stay yours. That suits a loan file, because you check every number yourself.

Hold fn to dictate. Dictation writes clean text at the cursor in any app, including a note cell or an email draft. Snippets and a personal dictionary handle phrases you repeat, like your standard rate lock wording.

Start with one table this week

Build the Pipeline table, name it, add the stage drop-down, and test one COUNTIF formula. That takes less time than reading about it. If you get stuck on the naming step, hold ⌥⌘ and ask where Table Design is. Lazur can point at it.

Lazur runs on Apple silicon and Intel Macs. Windows is planned, but it is not available. There is a free trial with no credit card. Check the pricing page for current plan details, and download Lazur when you are ready.