Как проверить порядок операций в своей таблице
Для короткой сверки достаточно трёх столбцов. Пусть в столбце A записан месяц, в B остаток при взносе в конце, в C остаток при взносе в начале. В строку 2 поставьте стартовые 100 000 ₽ в обе ячейки. В F1 запишите 1%, именно с символом процента, а в F2 сумму одного платежа, 5 000. Строка 3 будет итогом января.
- В B3 введите
=B2*(1+$F$1)+$F$2. Скобки дают коэффициент роста 1,01. Сначала растёт прежний остаток, затем увеличиваете его на сумму перевода. - В C3 введите
=(C2+$F$2)*(1+$F$1). Здесь в скобки попали оба слагаемых, поэтому и прежний остаток, и новый взнос получают проценты за январь. - Сравните январские ячейки со значениями 106 000 и 106 050 ₽. Затем скопируйте обе формулы вниз до строки 14 включительно. Ссылка на предыдущую строку сдвигается, а ссылки на F1 и F2 со знаком доллара остаются на месте.
В Excel можно сверить годовые суммы и без копирования строк. В русской версии функция БС вычисляет будущую стоимость при постоянных платежах и ставке. Для нашего примера =БС(1%;12;-5000;-100000;0) задаёт платежи в конце месяца. Чтобы получить второй итог, замените последний аргумент на 1. Минус перед взносом и начальной суммой означает, что вы отдаёте деньги на счёт. Итоговая сумма к получению выходит положительной.
Для этой функции «период» означает тот шаг, в котором одновременно заданы ставка и число платежей. При месячных взносах пишите 1% и 12 периодов. Если известны эффективные 12% за год, делить их на двенадцать уже нельзя. Нужна такая месячная ставка, которая при двенадцати начислениях даст годовой множитель 1,12. Разница разобрана в статье о номинальных и эффективных ставках.
Если результат не сходится, для начала подставьте в F1 нулевую ставку. Строка 14 должна показать 160 000 ₽ в обоих столбцах. Затем верните ставку и временно обнулите регулярный взнос. Общая стартовая сумма должна вырасти до 112 682,50 ₽ в обоих графиках. Эти две проверки позволяют по отдельности проверить подсчёт платежей и рост первоначальных денег.
Как избежать лишнего взноса при переносе даты
В быту начало и конец соседних месяцев почти совпадают. Ошибка обычно прячется там, где таблица начинается или заканчивается. Например, вы записали первые 5 000 ₽ в начальный остаток, подняв его до 105 000 ₽, а потом добавили двенадцать взносов с января по декабрь. Собственных денег к концу года в такой таблице уже 165 000 ₽. Перенос времени взноса сам по себе не требует дополнительного платежа.
Есть удобный способ воспроизвести раннее пополнение даже в таблице, которая всегда вносит деньги после начисления. Начните со 105 000 ₽ и делайте пополнения в конце января, февраля и так далее до конца ноября включительно. В конце декабря ничего не добавляйте. Получатся исходные 100 000 ₽, один платёж при открытии и ещё одиннадцать переводов. Остаток на каждое окончание месяца перед очередным переводом совпадёт с ранним графиком после начисления. При таком способе важно договориться, какой именно момент на границе двух соседних месяцев вы показываете в строке.
Если календарь не требует ручной сборки, в калькуляторе вклада можно указать 12 месяцев и переключить момент пополнения. Оставьте начальную сумму, ставку, частоту капитализации и размер платежа одинаковыми. Тогда изменение результата после переключения покажет стоимость именно этого сдвига. Для графика с разными суммами или пропущенным месяцем полезнее отдельный столбец по фактическим переводам. Сумма платежей из выписки поможет обнаружить забытый или задвоенный месяц.
Как сравнить расчёт с выпиской по счёту
В задаче выше каждый месяц получает одинаковый 1%. Банк может начислять проценты на ежедневный остаток или на минимальную сумму за месяц, а заработанное перечислять на отдельный счёт. Сначала откройте условия своего продукта и найдите, какая сумма участвует в расчёте и за какие дни. Именно эти параметры нужно перенести в таблицу, если вы хотите сойтись с выпиской до копейки.
Например, перевод 5 000 ₽ вечером 30 января и перевод утром 1 января модель с месячными шагами способна разместить только на одной из границ месяца. Если по договору важен остаток каждого дня, выпишите дату фактического зачисления, дату конца расчётного периода и дни, в которые эта сумма учитывалась. Если важен минимальный остаток, проверьте сумму, которая лежала на счёте до перевода. Она могла определить минимум за месяц ещё до пополнения. Пример такого условия есть в разъяснениях Банка России о ставках по вкладам.
Сверку удобнее начать с одного полностью закрытого месяца. Найдите в выписке остаток при открытии, все поступления и снятия, отдельно выпишите начисленные проценты. Проверьте, были ли они прибавлены к балансу или ушли на другую карту. Если уже в первом месяце не совпадает сумма собственных денег, проверьте даты зачислений. Если совпала база, а расхождение только в копейках, посмотрите, на каком шаге округлялся доход. Так можно отличить эффект более раннего пополнения от совсем другого способа начисления.