Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сделать формулу для расчёта пеней с меняющейся просрочки - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Pelena, китин  
Мир MS Excel » Работа и общение » Работа / Фриланс » Сделать формулу для расчёта пеней с меняющейся просрочки (Формулы/Formulas)
Сделать формулу для расчёта пеней с меняющейся просрочки
Antohnio Дата: Пятница, 11.01.2019, 19:56 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
В Экселе помогите придумать формулу пеней на сумму просрочки.
Дано: есть два столбца в плановом графике - даты платежей и требуемая сумма остатка долга на эту дату.
Задача: Рядом есть фактический график платежей, где каждый платёж в своей строчке.
В фактическом графике сделать формулу пеней по ставке 20% годовых с суммы просрочки. Чтобы на каждую дату эта формула помещалсь в 1-2 ячейках, чтобы не строить отдельную таблицу.

Трудность именно в том, что базой расчёта пеней является сумма просрочки.
Например, выдан кредит 1 января 2019 (ну например) на год с платежами по первым числам каждого месяца (1 февраля, 1 марта, 1 апреля...), а сумма долга при выдаче равна 120 000, каждый месяц уменьшается на 10 000. Тогда плановый график такой:

Дата Плановый долг Просрочка
01.01.2019 120 000,00 0
01.02.2019 110 000,00 10000
01.03.2019 100 000,00 20000
01.04.2019 90 000,00 30000
01.05.2019 80 000,00 40000
01.06.2019 70 000,00 50000
01.07.2019 60 000,00 60000
01.08.2019 50 000,00 70000
01.09.2019 40 000,00 80000
01.10.2019 30 000,00 90000
01.11.2019 20 000,00 100000
01.12.2019 10 000,00 110000
01.01.2020 - 120000

И вот, например, заемщик взяв кредит пропал и пришёл платить только 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).

Но вышло чертовски сложно... %) И не срабатывает ))))

Кто-то подскажет, как это записать в формуле Эксель попроще? :)
К сообщению приложен файл: 1556113.png(13.2 Kb)


Сообщение отредактировал Antohnio - Пятница, 11.01.2019, 20:16
 
Ответить
СообщениеВ Экселе помогите придумать формулу пеней на сумму просрочки.
Дано: есть два столбца в плановом графике - даты платежей и требуемая сумма остатка долга на эту дату.
Задача: Рядом есть фактический график платежей, где каждый платёж в своей строчке.
В фактическом графике сделать формулу пеней по ставке 20% годовых с суммы просрочки. Чтобы на каждую дату эта формула помещалсь в 1-2 ячейках, чтобы не строить отдельную таблицу.

Трудность именно в том, что базой расчёта пеней является сумма просрочки.
Например, выдан кредит 1 января 2019 (ну например) на год с платежами по первым числам каждого месяца (1 февраля, 1 марта, 1 апреля...), а сумма долга при выдаче равна 120 000, каждый месяц уменьшается на 10 000. Тогда плановый график такой:

Дата Плановый долг Просрочка
01.01.2019 120 000,00 0
01.02.2019 110 000,00 10000
01.03.2019 100 000,00 20000
01.04.2019 90 000,00 30000
01.05.2019 80 000,00 40000
01.06.2019 70 000,00 50000
01.07.2019 60 000,00 60000
01.08.2019 50 000,00 70000
01.09.2019 40 000,00 80000
01.10.2019 30 000,00 90000
01.11.2019 20 000,00 100000
01.12.2019 10 000,00 110000
01.01.2020 - 120000

И вот, например, заемщик взяв кредит пропал и пришёл платить только 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
Дата добавления - 11.01.2019 в 19:56
Antohnio Дата: Пятница, 11.01.2019, 20:06 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Готов кинуть денежку на пивас или печеньки за помощь. если объясните свою формулу, чтобы я её в дальнейшем использовал и модернизировал )))
 
Ответить
СообщениеГотов кинуть денежку на пивас или печеньки за помощь. если объясните свою формулу, чтобы я её в дальнейшем использовал и модернизировал )))

Автор - Antohnio
Дата добавления - 11.01.2019 в 20:06
Pelena Дата: Пятница, 11.01.2019, 20:13 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 13485
Репутация: 2970 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Готов кинуть денежку

Перенести тему в платный раздел?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
Готов кинуть денежку

Перенести тему в платный раздел?

Автор - Pelena
Дата добавления - 11.01.2019 в 20:13
Antohnio Дата: Пятница, 11.01.2019, 20:19 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, я редко делаю эти формулы и на форуме бываю редко. Поэтому правил платного раздела не знаю. Готов скинуть скромную благодарность - 1-2 тысячи, например.
Если это подходит под правила платного раздела и это поможет в решении задачи, то можно.
 
Ответить
СообщениеPelena, я редко делаю эти формулы и на форуме бываю редко. Поэтому правил платного раздела не знаю. Готов скинуть скромную благодарность - 1-2 тысячи, например.
Если это подходит под правила платного раздела и это поможет в решении задачи, то можно.

Автор - Antohnio
Дата добавления - 11.01.2019 в 20:19
Pelena Дата: Пятница, 11.01.2019, 21:06 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 13485
Репутация: 2970 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Написала в личку


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеНаписала в личку

Автор - Pelena
Дата добавления - 11.01.2019 в 21:06
Antohnio Дата: Четверг, 17.01.2019, 15:49 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо Вам, Елена, за проделанную работу.
Задача выполнена, цель достигнута :-)
 
Ответить
СообщениеСпасибо Вам, Елена, за проделанную работу.
Задача выполнена, цель достигнута :-)

Автор - Antohnio
Дата добавления - 17.01.2019 в 15:49
Мир MS Excel » Работа и общение » Работа / Фриланс » Сделать формулу для расчёта пеней с меняющейся просрочки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!