Полезные формулы excel для контроля финансов
Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
Выполнение расчетов с помощью финансовых функций
В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.
Переход к данному набору инструментов легче всего совершить через Мастер функций.
- Выделяем ячейку, куда будут выводиться результаты расчета, и кликаем по кнопке «Вставить функцию», находящуюся около строки формул.
- Запускается Мастер функций. Выполняем клик по полю «Категории».
- Открывается список доступных групп операторов. Выбираем из него наименование «Финансовые».
- Запускается перечень нужных нам инструментов. Выбираем конкретную функцию для выполнения поставленной задачи и жмем на кнопку «OK». После чего открывается окно аргументов выбранного оператора.
В Мастер функций также можно перейти через вкладку «Формулы». Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию», размещенную в блоке инструментов «Библиотека функций». Сразу вслед за этим запустится Мастер функций.
Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые». После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.
Урок: Мастер функций в Excel
ДОХОД
Одним из наиболее востребованных операторов у финансистов является функция ДОХОД. Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис». Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:
=ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])
БС
Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка»), общее количество периодов («Кол_пер») и постоянная выплата за каждый период («Плт»). К необязательным аргументам относится приведенная стоимость («Пс») и установка срока выплаты в начале или в конце периода («Тип»). Оператор имеет следующий синтаксис:
=БС(Ставка;Кол_пер;Плт;[Пс];[Тип])
ВСД
Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения»). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение». В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:
=ВСД(Значения;[Предположения])
МВСД
Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения») аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:
=МВСД(Значения;Ставка_финансир;Ставка_реинвестир)
ПРПЛТ
Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка»); номер периода («Период»), величина которого не может превышать общее число периодов; количество периодов («Кол_пер»); приведенная стоимость («Пс»). Кроме того, есть необязательный аргумент – будущая стоимость («Бс»). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:
=ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])
ПЛТ
Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период». Зато добавлен необязательный аргумент «Тип», в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:
=ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
ПС
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ. У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС»), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт»). Синтаксис соответственно такой:
=ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
ЧПС
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости. У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
=ЧПС(Ставка;Значение1;Значение2;…)
СТАВКА
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер»), величина регулярной выплаты («Плт») и сумма платежа («Пс»). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс») и указание в начале или в конце периода будет производиться платеж («Тип»). Синтаксис принимает такой вид:
=СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
ЭФФЕКТ
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:
=ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции. В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ
Мы подумали, что блок статей о формулах Экселя просто не сможет обойтись без обозревания таких удобных и востребованных функций, как финансовые. Поэтому представляем вашему вниманию небольшой экскурс в мир «денежных» функций.
По «старой» традиции начнем с того, как найти финансовые формулы в программе. Сделать это очень просто: на главной панели найти кнопку «Формулы», нажать на нее и выбрать в появившемся списке название раздела «Финансовые».
Дальше выпадет перечень формул, которые вы можете использовать:
В данном разделе больше 50 функций, которые могут помочь специалистам упростить расчеты и сэкономить время на составление формул.
Разумеется, рассказать о всех возможностях мы не успеем, но рассмотрим некоторые их них.
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.
Если вдруг мы упустили то, что вам было бы интересно узнать, пишите в комментариях, и мы обязательно учтем ваши пожелания и поможем познакомиться со всеми возможностями Excel.
Работа каждого современного специалиста, непременно, связана с цифрами, с отчетностью и, возможно, финансовым моделированием.
Большинство компаний используют для финансового моделирования и управления Excel, т.к. это простой и доступный инструмент. Excel содержит сотни полезных для специалистов функций.
В этой статье мы расскажем вам о 13 популярных базовых функций Excel, которые должен знать каждый специалист!
Без опытного помощника разбираться в этом очень долго. Можно потратить годы профессиональной жизни, не зная и трети возможностей Excel, экономящих сотни рабочих часов в год.
Освоить весь функционал программы и поднять свои навыки владения Excel на небывалый уровень вы сможете на нашем курсе по Excel/VBA.
Итак, основные функции, используемые в Excel.
1. Функция СУММ (SUM)
- Русская версия: СУММ (Массив 1, Массив 2…..);
- Английская версия: SUM (Arr 1, Arr 2…..)
Показывает сумму всех аргументов внутри формулы.
Пример: СУММ(1;2;3)=6 или СУММ (А1;B1;C1), т.е. сумма значений в ячейках.
2. Функция ПРОИЗВЕД (PRODUCT)
- Русская версия: ПРОИЗВЕД (Массив 1, Массив 2…..);
- Английская версия: PRODUCT (Arr 1, Arr 2…..)
Выполняет умножение аргументов.
Пример: ПРОИЗВЕД(1;2;3)=24 или ПРОИЗВЕД(А1;B1;C1), т.е. произведение значений в ячейках.
3. Функция ЕСЛИ (IF)
- Русская версия: ЕСЛИ (Выражение 1; Результат ЕСЛИ Истина, Результат ЕСЛИ Ложь);
- Английская версия: IF (Expr 1, Result IF True, Result IF False)/
Для функции возможны два результата.
Первый результат возвращается в случае, если сравнение – истина, второй — если сравнение ложно.
Пример: А15=1. Тогда, =ЕСЛИ(А15=1;2;3)=2. Если поменять значение ячейки А15 на 2, тогда получим: =ЕСЛИ(А15=1;2;3)=3/
С помощью функции ЕСЛИ строят древо решения:
Формула для древа будет следующая:
ЕСЛИ(А22=1; ЕСЛИ(А23<0;5;10); ЕСЛИ(А24<0;8;6)). ЕСЛИ А22=1, А23=-5, А24=6, то возвращается результат 5.
4. Функция СУММПРОИЗВ(SUMPRODUCT)
- Русская версия: СУММПРОИЗВ(Массив 1; Массив 2;…..)
- Английская версия: SUMPRODUCT(Array 1; Array 2;…..)
Умножает соответствующие аргументы заданных массивов и возвращает сумму произведений.
Пример: найти сумму произведений
Находим произведения:
ПРОИЗВ1 =1*2*3=6
ПРОИЗВ2 =4*5*6=120
ПРОИЗВ3 =7*8*9=504
Сумма произведений равна 6+120+504=630
Эти расчеты можно заменить функцией СУММПРОИЗВ.
= СУММПРОИЗВ(Массив 1; Массив 2; Массив 3)
5. Функция СРЗНАЧ (AVERAGE)
- Русская версия: СРЗНАЧ (Массив 1; Массив 2;…..)
- Английская версия: AVERAGE(Array 1; Array 2;…..)
Рассчитывает среднее арифметическое всех аргументов.
Пример: СРЗНАЧ (1; 2; 3; 4; 5)=3
6. Функция МИН (MIN)
- Русская версия: МИН (Массив 1; Массив 2;…..)
- Английская версия: MIN(Array 1; Array 2;…..)
Возвращает минимальное значение массивов.
Пример: МИН(1; 2; 3; 4; 5)=1
7. Функция МАКС (MAX)
- Русская версия: МАКС (Массив 1; Массив 2;…..)
- Английская версия: MAX(Array 1; Array 2;…..)
Обратная функции МИН. Возвращает максимальное значение массивов.
Пример: МАКС(1; 2; 3; 4; 5)=5
8. Функция НАИМЕНЬШИЙ (SMALL)
- Русская версия: НАИМЕНЬШИЙ (Массив 1; Порядок k)
- Английская версия: SMALL(Array 1, k-min)
Возвращает k наименьшее число после минимального. Если k=1, возвращаем минимальное число.
Пример: В ячейках А1;A5 находятся числа 1;3;6;5;10.
Результат функции =НАИМЕНЬШИЙ (A1;A5) при разных k:
k=1; результат =1
k=2; результат=2
k=3; результат=5
9. Функция НАИБОЛЬШИЙ (LARGE)
- Русская версия: НАИБОЛЬШИЙ (Массив 1; Порядок k)
- Английская версия: LARGE(Array 1, k-min)
Возвращает k наименьшее число после максимального. Если k=1, возвращаем максимальное число.
Пример: в ячейках А1;A5 находятся числа 1;3;6;5;10.
Результат функции = НАИБОЛЬШИЙ (A1;A5) при разных k:
k=1; результат =10
k=2; результат=6
k=3; результат=5
10. Функция ВПР(VLOOKUP)
- Русская версия: ВПР(искомое значение; таблица; номер столбца; {0 (ЛОЖЬ, т.е. точное значение);1(ИСТИНА, т.е. приблизительное значение)})
- Английская версия: VLOOKUP(lookup value, table, column number. {0;1})
Ищет значения в столбцах массива и выдает значение в найденной строке и указанном столбце.
Пример: Есть таблица находящаяся в ячейках А1;С4
Нужно найти (ищем в ячейку А6):
- Возраст сотрудника Иванова (3 столбец)
- ВУЗ сотрудника Петрова (2 столбец)
Составляем формулы:
- ВПР(А6; А1:С4; 3;0) Формула ищет значение «Иванов» в первом столбце таблицы А1;С4 и возвращает значение в строке 3 столбца. Результат функции – 22
- ВПР(А6; А1:С4; 2;0) Формула ищет значение «Петров» в первом столбце таблицы А1;С4 и возвращает значение в строке 2 столбца. Результат функции – ВШЭ
11. Функция ИНДЕКС(INDEX)
- Русская версия: ИНДЕКС (Массив;Номер строки;Номер столбца);
- Английская версия: INDEX(table, row number, column number)
Ищет значение пересечение на указанной строки и столбца массива.
Пример: Есть таблица находящаяся в ячейках А1;С4
Необходимо написать формулу, которая выдаст значение «Петров».
«Петров» расположен на пересечении 3 строки и 1 столбца, соответственно, формула принимает вид: =ИНДЕКС(А1;С4;3;1).
12. Функция СУММЕСЛИ(SUMIF)
- Русская версия: СУММЕСЛИ(диапазон для критерия; критерий; диапазон суммирования)
- Английская версия: SUMIF(criterion range; criterion; sumrange)
Суммирует значения в определенном диапазоне, которые попадают под определенные критерии.
Пример: в ячейках А1;C5
Найти:
- Количество столовых приборов сделанных из серебра.
- Количество приборов <=15.
Решение:
- Выражение =СУММЕСЛИ(А1:C5;«Серебро»; В1:B5). Результат = 40 (15+25).
- =СУММЕСЛИ(В1:В5;« <=» & 15; В1:B5). Результат = 25 (15+10).
13. Функция СУММЕСЛИМН(SUMIF)
- Русская версия: СУММЕСЛИ(диапазон суммирования; диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)
- Английская версия: SUMIFS(criterion range; criterion; sumrange; criterion 1; criterion range 1; criterion 2; criterion range 2;)
Суммирует значения в диапазоне, который попадает под определенные критерии.
Пример: в ячейках А1;C5 есть следующие данные
Найти:
- Количество столовых приборов сделанных из серебра, единичное количество которых <=20.
Решение:
- Выражение =СУММЕСЛИМН(В1:В5; С1:С5; «Серебро»; В1:B5;« <=» & 20). Результат = 15
Заключение
Excel позволяет сократить время для решения некоторых задач, повысить оперативность, а это, как известно, важный фактор для эффективности.
Многие формулы выше также используются в финансовом моделировании.
В статье представлены только часть популярных функции Excel. А еще в Excel есть сотни других формул, диаграмм и массивов данных.
Узнать больше формул и диаграмм, отточить их на примерах, научиться работать с расширенным функционалом Excel, вы можете на нашем курсе по Excel/VBA.