Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет пени по ставке рефинансирования, Таблица для ТСЖ
 
#1
08.02.2016 18:50:21
Есть жилой дом. В нем 89 квартир. с пару десятков хоз. блоков и гаражей.

Жители платят за коммуналку в ТСЖ. Неплательщикам начисляется пени после 10-го числа следующего месяца в размере:

За 1-30 день просрочки пени не взимаются.
После 31-90 дня пени взимаются в размере 1/300 ставки рефинансирования на каждый день просрочки.
Спустя 91 день и далее пени взимаются в размере 1/130 ставки рефинансирования на каждый день просрочки.

С 1 января 2016 года значение ставки рефинансирования Банка России приравнивается к значению ключевой ставки Банка России, определенному на соответствующую дату. С 1 января 2016 года для начисления пени необходимо применять ставку 11%.
Тут есть два важных момента:
1) Ключевая ставка меняется довольно часто (отображается на главной странице Банка России cbr.ru).
2) Изменение ставки происходит не с 1-го числа месяца. В 2015 году значение ключевой ставки менялось 5 раз: 2 февраля, 16 марта, 5 мая, 16 июня, 3 августа. Рассмотрим последний пример. Если бы новый закон действовал уже тогда, то при начислении пени за 1 и 2 августа необходимо применять ставку 11,5%, а за период с 3 августа 11%.

Ума не приложу, как это правильно сделать....
Изменено: elvin3000 - 10.02.2016 15:29:54
 
 
 
#2
08.02.2016 19:51:54
У нас не так часто меняется ставка рефинансирования. В тот месяц делать два этапа, с одной и с другой ставкой рефинансирования...
Если автоматизировать бардак, то получится автоматизированный бардак.
 
 
 
#3
08.02.2016 22:00:40
wowick, я это тоже главбуху говорил, но она сказала, что в том году 5 раз ставка менялась, причем в самый неподходящий момент.... Задача сделать так, чтобы если вдруг ставка меняется - происходил перерасчет именно с этого дня, а прежние дни считались по старой ставке....
Изменено: elvin3000 - 09.02.2016 08:12:04
 
 
 
#4
09.02.2016 04:30:09
Доброе время суток
Цитата
elvin3000 написал:
я это тоже главбуху говорил, но она сказала, что в том году 3 раза ставка менялась
Я конечно полный дуб в таких вопросах, но по Ставка рефинансирования ЦБ РФ на сегодня и за все годы - ставка рефинансирования в прошлом году не менялась, более того не менялась с 2012. Может бухгалтер имела ввиду ключевую ставку банка России?
А по существу, банальный ВПР по нестрогому совпадению. Не совсем понятно, ставку нужно использовать на начало месяца или даты поступления. Применение показал на листе ЯНВАРЬ 2016. Потом только пополняете данные на листе "Ставки рефинансирования".

Успехов.
 
 
 
#5
09.02.2016 08:27:46
Цитата
Андрей VG написал:
Ставка рефинансирования ЦБ РФ на сегодня и за все годы - ставка рефинансирования в прошлом году не менялась, более того не менялась с 2012. Может бухгалтер имела ввиду ключевую ставку банка России?
Я тоже не силен во всех этих ставках. И да, имелась ввиду ключевая ставка Центробанка.

Подправил текст в шапке для более понятного восприятия задачи.
 
 
 
#6
09.02.2016 08:33:32
Цитата
elvin3000 написал: Я тоже не силен во всех этих ставках
а деньги с людей берёте :)
А что у вас не получается?
 
 
 
#7
09.02.2016 09:28:11
Цитата
Андрей VG написал: а деньги с людей берёте
Я к этому ТСЖ непричастен. Просто попросили помочь))
а считают они все вручную.... это свихнуться можно столько параметров в голове держать... ведь долги из месяца в месяц переходят. Со своими пенями в каждом месяце.... Все это плюсуется и растет в прогрессии.

А не получается, т.к. в вашем варианте ставка тоже фиксированная. А ключевая ставка будет прыгать...
 
 
 
#8
09.02.2016 09:56:46
Цитата
elvin3000 написал:
А не получается, т.к. в вашем варианте ставка тоже фиксированная. А ключевая ставка будет прыгать.
Да и пусть себе прыгает. Главное пополнять на листе "Ставки рефинансирования" данные о дате наступления действия очередной ставки и следить, чтобы данные были упорядочены по датам по возрастанию. Прочитайте про ВПР уже ;)
 
 
 
#9
09.02.2016 10:14:16
Андрей, я Вам очень признателен. Только объясните, если ставка изменится, например, 14 февраля, как программа обсчитает первые 13 дней февраля? По старой 11% ставке?

И еще, я ведь имею право поставить в поле B2 функцию =СЕГОДНЯ() чтобы было "онлайн" )) ?
Изменено: elvin3000 - 09.02.2016 10:22:18
 
 
 
#10
09.02.2016 10:27:26
Цитата
elvin3000 написал: если ставка изменится, например, 14 февраля, как программа обсчитает первые 13 дней февраля? По старой 11% ставке?
Естественно, ведь действие новой ставки будет действовать с 14.02. Смотрите пример.
Цитата
elvin3000 написал: B2 функцию =СЕГОДНЯ()
И что будет в результате? При каждом открытии или изменении в файле СЕГОДНЯ будет пересчитываться (будет текущая дата), и, по моему, тогда клиенты, кто уплатил во время останутся должны :cry: , хотя выплатили всё во время. С чего бы собственно говоря? Или я, поскольку далёк от подобных расчётов, чего-то не улавливаю, может расчёты касаются только пенни?
 
 
 
#11
09.02.2016 11:37:47
Расчет заточен на подсчет пени. Есть сумма оплаты ЖКХ, например 4000р. за Январь. Она просрочена, опять же например на 39 дней. За эти 39 дней накопилось пени на сумму 58р. 67коп. Итого, человек должен 4058,67р.

Проходит уже февраль. Квартиросъемщик нажег за февраль энергоресурсов на 3000р. За январь он так и не заплатил. Итого на данный момент он должен 7000р. + пени за январь. Каждый день программа продолжает считать пени за январь. Проходит 30 дней после февраля, программа начинает еще считать пени за февраль. И так далее.

Важно понимать, что пени на каждом листе считаются только за свой месяц. "Голые" долги складываются в последнем месяце. Пени по месяцам складываются в последнем долге. Пени на пени прошлых месяцев не начисляются!

А СЕГОДНЯ будет отрабатываться правильно только в последнем месяце. В остальных нужно ставить дату расчета в поле В2 руками. Причем, последним днем, например в январе ставить в В2 31 января., чтобы в январе ставка оставалась старой, даже если в следующих месяцах ставка изменится и в лист "Ставка_РФ" впишется новая ставка. Я прав?
Изменено: elvin3000 - 20.09.2016 09:55:59
 
 
 
#12
09.02.2016 15:24:11
Вы правы. ВПР в этом случае опирается на задаваемую дату поиска. Если эта дата не изменяется, то будет выдаваться ставка по дате ближайшей меньшей или равной это дате поиска. Но, как будет учитываться пенни начисляются в конце месяца, а изменения по ставке произошли, допустим 15 числа? Тогда, по идее нужно пенни с 1 по 14 считать по одной ставке, а с 15 по 31 уже по новой. В этом же алгоритме будет применяться ставка от 15 числа на весь месяц.
Если это важно, то надо переделывать алгоритм вычисления. Скорее всего писать макросы или хотя бы udf-функции.
 
 
 
#13
09.02.2016 15:52:05
немного не догнал ход Ваших мыслей, но в том то и загвоздка, что при необходимости, если ставка меняется нежданно негаданно посреди месяца, то программа, считавшая пени до 15-го так и должна их запомнить по старой ставке, а с 15-го и до конца месяца по другой ставке, а потом все сложить))

если честно, я думал, что Ваша прога уже работает как надо... поясните, что Вас насторожило еще раз...
Изменено: elvin3000 - 09.02.2016 19:28:19
 
 
 
#14
09.02.2016 16:53:41
Такой вопрос:
если платеж просрочен на 100 дней то:
1) все 100 дней считаются по 1/130
2) первые 30 дней вообще ничего не начисляется, затем с 31 по 90й день начисляется по 1/300, а с 91го по 100й день считается как 1/130?
Следуя вашему описанию это второй вариант, но если смотреть формулы в файле это 1й?
 
 
 
#15
09.02.2016 17:49:51
Попробуйте такой вариант
Но во втором варианте придётся для каждого месяца строить свою собственную вспомогательную таблицу (возможно можно и одно обойтись, но тут думать надо)

Файлы удалены: превышение допустимого размера вложения [МОДЕРАТОР]
Изменено: Zoynels - 09.02.2016 20:20:29
 
 
 
#16
09.02.2016 19:17:57
Zoynels, второе, хотя... нужно будет уточнить... Спасибо за примеры. Сейчас буду изучать.
 
Изменено: elvin3000 - 10.02.2016 15:28:10
 
 
 
#17
09.02.2016 20:17:36
Zoynels, при всем уважении, но:

1) мне кажется вы не допоняли цель задачи. Почему напротив каждой квартиры разная дата, со смещением в +1? У вас все расписано с 2013г. Зачем...? Колонку В в листе "сверка" где прибавляется по рублю я вообще не понял...
2) бухгалтерша сойдет с ума с такой таблицей. Ей нужно делать только 3 действия. Ставить сумму за коммуналку, дату заполнения и, уж если это случилось добавлять в таблицу изменения ключевой ставки новую ставку, как предложил Андрей VG.
простите, но я ничего не понял...
В шапке прикреплен последний вариант программы с правками Андрея VG.
Изменено: elvin3000 - 09.02.2016 20:21:19
 
 
 
#18
10.02.2016 04:59:21
Цитата
elvin3000 написал:
У вас все расписано с 2013г. Зачем...? Колонку В в листе "сверка" где прибавляется по рублю я вообще не понял...
Не хотите лишних дат, просто удалите их. Сверка была для того чтобы вручную проверить что формула массива работает верно.
Цитата
elvin3000 написал:
1) ... Почему напротив каждой квартиры разная дата, со смещением в +1?
Это осталось от предыдущей проверки на главном листе и совершенно ни на что не влияет для первой квартиры у которой и установлены формулы. Прибавляется не по рублю, а по дню. Видимо нужно было лучше оформить заголовки раз вы этого не поняли.
 
 
 
#19
10.02.2016 05:34:54
Доброе время суток
Не специалист, вопросов у меня больше чем ответов. И в любом случае, если бы делал, то переходил бы на другую структуру данных, обслуживаемую макросами. Во вложении функция, вычисляющая пени с учётом изменений ставки. Входные параметры установленная дата платежа и сумма платежа.
Функция исходит из того, что если наступил 32 день просрочки, то пени насчитываются на все 32 дня (хотя по идее должно быть 2). Честно говоря, нет времени вникать в тонкости и детали.
 
 
 
#20
10.02.2016 08:49:42
Цитата
Андрей VG написал: Во вложении функция, вычисляющая пени с учётом изменений ставки.
Андрей VG, добрый день. Честно говоря, не увидел изменений с последней вашей помощи. Все это есть в последней версии программы во вложении в шапке.
 
 
 
#21
10.02.2016 14:00:45
Уважаемые, с изменением ставки считаю разобрались.

Теперь нужно подумать, как сделать разграничение, как писал Zoynels:
если платеж просрочен на 100 дней то, первые 30 дней вообще ничего не начисляется, затем с 31 по 90й
день начисляется по 1/300, а с 91го и далее считается как 1/130. А не так, как у меня считается - все 100 дней по 1/130.

Андрей VG, может ту же ВПР использовать получится?
Хелп ми плиз!

Исправил. Считало с 31-го дня за все 31 день. Сейчас считает за 1 день. Теперь победить бы 91 день...
Изменено: elvin3000 - 10.02.2016 15:28:47
 
 
 
#22
10.02.2016 17:31:44
Расчет через "суммеслимн", но для каждого месяца своя вспомогательная таблица, увы. Для себя бы я именно так оставил, так как дальше нужно слишком сильно вникать в формулы массива (мне проще каждый месяц отдельную таблицу составлять). Вот только всё равно такой расчет слабоват, так как взнос может оплачиваться частями (при чём неравномерными) и в этом случае получается что требуется вообще система учета кто, когда и сколько погасил.

Цитата
elvin3000 написал:
Исправил. Считало с 31-го дня за все 31 день. Сейчас считает за 1 день. Теперь победить бы 91 день...
Сделайте две колонки, одна считает количество просроченных дней с 31 по 90, а вторая с 91 и больше. Для каждой свой коэффициент увеличения.
 
 
 
#23
20.09.2016 09:23:51
Вот вариант расчета для суда по новым правилам исчисления пени, с учетом изменяющейся ставки банка.
Не решил для себя как выкрутиться из ситуации, когда  в период иска попадает задолженность возникшая в прошлом месяце. В файле примера строки выделены красным.
 
 
 
Страницы: 1
Читают тему
Наверх