Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Office Новый топик    Ответить
 Excel. Ошибка в формулах хорошего кредитного калькулятора  [new]
Igor_Exc_Run
Member

Откуда:
Сообщений: 10
На просторах рунета отыскал лучший (на мой взгляд) калькулятор кредитов в Excel, позволяющий вводить информацию о досрочных платежах и сразу же видеть изменения в графике платежей. Досрочные платежи можно вводить как в погашение срока, так и суммы ежемесячного платежа. Возможно, он пригодится не только мне.

Но! Проблема возникает, при попытке одновременно скомбинировать в одном расчете досрочные платежи как с уменьшением срока, так и с уменьшением платежа.

Например (он воспроизведен в файле во вложении):
1. Сумма займа: 1 млн, Ставка: 10%, Срок кредита, месяцы: 60. Получаем график платежей на эти 60 месяцев.
2. В столбце "Уменьшение срока" ставим 60 000,00, в Аннуитетном платеже получаем вместо 60 месяцев 56.
3. В следующем месяце дополнительно в столбце "Уменьшение платежа" ставим 50 000,00 и величина платежа уменьшается, однако, при этом число месяцев снова увеличивается и становиться равным 61-му месяцу. Что, конечно, не верно, т.к. мы предыдущим платежом сократили срок.

Обратная же ситуация - когда сперва уменьшаем платеж, а потом срок - отрабатывает корректно.
В чем ошибка, приводящая к такому поведению, я, к сожалению, так и не понял. Обратите, пожалуйста, внимание, что часть формул в соседних столбцах и строках скрыто заливкой.

Буду признателен за помощь!

PS
Указал бы ссылку на первоисточник этого калькулятора, но скачивал очень давно и сейчас не смог найти. Однако, у меня записано в заметках, что автор этого калькулятора Игорь Пустошило.

К сообщению приложен файл (kalkulator.xlsx - 58Kb) cкачать
15 дек 17, 18:09    [21037930]     Ответить | Цитировать Сообщить модератору
 Re: Excel. Ошибка в формулах хорошего кредитного калькулятора  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 16540
Igor_Exc_Run
В столбце "Уменьшение срока" ставим 60 000,00, в Аннуитетном платеже получаем вместо 60 месяцев 56.
Срок в рублях и платёж в месяцах... интересно, что круче?

Igor_Exc_Run
величина платежа уменьшается, однако, при этом число месяцев снова увеличивается и становиться равным 61-му месяцу. Что, конечно, не верно, т.к. мы предыдущим платежом сократили срок.
Ну в чём ошибка, можно сказать, даже не скачивая файл. Надо либо вести пересчёт с момента, когда внесена инициировавшая пересчёт корректировка, либо с начала, но учитывая все корректировки, а не только последнюю внесённую.
15 дек 17, 18:35    [21038004]     Ответить | Цитировать Сообщить модератору
 Re: Excel. Ошибка в формулах хорошего кредитного калькулятора  [new]
Igor_Exc_Run
Member

Откуда:
Сообщений: 10
Akina
Срок в рублях и платёж в месяцах... интересно, что круче?

Прошу прощения, не понял вашего вопроса... Подумал, что это намек на какую-то мою ошибку в тексте (ведь все наоборот - срок в месяцах, а платежи в рублях), но такой ошибки не увидел. Если же вопрос относительно того, как лучше быстрее погашать - уменьшая срок или уменьшая платеж - то тут каждый решает в меру собственных обстоятельств, не думаю, что есть универсальный ответ, подходящий всем.

Akina
Ну в чём ошибка, можно сказать, даже не скачивая файл. Надо либо вести пересчёт с момента, когда внесена инициировавшая пересчёт корректировка, либо с начала, но учитывая все корректировки, а не только последнюю внесённую.

Да, конечно, я это понимаю, однако, вопрос не с методологической точки зрения, а с точки зрения конкретной реализации, которая сбоит в этом файле. К сожалению, у меня не получилось сообразить, из-за чего возникает этот сбой.

Спасибо за ваше участие!
15 дек 17, 18:49    [21038061]     Ответить | Цитировать Сообщить модератору
 Re: Excel. Ошибка в формулах хорошего кредитного калькулятора  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 16540
Igor_Exc_Run
вопрос не с методологической точки зрения, а с точки зрения конкретной реализации, которая сбоит в этом файле.
Конкретная реализация должна базироваться на формуле, которая принимает текущие размер задолженности, процент по кредиту и размер платёжа (ежемесячный плюс одноразовый в этом месяце), и делит этот платёж на суммы погашения и обслуживания (точнее, возвращает одну из этих сумм). И формуле, которая принимает текущие размер задолженности, процент по кредиту и оставшийся срок на погашение, и возвращает рассчётную сумму ежемесячного платежа (в первый месяц расчёт выполняется на основании исходных данных, а далее каждый раз, когда производится изменение планового срока погашения). Если в текущем месяце изменения не было, ежемесячный платёж равен таковому в предыдущем месяце (есссно без учёта одноразового платежа). Чисто скалярные формулы...

Igor_Exc_Run
у меня не получилось сообразить, из-за чего возникает этот сбой

А Вы посмотрите зависимости - на какие ячейки влияют значения каждой из заполняемых колонок и от каких ячеек зависят. Достаточно это сделать в одной строке... и посмотрите правильно учитывает формула все ЧЕТЫРЕ возможных варианта (обе пусты, заполнена первая, заполнена вторая, заполнены обе). Раз расчёт сбоит - какой-то из вариантов учитывается некорректно. Я же в булгахтериях слаб...
15 дек 17, 20:40    [21038283]     Ответить | Цитировать Сообщить модератору
 Re: Excel. Ошибка в формулах хорошего кредитного калькулятора  [new]
Igor_Exc_Run
Member

Откуда:
Сообщений: 10
Akina,
спасибо за ваше участие и неравнодушие к теме.
К сожалению (или к счастью?), то о чем вы говорите мне ясно и, конечно, я смотрел зависимости ячеек.
Подход в расчете кредитов мне также ясен. Поймите, пожалуйста, я обратился на форум не с кандычка. Очевидно, при анализе формул я что-то упускаю, и, увы, теоретизирования на тему как рассчитываются кредиты мне не помогают.

Я не говорю, что это во чтобы то ни было надо сделать вам или еще кому-то, но, если все таки эта тема интересна/важна не только мне, то прошу помочь именно реальными действиями, ведь возможность загрузки файлов на форум придумана не просто так и загрузил я этот файл тоже не ради того, чтобы обсудить подходы расчетов в отрыве от конкретного примера.

Спасибо и рассчитываю на понимание.
16 дек 17, 00:39    [21038657]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Office Ответить