Как рассчитать ежемесячную сумму информатика

8

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

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

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

1. Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
2. При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
2. Составим график погашения кредита. Пока пустой.
3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.

Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:

• взяли кредит 500 000 руб.;
• вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
• переплата составила 184 881, 67 руб.;
• процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
• Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

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

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

Практическая работа по информатике на тему "Математические вычисления в MS Excel"

Расширить представление о возможностях табличного процессора MS Excel. Способствовать развитию творческого мышления.

Просмотр содержимого документа
«Практическая работа по информатике на тему «Математические вычисления в MS Excel»»

ПРАКТИЧЕСКАЯ РАБОТА №5

«MS EXCEL. Ввод формулы. Вычисления по формулам».

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

Исходные данные представлены на рис. 1, результаты работы — на рис. 2.

Порядок работы

Запустите редактор электронных таблиц MS Excel и создайте новую электронную книгу.

Создайте таблицу расчета заработной платы по образцу (рис. 6). Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.

Примечание. Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).

Рис. 1. Исходные данные для Задания 1

Произведите расчеты во всех столбцах таблицы.

При расчете «Премия» используется формула Премия = Оклад х % Премии; для этого в ячейке D5 наберите формулу =С5*$D$4 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка с константой будет напоминать вам, что следует установить абсолютную адресацию (набором символов $ с клавиатуры.

Формула для расчета «Всего начислено»: Всего начислено = Оклад + Премия.

При расчете «Удержания» используется формула: Удержание = Всего начислено х % Удержания; для этого в ячейке F5 наберите формулу =Е5*$F$4.

Формула для расчета столбца «К выдаче»: К выдаче = Всего начислено – Удержания.

Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Формулы/Другие функции/Статистические/СРЗНАЧ) или Формулы/Автосумма.

Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата за октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой контекстного меню ярлычка Переименовать, вызываемого правой кнопкой мыши.

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

Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить или Скопировать/Переместить в конец контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окошке Создать копию.

Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

Присвойте скопированному листу название «Зарплата за ноябрь». Исправьте название месяца в названии таблицы. Измените, значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.

Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата», выделив колонку «Всего начислено» (Главная/Ячейки/Вставить/Вставить столбцы на лист). Можно воспользоваться командой контекстного меню, вызываемого правой кнопкой мыши. Рассчитайте значение доплаты по формуле Доплата = Оклад х % Доплаты. Значение доплаты примите равным 5%.

Измените формулу для расчета значений колонки «Всего начислено»: Всего начислено = Оклад + Премия + Доплата.

Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 18000 и 20000 — зеленая заливка и темно-зеленый текст; меньше 18000 — красный текст; больше 20000 — светло-красная заливка (Главная/Условное форматирование).

Проведите сортировку по фамилиям в алфавитном порядке от А до Я (выделите фрагмент с 5 по 18 строки таблицы — без итогов, выберите меню Данные/Сортировка, сортировать по — Столбец В).

Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Рецензирование/Примечание), при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид расчета заработной платы за ноябрь приведен на рис. 2.

Рис.2. Конечный вид зарплаты за ноябрь

Защитите лист «Зарплата ноябрь» от изменений (Рецензирование/Защитить лист). Убедитесь, что лист защищен и невозможно удаление данных. Снимите защиту листа (Рецензирование/Снять защиту листа).

Задание 2. Сделать примечания к двум-трем ячейкам.

Задание 3. Выполнить условное форматирование оклада и премии за ноябрь месяц: меньше 5000 р. — красный текст; от 5000 до 15000 р. — желтая заливка и темно-желтый цвет; свыше 15000 р. — светло-красная заливка.

Задание 4. Рассчитать зарплату за декабрь и построить диаграмму.

Порядок работы:

Скопируйте содержимое листа «Зарплата за ноябрь» на новый лист электронной книги. Назовите скопированный лист «Зарплата за декабрь». Исправьте название месяца в ведомости на декабрь.

Измените значение Премии на 46 %, Доплаты — на 8 %. Программа произведет пересчет формул (рис. 3).

Рис. 3. Ведомость зарплаты за декабрь

По данным таблицы «Зарплата декабрь» постройте гистограмму доходов сотрудников.

Практическая работа по информатике «Расчет заработной платы с помощью электронных таблиц»

Разработайте электронную таблицу для расчета заработной платы сотрудников временного творческого коллектива (ВТК), создавшего Черноморское отделение Арбатовской конторы "Рога и копыта".

Трудовой вклад каждого сотрудника оценен коэффициентом трудового участия (КТУ). КТУ каждого сотрудника приведен в Основной таблице. В этой же таблице указано число детей каждого сотрудника, которое используется для расчета подоходного налога.

Общая сумма заработной платы ВТК составляет 100 000 руб. Каждому сотруднику начисляется из этой суммы часть в сответетствии с коэффициентом трудового участия (КТУ). Например, Бендеру О. И. — 0, 25 от общей суммы или 25%.

Естественно, сумма КТУ всех участников коллектива должна быть равна 1 или 100%.

Из начисленной зарплаты сотрудники должны заплатить следующие налоги:

1). отчисление в пенсионный фонд, составляющее 1, 5% от начисленной заработной платы;

2). отчисление в профсоюз, составляющее 1% от начисленной заработной платы

3). подоходный налог, составляющий 13% от начисленной заработной платы за вычетом некоторых сумм, не облагаемых подоходным налогом.

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

Задания

1. Введите заголовок таблицы "Расчет заработной платы сотрудников ВТК "Рога и копыта". Создайте Вспомогательную таблицу, в которую введите все исходные данные (См. рис. 1). Затем создайте Основную таблицу, в которой будут производиться расчеты. Оформите ваши таблицы точно также как на рисунке.

3. В столбце КТУ Основной таблицы в ячеку B14 введите формулу для суммирования КТУ всех сотрудников — результат суммирования должен быть равен 1. Это необходимо для проверки правильности начисления зарплаты сотрудникам из общей суммы.

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

и затем копируйте эти формулы в другие ячейки столбца!

4. Рассчитайте начисленные зарплаты в столбце Итого начислено. Для проверки правильности распределения денег между сотрудниками, в ячейке D14 организуйте подсчет общей суммы заработной платы, как суммы начисленных зарплат сотрудников. Она должна равняться сумме из ячейки A4.

5. Рассчитайте отчисления в Пенсионный фонд и в Профсоюз (размеры отчислений во Вспомогательной таблице).

6. Вычислите суммы, не облагаемые подоходным налогом по формуле:

ПФ — отчисление в пенсионный фонд (должно быть рассчитано в Основной таблице)

П — отчисление в профсоюз (должно быть рассчитано в Основной таблице)

ЧД — число детей до18 лет, находящихся на содержании сотрудника (в Основной таблице)

МЗП — минимальная заработная плата, установленная правительством России. ( ее размер во Вспомогательной таблице)

7. Затем рассчитайте величину Подоходного налога для каждого сотрудника

(ИН — СНОН) * СПН / 100

ИН — Итого начислено

СНОН — Сумма, не облагаемая подоходнным налогом

СПН — Ставка подоходного налога (ее размер указан во Вспомогательной таблице)

8. Рассчитайте сколько Всего удержано налогов с каждого сотрудника

ПФ — Отчисления в пенсионный фонд (должно быть рассчитано в Основной таблице)

П — Отчисления в профсоюз (должно быть рассчитано в Основной таблице)

ПН — Подоходный налог (должно быть рассчитано в Основной таблице)

9. Рассчитайте Сумму к выдаче для каждого сотрудника:

ИН — Итого начислено

ВУ — Всего удержано

Для проверки правильности расчетов в ячейке J14 запишите формулу для подсчета суммы блока I7:J13 — она должна равняться общей сумме заработной платы ВТК из ячейки A4.

10. В ячейках J15, J16, J17 с помощью соответствующих функций найдите соответственно Максимальную зарплату, Минимальную зарплату и Среднюю зарплату в коллективе.

11. В режим е автофильтра найдите всех сотрудников у кого

заработная плата меньше 15 000 руб, но больше 5 000 руб.

12. Постройте круговую диаграмму под названием "КТУ сотрудников ВТК" и разместите ее на листе с таблицей. Введите на диаграмме подписи данных с величинами коэффициентов трудового участия сотрудников в %.

13. Сохраните созданную вами таблицу с диаграммой в файле расчет — зарплаты. xls в вашей рабочей папке.

-80%

Формула для расчета аннуитетного платежа в Excel

Платежи по кредитам удобнее и быстрее рассчитывать с Microsoft Office Excel. На ручное вычисление уходит гораздо больше времени. В данной статье речь пойдет об аннуитетных платежах, особенностях их расчета, преимуществах и недостатках.

Что такое аннуитетный платеж

Способ ежемесячного погашения кредита, при котором вносимая сумма не меняется в течение всего времени кредитования. Т.е. человек по определенным числам каждого месяца вносит конкретную сумму денег до тех пор, пока полностью не погасит кредит.

Причем проценты по кредиту уже включены в общую сумму, вносимую в банк.

Классификация аннуитета

Аннуитетные платежи можно разделить на следующие виды:

1. Фиксированные. Платежи, которые не меняются, имеют фиксированную ставку вне зависимости от внешних условий.
2. Валютные. Возможность смены размера платежа при падении или росте курса валют.
3. Индексируемые. Платежи, зависящие от уровня, показателя инфляции. В период кредитования их размер часто меняется.
4. Переменные. Аннуитет, который может смениться в зависимости от состояния финансовой системы, инструментов.

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

Преимущества и недостатки аннуитетных платежей

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

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

Без недостатков не обошлось:

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

Из чего состоит платеж по кредиту?

Аннуитетный платеж имеет следующие составляющие части:

• Проценты, переплачиваемые человеком при погашении ссуды.
• Часть суммы основной задолженности.

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

Основная формула аннуитетного платежа в Excel

Как и говорилось выше, в Microsoft Office Excel можно работать с различными типами платежей по кредитам и ссудам. Аннуитет не является исключением. В общем виде формула, с помощью которой можно быстро вычислить аннуитетные взносы, выглядит следующим образом:

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

Основные значения формулы расшифровываются так:

• АП – аннуитетный платеж (название сокращено).
• О – размер основного долга заемщика.
• ПС – процентная ставка, выдвигаемая ежемесячно конкретным банком.
• С – число месяцев, на протяжении которых длится кредитование.

Для усвоения информации достаточно привести несколько примеров использования данной формулы. О них пойдет речь далее.

Примеры использования функции ПЛТ в Excel

Приведем простое условие задачи. Необходимо посчитать ежемесячный кредитный платеж, если банк выдвигает процент в размере 23%, а общая сумма составляет 25000 рублей. Кредитование продлится на протяжении 3-х лет. Задача решается по алгоритму:

1. Составить общую таблицу в Excel по исходным данным.

1. Активировать функцию ПЛТ и ввести для нее аргументы в соответствующее окошко.
2. В поле «Ставка» прописать формулу «В3/В5». Это и будет процентная ставка по взятому кредиту.
3. В строке «Кпер» написать значение в виде «В4*В5». Это будет общее количество выплат за весь срок кредитования.
4. Заполнить поле «Пс». Здесь нужно указать первоначальную сумму, взятую в банке, прописав значение «В2».

1. Удостовериться, что после нажать «ОК» в исходной таблице посчиталось значение «Ежемесячный платеж».

Дополнительная информация! Отрицательное число свидетельствует о том, что заемщик расходует деньги.

Пример расчета суммы переплаты по кредиту в Excel

В этой задаче надо подсчитать сумму, которую переплатит человек, взявший кредит 50000 рублей по процентной ставке 27% на 5 лет. Всего в год заемщик производит 12 выплат. Решение:

1. Составить исходную таблицу данных.

1. Из общей суммы выплат отнять первоначальный размер суммы по формуле «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Ее надо вставить в строку формул сверху главного меню программы.
2. В итоге в последней строке созданной таблички появится сумма переплат. Заемщик переплатит 41606 рублей сверху.

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

Задача с таким условием: клиент зарегистрировал счет в банке на 200000 рублей с возможностью ежемесячного пополнения. Нужно посчитать количество платежа, который человек должен вносить каждый месяц, чтобы через 4 года на его счету оказалось 2000000 рублей. Ставка составляет 11%. Решение:

1. Составить табличку по исходным данным.

1. В строку ввода Эксель ввести формулу «=ПЛТ(B3/B5;B6*B5;-B2;B4)» и нажать «Enter» с клавиатуры. Буквы будут отличаться в зависимости от ячеек, в которых размещена таблица.
2. Проверить, что сумма взноса автоматически посчиталась в последней строке таблицы.

Обратите внимание! Таким образом, чтобы на счету клиенту через 4 года накопилось 2000000 рублей по ставке 11%, ему нужно каждый месяц вносить по 28188 рублей. Минус в сумме свидетельствует о том, что клиент несет убытки, отдавая деньги в банк.

Особенности использования функции ПЛТ в Excel

В общем виде данная формула записывается следующим образом: =ПЛТ(ставка; кпер; пс; [бс]; [тип]). У функции есть следующие особенности:

1. Когда рассчитываются ежемесячные взносы, в рассмотрение берется исключительно годовая ставка.
2. Указывая размер процентной ставки, важно сделать перерасчет, опираясь на число взносов за год.
3. Вместо аргумента «Кпер» в формуле указывается конкретное число. Это период выплат по задолженности.

Расчет оплаты

В общем виде оплата по аннуитету рассчитывается в два этапа. Чтобы разбираться в теме, каждый из этапов необходимо рассмотреть по отдельности. Об этом пойдет речь далее.

Этап 1: расчет ежемесячного взноса

Чтобы в Excel посчитать сумму, которую нужно вносить каждый месяц по кредиту с фиксируемой ставкой, необходимо:

1. Составить исходную таблицу и выделить ячейку, в которую надо выводить результат и нажать по кнопке «Вставить функцию» сверху.

1. В списке функций выбрать «ПЛТ» и нажать «ОК».

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

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

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

Этап 2: детализация платежей

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

1. Составить исходную таблицу на 24 месяца.

1. Поставить курсор в первую ячейку таблицы и вставить функцию «ОСПЛТ».

1. Заполнить аргументы функции аналогичным образом.

1. При заполнении поля «Период» нужно сослаться на первый месяц в табличке, указав ячейку 1.

1. Проверить, что первая ячейка в графе «Выплата по телу кредита» заполнилась.
2. Чтобы заполнить все строки первого столбца, необходимо растянуть ячейку до конца таблицы

1. Выбрать функцию «ПРПЛТ» для заполнения второго столбца таблицы.
2. Заполнить все аргументы в открывшемся окошке в соответствии со скриншотом ниже.

1. Рассчитать общую ежемесячную выплату, сложив значения в двух предыдущих столбиках.

1. Чтобы посчитать «Остаток к выплате», надо сложить процентную ставку с выплатой по телу кредита и растянуть до конца таблички, чтобы заполнить все месяцы кредитования.

Дополнительная информация! При расчете остатка на формулу надо навешивать знаки долларов, чтобы она не съехала при растягивании.

Расчет аннуитетных платежей по кредиту в Excel

За вычисление аннуитета в Excel отвечает функция ПЛТ. Принцип вычисления в общем виде заключается в выполнении следующих шагов:

1. Составить исходную таблицу данных.
2. Построить график погашения долга для каждого месяца.
3. Выделить первую ячейку в столбике «Платежи по кредиту» и ввести формулу расчета «ПЛТ ($В3/12;$В$4;$В$2)».
4. Получившееся значение растянуть для всех столбцов таблички.

Расчет в MS Excel погашение основной суммы долга

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

Вычисление остатка суммы основного долга (при БС=0, тип=0)

Предположим, что кредит на 100000 рублей берется на 10 лет под 9%. Необходимо рассчитать сумму основного долга в 1 месяце 3-го года. Решение:

1. Составить таблицу данных и вычислить ежемесячный платеж по приведенной выше формуле ПС.
2. Рассчитать долю платежа, необходимую для погашения части долга, по формуле «=-ПМТ-(ПС-ПС1)*ставка=-ПМТ-(ПС +ПМТ+ПС*ставка)».
3. Посчитать сумму основного долга за 120 периодов по известной формуле.
4. Используя оператор ПРПЛТ найти количество процентов, выплаченных за 25 месяц.
5. Проверить результат.

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

Такой расчет лучше сделать простым способом. Нужно использовать следующие формулы для вычисления суммы в промежутке за два периода:

• =«-БС(ставка; кон_период; плт; [пс]; [тип]) /(1+тип *ставка)».
• = «+ БС(ставка; нач_период-1; плт; [пс]; [тип]) /ЕСЛИ(нач_период =1;1; 1+тип *ставка)».

Обратите внимание! Буквы в скобках заменяются конкретными значениями.

Досрочное погашение с уменьшением срока или выплаты

Если потребуется уменьшить срок кредитования, то придется производить дополнительные вычисления с помощью оператора ЕСЛИ. Так можно будет контролировать нулевой баланс, который не должен быть достигнут раньше окончания сроков выплаты.

Досрочное погашение с уменьшением срока

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

Уменьшение выплат кредитования

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

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

1. Ввести числа месяца, по которым вносятся платежи, и указать их количество.
2. Проконтролировать отрицательные и положительные суммы. Отрицательные предпочтительнее.
3. Посчитать дни между двумя датами, в которые вносились деньги.

Расчет периодического платежа в MS Excel. Срочный вклад

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

Заключение

Таким образом, аннуитетные платежи проще, быстрее и эффективнее рассчитывать именно в Эксель. За их вычисление отвечает оператор ПЛТ. С подробными примерами можно ознакомиться выше.