Repeater-zone.ru

ПК Репитер
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Кредитный калькулятор в Excel с равными платежами

Кредитный калькулятор в Excel с равными платежами

В данном уроке будем создавать кредитный калькулятор Аннуитета (оплата кредита равными платежами) в Excelе для расчета по таких параметров как:

  • ежемесячный платеж;
  • сумму оплаты за пользованием кредита;
  • эффективную ставку по кредиту.

Важно понимать, что данные, которые рассчитаем, будут близки к банковским расчетам, но все же могут незначительно отличаться.

Шаг 1. Создаем таблицу значений

В новом документе Excel создаем таблицу с данными, которые будем использовать для расчета:

  • Сумма кредита;
  • Процентная ставка (годовая);
  • Ежемесячная комиссия;
  • Единоразовая комиссия;
  • Срок кредита в месяцах.

Ячейки для ввода данных обозначим желтым.

Данные которые будем рассчитывать:

  • Ежемесячный платеж;
  • Сумма переплаты по кредиту;
  • Процент переплаты;
  • Эффективнаяставка.

Шаг 2. Рассчитываем ежемесячный платеж

Для того, чтобы рассчитать ежемесячный платеж используем функцию «ПЛТ», она находится в категории «Финансовые».

Аргументы функции «ПЛТ»

  • Ставка — Выбираем ячейку процентной ставки и делим ее на 12. Это связано с тем, что процентную ставку указываем годовую, а платеж мы рассчитываем ежемесячный
  • Кпер — срок кредитования;
  • ПС — сумма кредита, обязательно ставим знак «-» перед значением. Так как в параметрах есть Единоразовая комиссия Сумма долга = Сумма кредита + Сумма кредита * Единоразовою комиссию. Все кредитные учреждения Единоразовою комиссию включают в основной долг и насчитывают на них годовую процентную ставку.

После использования формулы расчета ежемесячного платежа по аннуитету «ПЛТ» с учетом «Единоразовой комиссии», остается учесть еще ежемесячную комиссию. Таким образом, в строке формулы к функции добавляем расчет суммы ежемесячной комиссии.

Шаг 3. Расчет оплаты за кредит.

Расчет суммы оплаты по кредиту производит путем умножение ежемесячного платежа по кредиту на срок кредита и вычитаем основную сумму кредита.

Процент переплаты по кредиту рассчитывается как сумма оплаты деленная на сумму кредита и умноженная на 100.

Шаг 4. Расчет эффективной ставки по кредиту

Эффективная ставка по кредиту включает в себя все проценты и все платежи по кредиту:

  • Процентная ставка;
  • Единоразовая комиссия;
  • Ежемесячная комиссия.

Для расчет эффективной ставки используем функцию «СТАВКА» в категории функций «ФИНАНСОВЫЕ».

  • Кпер — срок кредитования;
  • Плт — рассчитанный ежемесячный платеж, который включает в себя все проценты и комиссии;
  • Пс — сумма кредита, обязательно со знаком «-«.

После использования функции «СТАВКА» необходимо в строке формулы умножить данную функцию на 12, чтобы вычислить годовую эффективную ставку.

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

Составление элементарных формул

Проще всего в Эксель создаются формулы из четырех арифметических действий — сложения, вычитания, умножения и деления. При этом данные для вычислений должны находиться в разных ячейках/столбцах/строках.

  1. Для начала нужно выбрать свободную ячейку и напечатать в ней знак “=”. Для программы это будет сигналом, что в этой ячейке будет производиться расчет по формуле.

Составление элементарных формул в Эксель

Другой способ перевести ячейку в режим формулы — щелкнуть по ней левой кнопкой мыши, а знак равенства напечатать в строке формул.

Составление элементарных формул в Эксель

Эти 2 способа выше дублируют друг друга и можно выбрать тот, который больше по душе.

Составление элементарных формул в Эксель

Кредитный калькулятор для кредита с досрочным погашением

Этот вариант рассмотрим, когда вы будете не удовлетворены суммами платежей или сроками погашения и вам захочется закрыть кредит, досрочно, используя дополнительные платежи при наличии свободных средств.

Читать еще:  Как отключить NVIDIA GeForce Experience в Windows 10

Для реализации этого добавляем дополнительный столбик «Доп.платёж» в котором будут указываться сумма платежей уменьшающий остаток кредита. Но у банков есть два варианта развития событий:

  • во-первых, сокращения суммы выплат по кредиту на каждый месяц;
  • во-вторых, уменьшения срока выплат.

Для лучшей наглядности будем рассматривать каждый случай в отдельности.

Рассмотрим расчеты, когда происходит погашения кредита раньше срока, в этом случае будем использовать функционал функции ЕСЛИ и проверим, достигли ли мы нулевой задолженности раньше указанного срока: Credit calendar 9 Как создать кредитный калькулятор в Excel? В другом случае, когда у вас происходит уменьшение суммы выплат по кредиту, формула пересчитывает ваш ежемесячный платёж сразу же после внесённого дополнительного платежа. Credit calendar 10 Как создать кредитный калькулятор в Excel?

Недостатки калькулятора

  1. Нет учета возможное изменение процентной ставки во время выплат кредита
  2. Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
  3. Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
  4. Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
  5. Расчет производится для процентой ставки с 2мя знаками после запятой.

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

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.

Простые формулы

Все записи формул начинаются со знака равенства (=). Чтобы создать простую формулу, просто введите знак равенства, а следом вычисляемые числовые значения и соответствующие математические операторы: знак плюс (+) для сложения, знак минус () для вычитания, звездочку (*) для умножения и наклонную черту (/) для деления. Затем нажмите клавишу ВВОД, и Excel тут же вычислит и отобразит результат формулы.

Например, если в ячейке C5 ввести формулу =12,99+16,99 и нажать клавишу ВВОД, Excel вычислит результат и отобразит 29,98 в этой ячейке.

Пример простой формулы

Формула, введенная в ячейке, будет отображаться в строке формул всякий раз, как вы выберете ячейку.

Важно: Хотя существует функция СУММ, функция ВЫЧЕСТЬ не существует. Вместо этого используйте в формуле оператор минус (-). Например, =8-3+2-4+12. Вы также можете использовать знак «минус» для преобразования числа в его отрицательное значение в функции СУММ. Например, в формуле =СУММ(12;5;-3;8;-4) функция СУММ используется для сложить 12, 5, вычесть 3, сложить 8 и вычесть 4 в этом порядке.

Читать еще:  Формула деления в Экселе: 6 простых вариантов

Использование автосуммирования

Формулу СУММ проще всего добавить на лист с помощью функции автосуммирования. Выберите пустую ячейку непосредственно над или под диапазоном, который нужно суммировать, а затем откройте на ленте вкладку Главная или Формула и выберите Автосумма > Сумма. Функция автосуммирования автоматически определяет диапазон для суммирования и создает формулу. Она также работает и по горизонтали, если вы выберете ячейку справа или слева от суммируемого диапазона.

Примечание: Функция автосуммирования не работает с несмежными диапазонами.

Автосуммирование по вертикали

В ячейке B6 показана формула автосуммирования =СУММ(B2:B5)

На рисунке выше показано, что функция автосуммирования автоматически определила ячейки B2: B5 в качестве диапазона для суммирования. Вам нужно только нажать клавишу ВВОД для подтверждения. Если вам нужно добавить или исключить несколько ячеек, удерживая нажатой клавишу SHIFT, нажимайте соответствующую клавишу со стрелкой, пока не выделите нужный диапазон. Затем нажмите клавишу ВВОД для завершения задачи.

Руководство по функции Intellisense: СУММ(число1;[число2];. ) Плавающий тег под функцией — это руководство Intellisense. Если щелкнуть имя функции или СУММ, изменится синяя гиперссылка на раздел справки для этой функции. Если щелкнуть отдельные элементы функции, их представительные части в формуле будут выделены. В этом случае будет выделен только B2:B5, поскольку в этой формуле есть только одна ссылка на число. Тег Intellisense будет отображаться для любой функции.

Автосуммирование по горизонтали

В ячейке D2 показана формула автосуммирования =СУММ(B2:C2)

Дополнительные сведения см. в статье о функции СУММ.

Избегание переписывания одной формулы

После создания формулы ее можно просто копировать в другие ячейки, а не создавать ту же формулу. Вы можете скопировать формулу или использовать маркер заполнения для копирования формулы в смежные ячейки.

Например, когда вы копируете формулу из ячейки B6 в ячейку C6, в ней автоматически изменяются ссылки на ячейки в столбце C.

При копировании формулы ссылки на ячейки обновляются автоматически

При копировании формулы проверьте правильность ссылок на ячейки. Ссылки на ячейки могут меняться, если они являются относительными. Дополнительные сведения см. в статье Копирование и вставка формулы в другую ячейку или на другой лист.

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

Обычно Microsoft Excel сохраняет всю книгу как персональный шаблон. Но иногда вам может просто потребоваться часто повторно использовать определенный выбор. По сравнению с сохранением всей книги в виде шаблона, Kutools for Excel предоставляет симпатичный обходной путь Авто Текст Утилита для сохранения выбранного диапазона как записи автотекста, в которой могут оставаться форматы ячеек и формулы в диапазоне. И тогда вы сможете повторно использовать этот диапазон одним щелчком мыши. Полнофункциональная бесплатная 30-дневная пробная версия!

калькулятор процентов для автоматического текста объявления

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Читать еще:  Что делать, если Google Календарь не работает

стрелка синий правый пузырьСоздайте калькулятор процентов на погашение кредита в книге и сохраните его как шаблон Excel.

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

Здесь я приведу пример, чтобы продемонстрировать, как легко рассчитать проценты по погашению кредита. Я ссудил в банке 50,000 6 долларов, процентная ставка по кредиту составляет 10%, и я планирую возвращать ссуду в конце каждого месяца в ближайшие XNUMX лет.

Шаг 1. Подготовьте таблицу, введите заголовки строк, как показано на следующем снимке экрана, и введите исходные данные.

Шаг 2: Рассчитайте ежемесячный / общий платеж и общие проценты по следующим формулам:

(1) В ячейке B6 введите =PMT(B2/12,B3*12,B4,0,IF(A5=»End of Period»,0,1)) , и нажмите Enter ключ;

(2) В ячейке B7 введите = B6 * B3 * 12 , и нажмите Enter ключ;

(3) В ячейке B8 введите = B7 + B4 , и нажмите Enter ключ.

лента для заметокФормула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем!
Подробнее . Бесплатная пробная версия

Шаг 3: Отформатируйте таблицу так, как вам нужно.

(1) Выберите диапазон A1: B1, объедините этот диапазон, нажав Главная > Слияние и центр, а затем добавьте цвет заливки, нажав Главная > Цвет заливки и укажите цвет выделения.

(2) Затем выберите Range A2: A8 и залейте его, щелкнув Главная > Цвет заливки и укажите цвет выделения. См. Снимок экрана ниже:

Шаг 4: Сохраните текущую книгу как шаблон Excel:

  1. В Excel 2013 щелкните значок Файл > Сохраните > Компьютер > Приложения;
  2. В Excel 2007 и 2010 щелкните значок Файл/Кнопка офиса > Сохраните.

Шаг 5. В появившемся диалоговом окне «Сохранить как» введите имя этой книги в поле Имя файла поле, щелкните Сохранить как поле и выберите Шаблон Excel (* .xltx) из раскрывающегося списка, наконец, нажмите Сохраните кнопку.

Как Создать Кредитный Калькулятор В Excel?

Копируем его из инструкции и вставляем на белый лист редактора, где мигает курсор. Затем нажимаем кнопку «Сохранить» и закрываем редактор (жмем на крестик). Далее в «Редактор Макросов» необходимо внести стандартный код калькулятора. В открывшемся окне пишем название макроса «сalculator», устанавливаем место нахождения «Эта книга» и жмем «Создать». Макрос вводится через окно редактора VBA – вкладка файл → разработчик → кнопка Visual Basic.

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector