5346

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

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

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

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

Русский

2012-12-07

196.5 KB

56 чел.

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

Цель работы: приобрести навыки решения задач финансового менеджмента с использованием встроенных функций 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%.


 

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

80611. Літературні казки 37.5 KB
  Мета: Формувати компетентність вміння вчитися. Загальнокультурну компетентність Соціальну компетентність Предметні компетентності Практично ознайомити учнів з новим автором літературних казок – Василем Чухлібом та його творами Спонукати дітей пильніше оглядати світ навколо себе...
80612. Жива і нежива природа. Значення природи для людини 40.5 KB
  Мета: Розширити і уточнити знання учнів про різноманітність природи; формувати в учнів поняття природа жива і нежива природа; розвивати уміння спостерігати за обєктами природи відрізняти предмети природи від предметів створених руками людини; виховувати дбайливе ставлення до природи.
80613. В осінні барви шати вдяглись ліси й поля 71 KB
  Мета. Формування у школярів уміння добирати твори, книги із заданої теми; вчити учнів бачити і розуміти прекрасне в природі, в музиці, поезії; розвивати образне мислення й мовлення; вчити визначати тему і мету твору, виразно читати поетичні твори, читати в особах...
80614. Океани Землі (прес-конференція) 38.5 KB
  Мета: Ознайомити учнів із поняттям «океани», їх розміщенням на карті півкуль і глобусі; продовжити навчати читати карту і працювати з нею; висвітлити значення океанів та використання їх багатств людиною; сприяти розвиткові памяті, спостережливості, вихованню бережливого ставлення до природних багатств.
80615. Осінь така мила 61 KB
  Мета: Продовжити знайомство учнів з творами українських поетів, які описували красу осені. Удосконалювати уміння виразно і правильно інтонаційно читати та розповідати текст. Активізувати словниковий запас учнів. Розвивати пам’ять, увагу, спостережливість, мовлення дітей та їх творчу уяву.
80616. Використання засобів зворотнього зв’язку, як один з основних факторів розвитку комунікативних здібностей особистості 57.5 KB
  Розробка рекомендована вчителям початкових класів. У ній розкрито роль розвитку комунікативних здібностей для формува- ння людини, здатної творчо мислити, приймати рішення, адаптуватися до умов життя, мати свою позицію, бути комуні кабельною у спілкуванні.
80617. Осінь у лісі 59 KB
  Поглибити знання дітей про осінні зміни в природі; удосконалювати вміння правильно вимовляти та будувати висловлювання українською мовою, складати оповідання за малюнками; Формувати ключові компетентності. Розвивати мовлення, уяву, фантазію, спостережливість, працювати над збагаченням словникового запасу учнів.
80618. А осінь вже постукала у вікно 69 KB
  Мета уроку: Вчити дітей висловлювати свої думки на основі власних спостережень за картинами природи; мислити нестандартно. Удосконалювати навички виразного читання; стимулювати дітей висловлювати власні ідеї; формувати бережливе ставлення до слова.
80619. Закріплення звукового значення букви «е». Опрацювання тексту «Село Веселе». Кросворд 44.5 KB
  Мета. Закріпити звукове значення букви «е». Вчити учнів правильно, свідомо читати текст «Село Веселе». Формувати вміння давати відповіді на запитання тексту. Розвивати мислення, пам’ять, збагачувати словниковий запас. Виховувати любов до рідного краю.