Core Excel Skills for Financial Modeling

Microsoft Excel is a spreadsheet program: a grid of rectangular boxes called cells, arranged into columns (labeled A, B, C…) and rows (labeled 1, 2, 3…). Every cell has an address made from its column letter and row number — cell B4 sits where column B meets row 4. A saved Excel file is called a workbook, and a workbook is made up of one or more pages called worksheets (the tabs along the bottom of the screen).

Here is the entire reason Excel matters for commercial real estate (CRE): instead of typing a number, you type a formula — an instruction, always starting with an equals sign (=), that tells Excel to calculate a value from other cells. Type `=2+2` into a cell and Excel shows `4`. But type `=B2+B3` and Excel shows whatever B2 plus B3 currently equals — and if you later change the number in B2, that cell updates itself instantly, along with every other cell that depends on it. A CRE financial model is nothing more than a spreadsheet built this way: a handful of cells hold your assumptions (this year's rent, the vacancy rate, the interest rate on the loan), and every other number — Net Operating Income, the loan payment, the investor's return — is a formula that flows from those assumptions. Change one assumption, and the entire model recalculates correctly and instantly, with zero risk of forgetting to update a downstream number by hand. That property — one change producing many correct updates — is what a calculator can never do and Excel does by design.

When a formula points to another cell, like `=B2+B3`, that pointer is called a cell reference. By default, a reference is relative, meaning it describes a *position* relative to the formula's own cell, not a fixed address. This matters most when you copy a formula from one cell to another (drag the small square at the corner of a selected cell — called the fill handle — down a column, for example). A relative reference shifts by the same distance you copied it. If cell C2 contains `=B2*1.03` and you copy it down to C3, Excel doesn't paste `=B2*1.03` again — it automatically shifts to `=B3*1.03`, because the reference was relative to C2's position.

Sometimes you don't want that shift. If a formula needs to always point at one specific cell — a single shared assumption like a growth rate that every row should use — you lock the reference by adding dollar signs, turning it into an absolute reference: `$B$1` instead of `B1`. The dollar sign before the column letter locks the column; the dollar sign before the row number locks the row. Copy a formula containing `$B$1` anywhere you like, and that piece of the formula never moves. (You can also lock only one half — `$B1` locks just the column, `B$1` locks just the row — these are called mixed references; a beginner mainly needs relative and fully absolute.)

Worked example. Say cell B1 holds a single assumption, "Annual rent growth," set to 3% (entered as 0.03). Below it, column B lists the Year 1 monthly rent for three units: B2 = $1,000 (Unit 101), B3 = $1,100 (Unit 102), B4 = $1,250 (Unit 103). In cell C2 you type the formula `=B2*(1+$B$1)` and copy it down through C3 and C4. Because $B$1 is absolute, every copy of the formula still multiplies by the same 3% in B1, while B2 correctly shifts to B3, then B4. The results: Unit 101's Year 2 rent is $1,000 × (1 + 0.03) = $1,000 × 1.03 = $1,030. Unit 102's Year 2 rent is $1,100 × 1.03 = $1,133. Unit 103's Year 2 rent is $1,250 × 1.03 = $1,250 + ($1,250 × 0.03) = $1,250 + $37.50 = $1,287.50. One formula, typed once, produced three different correct answers — because the growth rate stayed locked while the rent figure moved.

Excel evaluates a formula using the same order of operations taught in algebra: parentheses first, then exponents, then multiplication and division, then addition and subtraction (often remembered as PEMDAS). So `=2+3*4` returns 14, not 20 — Excel multiplies 3×4 first, then adds 2. If you actually want (2+3)×4, you must add the parentheses yourself: `=(2+3)*4` returns 20.

For adding up a column or row of numbers, Excel has a dedicated function: SUM. A function is a pre-built formula that takes one or more arguments (the values you feed it, inside parentheses) and returns a result. `=SUM(B2:B5)` adds every number in the range (a block of cells, here B2 through B5) and is equivalent to typing `=B2+B3+B4+B5`, but with one major advantage: if you later insert a new row inside that range, SUM automatically expands to include it, while a formula built from individual plus signs does not.

Worked example: building Net Operating Income (NOI). NOI is the income a property earns after paying the costs of running it, but *before* the mortgage payment, income taxes, or depreciation — it is arguably the single most important number in CRE, because both a property's value and its ability to support a loan are built on top of it. Suppose a property's Year 1 figures are: Rental Income (B2) = $500,000; Other Income such as parking and laundry (B3) = $20,000; Vacancy Loss — rent lost to units sitting empty (B4) = –$25,000, entered as a negative number; Operating Expenses — property taxes, insurance, repairs, and management fees (B5) = –$180,000, also entered as negative. Typing `=SUM(B2:B5)` in B6 adds all four: $500,000 + $20,000 = $520,000; $520,000 + (–$25,000) = $495,000; $495,000 + (–$180,000) = $315,000. That $315,000 is the property's Year 1 NOI — and because it was built as a formula referencing four labeled input cells, changing the vacancy assumption alone (say, from –$25,000 to –$40,000) instantly recalculates the correct new NOI without touching anything else.

Not every useful function does arithmetic — some make decisions. The IF function tests a condition and returns one value if it's true and a different value if it's false: `=IF(logical_test, value_if_true, value_if_false)`. This is how a model flags problems automatically instead of making a human scan every row by eye. For example, lenders evaluate a loan partly using the Debt Service Coverage Ratio (DSCR) — a ratio showing how many times over a property's income covers its annual loan payment (NOI divided by the annual loan payment). Suppose a lender requires a minimum DSCR of 1.25, and a property's calculated DSCR sits in cell B10 at 1.18. The formula `=IF(B10>=1.25,"Passes","Fails")` checks whether B10 is greater than or equal to 1.25; since 1.18 is not, Excel returns the text "Fails" — instantly, and it re-checks every time the DSCR recalculates.

The other everyday task is retrieving a value from a table instead of retyping it. VLOOKUP ("vertical lookup") and its more flexible cousin INDEX/MATCH both search a lookup table for a key (say, a property type) and pull back an associated value (say, that property type's typical market cap rate) from another column in the same row — instead of manually retyping the same figure in ten different places in a model, risking a typo in one of them. Modern versions of Excel also offer XLOOKUP, a newer, simpler function that does the same job with fewer arguments, but VLOOKUP and INDEX/MATCH remain extremely common in CRE models built over the last two decades, so a beginner should recognize both.