Excel для финансистов: финансовые функции

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

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

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

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

Наиболее популярные финансовые функции Excel

1. БС

(англ. FV) – возвращает будущую стоимость инвестиций при условиях постоянной процентной ставки, периодических постоянных платежей или единого общего платежа (в виде начальной инвестиции, определяемой аргументом «пс»): = БС(ставка;кпер;плт;[пс];[тип]) , где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000.
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент «плт»),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

2. БЗРАСПИС

(англ. FVSCHEDULE) – возвращает будущую стоимость инвестиций после начисления ряда сложных процентов (с переменной процентной ставкой, подойдет для вкладов с капитализацией процентов): =БЗРАСПИС(первичное;план) , где:

  • «первичное» – стоимость инвестиции на текущий момент,
  • «план» – массив применяемых процентных ставок.

3. ПС

(англ. PV) – возвращает приведенную (текущую) стоимость инвестиции или займа (на основе постоянной процентной ставки): =ПС(ставка; кпер; плт; [бс]; [тип] ), где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000.
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0 и необходимо обязательно указать аргумент «плт»),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

4. ЧПС

(англ. NPV) – возвращает чистую приведенную или дисконтированную стоимость инвестиции при условии серии периодических денежных потоков и с использованием ставки дисконтирования: =ЧПС(ставка; значение1; [значение2],…) , где:

  • «ставка» – ставка дисконтирования за один период;
  • «значение1, значение2,…» – предполагаемые выплаты и поступления (должны быть равномерно распределены во времени, при этом выплаты должны осуществляться в конце каждого периода).

5. ЧИСТНЗ

(англ. XNPV) – возвращает чистую приведенную стоимость для денежных потоков, не обязательно являющихся периодическими: =ЧИСТНЗ(ставка;значения;даты), где:

  • «ставка» – ставка дисконтирования за один период;
  • «значение1, значение2,…» – предполагаемые выплаты и поступления (денежные потоки, соответствующие графику платежей, приведенному в аргументе “даты”. Если первое значение является затратами или выплатой, оно должно быть отрицательным. Все последующие выплаты дисконтируются на основе 365-дневного года. Ряд значений должен содержать по крайней мере одно положительное и одно отрицательное значение);
  • «даты» – график дат платежей, который соответствует платежам для денежных потоков.

6. ПЛТ

(англ. PMT) – возвращает сумму периодического платежа с постоянным процентом и постоянной суммой платежа (подходит для расчета платежей по аннуитету): =ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

7. ПРПЛТ

(англ. IPMT) – возвращает сумму процентных платежей за указанный период только в том случае, если платежи в каждом периоде осуществляются равными частями: =ПРПЛТ(ставка;период;кпер;пс;[бс];[тип]) , где:

  • «ставка» – процентная ставка за период (можно использовать ставку простого процента в случае с депозитами / вкладами): например, если ставка 6% годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 6%/12, при ежеквартальных выплатах аргумент «ставка» будет равен 6%/4;
  • «период» – период, для которого требуется найти платежи по процентам (число в интервале от 1 до аргумента “кпер”);
  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0),
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0),
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода).

8. СТАВКА

(англ. RATE) – возвращает ставку процентов по аннуитету за один период: =СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз] ), где:

  • «кпер» – общее количество периодов для ежегодного платежа: например, в случае кредита на 5 лет и ежемесячных платежах, аргумент «кпер» будет равен 5*12;
  • «плт» – постоянная выплата за каждый период (выплаты – отрицательные значения, поступления – положительные значения): например, если ежемесячный платеж по кредиту составляет 10 000 руб., то аргумент «плт» будет равен -10 000;
  • «пс» – приведенная стоимость или первоначальная (инвестированная или вложенная) сумма (если аргумент опущен, предполагается значение 0);
  • «бс» – будущая стоимость или желаемый остаток средств после последнего платежа (если аргумент опущен, предполагается значение 0, а аргумент «пс» является обязательным);
  • «тип» – срок выплаты в начале (1) или в конце периода (0) (если аргумент «тип» опущен, предполагается значение 0, т.е. в конце периода);
  • «прогноз» – предполагаемая величина ставки (если аргумент “прогноз” опущен, предполагается значение 10%).

9. ЭФФЕКТ

(англ. EFFECT) – возвращает фактическую (или эффективную) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты: =ЭФФЕКТ(номинальная_ставка;кол_пер), где:

  • «номинальная_ставка» — номинальная процентная ставка;
  • «кол_пер» – количество периодов в году, за которые начисляются сложные проценты.

10. ДОХОД

(англ. YIELD) – возвращает доходность ценных бумаг (облигаций), по которым производятся периодические выплаты процентов: =ДОХОД(дата_согл; дата_вступл_в_силу; ставка; цена; погашение, частота; [базис] ), где:

  • «дата_согл» — дата расчета за ценные бумаги (дата продажи ценных бумаг покупателю, более поздняя, чем дата выпуска);
  • «дата_вступл_в_силу» — срок погашения ценных бумаг (момент, когда истекает срок действия ценных бумаг);
  • «ставка» — годовая процентная ставка для купонов по ценным бумагам;
  • «цена» — цена ценных бумаг на 100 рублей номинальной стоимости;
  • «погашение» — выкупная стоимость ценных бумаг на 100 рублей номинальной стоимости;
  • «частота» — кол-во выплат по купонам за год (для ежегодных – 1, для полугодовых — 2, для ежеквартальных — 4);
  • «базис» — используемый способ вычисления дня (если 0 или опущен, то используется американский (NASD) 30/360).

11. ВСД

(англ. IRR) – возвращает внутреннюю ставку доходности для потоков денежных средств (для платежей (отрицательные величины) и доходов (положительные величины), которые имеют место в следующие друг за другом и одинаковые по продолжительности периоды): =ВСД(значения; [предположения]) , где:

  • «значения» – массив или ссылка на ячейки, содержащие ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящих в регулярные периоды времени (по крайней мере одна положительная и одна отрицательная величина);
  • «предположение» — величина, предположительно близкая к результату ВСД ( в большинстве случаев нет необходимости задавать аргумент “предположение”. Если он опущен, предполагается значение 10%).

12. МВСД

(англ. MIRR) – возвращает модифицированную внутреннюю ставку доходности, учитывая процент от реинвестирования средств (при котором положительные и отрицательные денежные потоки имеют разные значения ставки): =МВСД(значения;ставка_финанс;ставка_реинвест) , где:

  • «значения» – массив или ссылка на ячейки, содержащие ряд денежных выплат (отрицательные значения) и поступлений (положительные значения), происходящих в регулярные периоды времени (по крайней мере одна положительная и одна отрицательная величина);
  • «ставка_финанс» – ставка процента, выплачиваемого за деньги, находящиеся в обороте;
  • «ставка_реинвест» – ставка процента, получаемого при реинвестировании денежных средств.

Дополнительные финансовые функции Excel

13. ИНОРМА (англ. INTRATE) – возвращает процентную ставку для полностью инвестированных ценных бумаг: =ИНОРМА(дата_согл;дата_вступл_в_силу;инвестиция;погашение;[базис]).

14. ЧИСТВНДОХ (англ. XIRR) – возвращает внутреннюю норму прибыли для графика поступлений денежных средств, не обязательно носящих периодический характер: =ЧИСТВНДОХ(значения;даты[;предположение]).

15. ДОХОДСКИДКА (англ. YIELDDISC) – возвращает годовой доход по ценным бумагам, на которые сделана скидка (например, по казначейским векселям): =ДОХОДСКИДКА(дата_согл;дата_вступл_в_силу;цена;погашение;[базис]).

16. ДОХОДПОГАШ (англ. YIELDMAT) – возвращает годовой доход по ценным бумагам, проценты по которым выплачиваются в срок погашения: =ДОХОДПОГАШ(дата_согл;дата_вступл_в_силу;дата_выпуска;ставка;цена;[базис]).

17. СКИДКА (англ. DISC) – возвращает норму скидки для ценных бумаг: =СКИДКА(дата_согл;дата_вступл_в_силу;цена;погашение;[базис]).

18. ЦЕНА (англ. PRICE) – возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым производится периодическая выплата процентов: =ЦЕНА(дата_согл;дата_вступл_в_силу;ставка;доход;погашение,частота;[базис]).

19. АСЧ (англ. SYD) – возвращает величину амортизации актива за данный период, рассчитанную по сумме чисел лет срока полезного использования: =АСЧ(нач_стоимость;ост_стоимость;время_эксплуатации;период).

20. ЦЕНАКЧЕК (англ. TBILLPRICE) – возвращает цену за 100 рублей номинальной стоимости для казначейского векселя: =ЦЕНАКЧЕК(дата_согл;дата_вступл_в_силу;скидка).

Использование таблицы данных в Эксель

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

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

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

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

3. Лист прогнозов

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

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

Как работать

  1. Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
  2. Выделите два ряда данных.
  3. На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
  4. В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
  5. Выберите дату окончания прогноза.

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

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

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

Финансово-экономические расчеты в Excel

В учебном пособии приведены материалы к заданиям для самостоятельного выполнения контрольных, лабораторных, практических работ, примеры и методика их решения. Предназначены для студентов, обучающихся по направлениям: 38.03.02 (080200.62) «Менеджмент» (профили: «Производственный менеджмент», «Финансовый менеджмент»), 38.03.01 (080100.62) «Экономика» (профили: «Финансы и кредит», «Экономика предприятия и организации») всех форм обучения, слушателей системы послевузовского образования, преподавателей. Рекомендовано УМО РАЕ по классическому университетскому и техническому образованию в качестве учебного пособия для студентов высших учебных заведений, обучающихся по направлениям подготовки: 38.03.02 (080200.62) – «Менеджмент», 38.03.01 (080100.62) – «Экономика».

Оглавление

  • Введение
  • 1. Модели и методы финансово – экономических расчетов

Приведённый ознакомительный фрагмент книги Финансово-экономические расчеты в Excel предоставлен нашим книжным партнёром — компанией ЛитРес.

1. Модели и методы финансово — экономических расчетов

1.1. Общие положения

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

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

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

Рис. 1. Обобщенная классификация финансовых функций

Условно методы финансовой математики делятся на две категории: базовые и прикладные. К базовым методам и моделям относятся:

простые и сложные проценты. Простые проценты используются, как правило, в краткосрочных финансово-экономических операциях (продолжительностью до года). Базой для исчисления процентов за каждый период в этом случае служит исходная сумма сделки. Сложные проценты применяются в среднесрочных и долгосрочных финансовых операциях (более одного года), но могут применяться и в краткосрочных, если это вызвано объективной необходимостью (риски, высокий уровень инфляции и т.п.). При этом база для исчисления процентов за период включает в себя как исходную сумму сделки, так и сумму уже накопленных к этому времени процентов;

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

— по распределению во времени: регулярные (периодические) и нерегулярные;

— по величине элементов: на постоянные и переменные.

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

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

К прикладным методам финансовых расчетов относятся:

планирование и оценка эффективности финансово-кредитных операций;

расчет страховых аннуитетов;

планирование погашения долгосрочной задолженности;

планирование погашения ипотечных ссуд и потребительских кредитов;

финансовые расчеты по ценным бумагам;

лизинговые, факторинговые и форфейтинговые банковские операции;

планирование и анализ инвестиционных проектов и др.

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

Основными понятиями финансовых методов расчета являются:

процент — абсолютная величина дохода от предоставления денег в долг в любой его форме;

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

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

1.2. Специфика использования финансовых функций Excel

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

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

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

Читайте также:  Красивая диаграмма для презентации отчетов в Excel

3. Осуществляется добавление финансовой функции на рабочий листс помощью команды Формулы, из библиотеки функций активизацией опции Финансовые функции или одновременным нажатием клавиш Shift-F3, а также нажатием одноименной кнопки fx Вставить функцию на панели инструментов Стандартная.

4. Выполняется выбор категории Финансовые (рис. 2). В списке Категория содержится полный перечень доступных функций выбранной категории. Поиск функции осуществляется путем последовательного просмотра списка. Для выбора функции курсор устанавливается на имя функции. В нижней части окна приведен краткий синтаксис и справка о назначении выбираемой функции. Кнопка Справка по этой функции вызывает экран справки для встроенной функции, на которой установлен курсор. Кнопка Отмена прекращает работу опции Вставка функции. При нажатии на кнопку ОК осуществляется переход к работе с диалоговым окном выбранной функции.

Рис. 2. Экран вызова опции Вставка функции

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

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

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

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

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

9. Для отказа от работы со встроенной функцией нажимается кнопка Отмена.

10. Завершение ввода аргументов и запуск расчета значения встроенной функции выполняется нажатием кнопки ОК.

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

Рис.3. Диалоговое окно ввода аргументов функции

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

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

Безусловно, функцию можно ввести, набрав ее прямо в ячейке. Однако Microsoft Excel предоставляет на стандартной панели инструментов кнопку fx Вставить функцию (см. рис. 4).

Рис. 4. Стандартная панель инструментов (кнопка Вставитьфункцию)

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

— все аргументы, означающие расходы денежных средств, представляются отрицательными числами (например, ежегодные платежи), а аргументы, означающие поступления, представляются положительными числами (например, дивиденды);

— все даты как аргументы функции имеют числовой формат представления, например, дата 1 января 1995 года представлена числом 34700. Если значение аргумента типа дата берется из ячейки, то дата в ячейке может быть записана в обычном виде;

— для аргументов типа логический возможен непосредственный ввод констант типа ИСТИНА или ЛОЖЬ, либо использование встроенных функций аналогичного названия категории Логические

Показатели инвестиционного проекта

Для ответа на вышеприведённые вопросы используют следующие показатели эффективности инвестиционного проекта:

  • срок окупаемости проекта (обычно в месяцах)
  • чистая приведённая стоимость (net present value, NPV)
  • внутренняя норма доходности (IRR).

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

Недостаток этого показателя – игнорирование факта изменения стоимости денег во времени (дисконтирования). Дисконтирование — это приведение будущих денежных потоков к текущему периоду с учетом изменения стоимости денег с течением времени. Дисконтирование производится путём умножения значений будущих потоков на понижающий коэффициент:

Кд = 1 / (1 + Ставка дисконтирования)^Номер периода

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

  • стоимостью привлекаемого капитала инвестора;
  • прогнозной инфляцией;
  • премией за риск проекта.

Коэффициент дисконтирования используется для расчёта показателя Чистая приведённая стоимость (net present value -NPV), который по сути является совокупным дисконтированным денежным потоком. Проект считается экономически выгодным, если его NPV не отрицательна. Нулевое значение NPV говорит о том, что проект принесет прибыль, достаточную для выплаты процентов по привлечённому капиталу с учётом инфляции. Чем выше NPV проекта, тем он привлекательнее (при учете рисков).

Для того чтобы получить более универсальную оценку привлекательности инвестиционного проекта, можно рассчитать третий показатель: внутреннюю норму доходности (IRR) – значение ставки дисконтирования, при которой NPV равен нулю (то есть проект отобъёт вложенные в него средства). Считается, что проект приемлем, если расчётное значение IRR больше ставки дисконтирования. Кроме того, этот показатель удобно использовать при сравнении альтернативных инвестиционных проектов: для каждого рассчитывается показатель IRR и предпочтение отдаётся проекту с наибольшим IRR.

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

Рубрика Программирование, компьютеры и кибернетика
Вид реферат
Язык русский
Дата добавления 07.11.2012
Размер файла 481,0 K

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Министерство образования республики Беларусь

Белорусский государственный университет

Государственный институт управления и социальных технологий

на тему: «Финансовые функции Excel»

Студентка 1 курса 113 группы

Качан Яна Эрнстовна

1. Функции Excel

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

2.1 Перечень Финансовых функций Excel

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

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

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

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

1. Функции Excel.

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

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

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

Финансовые функции — это совокупность необходимых процессов, циклов и подразделений, которые взятые вместе:

· способствуют получению выручки на всех стадиях с помощью проведения финансового анализа;

· управляют расходами с помощью анализа необходимости закупок, утверждения всех расходов (включая капиталовложения) в масштабе всего предприятия;

· следят за имеющимися финансовыми средствами и обязательствами;

· управляют поступлениями и расходованиями денежных средств, включая оплату налогов;

· ведут поиск оптимальных источников и условий финансирования;

· способствуют проведению бартерных операций;

· осуществляет финансовый анализ проектов на всех этапах;

· выполняют работу по составлению бюджета, планированию и прогнозированию как для предприятия, так и его подразделений;

· учитывают и регистрируют каждую операцию, проводимую предприятием;

· способствуют распределению заработанного между сотрудниками и акционерами.

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

2.1 Перечень финансовых функций:

АМОРУВ — возвращает величину амортизации для каждого периода.

НАКОПДОХОД — данная функция возвращает накопленный процент по ценным бумагам с периодической выплатой процентов.

НАКОПДОХОДПОГАШ — возвращает накопленный процент по ценным бумагам, процент по которым выплачивается в срок погашения.

АМОРУМ — возвращает величину амортизации для каждого периода.

ДНЕЙКУПОНДО — возвращает количество дней от начала действия купона до даты соглашения.

ДНЕЙКУПОН — вычисляет число дней в периоде купона, содержащем дату расчета.

ДНЕЙКУПОНПОСЛЕ — возвращает число дней от даты расчета до срока следующего купона.

ДАТАКУПОНДО — возвращает число, представляющее дату следующего купона от даты соглашения.

ЧИСЛКУПОН — возвращает количество купонов, которые могут быть оплачены между датой соглашения и датой вступления в силу, округленное до ближайшего целого купона.

ОБЩПЛАТ — вычисляет кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат. exсel финансовый электронный таблица

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

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

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

СКИДКА — определяет ставку дисконтирования (норму скидки) для ценных бумаг.

РУБЛЬ.ДЕС — преобразует цену в рублях, представленную в виде дроби, в цену в рублях, выраженную десятичным числом. Функция РУБЛЬ.ДЕС используется для преобразования дробных значений денежных сумм, например стоимости ценных бумаг, в десятичное число.

РУБЛЬ.ДРОБЬ — преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, представленную в виде дроби. Функция РУБЛЬ.ДРОБЬ используется для преобразования десятичных чисел, например стоимости ценных бумаг, в дробные цены.

ДЛИТ — находит ежегодную продолжительность действия ценных бумаг с периодическими выплатами по процентам.

ЭФФЕКТ — определяет эффективную (фактическую) годовую процентную ставку, если заданы номинальная годовая процентная ставка и количество периодов в году, за которые начисляются сложные проценты.

ЭФФЕКТ вычисляется следующим образом:

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

БЗРАСПИС — вычисляет будущее значение начального вклада при изменяющихся сложных процентных ставках.

ИНОРМА — определяет ставку доходности полностью обеспеченной ценной бумаги.

ПРПЛТ — определяет сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

ВСД — вычисляет внутреннюю ставку доходности (отдачи) для серии потоков денежных средств.

ПРОЦПЛАТ — вычисляет проценты, выплачиваемые за определенный инвестиционный период.

МДЛИТ — определяет модифицированную длительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.

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

НОМИНАЛ — определяет номинальную годовую процентную ставку.

КПЕР — определяет общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

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

ЧПС аналогична функции ПС (текущее значение). Основное различие между функциями ПС и ЧПС заключается в том, что ПС допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. В функции ЧПС денежные взносы могут быть переменной величиной, тогда как в функции ПС они должны быть постоянными на протяжении всего периода инвестиции. ЧПС связана также с функцией ВСД (внутренняя ставка доходности). ВСД — это ставка, для которой ЧПС равняется нулю: ЧПС(ВСД(. ); . ) = 0.

ЦЕНАПЕРВНЕРЕГ — находит цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным первым периодом.

ДОХОДПЕРВНЕРЕГ — находит доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом.

ЦЕНАПОСЛНЕРЕГ — определяет цену за 100 рублей нарицательной стоимости ценных бумаг для нерегулярного (короткого или длинного) последнего периода купона.

ДОХОДПОСЛНЕРЕГ — определяет доход по ценным бумагам с нерегулярным последним периодом.

ПЛТ — Вычисляет величину выплаты по ссуде за один период.

ОСПЛТ — Вычисляет величину выплат на основной капитал для вклада в заданный период.

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

ЦЕНАПОГАШ — вычисляет цену за 100 рублей номинальной стоимости ценных бумаг, по которым процент выплачивается в срок погашения.

ЦЕНАСКИДКА — определяет цену за 100 рублей номинальной стоимости ценных бумаг, на которые сделана скидка.

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

СТАВКА — определяет процентную ставку по аннуитету за один период

ПОЛУЧЕНО — вычисляет сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг.

АПЛ — определяет величину амортизации актива за один период, рассчитанную линейным методом.

АСЧ — определяет величину амортизации актива за данный период, рассчитанную по сумме чисел лет срока полезного использования.

РАВНОКЧЕК — вычисляет эквивалентный облигации доход по казначейскому векселю.

ЦЕНАКЧЕК — вычисляет цену на 100 рублей номинальной стоимости для казначейского векселя.

ЦЕНАКЧЕК вычисляется следующим образом:

ДОХОДКЧЕК — вычисляет доходность по казначейскому векселю.

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

ЧИСТВНДОХ — вычисляет внутреннюю ставку доходности запланированных непериодических денежных потоков.

ЧИСТНЗ — вычисляет чистую текущую стоимость инвестиции, вычисляемую на основе ряда поступлений наличных, которые не обязательно являются периодическими.

Функция ЧИСТНЗ вычисляется следующим образом:

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

ДОХОДСКИДКА — вычисляет годовую доходность по ценным бумагам, на которые сделана скидка.

ДОХОДПОГАШ — определяет годовую доходность ценных бумаг, по которым проценты выплачиваются при наступлении срока погашения.

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

Задача 1. Определение срока платежа и процентной ставки

Для покрытия будущих расходов фирма создает фонд. Средства в фонд поступают в виде постоянной годовой ренты постнумерандо. Сумма разового платежа 16 000 руб. На поступившие взносы начисляются 11,2% годовых.

Необходимо определить, когда величина фонда будет равна 100 000 руб.

Алгоритм решения задачи.

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

ставка = 11,2%; плт = -16; бс = 100. В результате вычислений получим, что через 5 лет величина фонда достигнет отметки 100 000 руб.:

= КПЕР (11,2%;-16;;100) = 5

Решение задачи может быть найдено и иным способом — с помощью функций БС (либо ПС) и последующего подбора параметра.

Иллюстрация решения приведена на рис. 1.1

Рис.1.1 Применение функции БС и механизма подбора параметра для определения числа периодов.

Определить платежи по процентам за первый месяц от трехгодичного займа в 100 000 рублей из расчета 10% годовых.

Алгоритм решения задачи.

Для определения платежа по процентам за первый месяц заданного периода применим функцию ПРПЛТ со следующими аргументами:

Ставка = 10% / 12 (процентная ставка за месяц);

Период = 1 (месяц);

Кпер = 3*12=36 (месяцев);

ПС = 100 000 (величина займа).

Тогда платежи по процентам за первый месяц составят:

= ПРПЛТ (10%/12 ; 1 ; 36 ; 100 000) = — 833,33 руб.

Знак «минус» означает расход для заемщика, то есть что платеж по процентам необходимо внести.

Иллюстрация решения задачи приведена на рис. 1.2.

Заключение

В данном реферате были рассмотрены основные финансовые функции Exel, их синтаксис, значение; а на основе примеров наглядно продемонстрировано как, в каком случае работают эти функции. Конечно, зачастую не каждый пользователь нуждается в «Мастере функций», не говоря уже о конкретно финансовых. Но людям, непосредственно работающим в экономической сфере и имеющим дело с различными материальными и финансовыми операциями, знание этих функций просто необходимо. Размещено на Allbest.ru

Список литературы

1. Каймин В.А. Информатика. Учебник. — М.: Инфра-М, 2003

2. Козырев А.А. Информатика. Учебник. — СПб: изд-во Михайлова В.А., 2003

3. Справка по программе Microsoft Excel.

5. Microsoft Excel 2000. Шаг за шагом: Практическое пособие. /Пер. с англ. — М: Издательство ЭКОМ. 2001

6. Символоков, Л.В. Решение бизнес-задач в Microsoft Office: учеб. пособие / Л.В. Символоков. — М.: ЗАО «Издательство Бином», 2001.

Подобные документы

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

реферат [291,2 K], добавлен 03.07.2015

Функции Microsoft Excel — встроенные инструменты, которые применяются в формулах. Их виды и основы работы с ними. Организация обработки табличных данных при помощи статистических функций. Примеры решения различных задач при помощи электронных таблиц.

курсовая работа [958,6 K], добавлен 21.07.2011

Понятие и назначение электронных таблиц. Сравнительная характеристика редакторов электронных таблиц Microsoft Excel, OpenOffice.org Calc, Gnumeric. Требования к оформлению электронных таблиц. Методика создания электронных таблиц в MS Word и MS Excel.

контрольная работа [1,5 M], добавлен 07.01.2015

Понятие и возможности MS Excel. Основные элементы его окна. Возможные ошибки при использовании функций в формулах. Структура электронных таблиц. Анализ данных в Microsoft Excel. Использование сценариев электронных таблиц с их практическим применением.

курсовая работа [304,3 K], добавлен 09.12.2009

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

реферат [51,2 K], добавлен 22.01.2012

Процессор электронных таблиц Microsoft Excel — прикладная программа, предназначенная для автоматизации процесса обработки экономической информации, представленной в виде таблиц; применение формул и функций для производства расчетов; построение графиков.

Читайте также:  Расчет IRR в Excel с помощью функций и графика

реферат [2,4 M], добавлен 03.02.2013

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

Финансовые расчеты в MS Excel

Рубрика Финансы, деньги и налоги
Вид контрольная работа
Язык русский
Дата добавления 11.10.2013
Размер файла 27,1 K

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Размещено на http://www.allbest.ru/

    Введение
  • 1. Теоретическая часть
    • 1.1 Особенности использования финансовых функций вMSExcel
    • 1.2 Технология работы и виды финансовых функций в области кредитования вMSExcel
    • 2.1 Постановка задачи
    • 2.2 Решение поставленных задач

    В настоящее время трудно переоценить роль специалиста по финансовому анализу деятельности предприятия. Финансы являются «кровью» предприятия. Именно в деньгах оцениваются проданные товары и оказанные клиентам услуги. Именно деньги являются универсальным измерителем необходимых предприятию ресурсов — сырья и материалов, станков, человеческих ресурсов, информации и т.д. поэтому планирование и прогнозирование, контроль и оптимизация финансовых потоков являются жизненно важными задачами финансовой службы. (Л.А., 2006)

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

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

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

    1. Теоретическая часть

    1.1 Особенности использования финансовых функций в MS Excel

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

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

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

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

    Однако для ряда пользователей существуют трудности при использовании финансовых функций в среде EXCEL, поскольку синтаксис пакета использует иные обозначения основных понятий финансовых операций, нежели в классических расчетах. (Пикуза В., 2004)

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

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

    · можно вводить как сами значения аргументов, так и ссылки на адреса ячеек;

    · все расходы денежных средств (платежи) представляются отрицательными числами, а все поступления денежных средств — положительными числами;

    · процентная ставка вводится с использованием знака %;

    · все даты как аргументы функций имеют числовой формат.

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

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

    1.2 Технология работы и виды финансовых функций в области кредитования в MS Excel

    Технология работы с финансовыми функциями на рабочих листах Excel в целом не отличается от работы с другими функциями:

    1) подготовка исходных значений основных аргументов функции;

    2) для расчета финансовой функции курсор устанавливается в нужную ячейку и вызывается с панели задач Диспетчер функций;

    3) из появившегося списка выбираем в разделе финансовых функций необходимую;

    4) вводим аргументы функций;

    5) получаем результат.

    К основным финансовым функциям в Excel в области расчетов кредитования удобно использовать: ПС(), ПЛТ(), ОСПЛТ(), ПРПЛТ(), КПЕР(), БС(), СТАВКА().

    Назначение финансовых функций представлено в таблице 1.1. (Куприянова А.В., 2007)

    Таблица 1.1 Назначение финансовых функций

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

    Предназначена для расчета текущей стоимости, как единой суммы вклада (займа), так и будущих фиксированных периодических платежей

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

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

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

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

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

    Как видно из таблицы, практически все функции содержат одинаковый набор аргументов:

    Ставка — процентная ставка за период (норма доходности или цена заемных средств — r)

    Кпер — срок (число периодов n) процедения операции.

    Плт — выплата производимая каждый период и не меняющаяся за все время выплаты ренты.

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

    Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (например будущая стоимость займа равна 0)

    [тип] — число 0 или 1, обозначающее когда должна производится выплата (1 — начало периода (обычная рента или пренумерандо), 0 — конец периода (постнумерандо)).

    Как видно во многом функции перекрещиваются между собой, таким образом в решение одной финансовой задачи по расчету к примеру платежей по кредиту может использоваться несколько функций. (Мак-Федрис, 2006)

    2. Практическая часть

    2.1 Постановка задачи

    Необходимо на практике изучить финансовые функция для расчетов по кредитам: ПС(), БС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), КПЕР.

    Для этого необходимо решить следующие задачи:

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

    2) Рассчитать сумму ежемесячного вложения под 10% годовых, которое через 15 лет составит сумму вклада в 50000 рублей. Выплата производится в начале периода. (Использование функции ПЛТ()).

    3) Рассчитать сумму ежемесячного вложения под 10% годовых, которое через 15 лет составит сумму вклада 50000 рублей, при первоначальном взносе 1000 рублей. (Использование функции ПЛТ()).

    4) Рассчитать величину вложений под 18 % годовых, которые будут приносить ежегодно в течение 5 лет 20 000 рублей. (Использование функции ПС()).

    5) Рассчитать величину первоначальных вложений, под 15% годовых, которое через 10 лет принесет доход 100000 рублей, при условии внесении раз в год на счет 2000 рублей. (Использование функции ПС()).

    6) Вычислить выплаты по процентам за первый месяц для трехгодичного займа в 100000 рублей из расчета 10% годовых. (Использование функции ПРПЛТ()).

    7) Вычислить доход за последний год от трехгодичного займа в 100000 рублей из расчета 10% годовых при ежегодных выплатах. (Использование функции ПРПЛТ()).

    8) Вклад размером в 5000 рублей положен с 10.01.2010 по 03.04.2010 под 20% годовых. Найти величину капитала на 03.04.2010 при начислении простых процентов. (Использование функции БС()).

    9) Определить сумму капитала, если изначально вложена сумма в размере 10000 рублей, в банк на 3 года под 15% годовых, далее в течение всего периода раз в месяц вносится сумма 1000 рублей. Проценты начисляются раз в месяц, в начале. (Использование функции БС()).

    10) Определить будущую стоимость капитала 15000 рублей, помещенных в банк под 18% годовых, сроком на 5 лет. Проценты начисляются раз в квартал. (Использование функции БС()).

    11) Взята сумма в размере 90000 рублей сроком на 2 года под 15% годовых. Рассчитать сумму остаточных платежей для каждого года займа. (Использование функции ОСПЛТ()).

    12) С кредитно-дебетовой карты взята сумма в размере 70000 рублей сроком на 3 года под 17% годовых. Рассчитать сумму остаточных платежей для каждого квартала займа, при условии, что конец периода на счету должна быть накоплена сумма 8000 рублей. (Использование функции ОСПЛТ()).

    13) Рассчитать через сколько лет сумма вклада в размере 15000 рублей достигнет 50000 рублей, при процентной ставке 15% годовых. (Использование функции КПЕР()).

    14) Начиная с 30 лет каждый год на счет в банк вносится 1000 рублей. К какому возрасту человек станет миллионером, при условии, что процентная ставка равна 18% годовых. (Использование функции КПЕР()).

    15) Рассчитать через сколько лет произойдет полное погашение займа размером 2500000 рублей, если выплаты 50000 рублей производятся в конце каждого квартала, а процентная ставка равна 17% годовых. (Использование функции КПЕР()).

    2.2 Решение поставленных задач

    Для решение поставленных задач используются функции ПС(), БС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), КПЕР.

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

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

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

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

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

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

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

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

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

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

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

    1. Введение значений с клавиатуры.
    2. Выбор ячеек с указанными значениями.

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

    finansovye-funkcii-excel-podborka-populyarnyh-funkcij-i-ih-opisanie

    Библиотека финансовых функций

    Суммировать только нужное

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

    Мы попробуем узнать, сколько Аня тратит на еду в офисе. Для этого в таблице создаем формулу =СУММ((А2:А16=F2)*(B2:B16=F3)*C2:С16) и получаем 915 рублей. Теперь постепенно.

    В первой скобке программа ищет значение из ячейки F2 («Аня») в столбце с именами. Во второй скобке — значение из ячейки F3 («Еда на работе») из столбца с категориями расходов. А после считает сумму ячеек из третьего столбца, которые выполнили эти условия.

    Таблица в «Экселе»: плюсы, минусы, подводные камни

    Возвращаться к мобильным приложениям я не хотел, так как помнил, какие с ними были проблемы. Делать свое веб-приложение — тогда я зарабатывал на жизнь именно этим — было лень. К тому же не хотелось зависеть от наличия интернета и тратить время и деньги на поддержку сервера. И тут на помощь пришел старый добрый «Эксель».

    Преимуществ у электронных таблиц масса:

    1. Независимость от платформы. Хочешь — фиксируй траты на телефоне с Андроидом, а хочешь — анализируй сводку на Макбуке или традиционном компьютере с Виндоус.
    2. Функциональность ограничена только фантазией.
    3. Формулы либо элементарны, либо хорошо задокументированы.
    4. Абсолютно бесплатно!

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

    В итоге таблица обрела следующую структуру.

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

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

    Звучит слишком сложно? Полностью с вами согласен! Наиболее утомительная часть всего учета — переводы между счетами — была реализована в виде пары операций: расходной с одного счета и доходной для другого.

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

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

    Лист 3. Банки. Эта таблица складывает строки счетов и группирует данные по валютам. Благодаря автоматическому импорту курсов доллара и евро с сайта Центробанка РФ можно вычислить итоговую сумму в рублях, по которой строится диаграмма с долями финансов в разных банках.

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

    Лист 5. Ценные бумаги. На этот лист пришлось потратить больше всего времени: нужно было свести в одном месте данные по акциям, фондам и облигациям у четырех разных брокеров в разных валютах.

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

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

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

    Финансовые расчеты в Excel. Функция ПЛТ (PMT)

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

    Одна из самых больших и популярных категорий функций — финансовые. В последней версии Excel есть 55 функций, относящихся к этой группе. Многие из них специфические и узконаправленные, но некоторые могут пригодиться практически каждому. Одна из таких базовых функций — ПЛТ (PMT).

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

    Синтаксис функции

    Функция имеет следующий синтаксис:

    ПЛТ(ставка; кпер; пс; [бс]; [тип])

    Разберем по очереди все аргументы:

    • Ставка. Обязательный аргумент. Представляет процентную ставку за период. Самое главное здесь — не ошибиться в пересчете размера ставки на нужный период. Если предполагается погашать кредит ежемесячными платежами, а ставка годовая — то ее нужно перевести в месячную, разделив на 12. Если же, например, кредит гасится 1 раз в квартал, то годовую ставку нужно поделить на 4 (и получить таким образом ставку за 1 квартал). Ставку можно указать в процентах или в сотых долях.
    • Кпер. Обязательный. Этот аргумент представляет собой число расчетных периодов (сколько раз будет вноситься платёж в счёт погашения кредита). Как и ставка, этот аргумент зависит от того, какой расчетный период принят для вычислений. Если кредит получен на 5 лет с платежами 1 раз в месяц, то Кпер = 5*12 = 60 периодов . Если же на 3 года, с платежами 1 раз в квартал — то Кпер = 3*4 = 12 периодов .
    • Пс . Обязательный. Сумма кредита, то есть объем долга, который нужно будет погасить будущими платежами.
    • [бс]. Необязательный. Сумма долга, которая должна остаться неоплаченной после истечения всех расчетных периодов. Обычно этот аргумент равен 0 (кредит должен быть погашен полностью). Так как аргумент необязательный, то его можно не указывать (в таком случае он будет принят равным нулю).
    • [тип]. Необязательный. Обозначает момент произведения выплаты — в начале или в конце периода. Для первого случая нужно указать единицу, а для второго ноль (или вообще пропустить этот аргумент). В большинстве случаев используется второй вариант — выплаты в конце периода, а значит чаще всего этот аргумент можно опустить.

    Особенностью синтаксиса функции является указание направления денежного потока. Если денежный поток входящий (например, сумма полученного кредита, указанная в аргументе Пс), то необходимо указывать его как положительное число. Исходящие потоки наоборот, указываются как отрицательные числа (например, после вычисления функция ПЛТ вернет отрицательный результат, так как размер платежа по кредиту — это исходящий денежный поток).

    Примеры использования

    Задача 1. Расчет суммы выплат по кредиту

    Предположим, что в банке получен кредит на сумму 1 000 000 руб. под 17,5% годовых на срок 6 лет. Кредит будет погашаться равными платежами ежемесячно на протяжении всего срока займа. К концу срока будет выплачена вся сумма долга. Первый платеж будет внесен в конце первого периода. Необходимо найти величину ежемесячного платежа.

    Итак, нам известна годовая ставка, а кредит будет погашаться ежемесячно. Значит для расчета нам потребуется перевести годовую ставку в месячную, разделив 17,5% на 12 месяцев. В первый аргумент записываем 17,5%/12 .

    Кредит получен на 6 лет. Выплачивается ежемесячно. Значит, количество периодов выплат = 6*12. Во второй аргумент записываем 72 .

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

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

    Формула примет вид:

    Результат вычисления равен -22526,05 руб . Число отрицательное, так как платеж по кредиту для займополучателя является исходящим денежным потоком. Именно такую сумму нужно будет вносить каждый месяц для погашения кредита, описанного в условии.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Цена – цена бумаг на 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.

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

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

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

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

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

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

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

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

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

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

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

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

    Функция ЭФФЕКТ

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

    • Нс — номинальная процентная ставка.
    • Кпер — количество периодов в году, за которые начисляются сложные проценты.

    Существует множество способов упростить и ускорить работу в Excel, и мы с радостью расширим эти списки вашими советами.

Ссылка на основную публикацию