Excel функция ставка (rate)

Формула для расчета процентной части

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

Для примера возьмем расчет кредита в полмиллиона рублей на два года с процентной ставкой 12% и ежемесячной комиссией 1% (снимается ежемесячно со всей суммы) при аннуитетных платежах. Основная задолженность рассчитывается в разнице аннуитетных платежей и процентов. Сумма процентов будет произведением месячной ставки на остаток долга, который равен разнице от остатка предыдущего периода и суммы основного долга в прошлом периоде.

В итоге по расчетам нужно будет вернуть банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда эффективная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.

Cравнение силы роста простых и сложных процентов в excel

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

1. Сумма вклада составляет 100000 рублей.
2. Период действия договора – 5 лет.
3. Фиксированная ставка – 14,5%.
4. Сложные проценты: ежегодное увеличение ставки на 0,5%, начальное значение – 13,5%.

Вид таблицы данных:

Для расчетов используем следующую формулу:

С помощью функции ЕСЛИ сравниваем значения, которые вернули функция БС (для простых процентов) и БЗРАСПИС (для сложных процентов) и выводим результат с пояснением.

На самом деле, различие несущественное при пятилетнем сроке:

Однако, если по истечению 5 лет договор будет продлен с теми же условиями (увеличение ставки на 0,5% ежегодно), второй вариант окажется более выгодным.

Аннуитет. расчет периодического платежа в ms excel. срочный вклад

02 февраля 2022 г.

Рассчитаем в MS EXCEL сумму регулярного платежа в случае накопления определенной суммы. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу регулярных платежей с расшифровкой накопленной суммы и начисленных процентов.

Аннуитетная схема предусматривает пополнение вклада периодическими равновеликими платежами (например, ежемесячными). Такой равновеликий платеж называется аннуитет. В аннуитетной схеме предполагается неизменность процентной ставки по кредиту в течение всего периода накопления.

Базовая формула будущей стоимости в Excel

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

• Периодическая процентная ставка (оценивать): С2
• Количество периодов (Например): С3
• Сумма к оплате (пмт): С4

Допустим, вы собираетесь делать ежегодный платеж в размере 1000 долларов в течение 10 лет с годовой процентной ставкой 6%. Предполагается, что это обычный аннуитет, при котором все платежи производятся в конце года.

Чтобы найти будущее значение, настройте функцию FV следующим образом:

=ФВ(С2, С3, С4)

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

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

=ФВ(С2, С3, -С4)

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

Чтобы не утруждать себя расчетами вручную, попробуйте сделать это при помощи таблицы Excel. Там есть специальная функция под названием ПЛТ. Для расчетов следует создать новую таблицу и ввести строку в любой ячейке. Если вам выдали кредит в сумме 30000 руб., под 18% годовых на 36 месяцев, необходимо ввести в ячейку вот такое выражение.

= ПЛТ(18%/12; 36; -30000)

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

Можно внести запись и в таком виде:

=ПЛТ(0,015; 36; -30000)

Получается 1084,57 рублей.

Если лень вбивать формулу — просто скачайте готовый файл с формулой аннуитета или же обратитесь к кредитному калькулятору.

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

Какие данные нужны

Формула состоит из трёх компонентов:

Второй и третий достаточно ясны:

2. Значения — сколько денег потрачено на инвестиции и сколько возвращается. 3. Даты — когда именно средства приходят или уходят.

Первый компонент формулы — ставка дисконтирования. Обычно деньги со временем обесцениваются, и на одну и ту же сумму в будущем можно купить меньше, чем сейчас. Это значит, что нынешние 100 рублей равны, допустим, 120 рублям в 2025 году.

Если инвестор хочет не просто сохранить деньги, но и заработать, ему нужно учесть постепенное обесценивание валюты. Есть много способов это сделать, но самый простой — посмотреть доходность по надёжным облигациям: к примеру Параметры облигации федерального займа SU26234RMFS3 / Московская биржа , ОФЗ 26234 — 4,5%.

Работа в Excel

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

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

Если размер кредита равняется 100 000 рублей, а срок — 1 год, при этом номинальная ставка 18%, ежемесячно клиент будет выплачивать займ по ставке 18%/12 = 1,5%. До 9168 рублей клиент будет выплачивать ежемесячно. При работе с таблицей нужно учесть, что отрицательные денежные потоки обозначаются с минусом, а положительные — с плюсом. Это объясняют специалисты тем, что финансовый инструмент рассматривается «глазами инвестора».

Пример эффективной ПС

В программе Excel потенциальному заемщику предоставляется возможность предварительно определить ЭПС с дополнительными сборами и комиссиями, а также без них. К примеру, кредит взят на 12 месяцев в размере 150 000 рублей. Номинальная годовая ставка равняется 18%. В таблице указывают следующие выплаты:

• Займ. Так как в примере нет сборов, определяется ЭПС с помощью функции «Эффект». Вызывается «Мастер функций». В разделе «Финансовые» находится соответствующая функция.
• Аргументы. Размер номинальной ставки необходимо взять из договора. В примере это 18% или 0,18. Число периодов равняется 12 месяцев. Размер ставки будет равняться 19,56%.

Если единовременная комиссия составляет 1% от займа в 150 000 рублей, в денежном эквиваленте значение первого показателя будет равняться 1500 рублей. Заемщик получит на руки 148 500 рублей. В таблицу вносится последняя сумма со знаком минус и ежемесячные выплаты со знаком плюс. С помощью функции, предусмотренной в программе, размер ЭС будет равняться 1,69%. Чтобы вычислить номинальную ставку, ЭПС умножается на срок кредитования: 1,69%х12=20,28%.

С помощью дополнительных функций можно установить, что единовременная комиссия повысила фактическое значение ставки на 2,72%. В общем получается 22,28%. Размер НС: 2,04%х12=24,48%. Ежемесячно сборы повысятся до 27,42%. При этом в договоре с банком указывается номинальная ставка. По текущему законодательству кредитные структуры обязаны прописывать в соглашениях размер ЭГС в процентах.

Отличие лизинга от кредита

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

Расчет ЭПС по лизингу в Excel будет производиться по схеме, расписанной выше. Предварительно вычисляется внутренняя ставка доходности, а полученный результат умножается на 12. Также можно воспользоваться функцией ЧИСТВНДОХ. В таком случае ЭС по лизингу будет равняться 23,28%.

https://youtube.com/watch?v=TPZbfl8tlSk

Применение ОВГЗ

С помощью компьютерной программы можно выяснить размер ЭПС для облигаций. ОВГЗ часто сравнивают с депозитами в банковской структуре.

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

Отличие заключается только в том, кто выступает гарантом. При ОВГЗ гарантом сохранности облигаций выступает государство в лице Центрального банка, а в случае с депозитом — коммерческий банк. С помощью эффективной ставки оценивается настоящая прибыль. Для примера можно купить годичные ценные бумаги на сумму в 50 000 рублей под 17%. Расчет дохода осуществляется с помощью функции БС.

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

Функция Excel FV

FV — это финансовая функция Excel, которая возвращает будущую стоимость инвестиций на основе фиксированной процентной ставки. Он работает как для серии периодических платежей, так и для единовременного платежа.

Функция доступна во всех версиях Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel 2007.

Синтаксис FV следующий:

БС(ставка, кпер, плт, , )

Где:

• Ставка (обязательно) — процентная ставка за период. Если вы платите один раз в год, укажите годовую процентную ставку; если вы платите каждый месяц, то вы должны указать ежемесячную процентную ставку и так далее.
• Кпер (обязательно) — общее количество периодов выплат за время существования аннуитета.
• Pmt (необязательно) — постоянная сумма, выплачиваемая каждый период. Должен быть выражен отрицательным числом. Если он опущен, предполагается, что он равен 0, а пв аргумент должен быть включен.
• Pv (необязательно) — текущая стоимость инвестиций. Должен быть представлен отрицательным числом. Если этот параметр опущен, по умолчанию он равен 0, а пмт аргумент должен быть включен.
• Тип (необязательно) — указывает, когда осуществляются платежи:
  • 0 или опущен (по умолчанию) — в конце периода (обычный аннуитет)
  • 1 — на начало периода (аннуитетный платеж)

4 вещи, которые нужно помнить о функции Excel FV

Чтобы правильно построить формулу FV на листах и ​​избежать распространенных ошибок, помните об этих примечаниях по использованию:

1. Для любых притоков, таких как дивиденды или другие доходы, используйте положительные числа. Для любых оттоков, таких как депозиты на сберегательный или инвестиционный счет, используйте отрицательные числа.
2. Если текущая стоимость (пв) равен нулю или опущен, сумма платежа (пмт) должен быть включен, и наоборот.
3. оценивать Аргумент может быть выражен в процентах или десятичном числе, например, 8% или 0,08.
4. Чтобы получить правильную будущую стоимость, вы должны соответствовать Например а также оценивать. Например, если вы делаете 3 ежегодных платежа по годовой процентной ставке 5%, используйте 3 для Например и 5% для оценивать. Если вы делаете серию ежемесячных инвестиций сроком на 3 года, то используйте 3*12 (всего 36 платежей) для Например и 5%/12 для оценивать.

Расчет процентов в Excel.

Основная формула для расчета процента от числа в Excel такая же, как и во всех сферах жизни:

Часть / Целое = Процент

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

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

То есть, чтобы, к примеру, посчитать 10% в Экселе, то вместо =A1*0,1   или =A1*10/100, просто запишите формулу процентов  =A1*10%.

Хотя с точки зрения математики все 3 варианта возможны и все они дадут верный результат.

А теперь давайте посмотрим, как можно использовать формулу процента в Excel для реальных данных. Предположим, в вашей таблице Эксель записана сумма заказанных товаров в столбце F и оставленных товаров в столбце G. Чтобы высчитать процент доставленных товаров, выполните следующие действия:

• Введите формулу =G2/F2 в ячейку H2 и скопируйте ее на столько строк вниз, сколько вам нужно.
• Нажмите кнопку «Процентный стиль» ( меню «Главная» > группа «Число»), чтобы отобразить полученные десятичные дроби в виде процентов.
• Не забудьте при необходимости увеличить количество десятичных знаков в полученном результате.
• Готово! 

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

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

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

Запишите формулу в самую верхнюю ячейку столбца с расчетами, а затем протащите маркер автозаполнения вниз по столбцу. Таким образом, мы посчитали процент во всём столбце.

Кредитный калькулятор в Excel | СЕМЕЙНЫЙ БЮДЖЕТ и не только…

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

Вот автор блога «Эксель Практик» и предлагает завести собственный кредитный калькулятор у себя в книге Excel.

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

Итак, любой кредит имеет 4 основных параметра:

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

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

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

Мы сделаем сначала калькулятор. За расчет всех четырех показателей отвечают эти функции:

— Срок – Функция ПС()

— Сумма – Функция КПЕР()

— Ставка – Функция СТАВКА()

— Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист файла.

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

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

Переходим ко второму листу.

Первая строчка графика – дата выдачи, поэтому тут будет только первоначальная сумма кредита.

На второй строке:

Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА, где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день

Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД. Важно: дату можно корректировать вручную, на следующую дату влияния не окажет. Сумма ежемесячного платежа (которая определяется по функции ПЛТ)

Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА, чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму. Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов. Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения. Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.

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

Теперь сделаем такой же график для дифференцированных платежей.

Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной — сумма долга разделить на количество периодов (месяцев).

2) Ежемесячный платеж определяем как сумму двух частей — погашений основного долга и процентов.

Разница двух форм по сути в том, что вы больше платите в месяц по дифференцированному платежу, но быстрее расплачиваетесь и поэтому в итоге платите меньше процентов.

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

Для меня это была обоснованность взятия кредита. Я тогда снимал квартиру и поэтому мне нужно было рассчитать цену кредита. Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит. Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.

Если кому нужно более наглядно, то посмотрите видео, где этот файл и формировался:

Делаем кредитный калькулятор

А для тех, кому просто нужен калькулятор и график платежей, вот ссылка на файл:

Кредитный калькулятор

Основная формула аннуитетного платежа в 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. Проверить, что сумма взноса автоматически посчиталась в последней строке таблицы.

Окончательный результат расчета

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

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

Когда рассчитываются ежемесячные взносы, в рассмотрение берется исключительно годовая ставка

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

Это период выплат по задолженности.

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

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

Аннуитет имеет последующую специфику:

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