5346

Финансовый анализ. Технология подбора параметра

Лабораторная работа

Финансы и кредитные отношения

Финансовый анализ. Технология подбора параметра Цель работы: приобрести навыки решения задач финансового менеджмента с использованием встроенных функций MS Excel. Краткая теория ФИНАНСОВЫЕ ФУНКЦИИ В MS Excel встроен ряд функций, позволяющий...

Русский

2012-12-07

196.5 KB

55 чел.

Финансовый анализ. Технология подбора параметра

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

Краткая теория

ФИНАНСОВЫЕ ФУНКЦИИ

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

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

БЗ – расчет наращенной суммы капитала при постоянной процентной ставке и равномерных периодических выплатах;

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

ПЗ – расчет эквивалента будущей стоимости серии равномерных периодических платежей при постоянной процентной ставке;

КПЕР – количество периодов, за которые исходная сумма достигнет требуемой величины при постоянной процентной ставке и равномерных периодических выплатах;

НОРМА – величина процентной ставки, при которой исходная сумма достигнет требуемой величины при постоянных периодических выплатах;

ППЛАТ – величина периодических выплат, которые позволят достичь заданной величины вклада за определенный период при начальной сумме вложений и постоянной процентной ставке. Периодические выплаты включают основной платеж и плату по процентам, возвращаемые функциями ОСНПЛАТ и ПЛПРОЦ.

ПРОЦПЛАТ - вычисляет выплаты за указанный период выплат.

Финансовые функции позволяют решать задачи двух типов:

  •  Накопление капитала, размещенного на депозите в виде вклада, либо выданная в виде ссуды;
  •  Пользование заемными средствами.

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

Замечание1. В формулах вкладываемые денежные суммы (например, депозитные вклады) обозначаются отрицательными числами, а полученные денежные суммы – положительными (например, дивиденды).

Замечание2. 

Следует обратить внимание на выбор единиц измерения аргументов СТАВКА (НОРМА) и КПЕР. Для аргументов ставка и кпер должны использоваться согласованные единицы измерения (один и тот же период, например месяц). Если по двухгодичному займу при ставке 18 процентов годовых делаются ежемесячные выплаты, то в формуле нужно использовать значение 18%/12 для задания аргумента СТАВКА и значение 2*12 для аргумента КПЕР

ИСПОЛЬЗОВАНИЕ ТЕХНОЛОГИЙ ПОДБОРА ПАРАМЕТРА И ПОСТРОЕНИЯ СЦЕНАРИЕВ

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

Часто удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Это возможно с помощью меню Сервис/ Сценарии. В диалоговом окне Диспетчера сценария нужно нажать кнопку Добавить для создания первого сценария.

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

После нажатия ОК появится окно Значения параметров сценария, в поля которого нужно ввести значения параметров для первого сценария.

Таким образом, обычно создают несколько сценариев.

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

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

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

Синтаксис

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

Ставка  (норма)- это процентная ставка за период.

Кпер   - это общее число периодов выплат годовой ренты.

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

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

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

Тип означает когда производятся выплаты:

0 – в конце периода

1 – в начале периода

Пример1:

Вычислить сумму накоплений на депозитном вкладе  20000 руб., размещенном под 14% годовых сроком на 3 года с обязательством дополнительных вложений в сумме 1000 р. в конце каждого месяца.

A

B

3

Норма

14,00%

4

Число периодов

3

5

Выплаты

1000

6

НЗ

20000

7

Тип

0

8

БЗ

74 788,12р.

В ячейку B8 введите формулу  БЗ(B3/12;B4*12;-B5;-B6;B7)

Цветом в примере выделен результат, который должен получиться после ввода формулы.

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

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

Для этого будем использовать команду меню Сервис/ Подбор параметра.

Порядок решения:

  1.  Установите курсор в ячейку с формулой (в нашем примере B8 );
  2.  Выполните команду меню Сервис/ Подбор параметр;
  3.  В открывшемся диалоговом окне подбора параметра проверьте правильность значения (B8) в поле «Установить в ячейке». Оно задается автоматически, если курсор был установлен в ячейку с формулой;
  4.  В поле «Значение» введите требуемую величину требуемой величины накоплений (200000);
  5.  В поле «Изменяя значение ячейки» укажите адрес ячейки, содержащей срок ссуды (B4). Для этого установите курсор в поле и щелкните левой клавишей мыши в ячейке B4;
  6.  После подбора параметра закройте диалоговое окно кнопкой ОК.

Следующий простой пример рассмотрите самостоятельно.

Пример 2: Сумма вклада100000 руб. Ставка составляет 30% годовых. Определить наращенную сумму за 0,5 года.

Последовательность действий

  1.  Создайте таблицу с исходными данными на рабочем листе MS Excel.
  2.  Для вызова мастера функций воспользуйтесь п. меню Вставка/ Функция или значком на панели инструментов. В окне Мастера функций выберите категорию Финансовые, а в списке функций – БЗ.
  3.  Заполните диалоговое окно функции ссылками на соответствующие ячейки и нажмите кнопку <ОК>.

Результат решения задачи приведен в следующей таблице:

Норма

30%

Число периодов

0,5

Выплаты

0

НЗ

100000

Тип

0

БЗ

114 017,54р.

Определите для тех же исходных данных сумму наращенного капитала за 5 месяцев и за 3 года.

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

Синтаксис

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

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

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

Нз   - это текущая стоимость, или общая сумма всех будущих платежей с настоящего момента.

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

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

0 (по умолчанию) – конце периода

1 – в начале периода

Например, мы берем в долг 100000 руб. при годовой ставке 20% и собираемся выплачивать по 2000 руб. в год, то число выплат вычисляется следующим образом:

=КПЕР(20%; -2000;100000)

  1.  Функция ППЛАТ (ППЛТ)– вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки.

Синтаксис

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

Ставка   - это процентная ставка по ссуде.

Кпер  - это общее число выплат по ссуде.

Нз   - общая сумма, которую составят будущие платежи

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

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

0  – (по умолчанию) – в конце периода

1  –   в начале периода

Замечание

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

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

ППЛАТ(15%/12; 6; -5000)

Функцию ППЛАТ можно использовать для расчета платежей не только в случае ссуды. Например, если требуется накопить 50 000 руб. за 5 лет с 8% накоплением. С помощью этой функции можно определить размер ежемесячно откладываемых сумм.

ППЛАТ(8%/12; 5*12; 0; 50000)

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

Формат

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

Период - период, за который требуется найти прибыль.

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

Кпер – общее число периодов выплат

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

БЗ – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты.

Тип – число 0 или 1.

  1.  Функция ПЛПРОЦ (ПРПЛТ)– проценты (доход или к уплате) за учетный период.

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

Синтаксис

ПЛПРОЦ (ставка; период; Кпер; нз; бз; тип)

Аргументы те же, что и у ОСНПЛАТ.

Замечание

За один и тот же период верно равенство

ПЛПРОЦ=ОСНПЛАТ+ПЛПРОЦ

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

  1.  Функция ПЗ –возвращает текущий объем вклада при постоянной процентной ставке и постоянных периодических выплатах.

Синтаксис

ПЗ (ставка; Кпер; выплата; бз; тип)

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

Кпер – общее число периодов выплат

Выплата – величина постоянных периодических платежей

Бз – будущая стоимость или баланс наличности, которые нужно достичь после последней выплаты.

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

0  – (по умолчанию) – в конце периода

1  –   в начале периода

Пример: Допустим, что у вас просят в долг 30000 руб. и обещают возвращать по 6000 руб. в течение 8 лет. Будет ли выгодна эта сделка при годовой ставке 8% (т.е., если деньги положить в банк под 8% и в течение того же срока снимать со счета по 5000 руб.)?

Проанализируем ситуацию с помощью диспетчера сценариев MS Excel (меню Сервис/ Сценарии).

Последовательность действий:

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

2

A

B

3

Размер займа

30000

4

Срок

8

5

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

6000

6

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

8%

7

Чистый текущий объем вклада

34 479,83р.

=-ПЗ(B6;B4;B5;0;0)

8

Вывод

=ЕСЛИ(B3<B7;"выгоднее отдать в долг";

ЕСЛИ(B3=B7;"варианты равносильны";

"выгоднее вложить в банк"))

В последнем столбце таблице приведены расчетные формулы, по которым получены результаты в ячейках B7 и B8. Для простоты ПЗ учитывается со знаком «-».

  1.  Выполнить команду меню Сервис/ Сценарии. В диалоговом окне Диспетчера сценария нажать кнопку Добавить для создания первого сценария.

  1.  Далее в следующем окне вводится название сценария, а в поле Изменяемые ячейки ссылку на ячейки В4 и В5, в которые вводятся значения параметров задачи (срок и сумма возвращаемых денег).

  1.  После нажатия ОК появится окно Значения параметров сценария, в поля которого нужно ввести значения параметров для первого сценария.

  1.  Таким образом, обычно создают несколько сценариев.
    1.  С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки Отчет открывается диалоговое окно Отчет по сценарию. Можно выбрать отчет в виде структуры или в виде сводной таблицы. В поле Ячейки результата дать ссылку на ячейки, где вычисляются значения результирующих функций (В7-В8). После нажатия ОК создается отчет по сценариям.

  1.  Функция СТАВКА (НОРМА) – вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов.

Синтаксис

Норма (Кпер; выплата; нз; бз; тип; нач_прибл)

Кпер – общее число периодов выплат

Выплата – величина постоянных периодических платежей

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

Бз – будущая стоимость или баланс наличности, которые нужно достичь после последней выплаты.

Тип – 0 или1.

Нач_прибл- предполагаемая величина нормы. Если пропущено, то предполагается равным 10%. Если функция НОРМА не сходится, следует пытаться использовать различные начальные приближения.

Пример: определим процентную ставку для 4-х летнего займа размером в 8000 руб. с ежемесячными выплатами –200 руб. Используем в формуле аргументы, приведенные к одному периоду –месяц.

=НОРМА(48; -200;8000)

В результате получим ставку за месяц 0,77% и ставку за год соответственно 0,77%*12.

  1.  Функция ПРОЦПЛАТ – возвращает проценты платежа по прямому займу.

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

Период   — это период, для которого требуется найти прибыль; должен находиться в интервале от 1 до кпер.

Кпер   — это общее число периодов выплат для данного вклада.

Нз   — это текущее значение вклада. Для займа нз — это сумма кредита.

Пример 3: Рассчитать график платежей (сложные проценты) по ссуде 4000 руб., годовой ставке 35% на период 5 месяцев. Пример расчета приведен в таблице.

A

B

C

D                                                        E

1

График платежей

2

ПОСТОЯННЫЕ ВЫПЛАТЫ

РАСЧЕТНЫЕ ФОРМУЛЫ

3

ССУДА

4000

-800

=ПРОЦПЛАТ(C4/12;0;C5;$C$3)

4

СТАВКА

0,35

-800

=ПРОЦПЛАТ(C4/12;1;C5;$C$3)

5

ПЕРИОД

5

-800

=ПРОЦПЛАТ(C4/12;2;C5;$C$3)

6

-800

=ПРОЦПЛАТ(C4/12;3;C5;$C$3)

7

-800

=ПРОЦПЛАТ(C4/12;4;C5;$C$3)

СУММА

=СУММ(D3:D7)

=СУММ(E3:E7)

Задания к лабораторной работе

При решении задач используйте методику решения примеров из раздела «Краткая теория».

Каждое задание выполните на отдельном рабочем листе.

 Задание 1: Рассчитать ежемесячные выплаты по погашению кредита размером 50 000 руб. сроком 3 года при неизменной годовой ставке 20,5%. Выплаты производятся в начале периода. Определите, какую величину в сумме ежемесячных выплат составляет основной платеж и плата по процентам в начале расчетного периода (первый месяц) и после истечения 2-х лет. Объясните полученные результаты.

Задание 2: Рассчитать, какую сумму можно накопить на депозитном вкладе за 5 лет при неизменной годовой процентной ставке 11,5%. Сумма вклада составляет 30 000 руб., ежемесячное пополнение вклада в размере 5000 руб. производится в конце каждого месяца.

Используя инструмент подбора параметра, определите, при какой процентной ставке при тех же условиях можно накопить 500000 руб.

Задание 3: Определите срок, за который можно накопить 100000 руб. при  ежемесячных выплатах в начале учетного периода 2000 руб. при годовой ставке 19%.

Задание 4: Определите, какая первоначальная сумма обеспечит за 2 года величину вклада 60000 при постоянных ежемесячных дополнениях вложений в размере 1000 руб. и годовой процентной ставке 12%. Проанализируйте ситуацию, когда изменяется процентная ставка и период, используя средства построения сценариев.

 Задание 5: Рассчитать график платежей (сложные проценты) по ссуде на год в размере 50000 руб., годовой ставке 21%.


 

А также другие работы, которые могут Вас заинтересовать

12404. РОБОТА С ФРАГМЕНТАМИ IMAGE 77.5 KB
  ЛАБОРАТОРНА РОБОТА № 9 РОБОТА С ФРАГМЕНТАМИ IMAGE Ціль лабораторної роботи складається з вивчення: структури і призначення елементів інтегрованого середовища С Buіlder для розробки прикладних програм С по роботі на формі вікна з фрагментами зображення з файлу .bmp на осн
12405. Компоненты отображения иерархических данных 165 KB
  Лабораторная работа № 10 Компоненты отображения иерархических данных Цель лабораторной работы состоит в изучении методики работы с компонентами отображения произвольных иерархических данных. Общие сведения о компонентах В библиотеке VCL для отображения иерар...
12406. Принятие решений в условиях неопределенности. Критерий Лапласа 305 KB
  Принятие решений в условиях неопределенности Теория статистических решений может быть истолкована как теория поиска оптимального недетерминированного поведения в условиях неопределенности. Согласно А.Вальду поведение считается оптимальным если оно минимизирует...
12407. Измерение длины световой волны с помощью прозрачной дифракционной решетки 98 KB
  Отчёт по лабораторной работе По дисциплине: Физика. Тема: Измерение длины световой волны с помощью прозрачной дифракционной решетки Общие теоретические сведения: Интерференция явление перераспределения волны в результате наложения когерентных волн...
12408. Контролер станочных и слесарных работ 156.5 KB
  Для приобретения квалификации контролера станочных и слесарных работ необходимо: технологию сборочных работ; технические условия на приемку деталей и проведения испытаний операций, механической и слесарной обработки...
12409. Исследование свободных электрических затухающих колебаний 234.5 KB
  Отчет. К лабораторной работе 5.2. Исследование свободных электрических затухающих колебаний. Цель работы: Исследование закономерностей свободных электрических незатухающих колебаний в последовательном колебательном контуре определение их физических характерис
12410. Исследование вынужденных колебаний в последовательном контуре 80.5 KB
  Отчёт по лабораторной работе 53. Исследование вынужденных колебаний в последовательном контуре. Цель работы: исследовать зависимость резонансной частоты и вида резонансной кривой от параметров контура. Расчёт погрешностей
12411. Технологический процесс приготовления блюд: Рассольник Ленинградский; Котлета натуральная из филе птицы, со сложным гарниром; Торт Прага 3.25 MB
  Для приправы практически всех блюд используется соевый соус, который является одним из основных ингредиентов китайской кухниэто экстракт из соевых бобов, который практически ничем не заменяется. В европейских условиях этот соус готовят из местной сои. Кроме этого широко используется вей-су - глютаминат натрия...
12412. Исследование волны в натянутом шнуре 42 KB
  Отчёт по лабораторной работе 6. Исследование волны в натянутом шнуре. Цель работы: Исследование стоячих волн в горизонтальном натянутом шнуре. Измерение частоты источника методом стоячих волн. Рабочие формулы Пример расчета полной абсолютной погр