В Экселе помогите придумать формулу пеней на сумму просрочки. Дано: есть два столбца в плановом графике - даты платежей и требуемая сумма остатка долга на эту дату. Задача: Рядом есть фактический график платежей, где каждый платёж в своей строчке. В фактическом графике сделать формулу пеней по ставке 20% годовых с суммы просрочки. Чтобы на каждую дату эта формула помещалсь в 1-2 ячейках, чтобы не строить отдельную таблицу.
Трудность именно в том, что базой расчёта пеней является сумма просрочки. Например, выдан кредит 1 января 2019 (ну например) на год с платежами по первым числам каждого месяца (1 февраля, 1 марта, 1 апреля...), а сумма долга при выдаче равна 120 000, каждый месяц уменьшается на 10 000. Тогда плановый график такой:
И вот, например, заемщик взяв кредит пропал и пришёл платить только 5 апреля 2019. У него фактическая сумма остатка долга 120 000. В первый месяц просрочки нет, во второй она равна 10 000, в третий уже 20 000... Тогда нужно пени посчитать на сумму просрочки 10 000 за даты от 1 февраля до 1 марта. На сумму 20 000 от 1 марта до 1 апреля и на сумму 30 000 от 1 до 5 апреля.
Я пытался так: сначала сделать массив плановых дат и каждую проверять на то, чтоб она была меньше текущей (5 апреля) и больше даты прошлой строчки (прошлый платёж или если их не было - дата выдачи 1 января), проверять через ВПР, является ли сумма остатка долга в ней меньше, чем есть у нас на данный момент (120 000). И это выражения хотелось бы взять за X, потому что потом надо подставлять в формулу массива (через Alt+Shift+Enter): ставка пени/365*сумма долга * срок.
Где сумма = (120000 - ВПР (X; массив плановых дат; столбец с суммами; ЛОЖЬ). Должна была получаться разница между остатком долга на руках и тем, который должен быть в указанный период, то есть та самая просрочка.
А срок = (X - МАКС [Индекс(массив плановых дат; Строка(Х)-1); дата прошлого фактического визита в офис]). Дата наша из массива Х, проверенная по вышеуказанным условиям, минус дата либо предыдущего планового платежа, либо прошлого его прихода в офис. Должен был выходить срок конкретной просрочки ( 1 месяц для 10 000, 1 месяц для 20 000 и 5 дней для 30 000).
Но вышло чертовски сложно... И не срабатывает ))))
Кто-то подскажет, как это записать в формуле Эксель попроще?
В Экселе помогите придумать формулу пеней на сумму просрочки. Дано: есть два столбца в плановом графике - даты платежей и требуемая сумма остатка долга на эту дату. Задача: Рядом есть фактический график платежей, где каждый платёж в своей строчке. В фактическом графике сделать формулу пеней по ставке 20% годовых с суммы просрочки. Чтобы на каждую дату эта формула помещалсь в 1-2 ячейках, чтобы не строить отдельную таблицу.
Трудность именно в том, что базой расчёта пеней является сумма просрочки. Например, выдан кредит 1 января 2019 (ну например) на год с платежами по первым числам каждого месяца (1 февраля, 1 марта, 1 апреля...), а сумма долга при выдаче равна 120 000, каждый месяц уменьшается на 10 000. Тогда плановый график такой:
И вот, например, заемщик взяв кредит пропал и пришёл платить только 5 апреля 2019. У него фактическая сумма остатка долга 120 000. В первый месяц просрочки нет, во второй она равна 10 000, в третий уже 20 000... Тогда нужно пени посчитать на сумму просрочки 10 000 за даты от 1 февраля до 1 марта. На сумму 20 000 от 1 марта до 1 апреля и на сумму 30 000 от 1 до 5 апреля.
Я пытался так: сначала сделать массив плановых дат и каждую проверять на то, чтоб она была меньше текущей (5 апреля) и больше даты прошлой строчки (прошлый платёж или если их не было - дата выдачи 1 января), проверять через ВПР, является ли сумма остатка долга в ней меньше, чем есть у нас на данный момент (120 000). И это выражения хотелось бы взять за X, потому что потом надо подставлять в формулу массива (через Alt+Shift+Enter): ставка пени/365*сумма долга * срок.
Где сумма = (120000 - ВПР (X; массив плановых дат; столбец с суммами; ЛОЖЬ). Должна была получаться разница между остатком долга на руках и тем, который должен быть в указанный период, то есть та самая просрочка.
А срок = (X - МАКС [Индекс(массив плановых дат; Строка(Х)-1); дата прошлого фактического визита в офис]). Дата наша из массива Х, проверенная по вышеуказанным условиям, минус дата либо предыдущего планового платежа, либо прошлого его прихода в офис. Должен был выходить срок конкретной просрочки ( 1 месяц для 10 000, 1 месяц для 20 000 и 5 дней для 30 000).
Но вышло чертовски сложно... И не срабатывает ))))
Кто-то подскажет, как это записать в формуле Эксель попроще? Antohnio
Pelena, я редко делаю эти формулы и на форуме бываю редко. Поэтому правил платного раздела не знаю. Готов скинуть скромную благодарность - 1-2 тысячи, например. Если это подходит под правила платного раздела и это поможет в решении задачи, то можно.
Pelena, я редко делаю эти формулы и на форуме бываю редко. Поэтому правил платного раздела не знаю. Готов скинуть скромную благодарность - 1-2 тысячи, например. Если это подходит под правила платного раздела и это поможет в решении задачи, то можно.Antohnio
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
И не срабатывает ))))
