Справочные материалы "Финансовые функции MS EXCEL"

ФИНАНСОВО-ЭКОНОМИЧЕСКИЕ РАСЧЕТЫ В MS EXCEL.

К основным средствам Excel, используемым для проведения финансового анализа, относятся:

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

• подбор параметра;

• таблица подстановки.

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

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

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

3. Осуществляется вызов Мастера функций с помощью команды ВСТАВКА  Функции или нажатием одноименной кнопки на панели инструментов Стандартная.

4. Выполняется выбор категории Финансовые.

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

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

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

Определение будущей стоимости.

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

Синтаксис: БС (ставка;кпер;плт;пс;тип),

где ставка – процентная ставка за период;

кпер – общее число периодов;

плт – величина периодического платежа

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

пс – необязательный аргумент, задающий приведенную (текущую) стоимость;

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

Определение текущей стоимости.

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

Excel содержит ряд функций, которые позволяют рассчитать:

1. текущую стоимость единой суммы вклада (займа) и фиксированных периодических платежей – функция ПС;

2. чистую приведенную стоимость будущих периодических расходов и поступлений переменной величины – функция ЧПС.

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

Синтаксис: ПС (ставка;кпер;плт;бс;тип),

где ставка – процентная ставка за период;

кпер – общее число периодов;

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

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

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

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

где ставка – процентная ставка за период;

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

При вычислении функции ЧПС предполагается, что начисление процентов происходит только в конце периода.

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

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

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

1. значение постоянной процентной ставки за один период для серии фиксированных периодических платежей; значение ставки процента по вкладу или займу – функция СТАВКА;

2. номинальная и эффективная процентные ставки при заданном количестве периодов между промежуточными вычислениями – взаимообратные функции НОМИНАЛ и ЭФФЕКТ.

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

Синтаксис: СТАВКА (кпер;плт;пс;бс;тип;предположение),

где кпер – общее число периодов;

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

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

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

предположение – предполагаемая величина ставки, по умолчанию равная 10%.

Функция вычисляется методом последовательного приближения и может не иметь решения или иметь несколько решений. Если после 20 итераций погрешность определения ставки превышает 0,0000001, то функция СТАВКА возвращает значение ошибки. В этом случае можно попытаться задать другой аргумент предположение. В большинстве случаев этого не требуется.

Функции НОМИНАЛ и ЭФФЕКТ взаимообратные: одна вычисляет эффективную процентную ставку по номинальной, а другая – номинальную по эффективной и имеют одинаковый синтаксис:

Синтаксис: НОМИНАЛ (ставка;кол_периодов),

где ставка – эффективная процентная ставка;

кол_периодов – число процентных периодов.

Синтаксис: ЭФФЕКТ (ставка;кол_периодов)

где ставка – номинальная процентная ставка;

кол_периодов – число процентных периодов.

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

Если значение аргумента кол_периодов равно 1, то функции НОМИНАЛ и ЭФФЕКТ возвращают значения аргумента без изменения.

Определение срока платежа и срока окупаемости.

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

Синтаксис: КПЕР (ставка;плт;пс;бс;тип),

где ставка – процентная ставка за период;

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

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

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

Расчет периодических платежей.

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

В случае погашения кредита равными годовыми выплатами основную часть выплат, идущую на погашение кредита, вычисляет функция ОСПЛТ, процентную часть выплат за один период – функция ПРПЛТ, а полную сумму выплат – функция ПЛТ.

Синтаксис: ОСПЛТ (ставка;период;кпер;пс;бс;тип),

где ставка – процентная ставка за период;

период – указывает период, за который производится расчет;

кпер – общее число периодов;

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

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

Синтаксис: ПРПЛТ (ставка;период;кпер;пс;бс;тип),

где ставка – процентная ставка за период;

период – указывает период, за который производится расчет;

кпер – общее число периодов;

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

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

Синтаксис: ПЛТ (ставка;кпер;пс;бс;тип),

где ставка – процентная ставка за период;

кпер – общее число периодов;

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

бс – необязательный аргумент, задающий будущую (наращенную) стоимость или остаток средств после последней выплаты;

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

Определение внутренней нормы доходности инвестиций.

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

Синтаксис: ВСД (значения; предположение),

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

предположение – необязательный аргумент, задающий предполагаемый результат вычисления функции; если этот аргумент опущен, то предполагается, что он равен 0,1 (10%).

Функция ВСД вычисляет итеративным методом норму дисконтирования R, при которой чистая текущая стоимость равна нулю. Начиная со значения предположение, функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,00001%. Если функция ВСД не может получить результат после 20 попыток, то возвращается значение ошибки #ЧИСЛО! В большинстве случаев нет необходимости задавать аргумент предположение.

Если известна рыночная норма дохода k, то вычисленное значение можно использовать в качестве оценки целесообразности принятия того или иного проекта вложения средств. Проект принимается, если Rk, и отвергается, если Rk.