Готовые рецепты формул

В каждом рецепте есть задача, нужные поля, формула и подводные камни. Если поля у вас называются иначе, подставьте свои названия в квадратных скобках.

Рецепты 1–11 описывают поля-формулы в таблице, которые считают значение в каждой строке. Рецепты 12–16 описывают расчёт значения виджета на дашборде.

Поля-формулы в таблице

1. Сумма со знаком (доход плюс, расход минус)

Поля: «Тип» (Список: Доход / Расход), «Сумма» (Деньги).

if([Тип] == "Доход", [Сумма], -[Сумма])

Сумма этой колонки в виджете даёт чистый поток денег. Если «Тип» пуст, строка уйдёт в расход. Чтобы такая строка давала 0, проверьте оба значения: if([Тип] == "Доход", [Сумма], if([Тип] == "Расход", -[Сумма], 0)).

2. Среднегодовая доходность (CAGR)

Поля: «Стоимость на старте», «Стоимость сейчас» (Деньги), «Лет владения» (Число).

cagr([Стоимость на старте], [Стоимость сейчас], [Лет владения]) * 100

Результат в процентах. Пусто, если стартовая стоимость или срок ≤ 0.

3. CAGR по дате покупки

Поля: «Дата покупки» (Дата), «Цена покупки», «Стоимость сейчас» (Деньги).

cagr([Цена покупки], [Стоимость сейчас], max(1, date_diff([Дата покупки], today(), 'years'))) * 100

date_diff считает целые годы, поэтому для покупок моложе года ставим минимум 1. Иначе будет пусто.

4. Налог (УСН 6 % / НДФЛ 13 %)

Поля: «Сумма» (Деньги), «Режим» (Список: УСН 6%, НДФЛ 13%).

if([Режим] == "УСН 6%", [Сумма] * 0.06,
  if([Режим] == "НДФЛ 13%", [Сумма] * 0.13, 0))

5. Ежемесячный платёж по ипотеке

Поля: «Сумма кредита» (Деньги), «Ставка» (Процент, например 7), «Срок в годах» (Целое).

pmt([Ставка] / 100 / 12, [Срок в годах] * 12, [Сумма кредита])

Поле «Процент» хранит 7, а не 0,07, поэтому делим на 100, а потом на 12 месяцев. При нулевой ставке получатся равные доли суммы кредита.

6. Cap rate с учётом простоя

Поля: «Аренда в месяц», «Цена покупки», «Расходы за год» (Деньги), «Заполняемость» (Процент, например 85).

cap_rate([Аренда в месяц] * 12 * [Заполняемость] / 100 - [Расходы за год], [Цена покупки]) * 100

Годовую аренду уменьшаем на долю простоя, вычитаем расходы, делим на цену.

7. Чистая доходность аренды с налогом

Поля: «Аренда в месяц», «Цена покупки», «Расходы за год» (Деньги), «Налог» (Процент, например 13).

net_yield([Аренда в месяц] * 12 * (1 - [Налог] / 100) - [Расходы за год], [Цена покупки]) * 100

8. NPV и IRR проекта

Одна строка описывает один проект, потоки по годам лежат в полях «Год 0» … «Год 5».

npv(0.10, [Год 0], [Год 1], [Год 2], [Год 3], [Год 4], [Год 5])

irr([Год 0], [Год 1], [Год 2], [Год 3], [Год 4], [Год 5]) * 100

«Год 0» обычно отрицательный, это вложение. Без него irr вернёт пусто. Потоки считаются равными периодами, без дат.

9. Дней до события

Поле: «Срок» (Дата).

max(0, date_diff(today(), [Срок], 'days'))

Без max для прошедших дат будет минус.

10. Выходной или будний

Поле: «Дата» (Дата).

if(is_weekend([Дата]), "Выходной", "Будний")

11. Категория риска

Поле: «Ожидаемая доходность» (Процент, например 15).

if([Ожидаемая доходность] >= 20, "Высокий",
  if([Ожидаемая доходность] >= 10, "Средний", "Низкий"))

Пустое значение попадёт в «Низкий». Колонку удобно использовать для разбивки на дашборде.

Расчёт значения виджета

12. Доходы минус расходы из двух таблиц

KPI → «Что считать»: шаг 1: таблица «Доходы», колонка «Сумма», «Как»: «Сложить». «Добавить шаг» → «Ещё одна колонка»: таблица «Расходы», колонка «Сумма», «Сложить». Между шагами: «минус −».

То же формулой:

SUM([Доходы.Сумма]) - SUM([Расходы.Сумма])

13. Средняя цена покупки актива

KPI по таблице «Сделки», формула:

SUM([Сделки.Количество] * [Сделки.Цена]) / SUM([Сделки.Количество])

В «Какие записи» → «По условию…» оставьте только покупки нужного тикера: «Тип» равно «Покупка», «Тикер» равно «BTC».

14. Стоимость портфеля по биржевым ценам

SUM([Сделки.Количество] * LOOKUP([Сделки.Тикер], [Рыночные цены.Тикер], [Рыночные цены.Цена]))

Или без формулы, шагом «Значение из справочника». Подробно в «Биржевые цены в формулах».

15. Доля одной категории в расходах

KPI, «Показать как»: %:

SUM(IF([Расходы.Категория] == "Аренда", [Расходы.Сумма], 0)) / SUM([Расходы.Сумма]) * 100

16. Изменение к прошлому месяцу

Формула не нужна. Возьмите KPI и на вкладке «Вид» в «Сравнение» выберите «С предыдущим периодом». Под числом появится изменение в процентах. Для нескольких показателей сразу есть виджет «Сравнение периодов».

Частые ошибки во всех рецептах

Путают долю и проценты. Финансовые функции возвращают долю, поле «Процент» хранит проценты.
Забыли про пустые значения. Любая арифметика с пустым полем даёт пусто. Защищайтесь coalesce([Поле], 0).
Считают динамику межстрочными функциями. В поле-формуле lag и running_sum идут по порядку добавления строк. Для динамики берите виджеты.