Категория: Программы
В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер :
«Не надо напрягать свой мозг там, где это могут сделать за вас другие!»
В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.
Хотим предупредить, что команда портала temabiz.com поставила перед собой цель не просто дать вам «халяву» в виде «экселевского» файла с готовыми расчетами. Нет, в этой публикации мы вас научим самостоятельно рассчитывать аннуитетные платежи, а также составлять в программе Excel графики погашения аннуитетных кредитов. Ну а для ленивых мы, конечно же, выложим готовые файлы кредитных калькуляторов.
Как рассчитать аннуитетный платеж в ExcelТе, кто читал предыдущую публикацию. наверняка ещё долго будут с ужасом вспоминать формулу аннуитетного платежа. Но сейчас вы, дорогие друзья, можете облегчённо вздохнуть, ибо все расчёты за вас сделает программа Microsoft Excel.
Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.
Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка. срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Начнём с расчёта ежемесячной суммы аннуитетного платежа. Для этого надо сделать активным окошко, в котором вы хотите видеть это значение (в нашем случае – это поле C11. на рисунке оно обведено и указано под номером 1 ). Далее слева от строки формул жмём на «fx» (на рисунке эта кнопка обведена и указана под номером 2 ). После этих действий у вас появится такая табличка:
Выбираем функцию «ПЛТ» и жмём «Ок». Перед вами появится таблица, в которую надо будет ввести исходные данные:
Здесь нам требуется заполнить три поля:
Обратите внимание на то, что мы не вводим готовые цифры в эту таблицу, а указываем координаты ячеек нашего блока с исходными данными. Так, в поле «Ставка» мы указываем координаты ячейки, в которой будет вписываться вручную процентная ставка (C5) и делим её на 12 ; в поле «Кпер» указываются координаты ячейки, в которой будет вписываться срок кредитования (C6) ; в поле «Пс» – координаты ячейки в которой вписывается сумма кредита (C4). Так как сумма кредита у нас указывается со знаком минус, то перед координатой (C4) мы ставим знак минус.
После того как исходные данные будут введены, жмём кнопку «Ок». В результате мы видим в блоке расчетов точное значение ежемесячного аннуитетного платежа:
Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1 ). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.
Кстати, обратите внимание на значение функции, обозначенное на рисунке под номером 2. =ПЛТ(C5/12;C6;-C4). Да, да, это и есть те самые координаты, которые мы вводили в таблицу, выбрав функцию «ПЛТ». По сути, вы могли бы не проделывать всех тех сложных телодвижений, которые показаны на втором и третьем рисунках. Можно было просто вписать в строке формул то, что там сейчас вписано.
Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:
На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1 ). Так как она равна сумме аннуитетного платежа (ячейка C11 ) умноженной на общее количество месяцев кредитования (ячейка C6 ), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2 ). В результате мы получили значение 56 157 рублей .
Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12 ) надо отнять сумму кредита (ячейка C4 ). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей .
Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита ). Она рассчитывается так: общую сумму выплат (ячейка C12 ) делим на сумму кредита (ячейка C4 ), отнимаем единицу. затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12 ). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3% .
Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:
Расчет в Excel суммы кредита для заданного аннуитетного платежаВ чём «фишка» аннуитетной схемы погашения кредита? Правильно! Основная «фишка» в том, что заёмщик выплачивает кредит равными суммами на протяжении всего срока кредитования. С такой схемой очень удобно планировать свой бюджет. Например, вы готовы ежемесячно выделять на погашение кредита 5000 рублей. По вашим скромным подсчётам, такая нагрузка будет для вас не слишком обременительной. Естественно, у вас возникает закономерный вопрос: «А на какую сумму кредита я могу рассчитывать?» В общем, нам нужен новый кредитный калькулятор, у которого в исходных данных будет не сумма кредита, а величина аннуитетного платежа.
Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!
Итак, структура нового кредитного калькулятора почти не изменилась. Здесь также есть блок с исходными данными и блок с расчётами. Единственное изменение, это то, что в исходных данных мы вводим ежемесячный аннуитетный платёж, который готовы выплачивать, а в расчётах получаем сумму кредита, на которую мы можем рассчитывать. Собственно, она на нашем рисунке обведена и отмечена под номером 1 .
Чтобы рассчитать сумму ожидаемого кредита надо воспользоваться функцией ПС. предварительно кликнув по ячейке, в которой мы хотим видеть свой расчёт (в нашем калькуляторе это ячейка с координатой C11 ). Вызвать функцию ПС можно нажав на знакомую вам кнопку «fx». которая находится слева от строки формул. В появившемся окне выбираем «ПС» и жмём «Ок». В открывшейся таблице вводим следующие данные:
Жмём «Ок» и в ячейке С11 появилась сумма 53 422 руб. – именно на такой размер кредита может рассчитывать заёмщик, который готов на протяжении 12 месяцев ежемесячно выплачивать по 5000 руб.
Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2 ). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС .
Расчёт остальных показателей выполняется по такому же принципу, как и в предыдущем калькуляторе:
Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей. мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24. то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:
Кредитный калькулятор в Excel по расчету графика аннуитетных платежейДва предыдущих кредитных калькулятора очень удобны, но они выполняют краткие (общие) расчёты. А иногда заёмщику нужна расширенная информация – график ежемесячных аннуитетных платежей с детальной расшифровкой каждой выплаты (с указанием сумм, идущих на погашение процентов, и сумм, погашающих тело кредита ). В общем, сейчас мы сделаем в программе Excel ещё один кредитный калькулятор, который будет автоматически рассчитывать график аннуитетных платежей. Щёлкаем мышкой по рисунку:
Перед вами расширенная и доработанная версия нашего первого кредитного калькулятора (того, который рассчитывает размер ежемесячного аннуитетного платежа по кредиту). Здесь кроме стандартных блоков с исходными данными и расчётами, появилась таблица, в которой детально расписаны все наши будущие ежемесячные выплаты. Таблица имеет пять колонок:
Вот так легко и непринуждённо мы разработали кредитный калькулятор по расчёту графика аннуитетных платежей. Скачать его можно ссылке ниже:
Итак, друзья, теперь у вас есть целых три кредитных калькулятора по расчёту аннуитетных платежей, разработанных в программе Microsoft Excel. В следующей публикации мы расскажем о досрочном погашении аннуитетного кредита .
Если вы хотите расчитать платежи по кредиту (аннуитетные или дифференцированные), переплату и график платежей по месяцам в excel, то скачайте следующие xls-файлы:
Альтернатива использованию MS Excel - кредитный калькулятор on-line (функционал практически такой же, ничего качать на компьютер не нужно: все операции выполняются на нашем сайте).
Расскажем о файлах подробнее: дадим мини-инструкции.
Кредитный калькулятор в ExcelПользоваться файлом довольно удобно: вбиваете значения в верхние четыре поля (сумма, процентная ставка, срок в месяцах, дата получения - последнее нужно для определения точного графика) и умное детище Билла Гейтса тут же заполняет все графы таблицы актуальной информацией.
Простая функция для платежейВы и сами может сконстурировать подобный расчет - подробно это описано в статье про расчет по формуле аннуитета. Ничего сложного нет, но для удобства мы подготовили специальный файл.
Содержит всего одну функциональную ячейку:
Поменяйте значения на свои (вместо 14 - свою ставку, вместо 12 во втором случае - свой срок кредита в месяцах, вместо 100000 - свою сумму займа).
После смены данных достаточно нажать на "Энтер", чтобы получить результат:
Напоминаем, ссылки на оба xls-файла представлены в начале страницы.
Желаем успешного использования!
Ирина 2013-11-27 15:50:23
Спасибо большое. А как сделать поправку на высокосный /невысокосный год? из=за этого не бьются копейки, но все же. Всем кто спрашивал, почему ваши расчеты отличаются от формулы могу посоветовать сравнить даты выплат (это могут быть выходные и перенестись на след. рабочий, за счет этого период начисления процентов увеличиается ) и ваш банк может округлять сумму аннуитетного платежа, например, 4995 до 5000 (разница на основной долг кидается),
[Ответить] [Ответить с цитатой] [Отменить ответ]
Арман 2013-11-26 14:43:00
Здравствуйте!
Взял в банке кредит под залог квартиры на 5 лет. Сумма 4000$. Ежемесячный платеж 99$ (33 проценты и 66 основной долг). Кредит погасил все устроило, через какое то время в этом же банке решил взать кредит на сумму 17500$ сроком на 15 лет. Ежемесячный платеж составил 275$ но при этом я почти не гашу основной долг, процент расчитывают по формуле = ОстатокДолга*16%/360дней*30дней. Кредит плачу уже 76 месяцев, сумма основного долга составляет 14415
[Ответить] [Ответить с цитатой] [Отменить ответ]
swet2007@yandex.ru 2013-10-14 23:32:34
Здравствуйте! Решила рассчитать по, указанной Вами формуле аннуитетные платежи по ипотечному кредиту. Сумма платежа намного отличается от той, что я плачу в Банке. Не смогли бы Вы мне разъяснить. Сумма кредита 5800 тыс.руб. Срок погашения 19 лет. Ставка 8.5%. Спасибо.
[Ответить] [Ответить с цитатой] [Отменить ответ]
Admin 2013-10-15 22:37:12
Добрый день!
По-моему, тут недостаточно данных для расчета - вы учли первый взнос?
Напишите, пожалуйста, сколько получилось по калькулятору и сколько платите в реальности.
Также можете пользоваться онлайн-версией: http://biznes-kredit.info/analiz/kalkulator-platez hi.html
[Ответить] [Ответить с цитатой] [Отменить ответ]
Домашняя бухгалтерия 5.4.0.91
"Домашняя бухгалтерия" предназначена для ведения учета личных финансов.
- Shareware | 4,602 Скачали
AceMoney Lite 4.36.1
AceMoney Lite - личный бесплатный финансовый помощник
- Freeware | 3,749 Скачали
Simple Home Budget 1.9.3.1
Абсолютно простая и доступная в использовании домашняя бухгалтерия.
- Shareware | 2,207 Скачали
Персональные финансы 3.1.6
Кроссплатформенная программа (Windows, Linux, Mac OS X) для ведения личной бухгалтерии. Журнал операций. Планирование. Работа с долгами. Отчеты. Анали
- Shareware | 1,112 Скачали
MoneyTracker - Домашняя бухгалтерия 1.0
Удобная и многофункциональная программа для учета личных и семейных финансов
- Shareware | 1,296 Скачали
Чтобы создать обратную ссылку на страницу с программой Ипотечный калькулятор, скопируйте приведенный ниже код и вставьте его на свою страницу. За каждого прошедшего по данной ссылке посетителя Ипотечный калькулятор получит 10 баллов рейтинга.
Альтернатива платному софту
Самый полный каталог бесплатных программ для компьютера на русском языке
В каталоге выкладываются бесплатные программы со ссылками на официальные сайты разработчиков ПО, которые можно скачать бесплатно и без регистрации
Программы обновляются ежедневно и по ссылке "Скачать бесплатно. " доступна только последняя версия
По состоянию на 07.09.2017 каталог содержит 2715 файл(а)(ов) в рубрике - бесплатные программы на русском языке, альтернатива платному софту.
Дюжина самых необходимых бесплатных программ для Вашего компьютера:
На момент публикации программа Ипотечный калькулятор - была проверена антивирусами Dr.Web, KAV и NOD32 на предмет наличия вредоносного кода.
Программа безопасна для конечного пользователя.
В целях безопасности Вашего компьютера, при скачивании, все же проверяйте указанную программу на наличие в ней вирусов, так как они могут появиться в новых версиях.
Наш сайт не несёт ответственности за информацию, которая указана разработчиком в описании программы.
Если данная программа перестала быть бесплатной!
Администрация сайта благодарит Вас за посещение нашего ресурса. Не забудьте наш адрес: RusProgram.3dn.ru Приятного Вам времени суток.
Это интересно. Рекомендуем посмотреть, быть может даже и купить
Горячая лента рекламы в Интернете
Бесплатные программы на русском языке
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.
На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel. Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.
Читайте также: Как рассчитать кредит в Excel самому?
Первый кредитный калькулятор в Excel можно скачать по ссылке .
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Можно воспользоваться кредитным калькулятором Сбербанка онлайн.
На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Разработанный ипотечный калькулятор является универсальным, так как подходит включает в себя расчет кредита с учетом комиссий, страховки и графики сравнения кредитов до и после досрочных погашений.
Данный калькулятор идеально подходит для расчета ипотечного кредита и эмуляции наличия досрочных погашений.
Построенный при расчете график совпадает с моим графиком платежей.
Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone. В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.
Другой кредитный калькулятор в Excel можно скачать по данной ссылке. Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами. Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно. В графике получаются отрицательные суммы.
Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.