Bugsp.ru

Ваша компьютерная помощь
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Финансовые функции в Excel: подробное описание

10 популярных финансовых функций в Microsoft Excel

Финансовые функции в Microsoft Excel

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

Выполнение расчетов с помощью финансовых функций

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

Переход к данному набору инструментов легче всего совершить через Мастер функций.

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

Переход в мастер функций в Microsoft Excel

Мастер функций в Microsoft Excel

Переход к группе финансовых функций в Microsoft Excel

Выбор конкретной финансовой функции в Microsoft Excel

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

Переход в мастер функций через вкладку Формулы в Microsoft Excel

Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые». После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.

Переход к выбору финансовых функций через кнопку на ленте в Microsoft Excel

ДОХОД

Одним из наиболее востребованных операторов у финансистов является функция ДОХОД. Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис». Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:

Функция ДОХОД в Microsoft Excel

Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка»), общее количество периодов («Кол_пер») и постоянная выплата за каждый период («Плт»). К необязательным аргументам относится приведенная стоимость («Пс») и установка срока выплаты в начале или в конце периода («Тип»). Оператор имеет следующий синтаксис:

Фнкция БС в Microsoft Excel

Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения»). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение». В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:

Фнкция ВСД в Microsoft Excel

Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения») аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:

Фнкция МВСД в Microsoft Excel

ПРПЛТ

Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка»); номер периода («Период»), величина которого не может превышать общее число периодов; количество периодов («Кол_пер»); приведенная стоимость («Пс»). Кроме того, есть необязательный аргумент – будущая стоимость («Бс»). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:

Функция ПРПЛТ в Microsoft Excel

Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период». Зато добавлен необязательный аргумент «Тип», в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:

Фнкция ПЛТ в Microsoft Excel

Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ. У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС»), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт»). Синтаксис соответственно такой:

Фнкция ПС в Microsoft Excel

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

Функция ЧПС в Microsoft Excel

СТАВКА

Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер»), величина регулярной выплаты («Плт») и сумма платежа («Пс»). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс») и указание в начале или в конце периода будет производиться платеж («Тип»). Синтаксис принимает такой вид:

Функция СТАВКА в Microsoft Excel

ЭФФЕКТ

Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:

Функция ЭФФЕКТ в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12307 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

7 функций Excel для экономиста: простые примеры для сложных задач

Вычислительные функции — это сердцевина Excel и то, что, по-сути, отличает программу от обычного калькулятора. Функций в программе довольно много — более 400. Часть из них дублируется, но тем не менее, возможности для экономических расчетов в программе огромные.
С помощью формул можно безошибочно рассчитать себестоимость товара, подготовить аналитические отчеты, свести планы. Решить как простые экономические задачи учета, так и составить полный баланс предприятия. Подборку инструментов Excel для бухгалтера мы уже делали. Настал черед рассмотреть основные функции Excel для экономиста, которые упростят работу с данными и большими таблицами.

Читайте так же:
Как купить музыку в iTunes

Что такое функции Excel и где они находятся

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

Как мы уже говорили, в программе функций много — около 10 категорий: есть математические, логические, текстовые. И специальные функции — финансовые, статистические и пр. Все функции лежат во вкладке «Формулы». Перейдя в нее, нужно нажать на кнопку «Вставить функцию» на панели инструментов, после чего запустится «Мастер функций».

Вставка функции

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

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

подсказки в Excel

Запоминать, что делает каждая функция не нужно. В Excel есть удобные подсказки, зачем нужна та или иная формула, и как ее использовать.

Ниже рассмотрим основные и часто используемые формулы в Excel для экономистов: ЕСЛИ, СУММЕСЛИ, ВПР, СУММПРОИЗВ, СЧЁТ, СРЗНАЧ и МАКС/МИН.

Функция ЕСЛИ для сравнения данных

Самая популярная логическая функция, которая полезна экономистам, бухгалтерам. С ее помощью можно сравнить числовые и текстовые значения по определенным критериям. Например, рассчитать амортизацию, скидки, наценку или убрать ошибки из расчетов. Иногда используется встроенная функция ЕСЛИ в статистических, текстовых и математических формулах, что наделяет их способностью «принимать решения».

Функция ЕСЛИ помогает точно сравнить значения и получить результат, в зависимости от того, истинно сравнение или нет.

Так выглядит формула:
=ЕСЛИ(лог_выражение;[значение_если_истина];[значение_если_ложь])

  • Лог_выражение — это то, что нужно проверить или сравнить (числовые или текстовые данные в ячейках)
  • Значение_если_истина — это то, что появится в ячейке, если сравнение будет верным.
  • Значение_если_ложь — то, что появится в ячейке при неверном сравнении.

Например, магазин торгует аксессуарами для мужчин и женщин. В текущем месяце на все женские товары скидка 20%. Отсортировать акционные позиции можно с помощью функции ЕСЛИ для текстовых значений.

Пропишем формулу в столбце «Скидка» так:
=ЕСЛИ(B2=»женский»;20%;0)
И применим ко всем строкам. В ячейках, где равенство выполняется, увидим товары по скидке.

функция ЕСЛИ для текстовых значений

Так применяется функция ЕСЛИ для текстовых значений с одним условием

Функции СУММЕСЛИ и СУММЕСЛИМН

Еще одна полезная функция СУММЕСЛИ, которая позволяет просуммировать несколько числовых данных по определенному критерию. Состоит формула из 2-х частей:

  • СУММ — математическая функция сложения числовых значений. Записывается как =СУММ(ячейка/диапазон 1; ячейка/диапазон 2; …).
  • и функция ЕСЛИ, которую рассмотрели выше.

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

В формуле нужно прописать такие аргументы:

  • Выделить диапазон всех должностей сотрудников — в нашем случае B2:B10.
  • Прописываем критерий выбора через точку с запятой — “менеджер”.
  • Диапазон суммирования — это заработные платы. Указываем C2:C10.

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

функции excel для экономистов

С помощью СУММЕСЛИ можно просуммировать ячейки, которые соответствуют определенному критерию

Важно! Функция СУММЕСЛИ чувствительна к правильности и точности написания критериев. Малейшая опечатка может дать неправильный результат. Это также касается названий ячеек. Формула выдаст ошибку, если написать диапазон ячеек кириллицей, а не латиницей.

Более сложный вариант этой формулы — функция СУММЕСЛИМН. По-сути, это выборочное суммирование данных, отобранных по нескольким критериям. В отличие от СУММЕСЛИ, можно использовать до 127 критериев отбора данных. Например, с помощью этой формулы легко рассчитать суммарную прибыль от поставок разных товаров сразу в несколько стран.

В функции СУММЕСЛИМН можно работать с подстановочными символами, использовать операторы для вычислений типа «больше», «меньше» и «равно». Для удобства работы с функцией лучше применять абсолютные ссылки в Excel — они не меняются при копировании и позволяют автоматически пересчитать формулу, если данные в ячейке изменились.

Функции ВПР и ГПР — поиск данных в большом диапазоне

Экономистам часто приходится обрабатывать огромные таблицы, чтобы получить необходимые данные для анализа. Или сводить две таблицы в одну, что тоже не редкость. Функция ВПР или, как ее еще называют, вертикальный просмотр (англ. вариант VLOOKUP) позволяет быстро найти и извлечь нужные данные в столбцах. Либо перенести данные из одной таблицы в соответствующие ячейки другой.

Синтаксис самой простой функции ВПР выглядит так:
= ВПР(искомое_значение; таблица; номер_столбца; [интервальный просмотр]).

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

Функции ВПР и ГПР

Функция ВПР позволяет быстро найти нужные данные и перенести их в выделенную ячейку.

В ячейке С1 мы указали номер товара. Потом выделили диапазон ячеек, где его искать (A1:B10) и написали номер столбца «2», в котором нужно взять данные. Нажали Enter и получили нужный товар в выделенной ячейке.

Есть менее популярная функция ГПР. Она работает также, как ВПР, но ищет данные в горизонтальных списках.

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

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

Функция СУММПРОИЗВ в Excel

Четвертая функция нашего списка — СУММПРОИЗВ или суммирование произведений. Поможет быстро справиться с любой экономической задачей, где есть массивы. Включает в себя возможности предыдущих формул ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, а также позволяет провести расчеты в 255 массивах. Ее любят бухгалтеры и часто используют при расчетах заработной платы и других расходов.

Что в данном случае может быть произведениями? Как заданные диапазоны, так и целые массивы данных. Чтобы задать количество массивов, их нужно перечислить через точку с запятой, потом перемножить, а затем просуммировать. При этом, есть одно условие — массивы должны быть одинаковые по типу и длине.

Например, у вас есть месячный отчет по продажам в магазине, из которого нужно узнать, на какую сумму продали футболок размера «М».

Читайте так же:
PrivaZer скачать бесплатно на русском языке для Windows 7

Для этого используем функцию СУММПРОИЗВ и указываем 2 условия. Каждое из них берем в скобки, а между ними ставим «звездочку», которая в Excel читается как союз «и».

Запишем команду так: =СУММПРОИЗВ((A5:A11=A13)*(B5:B11=B13)*C5:C11), где

  • первое условие A5_A11=A13— диапазон поиска и наименование нужного товара
  • второе условие B5_B11=B13 — диапазон поиска и размер
  • C5:C11 — массив, из которого берется итоговая сумма

С помощью функции СУММПРОИЗВ мы узнали за пару минут, что в магазине за месяц продали футболок М-размера на 100 у.е.

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

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

Как применить МАКС, ВПР и ПОИСКПОЗ для решения задач

Функции МИН и МАКС помогают найти наименьшее или наибольшее значение данных. Функция ПОИСКПОЗ помогает найти номер указанного элемента в выделенном диапазоне. А формула ВПР, напомним, позволяет извлечь нужные данные из столбцов в указанные ячейки.

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

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

МАКС, ВПР и ПОИСКПОЗ для решения задач

Для решения задачи, можно применить функции последовательно:

  • Найти самый крупный долг поможет функция МАКС (=МАКС(B2:B10)), где B2:B10 — столбец с данными по задолженности.
  • Чтобы найти номер компании-должника в списке, нужно в таблицу добавить столбец с нумерацией. Так как функция ПОИСКПОЗ ищет данные только в крайнем левом столбце выделенного диапазона.

Составляем функцию по формуле:
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

В нашем случае это будет =ПОИСКПОЗ(14569;C2:C10;0), где искомое — максимальная сумма долга. Тип сопоставления будет “0”, потому что к столбцу с долгами мы не применяли сортировку.

  • Чтобы узнать название компании-должника, применим знакомую функцию ВПР.

Выглядеть она будет так =ВПР(D14;A2:B10;2), где D4 — искомое, A2:B10 — таблица или выделенный диапазон с названиями компаний и нумерацией, а “2” — номер столбца с должниками.

решение финансовых задач в excel примеры

Этот же результат можно было получить, собрав одну формулу из 3-х:

=ВПР (ПОИСКПОЗ (МАКС (C2:C10); C2:C10;0); A2:B10;2).

В экономических расчетах функция ВПР помогает быстро извлечь нужное значение из огромного диапазона данных. Причем значение можно найти по разным критериям отбора. Например, цену товара можно извлечь по идентификатору, налоговую ставку — по уровню дохода и пр.
Кроме вышеупомянутых функций, экономисты часто используют формулу СРЗНАЧ, например, для расчета средней заработной платы. Функцию СЧЁТ, когда нужно рассчитать количество отгрузок в разрезе клиентов или стоимости товара за определенный период. Кстати, на примере отгрузок, формула МИН/МАКС поможет отследить диапазон, в котором изменялась стоимость товара.

Существует еще сотня математических и финансовых функций в Ехсеl, с помощью которых можно решить экономические задачи разной сложности. Например, обрабатывать заказы, планировать загрузку производства, рассчитывать налоги, вести кадровый учет и управлять сбытом. Но для решения подобных задач, подсказок в Excel будет уже недостаточно. Тут необходимо глубже погружаться в программу и учиться работать с разными ее инструментами: от функций и сводных таблиц, до диаграмм и макросов.

Финансовые функции в Excel: подробное описание

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

Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.

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

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

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

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

Возвращает количество дней от начала действия купона до даты соглашения.

Возвращает количество дней в периоде купона, который содержит дату расчета.

Возвращает количество дней от даты расчета до срока следующего купона.

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

Возвращает количество купонов между датой соглашения и сроком вступления в силу.

Возвращает порядковый номер даты предыдущего купона до даты соглашения.

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

Возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами.

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

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

Возвращает ставку дисконтирования для ценных бумаг.

Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.

Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.

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

Возвращает фактическую (эффективную) годовую процентную ставку.

Возвращает будущую стоимость инвестиции.

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

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

Возвращает проценты по вкладу за данный период.

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

Вычисляет выплаты за указанный период инвестиции.

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

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

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

Возвращает общее количество периодов выплаты для инвестиции.

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

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг с нерегулярным (коротким или длинным) первым периодом купона.

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом купона.

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг с нерегулярным (коротким или длинным) последним периодом купона.

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом купона.

Читайте так же:
Как посмотреть комментарии на Ютубе с телефона

ПДЛИТ
Excel 2013

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

Возвращает регулярный платеж годичной ренты.

Возвращает платеж с основного вложенного капитала за данный период.

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

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, на которые сделана скидка.

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

Возвращает приведенную (к текущему моменту) стоимость инвестиции.

Возвращает процентную ставку по аннуитету за один период.

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

ЭКВ.СТАВКА
Excel 2013

Возвращает эквивалентную процентную ставку для роста инвестиции.

Возвращает величину амортизации актива за один период, рассчитанную линейным методом.

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

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

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

Возвращает доходность по казначейскому векселю.

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

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

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

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

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

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

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

7 функций Excel, которые помогут управлять финансами

7 функций Excel, которые помогут управлять финансами

1. PMT (ПЛТ) — рассчитывает сумму ежемесячных платежей по долгам

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

В Telegram-канале «Лайфхакер» только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!

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

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

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

Для начала надо правильно написать формулу — в любой свободной ячейке.

В скобках стоят три обязательных аргумента, без которых не получится ничего посчитать:

  1. Ставка — процент по кредиту, который предлагает банк. Пусть будет 9,5%.
  2. Кпер — количество выплат по займу. Ремонт дорогой, но не смертельно, так что возьмём на полтора года: это 18 ежемесячных платежей.
  3. Пс — сумма, которая нужна на обновление жилья. Оценим это дело в 300 000 рублей.

Как всё посчитать

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

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

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

Главное — не перепутать местоположение ячеек: все значения остаются в одних и тех же строках

2. EFFECT (ЭФФЕКТ) — позволяет рассчитать сложный процент

Функция подойдёт инвестору, который выбирает облигации для своего портфеля и хочет понять, какую годовую доходность получит на самом деле.

Россия занимает деньги через множество облигаций федерального займа (ОФЗ). У каждого выпуска таких бумаг есть номинальная доходность, определяющая, какой процент годовых от вложенной суммы получит инвестор. Например, по ОФЗ 26209 обещают Параметры облигации федерального займа SU26209RMFS5 / Московская биржа 7,6%, а по ОФЗ 26207 ещё больше Параметры облигации федерального займа SU26207RMFS9 / Московская биржа — 8,15%.

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

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

Формула расчёта довольно простая:

В ней всего две переменные:

  1. Номинальная_ставка — та доходность, которая обещана облигацией при выпуске. Это 7,6% и 8,15% в нашем примере.
  2. Кол_пер — количество периодов в году, когда инвестору начисляется прибыль (в облигациях её называют купоном).

Как всё посчитать

Принцип сохраняется: вносим исходные данные в таблицу. Номинальную доходность и периодичность выплат по купонам обязательно публикуют для каждой облигации на Мосбирже в разделе «Параметры инструмента». Теперь легко всё посчитать:

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

Только заметим, что облигации устроены очень хитро, инвестору нужно учитывать и другие факторы, которые влияют на прибыльность. Например, номинал бумаги равен 1 000 рублей, а её продают за 996 — реальная доходность будет выше. С другой стороны, инвестору придётся заплатить ещё и накопленный купонный доход — автоматически рассчитываемая компенсация предыдущему владельцу облигации. Эта сумма может быть равна 20–30 рублям, из‑за чего доходность опять упадёт. Одной формулой здесь не обойтись.

3. XNPV (ЧИСТНЗ) — вычисляет общую прибыль инвестора

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

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

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

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

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

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

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

Читайте так же:
Как отключить синхронизацию в Яндекс Браузере

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

Как всё посчитать

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

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

Итоговое значение — фактическая прибыль инвестора через четыре года с учётом ставки дисконтирования. Она совсем маленькая, несмотря на 92 тысячи инвестиций: для больших поступлений нужно подбирать более рискованные, но доходные инструменты.

4. XIRR (ЧИСТВНДОХ) — оценивает доходность инвестиций по притокам денег

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

Функция помогает сравнить доходность, если мы заранее не знаем процент годовых. К примеру, ставка по банковскому вкладу равна 6%. Можно вложить деньги туда, а можно в бизнес знакомого, который обещает раз в квартал платить плавающую сумму в зависимости от успехов.

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

Чтобы определить более выгодное предложение, применим формулу:

Достаточно знать всего две переменные:

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

Как всё посчитать

Допустим, человек вложил 100 000 рублей и получил четыре платежа, по одному в квартал. В конце года инвестор знает их размер и может вычислить доходность — больше 40%. Это на 37% выгоднее банковского вклада, хотя и рискованнее.

5. RATE (СТАВКА) — вычисляет месячную или годовую процентную ставку по займам

Бывают и такие ситуации, что заём уже есть, а процент не оговорён. Допустим, если человек взял в долг 100 000 рублей у знакомого и пообещал в течение полугода возвращать по 20 тысяч ежемесячно. Кредитор может захотеть узнать, какова выходит ставка.

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

Полезной будет эта формула:

Три переменных в ней означают следующее:

  1. Кпер — количество выплат. В нашем примере заём полугодовой, то есть их будет шесть.
  2. Плт — размер платежей. Считаются и основной долг, и проценты.
  3. Пс — общая сумма займа. В нашем примере это 100 000 рублей.

Как всё посчитать

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

6. PV (ПС) — подсказывает, сколько денег можно взять в долг

Люди иногда делают большие покупки. Например, приобретают автомобили. Они стоят дорого, и для машин берут автокредит, обслуживать который тоже недёшево. Если человек не готов отдавать всю зарплату на ежемесячные платежи, то может заранее прикинуть, какой заём будет комфортным.

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

Пригодится формула расчёта текущей стоимости:

=ПС(ставка; кпер; плт)

Для этого потребуется информация, которая есть на сайте любого банка:

  1. Ставка — под какой процент придётся брать деньги на покупку. Допустим, 9% годовых, или 0,75% в месяц.
  2. Кпер — сколько времени предстоит выплачивать кредит. Например, четырёхлетний заём равен 48 ежемесячным переводам средств.
  3. Плт — размер комфортного платежа.

Как всё посчитать

Предположим, что человеку будет по силам отдавать от 40 до 50 тысяч рублей в месяц. В этом случае нужны два столбца: ставка и срок постоянны, меняется только значение платежа. В результате увидим, что машина должна стоить не больше 1,6 или 2 миллионов рублей.

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

7. NPER (КПЕР) — помогает рассчитать время накоплений

Обычно банки объясняют, какой процент человек получит по их депозиту и сколько денег заработает. Но иногда у вкладчика другая цель — накопить конкретную сумму к определённой дате. Функция поможет высчитать этот срок.

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

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

Она состоит из четырёх основных значений и одного дополнительного:

  1. Ставка — годовая процентная ставка, которую предлагают вкладчику. Предположим, что 7%.
  2. Периоды_капитализации — количество раз в году, когда банк начисляет проценты. Это часто делают ежемесячно, поэтому пишем «12».
  3. Плт — ежемесячный платёж. Скажем, вклад непополняемый, так что показатель будет равен нулю.
  4. Пс — начальная сумма на депозите. Допустим, 100 000 рублей.
  5. Бс — сумма, которую вкладчик намерен получить в конце срока. Например, 200 000 рублей.

Как всё посчитать

Человек собирается положить на депозит 100 000 рублей под 7% и хочет однажды забрать вдвое больше.

Для этого придётся подождать два с лишним года. Либо искать более доходную инвестицию, которая сократит срок.

8 денежных функций в Excel, которые должен знать каждый финансист

Научитесь использовать все прикладные инструменты из функционала MS Excel.

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

По «старой» традиции начнем с того, как найти финансовые формулы в программе. Сделать это очень просто: на главной панели найти кнопку «Формулы», нажать на нее и выбрать в появившемся списке название раздела «Финансовые».

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

Рис. 1. Список финансовых функций

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

Разумеется, рассказать о всех возможностях в рамках этой статьи мы не успеем, но рассмотрим некоторые их них. Если вы хотите узнать о функционале математических функций в Excel, то скачивайте бесплатный гайд «Математические функции Excel».

1. Функция ДОХОД()

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

Читайте так же:
Как перепрошить Айфон 5s самостоятельно

Аргументов у функции много, поэтому медленно и по порядку со всеми разберемся!

Дата_согл – дата покупки ценных бумаг.

Дата_вступл_в_силу – дата, показывающая истечение срока действия бумаг.

Ставка – купонная ставка ценных бумаг за год.

Цена – цена бумаг на 100 руб. номинальной стоимости.

Погашение – выкупная стоимость ценных бумаг на 100 руб. номинальной стоимости.

Частота – цифра, показывающая количество выплат в год. Ежегодные выплаты – 1, полугодовые – 2, ежеквартальные – 4.

Помимо перечисленных обязательных аргументов есть один необязательный:

Базис – число, характеризующее способ вычисления дня. По умолчанию ставится 0.

Примечание. Обязательные аргументы выделены жирным шрифтом, а необязательные – обычным.

Рис.2. Применение функции ДОХОД()

Замечание. Не рекомендуется вводить дату как текстовую запись. Лучше использовать функцию ДАТА во избежание ошибок и проблем с работой функции.

Например, число 21 сентября 2013 г. лучше записать так: ДАТА(2013,09,21).

2. Функция ПЛТ()

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

У функции 3 обязательных аргумента и 2 – необязательных. Разберемся со всеми по порядку.

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

Кпер – количество выплат или периодов.

Пс – общая сумма, которую нужно выплатить.

БС – показывает, сколько останется выплатить после последней выплаты. По умолчанию подразумевается 0 (то есть после последней выплаты стоимость ссуды составит 0 руб.).

Тип – аргумент, который принимает значения: 0 – когда платежи совершаются в конце периода, 1 – если в начале.

Нужно рассчитать ежемесячный платеж по кредиту в размере 500 000 руб., взятого на 4 года под 6% годовых:

Рис.3. Применение функции ПЛТ()

Так как в условиях задачи была дана процентная ставка за год, то, чтобы рассчитать ставку за один месяц, мы разделили 6% на 12 месяцев.

Так как выплаты производятся каждый месяц, то количество периодов рассчитываем так: 4 * 12 = 48:

Рис.4. Результат функции ПЛТ()

Обратим внимание на то, то результат получился отрицательным. Знак «-» показывает, что эту сумму нужно отдать (вычесть из задолженности).

3. Формула ПС()

Формулу ПС() необходима для нахождения приведенной стоимости (то есть общей суммы, которую нужно выплатить на текущий момент).

Её можно назвать обратной к предыдущему оператору ПЛТ(). У неё точно такие же аргументы, только вместо «Пс» — «Плт» — сумма периодической выплаты.

Функция записывается следующим образом:

ПС(Ставка; Кпер; Плт; Бс; Тип)

Рис.5. Применение функции ПС()
Рис.6. Результат функции ПС()

Мы получили сумму, которую в итоге заплатил бы человек, взявший кредит под 6% годовых на 4 года с ежемесячными выплатами в размере 12 000 руб.

4. Формула ОСПЛТ()

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

При этом учитывается, что параметры Ставка и размер выплат не меняются.

У функции ОСПЛТ() такие же аргументы, как и предыдущая формула: Ставка, Кпер, Пс, БС, Тип.

Еще добавляется Период (обязательный аргумент) – число от 1 до Кпер.

Посмотрим результат функции на предыдущем примере. Нужно рассчитать, сколько денег от первого платежа идет на погашение ссуды, не учитывая оплату процентов:

Рис.7. Применение функции ОСПЛТ()
Рис.8. Результат функции ОСПЛТ()

Мы видим, что основная часть первого платежа равна 9 242,51 руб – это примерно 79% от ежемесячной выплаты.

Если посмотреть результат формулы за 48-ой период, то получим уже 11 684,1 – это 99,5%. Заметная разница говорит о том, что процентные начисления в большей степени выплачиваются в первые расчетные периоды.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

5. Формулы ПРПЛТ(), ОБЩПЛАТ()

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

У функция ПРПЛТ() точно такие же аргументы, как и у ОСПЛТ(), и выглядит в строке ввода формул так:

ПРПЛТ(Ставка; Период; Кпер; Пс; БС; Тип)

Применим формулу к нашему примеру:

Рис.9. Применение функции ПРПЛТ()
Рис.10. Результат функции ПРПЛТ()

Получили, что за первый период сумма выплат по процентам составит 2 500 руб., а в 48 месяце — всего 58,4 руб.

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

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

ОБЩПЛАТ(Ставка;Кпер; Пс; Нач_пер;Кон_пер)

Ниже представлен пример применения функции ОБЩПЛАТ(), где в качестве Нач_пер берем первый период и Кон_пер — второй.

Выплаты происходят в конце месяца:

Рис.11. Применение функции ОБЩПЛАТ()

С помощью этих формул даже рядовой пользователь сможет рассчитать самые выгодные условия кредитования!

6. Формула СТАВКА()

Мы уже узнали, как считать объем ежемесячных выплат, процентные переплаты, число будущих выплат и так далее. Помимо этих действий в Excel можно вычислить ставку по кредиту, используя одноименную функцию СТАВКА().

В качестве аргументов выступают хорошо известные нам критерии: Кпер, Плт, Пс, Бс, Тип.

Два последних аргумента — необязательные:

Рис.12. Применение функции СТАВКА()
Рис.13. Результат функции СТАВКА()

7. Формула БС()

Теперь поговорим о функции БС() – высчитывает стоимость инвестиций после определенного количества периодов при условии неизменной ставки.

Формула записывается следующим образом:

БС(Ставка; Кпер; Плт; Пс; Тип).

Здесь аргумент Пс является необязательным.

Пусть 12% — годовая ставка, количество платежей – 12, каждая выплата — 1 000 руб. (знаком минус покажем, что эти деньги нужно отдавать).

Посчитаем стоимость инвестиций при таких условиях:

Рис.14. Применение функции БС()
Рис.15. Результат функции БС()

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

Заключение

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

Научитесь использовать все прикладные инструменты из функционала MS Excel.

голоса
Рейтинг статьи
Ссылка на основную публикацию