Кряк, не откладываем в долгий ящик — начинаем делать нашу табличку для кредитной карусели. В прошлой части мы с вами обсудили ТЗ, которое я буду стремиться выполнить.
Для тех, кто не читал, кратко: я буду пробовать сделать калькулятор для кредитной карусели без макросов и прочих финтифлюшек. Только формулы, умные таблицы и сводные — только хардкор. Такое ограничение связано с тем, что файл должно быть удобно просматривать в приложении с телефона, а там функционал ограничен.
В качестве исходных данных для примера взяла файл у другого автора (ссылка в предыдущей публикации).
Дисклеймер
Будет душновато, потому что карусель — это рискованный финт ушами. Поэтому, если вам не хочется скрипеть мозгами и вникать в логику, можете закрывать статью — я точно не обижусь.
Начинаем собирать таблицу
Собираем необходимые параметры карты
Посмотрев исходный файл и на свою карту, я выделила следующую информацию, которую нам надо учитывать.
- Дата открытия карты
- Сколько дней закрывается карта
- Закрыть карту до даты
- Сколько стоит обслуживание в год
- Грейс-период
- Лимит вывода без процентов
- Дата первой выписки
- Ближайшая выписка
- Минимальный платёж
В процессе работы и добавления других карт могут появиться дополнительные поля, которые надо учитывать, но сейчас нам этого достаточно.
Про заполнение полей мы поговорим далее, сейчас пока прикидываем, что нам потребуется для контроля за карточкой.
Данные сгруппированы так, чтобы можно было скрывать лишнее и оставлять только нужное.
Заполняем информацию по карте Tinkoff All Airlines
Поскольку я предполагаю, что у всех сценарии использования будут разные, я дополнительно поясняю логику по каждому полю.
Поле [Карта] — это имя для вашей карты в таблице. Название должно быть уникальным, чтобы можно было использовать ВПР, поэтому для себя я указываю "Tinkoff All Airlines-1". Если вам важно различать свои карточки и мужа, например, я бы писала так: "Tinkoff All Airlines[Я]" и "Tinkoff All Airlines[Муж]". Но тут вкусовщина, можете и через чёрточку писать.
Поля [Дата открытия], [Закрытие счёта, дни], [Закрыть до даты]
Эти поля нужны в том случае, если у вас карта взята по рекламному предложению, а вы хотите потом карту вовремя закрыть. Например, первый год обслуживания бесплатно. Тогда вам нужно указать в этих полях информацию по срокам и датам, чтобы не забыть.
Поле [Обслуживание в год, руб.] — здесь стоимость годового обслуживания для справки.
Поле [Грейс-период] — здесь нужно поставить количество дней периода, за который банк не начисляет проценты.
Поле [Лимит вывода без %, руб.] — это максимальная сумма вывода средств без лимита. В конкретном случае для карты All Airlines это зависит от наличия подписки Tinkoff Pro и Tinkoff Premium. Также надо заметить, что лимитов там два: на снятие наличных и на перевод. Я не вижу смысла считать сумму каждого из лимитов отдельно, поэтому указываю результат целиком.
Поле [Дата первой выписки] — здесь нужно указать дату, когда у вас приходит первая выписка. Именно дату, не число. Зачем — позже объясню.
Поля [Платёж (min), руб.], [Платёж (min), %] — это сумма минимального платежа для расчетных периодов. В случае с картой Tinkoff All Airlines для карусели эта информация неактуальна, но можно указать для справки. Это поле понадобится потом для следующих карт.
Поле [Ближайшая выписка] — это дата ближайшей выписки, расчётное поле. Как считается, читайте далее.
А пока что вот вам мой промежуточный результат заполнения.
Как правильно посчитать дату ближайшей выписки с помощью Excel?
Автор исходного файла использовала получение новой даты платежей по следующей формуле:
=ДАТА(ГОД(X);МЕСЯЦ(X)+1;ДЕНЬ(X)),
где X - это дата предыдущего платежа.
Эта формула работает правильно только в тех случаях, когда у нас дата выписки попадает на 1-28 число месяца. У меня, например, выписка 30 числа, поэтому у меня эта формула считать правильно не будет в феврале. Поэтому для правильного расчёта сдвига надо использовать функцию ДАТАМЕС().
Поэтому используйте для таких случаев ДАТАМЕС() и не ошибётесь.
Но это ещё не всё!
Поскольку ДАТАМЕС() после 29 февраля вернёт?.... правильно, 29 марта, нам нужно считать новую дату выписки каждый раз от текущей даты.
Чтобы это сделать, нам надо считать разницу в месяцах между датами. Делается это по следующей формуле.
(ГОД(СЕГОДНЯ())-ГОД(X))*12+МЕСЯЦ(СЕГОДНЯ())-МЕСЯЦ(X),
где X — дата первой выписки.
Тогда наша формула уже выглядит вот так.
=ДАТАМЕС(X;(ГОД(СЕГОДНЯ())-ГОД(X))*12+МЕСЯЦ(СЕГОДНЯ())-МЕСЯЦ(X))
Она работает хорошо, но...
Чтоб такого не было, мы добавляем в нашу формулу условие: если сегодняшняя дата больше получившейся даты выписки, то надо прибавить к сдвигу ещё единицу. Итого эта змея выглядит так.
Ну что, живы там? Осталось немножко добить про эту карту.
Создаём таблицу для контроля за возвратом средств и прогнозом
Чтоб вас не мучать, скажу, что тут аналогично: создаём на новом листе умную таблицу с полями. Часть полей формульная, часть надо ввести самим.
Единственное, что тут скажу — это про поле [Выписка (расч.)]. Она нужна для того, чтобы можно было сохранять исторические данные и считать наперёд. Поэтому поля [Дата первой выписки] и [Ближайшая выписка] здесь выводятся повторно для справки (их можно скрыть).
Здесь внимательный пирожок скажет:
— Эй, утка, у нас же грейс-период 55 дней! Почему мы держим средства всего 28 дней?
А патаму чта в этом смысла нет для карты Tinkoff All Airlines. Что касается даты возврата, там у меня стоит минус один день для страховки. Можно и 29 дней получить, но не суть.
Почему льготный период в 55 дней бесполезен для карусели?
Патаму чта №1. По этой и похожей карте такие условия
Вы если заплатите только минимальный платёж по истечению 55 дней, вас просто не оштрафуют, а вот проценты на задолженность за период вам начислят. Это будет прямое попаделло и кряк-здец.
Но даже если вы решите сидеть весь период — т.е. 54 дня — то...
Патаму чта №2. Вы теряете в доходе из-за потери дней
У банка лимиты обновляются по карте на следующий день после даты выписки. И вам сверху не сыпятся ещё деньги, как есть у Альфы, например.
Гораздо выгоднее раз в месяц гонять туда-сюда деньги.
Поэтому намного выгоднее брать деньги на короткий срок между выписками и не париться.
На этом на сегодня всё. В следующий раз возьмём другую кредитную карту посложнее 🦆
И спасибо, что дочитали до этого места! Если вам понравилось и хочется меня поддержать, поставьте лайк и напишите комментарий. Если вам хочется сделать больше, вы можете кинуть монетку в мой пруд по этой ссылке.
Подписывайтесь, чтобы ничего не пропустить! Ну а если всё равно непонятно, что тут происходит — заходите в навигацию.