Excel имеет значительную популярность среди бухгалтеров, экономистов и финансистов не в последнюю очередь благодаря обширному инструментарию по выполнению различных финансовых расчетов. Главным образом выполнение задач данной направленности возложено на группу финансовых функций. Многие из них могут пригодиться не только специалистам, но и работникам смежных отраслей, а также обычным пользователям в их бытовых нуждах. Рассмотрим подробнее данные возможности приложения, а также обратим особое внимание на самые популярные операторы данной группы.
В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.
Переход к данному набору инструментов легче всего совершить через Мастер функций.
В Мастер функций также можно перейти через вкладку «Формулы» . Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию» , размещенную в блоке инструментов «Библиотека функций» . Сразу вслед за этим запустится Мастер функций.
Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые» . После этого откроется выпадающий список всех доступных инструментов данного блока. Выбираем нужный элемент и кликаем по нему. Сразу после этого откроется окно его аргументов.
ДОХОД
Одним из наиболее востребованных операторов у финансистов является функция ДОХОД . Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота). Именно эти параметры являются аргументами данной формулы. Кроме того, имеется необязательный аргумент «Базис» . Все эти данные могут быть введены с клавиатуры прямо в соответствующие поля окна или храниться в ячейках листах Excel. В последнем случае вместо чисел и дат нужно вводить ссылки на эти ячейки. Также функцию можно ввести в строку формул или область на листе вручную без вызова окна аргументов. При этом нужно придерживаться следующего синтаксиса:
ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])
БС
Главной задачей функции БС является определение будущей стоимости инвестиций. Её аргументами является процентная ставка за период («Ставка» ), общее количество периодов («Кол_пер» ) и постоянная выплата за каждый период («Плт» ). К необязательным аргументам относится приведенная стоимость («Пс» ) и установка срока выплаты в начале или в конце периода («Тип» ). Оператор имеет следующий синтаксис:
БС(Ставка;Кол_пер;Плт;[Пс];[Тип])
ВСД
Оператор ВСД вычисляет внутреннюю ставку доходности для потоков денежных средств. Единственный обязательный аргумент этой функции – это величины денежных потоков, которые на листе Excel можно представить диапазоном данных в ячейках («Значения» ). Причем в первой ячейке диапазона должна быть указана сумма вложения со знаком «-», а в остальных суммы поступлений. Кроме того, есть необязательный аргумент «Предположение» . В нем указывается предполагаемая сумма доходности. Если его не указывать, то по умолчанию данная величина принимается за 10%. Синтаксис формулы следующий:
ВСД(Значения;[Предположения])
МВСД
Оператор МВСД выполняет расчет модифицированной внутренней ставки доходности, учитывая процент от реинвестирования средств. В данной функции кроме диапазона денежных потоков («Значения» ) аргументами выступают ставка финансирования и ставка реинвестирования. Соответственно, синтаксис имеет такой вид:
МВСД(Значения;Ставка_финансир;Ставка_реинвестир)
ПРПЛТ
Оператор ПРПЛТ рассчитывает сумму процентных платежей за указанный период. Аргументами функции выступает процентная ставка за период («Ставка» ); номер периода («Период» ), величина которого не может превышать общее число периодов; количество периодов («Кол_пер» ); приведенная стоимость («Пс» ). Кроме того, есть необязательный аргумент – будущая стоимость («Бс» ). Данную формулу можно применять только в том случае, если платежи в каждом периоде осуществляются равными частями. Синтаксис её имеет следующую форму:
ПРПЛТ(Ставка;Период;Кол_пер;Пс;[Бс])
ПЛТ
Оператор ПЛТ рассчитывает сумму периодического платежа с постоянным процентом. В отличие от предыдущей функции, у этой нет аргумента «Период» . Зато добавлен необязательный аргумент «Тип» , в котором указывается в начале или в конце периода должна производиться выплата. Остальные параметры полностью совпадают с предыдущей формулой. Синтаксис выглядит следующим образом:
ПЛТ(Ставка;Кол_пер;Пс;[Бс];[Тип])
ПС
Формула ПС применяется для расчета приведенной стоимости инвестиции. Данная функция обратная оператору ПЛТ . У неё точно такие же аргументы, но только вместо аргумента приведенной стоимости («ПС» ), которая собственно и рассчитывается, указывается сумма периодического платежа («Плт» ). Синтаксис соответственно такой:
ПС(Ставка;Кол_пер;Плт;[Бс];[Тип])
ЧПС
Следующий оператор применяется для вычисления чистой приведенной или дисконтированной стоимости. У данной функции два аргумента: ставка дисконтирования и значение выплат или поступлений. Правда, второй из них может иметь до 254 вариантов, представляющих денежные потоки. Синтаксис этой формулы такой:
ЧПС(Ставка;Значение1;Значение2;…)
СТАВКА
Функция СТАВКА рассчитывает ставку процентов по аннуитету. Аргументами этого оператора является количество периодов («Кол_пер» ), величина регулярной выплаты («Плт» ) и сумма платежа («Пс» ). Кроме того, есть дополнительные необязательные аргументы: будущая стоимость («Бс» ) и указание в начале или в конце периода будет производиться платеж («Тип» ). Синтаксис принимает такой вид:
СТАВКА(Кол_пер;Плт;Пс[Бс];[Тип])
ЭФФЕКТ
Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:
ЭФФЕКТ(Ном_ставка;Кол_пер)
Нами были рассмотрены только самые востребованные финансовые функции. В общем, количество операторов из данной группы в несколько раз больше. Но и на данных примерах хорошо видна эффективность и простота применения этих инструментов, значительно облегчающих расчеты для пользователей.
Практический курс «Программирование в Excel для финансистов» разработан для бухгалтеров, финансовых аналитиков, специалистов в сфере управленческого учета, а также специалистов, которые часто сталкиваются с обработкой больших объемов финансовых данных и хотят изучить возможности автоматизации этих процессов с помощью создания программ в MSExcel.
Расширение практических навыков позволит слушателям взглянуть на обработку финансовой информации под другим углом - взглядом программиста и поможет самостоятельно решать сложные задачи обработки финансовой информации наиболее эффективно и быстро.
С помощью полученных знаний вы легко сможете самостоятельно автоматизировать свою рутинную работу (сверить две выписки, сформировать график платежей, произвести сверку актов, загрузить курсы валют с сайта Центрального Банка РФ, сделать консолидированный ОДДС (Cash-flowstatement) по выпискам нескольких компаний холдинга и многое другое).
Тренинг носит практическую направленность, поэтому для участия в нём слушателям необходимо принести ноутбук с установленным Microsoft Office не ниже 2010 версии (для Windows) с возможностью создания макросов.
Программа обучения
Практический курс «Программирование в Excel для финансистов» состоит из двух блоков:
Базовый блок
Данный блок предлагает освоить (освежить) базовые знания в области программирования на языке VisualBasic (forExcel).
1) Интерфейс редактора Visual Basic
- Окно редактора VBA Безопасность макросов, рекомендации по выбору уровней безопасности
- Функциональные окна и их назначение. Работа в редакторе
2) Простая запись макроса
- Просмотр и корректировка программного кода, созданного автоматически
- Оптимизация кода
- Создание и отладка процедуры Sub
- Ввод программного кода в «ручном режиме», варианты запуска макросов. Упрощенный ввод программного кода в ручном режиме
3) Основы программирования в Visual Basic (изучение на основе редактора VB для MSExcel)
- Использование служебного блока WITH … END WITH
- Объявление переменных «Область жизни» переменной (локальные и глобальные переменные)
- Типы переменных Возможности использования сложных (объектных) переменных
- Управляющие операторы языка Visual Basic
- Циклы в Visual Basic
- Встроенные функции VB for Office
- Основные функции обработки данных разных типов. Преобразование типов данных
- Создание пользовательских функций
- Отличие от процедур. Различные варианты передачи параметров (переменных) в функцию Практическая отработка навыков разработки функций
4) Отладчик программ
- Возможные ошибки при написании программ. Ошибки первого уровня (синтаксические), второго уровня (логические) и Runtime-ошибки
- Возможности по использованию отладчика для поиска ошибок в программах
5) Заключительная часть
- Основы объектной модели MSExcel
- Основные свойства и методы объекта Range Варианты использования. Практическое применение для обработки данных
- Объект Selection. Практическое использование объекта
По итогам обучения на базовом блоке слушатели смогут:
- Самостоятельно записывать макросы, вносить в них правки, оптимизировать код макросов
- Писать процедуры в «ручном режиме», запускать их в различных режимах
- Создавать пользовательские функции для автоматизации обработки данных в MSExcel
- Использовать переменные и понимать особенности применения различных типов переменных
- Разбираться в различиях объявления переменных
- Выявлять ошибки в программах, используя отладчик редактора Visual Basiс
Продвинутый блок
Данный блок предлагает освоить сложные навыки знания в области создания программ на языке Visual Basic (for Excel).
1) Объектно-ориентированное программирование (изучение на основе редактора VB для MSExcel)
- Понятие объекта, классов, событий и методов
2) Объектная модель MSExcel
- объект Workbook (коллекция WorkBooks)
- объект Worksheet (коллекция WorkSheets)
- объект Range - основы использования
3) Свойства, методы и возможности использования. Работа со справочной системой Microsoft (MSDN )
4) Перехват событий в среде VB for Office
5) UserForm и элементы управления для использования на формах пользователя:
- TextBox (текстовое поле)
- Label (надпись, метка)
- ListBox (список) и ComboBox (поле со списком)
- CheckBox (флажок) и OptionButton (переключатель)
- CommandButton (кнопка)
второстепенные:
- TabStrip (набор вкладок)
- MultiPage (набор страниц)
- ToggleButton (выключатель)
- ScrollBar (полоса прокрутки)
- SpinButton (счетчик)
- Image (рисунок)
6) Диалог открытия файлов FileDialog
- Синтаксис, варианты использования
7) Организация доступа к файловой системе компьютера
8) Организация взаимодействия между приложениями. Управление MSWord из программ MSExcel
9) Практические работы
- Практическая работа 1. Разработка функции «Курс ЦБ РФ»
- Практическая работа 2. Разработка программы «Сверка выписок»
- Практическая работа 3. Разработка программы «Формирование консолидированного ОДДС»
11) Итоговое тестирование
- По итогам обучения слушатели проходят тестирование на усвоение пройденного материала.
- При успешной сдаче итогового теста слушателям выдается Удостоверение о повышении квалификации.
По итогам обучения на продвинутом блоке слушатели смогут:
- Понимать и использовать особенности Объектно-Ориентированного программирования в MSExcel
- Создавать программы (1) для автоматической обработки данных, используя объектную модель MSExcel
- Уметь создавать и использовать в программах формы пользователя
- Работать из программ c файловой системой компьютера (с файлами и директориями)
- Управлять приложениями MSOffice из создаваемых программ
- Подгружать данные через Интернет и автоматизировать данную загрузку
- Обрабатывать данные из нескольких файлов
Расписание
Продолжительность курса: 36 ак. часов
Время занятий: с 09:30 до 17:00 по субботам
- 16 ноября
- 23 ноября
- 30 ноября
- 7 декабря
- 19 декабря (четверг) с 18.45-22.00 Защита итоговой работы
Полная стоимость: 28 900 руб.
" № 28/2011
Александр
Зеляев,
заместитель начальника отдела
проектного планирования управления инвестиционных программ ОАО «АВТОВАЗ»
Павел Зырянов,
начальник управления инвестиционных программ
ОАО «АВТОВАЗ»
При выборе оптимального банковского вклада для размещения средств компании финансовой службе надо одновременно оценить несколько показателей: срок вклада, сумму, процент и, наконец, банк. Выполнять подсчеты вручную сложно и трудоемко. Упростить работу можно при помощи функции программы Excel «Поиск решения».
Установка ограничений
Суть в том, что программа перебирает все возможные варианты и выбирает самый лучший по заданным параметрам. Поэтому сначала надо задать условия, по которым Excel будет выбирать вклад. Для депозита главное условие – максимальный доход, но есть и другие.
Всю информацию заносят в одну таблицу, например, как показано на скриншоте (см. рисунок). В примере условий несколько. Во-первых, это свободные суммы, которые есть у компании на счетах, и время, в течение которого эти деньги не нужны в обороте. На скриншоте это диапазоны ячеек H12:K12 и H11:K11.
Во-вторых, надо внести ставки по депозитам, которые предлагают банки (D17:G25). Наконец, в-третьих, предстоит ввести сведения об объемах неиспользованных лимитов по каждому из банков (С17:С25).
Настройка файла для расчетов
После надо создать табличные блоки для вывода промежуточных и итоговых данных. В примере это разделы «Суммы к размещению на депозитах по срокам в днях, руб.», «Итого размещено, руб.», «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» и «Доходность от размещения на депозитах, итого».
В блоке «Суммы к размещению на депозитах по срокам в днях, руб.» (H17:K25) отразятся результаты. Excel здесь заполнит все сам.
В разделы «Итого размещено, руб.» (С17:С25)) и «Доход от средств, размещенных на депозитах, по срокам в днях, руб.» (M17:P25) потребуется вручную ввести формулы. Они нужны, чтобы суммировать депозиты и вычислить доход от вклада. Итоговый результат будет в ячейке «Доходность от размещения на депозитах итого, в рублях».
Поиск решения
Запускаем процедуру «Поиск решения». Для этого надо кликнуть по одноименной кнопке на вкладке панели инструментов «Данные». В нашем примере это будет выглядеть так. В появившемся диалоговом окне в графе «Установить целевую ячейку» надо поставить ячейку С30 «Доходность от размещения на депозитах итого, в рублях». Затем выбрать «максимальное значение». Диапазон, в котором будут отражаться результаты решения, то есть суммы вкладов в различных банках (H17:K25).
И в этом же диалоговом окне необходимо задать ограничения. Они у нас следующие
– все свободные деньги должны быть размещены на депозитах (Н27:К27 = Н12:К12);
– сумма вкладов в одном банке с учетом лимита (L17:L25 <= C17:C25).
Затем надо нажать кнопку «Выполнить». Программа найдет самое выгодное решение.
КСТАТИ. В Excel настройка «Поиск решения» по умолчанию отключена. Включают ее так: кнопка «Office», затем «Параметры Excel» > «Надстройки» > «Перейти», в появившемся окне поставить галочку «Поиск решения» и нажать «Ок».
Скачать файл с готовыми формулами и инструкцию пользователя .
Подготовлено в сотрудничестве с редакцией журнала
Или . Чтобы воспользоваться автоматической установкой Вам потребуются права администратора. Если прав администратора нет - ничего страшного, ручная установка не вызовет проблем. Инструкция по установке ниже:
Для тех, у кого есть права администратора
- Скачайте надстройку
- Запустите разархивированный файл (Установка и удаление надстройки) из папки «Автоматическая установка»
- Готово
Для тех, кто не хочет лишний раз беспокоить сисадмина:)
- Скачайте надстройку
- Разархивируйте сохраненный файл
- Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
- Запустите разархивированный файл Excel «Установка и удаление надстройки» из папки «Ручная установка»
- Разрешите запуск макросов при запуске файла и кликните по кнопке «Установить надстройку Финансист»
- Перезагрузите Excel (закройте и заново откройте)
- Готово
Если не получилось, можно действовать так:
Как обновить надстройку
Автоматическое обновление. Для тех, у кого есть права администратора на компьютере
- Скачайте надстройку
- Разархивируйте сохраненный файл в новую папку
- Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
- Запустите разархивированный файл «Установка и удаление надстройки» (из папки «Автоматическая установка»)
- в открывшемся окне выбираем опцию «Удалить надстройку» -> кнопка «Выполнить»
- запускаем этот же файл («Установка и удаление надстройки») повторно, опция «Добавить надстройку» -> кнопка «Выполнить»
- Перезагрузите Excel (закройте и заново откройте)
- Готово
Ручное обновление. Для тех, кто не хочет лишний раз беспокоить сисадмина:)
- Скачайте надстройку
- Разархивируйте сохраненный файл
- Закройте все приложения MS Office (Word, Excel, Outlook и др.), предварительно сохранив открытые файлы
- Из папки «Ручная установка» запустите разархивированный файл Excel «Установка и удаление надстройки»
- Разрешите запуск макросов при запуске файла и кликните по кнопке «Удалить надстройку Финансист» (файл удалит старую версию)
- Кликните по кнопке «Установить надстройку Финансист»
- Перезагрузите Excel (закройте и заново откройте)
- Готово
Ответы на вопросы
Что такое надстройка «Финансист»? Надстройка «Финансист» - это программа, добавляющая в Excel дополнительные команды и функции, которые будут полезны в работе финансовому директору и другим сотрудникам финансовой службы. В стандартной версии Excel такого функционала нет, либо он запрятан так далеко, что использовать его не слишком удобно.
Кому надстройка будет полезна. В-первую очередь надстройка будет полезная финансовым директорам и другим специалистам финансовой службы. Ведь именно с учетом их специфики работы формировался список команд и функций, вошедших в состав надстройки. Тем не менее надстройка будет полезна и другим специалистам, которым приходится много работать с Excel, формируя всевозможные отчеты. Например, генеральным директорам и главным бухгалтерам.
Для иллюстрации наиболее популярных финансовых функций Excel, мы рассмотрим заём с ежемесячными платежами, процентной ставкой 6% в год, срок этого займа составляет 6 лет , текущая стоимость (Pv) равна $150000 (сумма займа) и будущая стоимость (Fv) будет равна $0 (это та сумма, которую мы надеемся получить после всех выплат). Мы платим ежемесячно, поэтому в столбце Rate вычислим месячную ставку 6%/12=0,5% , а в столбце Nper рассчитаем общее количество платёжных периодов 20*12=240 .
Если по тому же займу платежи будут совершаться 1 раз в год , то в столбце Rate нужно использовать значение 6% , а в столбце Nper – значение 20 .
ПЛТ
Выделяем ячейку A2 и вставляем функцию ПЛТ (PMT).
Пояснение: Последние два аргумента функции ПЛТ (PMT) не обязательны. Значение Fv для займов может быть опущено (будущая стоимость займа подразумевается равной $0 , однако в данном примере значение Fv использовано для ясности). Если аргумент Type не указан, то считается, что платежи совершаются в конце периода.
Результат: Ежемесячный платёж равен $1074.65 .
Совет: Работая с финансовыми функциями в Excel, всегда задавайте себе вопрос: я выплачиваю (отрицательное значение платежа) или мне выплачивают (положительное значение платежа)? Мы получаем взаймы сумму $150000 (положительное, мы берём эту сумму) и мы совершаем ежемесячные платежи в размере $1074.65 (отрицательное, мы отдаём эту сумму).
СТАВКА
Если неизвестная величина – ставка по займу (Rate), то рассчитать её можно при помощи функции СТАВКА (RATE).
КПЕР
Функция КПЕР (NPER) похожа на предыдущие, помогает рассчитать количество периодов для выплат. Если мы ежемесячно совершаем платежи в размере $1074.65 по займу, срок которого составляет 20 лет с процентной ставкой 6% в год, то нам потребуется 240 месяцев, чтобы выплатить этот заём полностью.
Это нам известно и без формул, но мы можем изменить ежемесячный платёж и увидеть, как это отразится на количестве платёжных периодов.
Вывод: Если мы будем ежемесячно вносить платёж в размере $2074.65 , то выплатим заём менее чем за 90 месяцев.
ПС
Функция ПС (PV) рассчитывает текущую стоимость займа. Если мы хотим выплачивать ежемесячно $1074.65 по взятому на 20 лет займу с годовой ставкой 6% , то какой размер займа должен быть? Ответ Вы уже знаете.
БС
В завершение рассмотрим функцию БС (FV) для расчёта будущей стоимости. Если мы выплачиваем ежемесячно $1074.65 по взятому на 20 лет займу с годовой ставкой 6% , будет ли заём выплачен полностью? Да!
