Мы думаем, что блок статей о формулах Excel просто не мог обойтись без обзора таких удобных и популярных функций, как финансовые. Поэтому представляем вашему вниманию небольшой экскурс в мир «денежных» функций.
По «старой» традиции начнем с того, как найти в программе финансовые формулы. Сделать это очень просто: найдите в главной панели кнопку «Формулы», нажмите на нее и в появившемся списке выберите название раздела «Финансы».
Ниже приведен список формул, которые вы можете использовать:
Рис. 1. Перечень финансовых функций
В этом разделе собрано более 50 функций, которые помогут специалистам упростить расчеты и сэкономить время на формулировку.
Конечно, у нас не будет времени рассказать обо всех возможностях в рамках этой статьи, но некоторые из них мы рассмотрим. Если вы хотите узнать о функциональности математических функций в Excel, загрузите бесплатное руководство «Математические функции Excel».
1. Функция ДОХОД()
Очень популярная формула среди финансистов. Позволяет рассчитать доход от ценных бумаг, по которым осуществляются процентные выплаты в течение заданного периода.
У функции много аргументов, так что давайте разбираться со всеми медленно и по порядку!
Date_accord — дата покупки ценных бумаг.
Effective_date — это дата, указывающая на истечение срока действия ценных бумаг.
Ставка — купонная ставка ценной бумаги за год.
Цена: цена ценных бумаг за 100 рублей номиналом
Выкуп: стоимость выкупа ценных бумаг в расчете на 100 рублей номинала
Частота: цифра, показывающая количество платежей в год. Годовые платежи – 1, полугодовые – 2, квартальные – 4.
Помимо перечисленных обязательных аргументов, есть необязательный:
База — это число, которое характеризует способ расчета дня. Значение по умолчанию — 0.
Примечание. Обязательные аргументы выделены жирным шрифтом, а необязательные аргументы — обычным.
Рис. 2. Использование функции ДОХОД()
Комментарий. Ввод даты в виде текста не рекомендуется. Лучше использовать функцию ДАТА, чтобы избежать ошибок и проблем с функцией.
Например, 21 сентября 2013 года лучше записать так: ДАТА(2013,09,21).
2. Функция ПЛТ()
Функция ПЛТ() помогает рассчитать сумму, которую необходимо периодически выплачивать для погашения кредита с учетом переплаты по процентам за расчетный период. Предполагается, что объем платежей и ставка не изменятся.
Функция имеет 3 обязательных и 2 необязательных аргумента. Разберемся со всеми по порядку.
Ставка — процент, на который увеличивается сумма платежа за период.
Кпер — количество платежей или периодов.
Ps — общая сумма к оплате.
BS: Показывает, сколько осталось заплатить после последнего платежа. По умолчанию предполагается 0 (то есть после последнего платежа стоимость кредита будет 0 руб.).
Тип — аргумент, который принимает следующие значения: 0 — если выплаты производятся в конце периода, 1 — если в начале.
Рассмотрим пример.
Необходимо рассчитать ежемесячный платеж по кредиту в размере 500 000 рублей, взятому на 4 года под 6% годовых:
Рис. 3. Использование функции PAY()
Поскольку по условиям задачи была дана процентная ставка за год, для расчета ставки за один месяц делим 6% на 12 месяцев.
Так как выплаты производятся каждый месяц, количество периодов рассчитываем так: 4 * 12 = 48:
Рисунок 4. Результат функции PAY()
Обратите внимание, что результат отрицательный. Знак «-» указывает на то, что эта сумма должна быть возвращена (вычтена из долга).
3. Формула ПС()
Формула PS() нужна для нахождения текущего значения (то есть общей суммы, которую необходимо заплатить в текущий момент времени).
Можно вызвать обратный оператор PMT() выше. У нее точно такие же аргументы, только вместо «Ps» — «Plt» — сумма периодического платежа.
Функция записывается следующим образом:
PS(Ставка; Nper; Pm; Fs; Тип)
Рассмотрим пример:
Рисунок 5. Использование функции PS()
Рисунок 6. Результат функции PS()
Получаем сумму, которую в итоге выплатил бы человек, если бы взял кредит под 6% годовых на 4 года с ежемесячными платежами 12 000 рублей.
4. Формула ОСПЛТ()
В результате эта формула показывает основную часть платежей по кредиту за определенный период (то есть часть платежа, которая идет на погашение кредита, а не на проценты).
При этом учитывается, что параметры Тарифа и суммы выплат не меняются.
Функция OSLT() имеет те же аргументы, что и предыдущая формула: Rate, Nper, Ps, BS, Type.
Также добавляется Период (обязательный аргумент), число от 1 до Кпер.
Давайте посмотрим на результат функции в примере выше. Необходимо рассчитать, сколько денег с первого платежа идет на погашение кредита, без учета процентных платежей:
Рисунок 7. Использование функции OSLT()
Рисунок 8. Результат функции OSLT()
Мы видим, что основная часть первого платежа составляет 9 242,51 рубля, что составляет около 79% от ежемесячного платежа.
Если мы посмотрим на результат формулы для периода 48, то получим уже 11 684,1, то есть 99,5%. Заметная разница свидетельствует о том, что начисленные проценты в большей степени выплачиваются в досрочные расчетные периоды.
5. Формулы ПРПЛТ(), ОБЩПЛАТ()
Функция очень похожа на OSLT() с одной небольшой оговоркой: она помогает рассчитать сумму процентных платежей за выбранный период, предполагая, что сумма платежа и ставка не меняются.
Функция MPMT() имеет точно такие же аргументы, что и функция OSPLT(), и выглядит в строке ввода формулы следующим образом:
IPMT (Коэффициент, Период, Кпер, Ps, FV, Тип)
Применим формулу к нашему примеру:
Рисунок 9. Использование функции PRMT()
Рисунок 10. Результат функции PRMT()
Получили, что за первый период сумма процентных платежей составит 2500 руб., а в 48-м месяце — всего 58,4 руб.
Другими словами, эта формула еще раз подтверждает тот факт, что большая часть процентных платежей приходится на начальные платежные периоды.
ОБЩАЯ ВЫПЛАТА(Ставка; Кпер; Ps; Начало_конец; Конец_конец)
Ниже приведен пример использования функции ИТОГО ОПЛАТА(), где в качестве Start_trans мы берем первый период, а в качестве End_trans — второй.
Выплаты производятся в конце месяца:
Рис. 11. Использование функции ИТОГО ОПЛАТА()
По этим формулам даже обычный пользователь сможет рассчитать самые выгодные условия кредита!
6. Формула СТАВКА()
Мы уже научились рассчитывать сумму ежемесячных платежей, процентную переплату, сумму будущих платежей и т.д. Помимо этих действий в Excel можно рассчитать ставку по кредиту с помощью одноименной функции СТАВКА().
В качестве аргументов используются хорошо известные нам критерии: Nper, Plt, Ps, Bs, Type.
Последние два аргумента являются необязательными:
Рисунок 12. Использование функции RATE()
Рисунок 13. Результат функции RATE()
7. Формула БС()
Теперь поговорим о функции BS(): она рассчитывает стоимость инвестиций через определенное количество периодов при условии постоянной ставки.
Формула записывается следующим образом:
BS(Норма; Nper; Pmt; Ps; Тип).
Здесь аргумент ps является необязательным.
Рассмотрим пример:
Пусть годовая ставка 12%, количество платежей 12, каждый платеж 1000 руб. (со знаком минус покажем, что эти деньги надо вернуть).
Рассчитайте стоимость инвестиций при следующих условиях:
Рисунок 14. Применение функции BS()
Рис. 15. Результат функции BS()
Обратите внимание, что мы намеренно сделали «сумму платежа» отрицательной, чтобы показать, что эти деньги подлежат вычету и что сумма инвестиции не может быть отрицательной.
Заключение
Мы проделали большую работу и ознакомились с основными финансовыми формулами, которые могут использовать не только специалисты в узкой области, но и обычные пользователи Excel.
ХОРОШО
АКАДЕМИЯ EXCEL
Научитесь использовать все инструменты приложения из функционала MS Excel. excel, обработка данных, финансовое моделирование, формулы excel, функции excel
- Как не ошибиться при выборе брокера?
- Типичные ошибки при оценке компаний
- Оценка государственных и частных компаний
Вы работаете в компании «большой тройки» (то есть трех крупнейших консалтинговых компаний: McKinsey, Boston Consulting Group и Bain & Company), в которой мечтают работать тысячи подписчиков наших каналов и читателей vc.ru. Что это значит для тебя?