Мы подумали, что блок статей о формулах Excel просто не сможет обойтись без обозревания таких удобных и востребованных функций, как финансовые. Поэтому представляем вашему вниманию небольшой экскурс в мир «денежных» функций.
По «старой» традиции начнем с того, как найти финансовые формулы в программе. Сделать это очень просто: на главной панели найти кнопку «Формулы», нажать на нее и выбрать в появившемся списке название раздела «Финансовые».
Дальше выпадет перечень формул, которые вы можете использовать:
В данном разделе больше 50 функций, которые могут помочь специалистам упростить расчеты и сэкономить время на составление формул.
Разумеется, рассказать о всех возможностях в рамках этой статьи мы не успеем, но рассмотрим некоторые их них. Если вы хотите узнать о функционале математических функций в Excel, то скачивайте бесплатный гайд «Математические функции Excel».
1. Функция ДОХОД()
Очень популярная формула у финансистов. Она позволяет высчитать доход от ценных бумаг, по которым происходят выплаты процентов за определенный период.
Аргументов у функции много, поэтому медленно и по порядку со всеми разберемся!
Дата_согл – дата покупки ценных бумаг.
Дата_вступл_в_силу – дата, показывающая истечение срока действия бумаг.
Ставка – купонная ставка ценных бумаг за год.
Цена – цена бумаг на 100 руб. номинальной стоимости.
Погашение – выкупная стоимость ценных бумаг на 100 руб. номинальной стоимости.
Частота – цифра, показывающая количество выплат в год. Ежегодные выплаты – 1, полугодовые – 2, ежеквартальные – 4.
Помимо перечисленных обязательных аргументов есть один необязательный:
Базис – число, характеризующее способ вычисления дня. По умолчанию ставится 0.
Примечание. Обязательные аргументы выделены жирным шрифтом, а необязательные – обычным.
Замечание. Не рекомендуется вводить дату как текстовую запись. Лучше использовать функцию ДАТА во избежание ошибок и проблем с работой функции.
Например, число 21 сентября 2013 г. лучше записать так: ДАТА(2013,09,21).
2. Функция ПЛТ()
Функция ПЛТ() помогает высчитать сумму, которую нужно платить периодически для погашения ссуды с учетом процентных переплат за один расчетный период. Предполагается, что объем платежей и ставка не меняются.
У функции 3 обязательных аргумента и 2 – необязательных. Разберемся со всеми по порядку.
Ставка – процент, на который возрастает сумма платежа за один период.
Кпер – количество выплат или периодов.
Пс – общая сумма, которую нужно выплатить.
БС – показывает, сколько останется выплатить после последней выплаты. По умолчанию подразумевается 0 (то есть после последней выплаты стоимость ссуды составит 0 руб.).
Тип – аргумент, который принимает значения: 0 – когда платежи совершаются в конце периода, 1 – если в начале.
Рассмотрим пример.
Нужно рассчитать ежемесячный платеж по кредиту в размере 500 000 руб., взятого на 4 года под 6% годовых:
Так как в условиях задачи была дана процентная ставка за год, то, чтобы рассчитать ставку за один месяц, мы разделили 6% на 12 месяцев.
Так как выплаты производятся каждый месяц, то количество периодов рассчитываем так: 4 * 12 = 48:
Обратим внимание на то, то результат получился отрицательным. Знак «-» показывает, что эту сумму нужно отдать (вычесть из задолженности).
3. Формула ПС()
Формулу ПС() необходима для нахождения приведенной стоимости (то есть общей суммы, которую нужно выплатить на текущий момент).
Её можно назвать обратной к предыдущему оператору ПЛТ(). У неё точно такие же аргументы, только вместо «Пс» – «Плт» – сумма периодической выплаты.
Функция записывается следующим образом:
ПС(Ставка; Кпер; Плт; Бс; Тип)
Рассмотрим пример:
Мы получили сумму, которую в итоге заплатил бы человек, взявший кредит под 6% годовых на 4 года с ежемесячными выплатами в размере 12 000 руб.
4. Формула ОСПЛТ()
Данная формула в качестве результата выводит основную часть выплат по кредиту за заданный период (то есть ту часть платежа, которая уходит на оплату именно ссуды, а не процентов).
При этом учитывается, что параметры Ставка и размер выплат не меняются.
У функции ОСПЛТ() такие же аргументы, как и предыдущая формула: Ставка, Кпер, Пс, БС, Тип.
Еще добавляется Период (обязательный аргумент) – число от 1 до Кпер.
Посмотрим результат функции на предыдущем примере. Нужно рассчитать, сколько денег от первого платежа идет на погашение ссуды, не учитывая оплату процентов:
Мы видим, что основная часть первого платежа равна 9 242,51 руб – это примерно 79% от ежемесячной выплаты.
Если посмотреть результат формулы за 48-ой период, то получим уже 11 684,1 – это 99,5%. Заметная разница говорит о том, что процентные начисления в большей степени выплачиваются в первые расчетные периоды.
5. Формулы ПРПЛТ(), ОБЩПЛАТ()
Функция очень похожа на ОСПЛТ() с небольшой оговоркой: она помогает высчитать размер выплат по процентам за выбранный период, предполагая неизменяемыми размер платежей и ставку.
У функция ПРПЛТ() точно такие же аргументы, как и у ОСПЛТ(), и выглядит в строке ввода формул так:
ПРПЛТ(Ставка; Период; Кпер; Пс; БС; Тип)
Применим формулу к нашему примеру:
Получили, что за первый период сумма выплат по процентам составит 2 500 руб., а в 48 месяце – всего 58,4 руб.
То есть данная формула еще раз подтверждает факт, что большая часть выплат по процентам осуществляется в начальные периоды платежей.
Замечание. Чтобы рассчитать, какая сумма из ваших платежей ушла на оплату процентов между любыми периодами, нужно использовать формулу:
ОБЩПЛАТ(Ставка;Кпер; Пс; Нач_пер;Кон_пер)
Ниже представлен пример применения функции ОБЩПЛАТ(), где в качестве Нач_пер берем первый период и Кон_пер – второй.
Выплаты происходят в конце месяца:
С помощью этих формул даже рядовой пользователь сможет рассчитать самые выгодные условия кредитования!
6. Формула СТАВКА()
Мы уже узнали, как считать объем ежемесячных выплат, процентные переплаты, число будущих выплат и так далее. Помимо этих действий в Excel можно вычислить ставку по кредиту, используя одноименную функцию СТАВКА().
В качестве аргументов выступают хорошо известные нам критерии: Кпер, Плт, Пс, Бс, Тип.
Два последних аргумента – необязательные:
7. Формула БС()
Теперь поговорим о функции БС() – высчитывает стоимость инвестиций после определенного количества периодов при условии неизменной ставки.
Формула записывается следующим образом:
БС(Ставка; Кпер; Плт; Пс; Тип).
Здесь аргумент Пс является необязательным.
Рассмотрим пример:
Пусть 12% – годовая ставка, количество платежей – 12, каждая выплата – 1 000 руб. (знаком минус покажем, что эти деньги нужно отдавать).
Посчитаем стоимость инвестиций при таких условиях:
Отметим, что «сумму выплат» мы специально сделали отрицательной, чтобы показать, что эти деньги вычитаются, и что сумма инвестиций не может быть отрицательной.
Заключение
Мы с вами проделали большую работу и познакомились с базовыми финансовыми формулами, которые могут применять не только специалисты в узкой области, но и простые пользователи Excel.