bigpo.ru
добавить свой файл
1 2 ... 9 10
1Финансовые функции

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


функции для анализа инвестиций;


 функции для вычисления скорости оборота;


 функции для вычисления амортизации;


 функции для анализа ценных бумаг.


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

Ставка — это процентная ставка за период.

Кпер — общее число периодов платежей по аннуитету.

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

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

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

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

Основные функции

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

ЧПС(ставка; блок клеток для работы со счетом; тип)

Блок клеток для работы со счетом – это от 1 до 29 аргументов, предоставляющих расходы и доходы. Платежи и поступления должны быть введены в правильном порядке. Все операции производятся в конце периода.

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

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

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

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

ПЛТ (ППЛАТ) — возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

ПЛТ = ОСПЛТ + ПРПЛТ

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

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

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

ОСПЛТ (ОСНПЛАТ) — возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

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

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

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

СТАВКА (НОРМА) — Возвращает процентную ставку по аннуитету за один период. СТАВКА вычисляется путем итерации и может давать нулевое значение или несколько значений. Если последовательные результаты функции СТАВКА не сходятся с точностью 0,0000001 после 20-ти итераций, то СТАВКА возвращает сообщение об ошибке #ЧИСЛО!.

СТАВКА(срок; плт; пс; бс; тип)

Обзор возможностей Excel

В лекции будут рассмотрены следующие возможности Excel:


 Анализ «что если»:


o Диспетчер сценариев;


o Подбор параметра;


o Таблица данных;


 Поиск решения;


 Средства статистического анализа данных:


o Функции для расчета основных статистических показателей;


o Линейная и экспоненциальная регрессия;


o Описательная статистика;


 Нахождение функциональной зависимости;


 Циклические ссылки.


Анализ «что если»

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

Включает в себя следующие инструменты:


 Диспетчер сценариев;


 Подбор параметра;


 Таблицы подстановки.


2Диспетчер сценариев

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

Пример 1

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

Рисунок 1 — Использование сценариев

С использованием Диспетчера сценариев (Рисунок 2) добавляем значения для ставки (B2) и срока (B4).

Рисунок 2 — Диспетчер сценариев

Используя кнопку [Вывести], можно посмотреть результаты по итоговому размеру вклада для каждого из сценариев. Для создания общего отчета нажимаем на кнопку [Отчет].







3Инструмент «Подбор параметра» в Excel (Goal seek)

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

В Excel реализован более эффективный метод решения. Используется инструмент Подбор параметра для поиска значения (параметра), которое приведет к заданному результату. При вычислении Х используются численные методы, поэтому найденное значение не будет точным. Ограничения на искомое значение ячейки не налагают. Таким образом, используя численные методы, встроенные в Excel, можно решать любые уравнения с одной переменной.

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

Пример 2

Найти корень уравнения x2 – sin x = 0. Необходимо подготовить данные, например, следующим образом:


A

B

1

0,7

=A1*A1 - SIN(A1)

Очевидно, что в том случае, если в ячейке В1 будет 0, то значение в ячейке A1 является корнем, чтобы выполнить это условие, подберем требуемое значение при помощи подбора параметра. Для этого:


 ячейку В1 сделайте текущей;


 выберите команду Сервис/Подбор параметра;


 в поле Значение введите 0;


 в поле Изменяя значение ячейки A1.



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

Обратите внимание, что уравнение имеет два корня: 0 и 0,87 (приближенно), однако Excel не может найти их одновременно при помощи побора параметра. Найденный корень зависит от выбранного первоначального приближения. Поэтому перед решением уравнения средствами Excel, необходимо найти приблизительные значения корней уравнения, например, при помощи графического метода (построить диаграмму, см. Рисунок 4), а затем воспользоваться подбором параметра.


Рисунок 4 — График функции f(x) = х2 - sin x, видны точки пересечения с осью Х




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

Пример 3

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

Вы хотите приобрести через 5 лет автомобиль стоимостью 100 тыс. рублей. У вас есть возможность вложить некоторую сумму денег на депозит под 100% годовых. В конце каждого года вы хотите забирать 12 тысяч рублей на текущие расходы. Какую сумму следует положить на депозит для выполнения этих условий?

А

В

1

Сумма вклада

-10000

2

Годовая ставка в %

100%

3

Ежегодные выплаты

12000

4

Срок вклада

5

5

Будущее значение

-52000

B5=БЗ(В2; В4; В3; В1) = -52000

Очевидно, что 10 000 рублей недостаточно, чтобы выполнить условие, подберем требуемое значение при помощи подбора параметра. Для этого:


 ячейку В5 сделайте текущей;


 выберите команду Сервис/Подбор параметра;


 в поле Значение введите 100 000;


 в поле Изменяя значение ячейки В1.


Excel установит в ячейке В1 такое значение, чтобы в ячейке В5, было число 100 000.

В результате в ячейке В5 появится сумма 14 750 рублей.


4Таблица данных

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

Таблицы данных с одной переменной. Таблица данных с одной переменной используется, например, при необходимости просмотреть, как различные процентные ставки влияют на размер ежемесячных выплат по закладной. В следующем примере (Рисунок 5) ячейка D2 содержит формулу вычисления платежа, =ППЛАТ(B3/12;B4-B5), которая ссылается на ячейку ввода B3.




А

В

С

D

1

Ссуды на недвижимость

Выплаты

2

Первый взнос

Нет




672,68р.

3

Процентная ставка

- 9,50%

,- 9,00%

643,70р.

4

Срок (месяцы)

360

- 9,25%

658,14р.

5

Сумма ссуды

80 000р.

- 9,50%

672,68р.

Ячейка ввода




Список значений,





Таблицы данных с двумя переменными. Таблица данных с двумя переменными может показать влияние на размер ежемесячных выплат по закладной различных процентных ставок и сроков займа. В следующем примере ячейка C2 содержит формулу вычисления платежа, =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.


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

Пример 4

Провести вычисления для таблиц данных, приведенных на рисунках выше. Для этого в первом случае:


1. Выделите диапазон ячеек, содержащих формулы и значения, которые нужно заменить. На первом рисунке (Рисунок 5) таким диапазоном является C2:D5.


2. На вкладке Данные в группе Работа с данными выберите команду Анализ «что если», а затем выберите в списке пункт Таблица данных.


3. Подставлять значения по строкам в В3.


Во втором случае:


1. Выделите диапазон ячеек, содержащих формулы и значения, которые нужно заменить. На первом рисунке (Рисунок 6) таким диапазоном является C9:E12.


2. На вкладке Данные в группе Работа с данными выберите команду Анализ «что если», а затем выберите в списке пункт Таблица данных.


3. Подставлять значения по строкам в В3. Подставлять значения по столбцам в В4.



следующая страница >>