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

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

Делайте расчеты с внедрением денежных функций

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

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

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

ДОХОД

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

= ДОХОД (Дата_входа; Дата_Входа; Ставка; Стоимость; Погашение »Частота; [Базовый])

Основная задачка функции BC — найти будущую стоимость инвестиции. Его аргументы — процентная ставка на период ( "курс" ), полное количество периодов ( "Кол_пер" ) и неизменный платеж за любой период ( "Plt" ). Необязательные значения включают текущую стоимость ( "Ps" ) и установка периода погашения в начале либо в конце периода ( "Тип" ). Оператор имеет последующий синтаксис:

= BS (Ставка; Кол-во; За; Плат; [Па]; [Тип])

Оператор IRR рассчитывает внутреннюю норму доходности для валютных потоков. Единственный неотклонимый аргумент данной для нас функции — это суммы валютных потоков, которые могут быть представлены на листе Excel в виде спектра данных в ячейках ( "Ценности" ). И в первой ячейке спектра обязана быть указана сумма вложения со знаком "-", и в других суммах выручки. Также есть аргумент выбора "Предположение" . Указывает ожидаемую доходность. Если вы не укажете его, значение по дефлоту — 10%. Синтаксис формулы последующий:

= IRR (значения; [предположения])

Оператор МСФО рассчитывает измененную внутреннюю норму доходности с учетом ставки реинвестирования. В данной для нас функции кроме спектра валютных потоков ( "Ценности" ) аргументами являются размер финансирования и степень реинвестирования. Соответственно, синтаксис последующий:

= MIA (значения; ставка_финансирования; ставка реинвестирования)

Оператор PRPLT рассчитывает сумму процентных выплат за обозначенный период. Аргументами функции являются процентная ставка за период ( " курс " ); номер периода ( "Период" ), значение которых не может превосходить полное количество периодов; количество периодов ( "Col_per" ); приведенная стоимость ( "Ps" ). Также есть необязательный аргумент — будущее значение ( "Bs" ). Эту формулу можно употреблять лишь в том случае, если выплаты выполняются идиентично за любой период. Его синтаксис последующий:

= PRPLT (Ставка; Период; Кол-во_пер; Ps; [Bs])

Оператор PLT рассчитывает размер повторяющегося платежа по неизменной ставке. В отличие от предшествующей функции, тут нет аргумента. "Период" . Добавлен доп аргумент "Тип" , который показывает на начало либо конец периода, когда нужно произвести платеж. Другие характеристики вполне совпадают с предшествующей формулой. Синтаксис последующий:

= PLT (Ставка; Кол-во; За; Ps; [Bs]; [Тип])

Формула PS употребляется для расчета приведенной цены инвестиций. Эта функция обратна оператору PDT. Он имеет те же аргументы, но заместо аргумента текущего значения ( "PS" ), который практически рассчитывается, укажите размер повторяющейся выплаты ( "Plt" ). Синтаксис последующий:

= PS (Ставка; Кол-во; За; Плата; [Bs]; [Тип])

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

= ЧПС (ставка; значение1; значение2;…)

Функция СТАВКА рассчитывает аннуитетный процент. Аргументами этого оператора являются количество периодов ( "Col_per" ), размер постоянного платежа ( "Plt" ) и размер платежа ( "Ps" ). Не считая того, есть доп доп аргументы: будущее значение ( "Bs" ) и указание в начале либо конце периода будет произведена выплата ( "Тип" ). Синтаксис последующий:

= СТАВКА (Кол-во; Plt; Ps [Bs]; [Тип])

ЭФФЕКТ

Оператор EFFECT рассчитывает фактическую (либо эффективную) процентную ставку. Эта функция имеет лишь два аргумента: количество периодов в году, за которые используются проценты, и номинальный обменный курс. Его синтаксис смотрится так:

= ЭФФЕКТ (Номер_элемента; Кол-во_пер)

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

Как вести управленческий учет в Excel

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

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

Бюджетирование и управленческий учет

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

Главными задачками для заслуги поставленной цели будут:

• планирование (на базе способа бюджетирования);
• определение издержек и контроль (в т.ч. калькулирование себестоимости на базе принятой систематизации издержек);
• принятие решений.

На практике эти задачки реализуются последующим образом:

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

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

Плюсы ведения «управленки» в Excel:

• Доступность
• Excel имеет ряд специализированных денежных функций
• Наглядность и относительная простота для маленьких компаний
• Возможность развития системы с помощью макросов

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

Управленческая отчетность в Excel

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

• Отчета по доходам и расходам (ОДР, P&L, Profit and Loss, отчет по денежному результату компании)
• Отчета по движению валютных средств (ДДС, CF, Cashflow)
• Баланса (БЛ, BS, Balance sheet)

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

Но есть общие советы для сотворения управленческих отчетов в Excel.

1. Таблицы с различными отчетами должны быть разбиты
   Для облегчения поиска и анализа инфы в разных отчетах нужно делить отчеты по различным листам. Не стоит умещать все данные на одном листе, потому что в этом случае очень затрудняется обновление данных и получение животрепещущих отчетов.
2. Внедрение категорий для суммирования значений
   Для удобства работы с отчетом неплохим тоном является размещение на первой страничке сводных характеристик отчета и их расшифровкой на следующих. Для этого нужно соединить начальные данные по доборной группировке (группы) Таковой формат представления инфы будет нагляднее и удобнее для юзеров.
3. Внедрение автозаполнения
   Справочники единиц бизнеса, центров денежного учета, проектов, статей существенно упрощают работу. С помощью их можно существенно сберегать время на составлении подобных учетных таблиц, пользуясь автозаполнением ячеек либо ограничивая выбор значения из перечня.
4. Пользуйтесь доп функционалом Excel
   Интегрированные аналитические функции, такие как дисперсия, корреляция, математическое и среднее ожидание, способ экстраполяции и остальные, наращивают свойство анализируемой инфы в отчетах и разрешают выявить главные тренды в развитии бизнеса и вовремя на их среагировать.
5. Листы корректировки
   Если отчеты строятся на выгрузке данных из остальных систем, корректировать от руки данные в этих таблицах недозволено. Для этого создаются особые листы корректировки, в которых можно отследить все конфигурации начальных данных.
6. Проверочные ячейки
   При трансформации данных выделяйте проверочные ячейки цветом либо иным методом. Они будут обращать на себя внимание и посодействуют не пропустить ошибки.
7. Настройка защиты листов
   Контрольные листы отчетных форм, листы выгрузки данных, итоговые отчеты должны быть защищены от конфигураций. Конфигурации можно заносить лишь в корректировочные листы.

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

Примеры управленческого учета в Excel

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

Рис. 1 Таблица управленческого учета Excel

Рис. 2 Таблица управленческого учета Excel

Есть ли кандидатуры Excel для управленческого учета

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

При всех собственных плюсах ведение «управленки» в Экселе имеет свои недостатки. К примеру, заносить корректировки в бюджет с помощью Excel и поддерживать их актуальность весьма трудно (а если уж быть поточнее, фактически нереально). Если в бюджете что-то изменяется, если внести в таблицу правку, то потом очень проблемно осознать, почему поменялась какая-то цифра, откуда она совершенно взялась.

Все эти задачи теряют свою актуальность для клиентов 1C-WiseAdvice, которые к всеохватывающему бухгалтерскому обслуживанию подключили наш неповторимый сервис управления деньгами – «Управленка» .

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

Вкладывательные характеристики NPV, IRR: Excel на службе у денежного директора

Как высчитать NPV и IRR, оценить эффективность вкладывательных проектов, высчитать сумму аннуитета и проверить банк на честность. Денежных формул в Excel много. Часть из их создана для расчета амортизации различными методами. Остальные – для определения цены ценных бумаг. Третьи зачем-то еще. Тут мы разберем самые главные и «актуальные» (на мой взор).

Это формулы, которые дозволят высчитать:
— NPV (Net Present Value) — чистую приведенную стоимость.
— IRR (Internal Rate of Return) — внутреннюю ставку доходности.
— Аннуитеты – равномерные платежи.

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

Оценка необходимости проекта с помощью NPV

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

Оцениваем NPV проекта. Напомню формулу этого показателя:

Если валютные потоки, приведенные к текущему периоду, больше инвестированных средств (NPV > 0), то проект прибыльный. В неприятном случае – нет. Иными словами, нам будет нужно создать в Excel последующее:

Добавить порядковые номера лет: 0 – стартовый год, к нему приводятся потоки. 1, 2, 3 и т.д. – это годы реализации проекта. В формуле на рисунке выполнены деяния, которые прописаны выше опосля знака суммы (Σ): валютный поток за период делится на сумму 1 и ставки дисконтирования, возведенную в степень соответственного года.

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

Выходит «-52 303». Проект невыгоден.

Чтоб найти NPV, по сути необязательно готовить такую таблицу. Довольно пользоваться формулой Excel ЧПС. Синтаксис формулы таковой (тут и дальше будет написано не как в справке Excel, а в переводе на понятный язык):

ЧПС(Ставка дисконтирования; Спектр дисконтируемых значений)

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

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

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

Оценка необходимости проекта с помощью IRR

Как еще можно оценить проект? Можно поглядеть на него с точки зрения ставки дисконтирования. Задать вопросец: а какая обязана быть ставка, чтоб NPV стала = 0? Вот данной для нас ставкой как раз и является IRR. Если Ставка дисконтирования < IRR, то проект стоит принять, если нет – отрешиться. Высчитать IRR с помощью Excel весьма просто: подставляем в функцию ВСД итоговый валютный поток.

IRR оказался меньше ставки доходности. Проект нерентабельный (этот же вывод, что и при NPV).

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

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

Аннуитеты – возлюбленная банковская цифра

Поначалу побеседуем о волнующем вопросце – как банки рассчитывают сумму равномерного платежа, как их проверить и как это осознавать. Допустим, вы собираетесь взять кредит 1 000 000 руб. на 5 лет под 10% годичных. Платить будете раз в год равными платежами. Формулу из учебника по денежному менеджменту тут приводить не будем. Приведем формулу Excel:

ПЛТ(Ставка дисконтир; Количество периодов; Сумма кредита которую вы берете)

В формуле еще есть два необязательных пт: сумма, которая обязана остаться (по дефлоту ноль), и как высчитывать сумму – на начало месяца, тогда и ставят 1, либо на конец – ставят ноль. В 90% случаев эти пункты не необходимы, потому их можно не ставить совершенно. Итого аннуитет определяется так:

Сумма каждогоднего платежа выходит сходу с минусом. Эту сумму необходимо любой год платить банку.

В ней содержатся две части: 1) платеж по кредиту, 2) тело кредита.

Ниже они показаны. Платеж по кредиту берется как 10% (процент по кредиту) от суммы задолженности на начало периода. Тело – как разность меж каждогодним платежом и платежом по процентам (в Excel можно отыскать формулы, которые высчитают для вас и эти платежи). Задолженность на конец рассчитывается как разность меж Задолженностью на начало и платежом по телу кредита.

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

Мы бы годичную ставку разделили на 12 (привели к каждомесячному), и взяли не 5 периодов, а 5 • 12 = 60 месяцев. И получили каждомесячный платеж в 21 247 руб.

Аспекты и тонкости

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

Когда кредит и платежи по нему рассчитаны верно, то NPV, взятый по той же процентной ставке, равен нулю. А IRR указывает ставку. Когда банк делает предложение, от которого нереально отрешиться и которое прирастит кредитную ставку «всего» на несколько процентов – не веруйте и пересчитывайте! К примеру, в нашем случае банк предложил страховку «всего» 2 % от суммы кредита в год. Думаете это прирост всего в 2%? Нет! Дело в том, что реальный кредит в начале всякого года миниатюризируется:

В итоге видно, что NPV не равен нулю. А настоящий процент не 10, а 12,9%! Направьте внимание: тут же выросла сумма переплаты. Если вас это смутит, для вас могут предложить «еще наиболее прибыльные условия» — заплатить переплату на данный момент, а остальное позже, наименьшими платежами, либо в нашем примере просто заплатить больше, а позже меньше. Сумма переплаты не поменяется, а вот процент…

Что тут изготовлено? Из всякого следующего платежа взята сумма 43 797 руб. и добавлена к первому же платежу (а бывает выворачивают сумму в момент выдачи кредита). Если для настоящего сектора финансовая математика «средства вчера – средства завтра» кажется несколько отдаленной от жизни, для банков это настоящая прибыль. Потому всеми силами нагружают 1-ый платеж. А вы с помощью обычных формул можете приготовить базу для последующих переговоров.