Formula columns in the Database

A formula column calculates its value from other columns, on every record, and keeps itself up to date. Instead of exporting to a spreadsheet to work out price per unit or debt service, you add the column once and it's there every time you open the Database.

Adding a formula column

Open Columns in the top bar, then click New formula column at the bottom of the panel.

You'll see four things:

Column name — what appears in the header, like "Debt service".
Shows as — number, currency, percent, text, date, or yes/no. This only affects how the result is displayed.
Formula — the calculation itself.
Preview — what your formula produces on five real properties, updating as you type.

The preview is the part worth watching. A formula that saves cleanly can still produce nothing useful, and seeing the actual number on a property you recognise is the fastest way to know it's right.

Starting from a standard formula

Click Start from a standard formula to see the ones we ship — price per unit, DSCR, debt yield, effective tax rate, GRM, and about a dozen more.

Each one gives you two choices:

Add creates the column exactly as written.
Customise loads it into the editor so you can change it first — swapping the 6.5% interest rate in the debt service formula for the rate you're actually quoting, for example.

Once added, a standard formula is an ordinary column. You can rename it, edit the formula, or delete it, and nothing links it back to the original.

Writing your own

Reference a column by putting its key in square brackets. Click any field in the Fields list to insert it at your cursor:

[market_value] / [units]

The Functions list works the same way, grouped into Finance, Math, Logic, Dates and Text. Search by what you want rather than the name — typing "payment" finds PMT.

Finance functions

These follow the same rules as Excel, including the sign convention: a payment is money going out, so PMT returns a negative number. Write a minus in front when you want debt service as a positive figure.

ROUND(-PMT(0.065 / 12, 360, [market_value] * 0.7) * 12, 2)

That's a 30-year loan at 6.5% on 70% leverage, expressed as an annual figure.

PMT, IPMT, PPMT, FV, PV, NPV and IRR are all available and all match what your spreadsheet would give you for the same inputs.

Blank stays blank

This is the one rule that differs from a spreadsheet, and it's deliberate.

If a property is missing one of the values a formula needs, the cell is empty — not zero. A property with no market value on file shows nothing in a price-per-unit column, rather than showing $0.

A zero looks like an answer. If a third of your portfolio showed $0 per unit, an average across the column would be badly wrong and nothing on screen would tell you why. An empty cell says "we don't have this" plainly.

When you do want a missing value treated as zero, say so:

BLANK_TO_ZERO([other_income]) + [rental_income]

ISBLANK() and IF() give you finer control:

IF(ISBLANK([units]), "Not recorded", [market_value] / [units])

Setting a formula on one property

Sometimes a column's formula is right for the portfolio but wrong for one building.

Unlock the table for editing, click the cell, and type a formula starting with =:

= [units] * 100000

That property now calculates its own value; every other property keeps using the column's formula. This is useful when one asset needs a different assumption — a different price per door, a rate you've actually been quoted — without creating a second column for it.

See Editing records in the table for how to unlock the table.

Reading the markers

A small ƒ beside a value means the column's formula produced it.

A ± means something else did — usually a number typed directly into that cell, which takes precedence over the calculation. The formula is still there and still applies to every other property.

When a formula won't save

Three things are checked before a formula is stored, and each tells you what to fix:

It doesn't parse. A bracket left open, or an operator with nothing after it.
A field doesn't exist. Usually a typo — [markt_value] instead of [market_value]. This one is worth catching at save time, because an unknown field simply produces blank cells, and a column of blanks looks exactly like missing data.
It refers back to itself. Directly, or through another formula that refers back to the first.

Keeping up to date

A formula recalculates for a property the moment anything on that property changes.

Editing the formula itself affects every record, so that runs in the background. The numbers refresh within a minute or so — you don't have to do anything.

Tips

Percent columns show the number as written. 6.72 displays as 6.72%, so write your formula to produce the figure you want to see.
Formula columns can't be sorted yet. Sort on an underlying column instead.
A formula can reference another formula column, so you can build up a calculation in readable steps rather than writing one long expression.