Функция БС или как рассчитать в Excel сложный процент?

Анализ “что-если”

Анализ “Что-если” позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов.

Существует четыре способа прогнозирования значений с помощью:

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

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

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

Анализ формулы начинается с подготовки таблицы подстановки:

1. Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.
2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения варьируемой переменной.
3. В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.

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

1. Выделить таблицу подстановки (в ячейки, расположенные рядом с таблицей, можно ввести пояснительные надписи, но эти ячейки не входят в таблицу подстановки данных и, следовательно, не выделяются).
2. В меню Данные выбрать Анализ “Что-если” и выбрать команду Таблица данных.
3. Если значения варьируемой переменной расположены в столбце, то надо щелкнуть по полю Подставлять значения по строкам и ввести в это поле адрес изменяемой ячейки (т.е. ячейки, которая играет роль варьируемой переменной в формуле). Если значения варьируемой переменной расположены в строке, то адрес изменяемой ячейки вводится в поле Подставлять значения по столбцам.
4. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями.

В случае анализа зависимости формулы от двух переменных таблица подстановки подготавливается по-другому:

1. В левую верхнюю ячейку блока, отведенного под таблицу, ввести ссылку на ячейку с анализируемой формулой.
2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения одной из варьируемых переменных.
3. В верхнюю строку блока, начиная со второй ячейки, ввести значения другой варьируемой переменной.
4. Выделить таблицу подстановки.
5. В меню Данные выбрать Анализ “Что-если” и выбрать команду Таблица данных.
6. В поле Подставлять значения по строкам в ввести ссылку на ячейку с переменной, значения для которой расположены в левом столбце таблицы подстановки.
7. В поле Подставлять значения по столбцам ввести ссылку на ячейку с переменной, значения для которой расположены в первой строке таблицы подстановки.
8. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями.

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

Пример 2.

Определить какими будут выплаты по ссуде при меняющейся процентной ставке (для примера 1)

В ячейки А9:В13 введите следующие значения, оставив пустой строку перед числовыми значениями (рис. 9.2
рис.
9.2 ):

В ячейку В10 скопировать ссылку на ячейку с формулой для расчета ежемесячных выплат.

Для расчета выплат по каждой из ставок воспользуйтесь возможностью автоматической подстановки значений в нужную ячейку (в нашем случае в В1).

Для этого нужно:

1. Выделить диапазон А10:В13, включив в него значения процентных ставок и расчетную формулу (формула должна находиться в ячейке, расположенной правее и выше заданных значений).
2. В меню Данные выбрать Анализ “Что-если” и выбрать команду Таблица данных.
3. В поле “Подставлять значения по строкам в:” указать ячейку В1 (рис.9.3 ).

Рядом с каждой процентной ставкой появится соответствующий результат.

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

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

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

Сценарий – это множество входных значений, называемых изменяемыми ячейками, которое можно сохранить под указанным именем, а затем применить к модели рабочего листа, чтобы проследить, как значения изменяемых ячеек влияют на другие значения модели. Для каждого сценария можно определить до 32 изменяемых ячеек.

Чтобы создать сценарий, следует:

1. В меню Данные выбрать команду Анализ “Что-если”, указав Диспетчер сценариев (рис. 9.4
рис.
9.4 ).

Появится окно “Диспетчер сценариев” (рис. 9.5
рис.
9.5)

2. Щелкнуть по кнопке Добавить. Откроется окно Добавление сценария (рис. 9.6
рис.
9.6).

3. В поле Название сценария ввести имя сценария.

4. В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с запятыми. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>.

5. Щелкнуть по кнопке ОК.

6. В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки (рис. 9.7
рис.
9.7).

7. Для создания других сценариев щелкнуть по кнопке Добавить (откроется диалоговое окно Добавление сценария) и повторить пункты 3 – 6.

Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.

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

Для просмотра сценария нужно:

1. В меню Данные выбрать команду Анализ данных и указать Диспетчер сценариев.
2. В поле Сценарии выделить имя сценария, который необходимо просмотреть.
3. Щелкнуть по кнопке Вывести.

Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нужного сценария.

Чтобы отредактировать сценарий, надо:

1. В меню Данные выбрать команду Анализ данных и указать Диспетчер сценариев.
2. В поле Сценарии выделить имя сценария, который необходимо отредактировать.
3. Щелкнуть по кнопке Изменить.
4. Внести необходимые изменения: можно изменить имя сценария, изменяемые ячейки, значения изменяемых ячеек.
5. Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем – по кнопке Закрыть.

Для создания итогового отчета по сценариям следует:

1. В меню Данные выбрать команду Анализ данных и указать Диспетчер сценариев.
2. Щелкнуть по кнопке Отчет.
3. Выбрать тип отчета: Структура или Сводная таблица.

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

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

4. В поле Ячейки результата ввести ссылки на ячейки, значения которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой клавише <Ctrl>. Итоговые отчеты создаются на отдельных листах.

3 способ. Подбор параметра

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

Математическая суть задачи состоит в решении уравнения X = a, где функция х описывается заданной формулой, х – искомый параметр, а – требуемый результат формулы.

Для решения этой задачи необходимо выполнить следующие действия:

1. Выделить ячейку, содержащую формулу, для которой нужно найти определенное решение.
2. В меню Данные > Анализ “что-если” выбрать команду Подбор параметра. В поле Установить в ячейке ввести ссылку на ячейку, содержащую формулу (по умолчанию в это поле вводится адрес текущей ячейки).
3. В поле Значение ввести значение, которое нужно получить по заданной формуле.
4. В поле Изменяя ячейку ввести ссылку на ячейку, содержащую значение изменяемого параметра (эта ячейка называется изменяемой).
5. Щелкнуть по кнопке ОК.

Пример 3.

Дано уравнение:

где: А – требуемый результат формулы; Х – искомый параметр.

Определить такое значение параметра X, при котором А будет равно 20.

1. Занести в ячейку A1 любое значение, например, 1.
2. Ввести в ячейку А2 указанную формулу, которая примет следующий вид: =A1^2 3*A1-2. В формуле указана ссылка на ячейку А1, в которой условно находится параметр X.
3. Задать команду Данные > Анализ “что-если” > Подбор параметра (рис. 9.8
   рис.
   9.8 ).
4. В поле Установить в ячейке указать А2 (по умолчанию в это поле вводится адрес текущей ячейки).
5. В поле Значение ввести – 20.
6. В поле Изменяя значение ячейки указать адрес ячейки, в которой должен находиться параметр X, т.е. А1.

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

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

При подборе параметра одна из ячеек обязательно должна содержать формулу.

R,
ве­дется по формуле:

pv
= ртt ртt/(1 r) …
pmt/(1 r)n-1
=pmt(1-1/(1 r)n)/r*(1 r),

где pv – текущая
стоимость серии фиксированных периодических платежей,

pmt –
фиксированная периодическая сумма платежа,

п   – общее число периодов
выплат (поступлений),

r   –
постоянная процентная ставка.

 Для расчета этой величины функция ПС
используется в виде:

= ПС (ставка, кпер, выплата, , 1).

Видео-урок: «примеры расчета простых и сложных процентов»

Резюме

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

Доход

Одним из наиболее востребованных операторов у финансистов является функция ДОХОД. Она позволяет рассчитать доходность ценных бумаг по дате соглашения, дате вступления в силу (погашения), цене за 100 рублей выкупной стоимости, годовой процентной ставке, сумме погашения за 100 рублей выкупной стоимости и количеству выплат (частота).

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

=ДОХОД(Дата_сог;Дата_вступ_в_силу;Ставка;Цена;Погашение»Частота;[Базис])

Задания

1. Используя соответствующие финансовые функции, решите следующие задачи, (номер варианта задания – номер компьютера в учебной аудитории).
2. Для созданной задачи изменить величины ее параметров так, чтобы (не меняя формулы) результат вычислений тоже изменился.
3. Выполнить анализ данных “Что – если”, используя таблицу подстановок.
4. Изменить результат вычислений задачи с помощью Сценария. Вывести итоговый отчет типа структура.
5. Изменить результат вычисления при помощи Подбора параметров.

Вариант 1.

1. Определить величину ежемесячной амортизации имущества (АПЛ (SLN)) при условии, что начальная стоимость его 10000р., а остаточная (в конце периода амортизации) 2000р.; амортизация имущества занимает период 10 месяцев.
2. Затем вычислить эту величину (не меняя формулу) при условии, что остаточная амортизация равна 1500р..
3. Определить величину ежемесячной амортизации имущества (АПЛ), используя таблицу подстановок:
4. при различных периодах: 5, 7, 8, 9, 10, 12 месяцев;
5. при различных периодах: 5, 7, 8, 9, 10, 12 месяцев, а также при остаточных стоимостях 5000, 4500, 4000, 3000, 2000, 1000 соответственно.
6. Составить сценарий, если начальная стоимость имущества изменится на 15000р.
7. Подобрать параметр срока полной амортизации при условии, что ежемесячные отчисления составят 1500р.

Вариант 2.

1. Вычислить, на сколько снизится стоимость основных фондов, рассчитанная по методу двойной амортизации (ДДОБ), если начальная стоимость имущества 30000р., а в конце периода эксплуатации 4000р. Время эксплуатации считать равным 3 года, период, для которого вычисляется амортизация, равным 2,5 года.
2. Вычислить эту величину (не меняя формулу) при условии, что остаточная стоимость имущества равна 5000р..
3. Определить величину ДДОБ используя таблицу подстановок:
4. при изменении времени эксплуатации: 7, 6, 5, 4, 3 года;
5. при изменении времени эксплуатации: 7, 6, 5, 4, 3 года; и начальной стоимости 70000, 60000, 50000, 40000, 30000 руб. соответственно.
6. Составить сценарий, если величина начальной стоимости изменится на 35000р.
7. Подобрать параметр срока эксплуатации, если стоимость основных фондов снизится на 1000р.

Вариант 3.

1. Вычислить величину ежемесячной выплаты (ПЛТ) фирмой, взявшей кредит в размере 50000р. со ставкой 5% годовых сроком на 4 года и будущей стоимостью 5000р.
2. Вычислить эту величину (не меняя формулу) при условии, что ставка процента изменится на 7%.
3. Определить величину ежемесячной выплаты (ПЛТ) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и сроком на 5, 7, 8, 9, 10 лет соответственно.
6. Составить сценарий, если величина кредита изменится на 60000р, а срок на 5 лет.
7. Подобрать параметр срока, на который был взят кредит, если ежемесячные выплаты составят 7000р.

Вариант 4.

1. Вычислить величину всех выплат (БС) фирмой, взявшей кредит в размере 45000р., сроком на год, с ежемесячной выплатой 3000р. и годовой ставкой процента, равной 5%.
2. Затем, вычислить эту величину (не меняя формулу) при условии, что величина процентной ставки изменится на 3%.
3. Определить величину всех выплат (БС) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и суммах кредита 50000, 60000, 70000, 80000, 90000, 100000 руб. соответственно.
6. Составить сценарий, если величина кредита изменится на 50000р.
7. Подобрать параметр ежемесячной выплаты, если величина всех выплат составит 85000р.

Вариант 5.

1. Вычислить общее количество периодов выплаты (КПЕР) фирмой, взявшей кредит в размере 73000р. Ставка процента постоянна и равна 6%. Ежемесячные выплаты фирмой также постоянны и равны 5500р.
2. Вычислить эту величину (не меняя формулу) при условии, что величина кредита изменится на 60000р.
3. Определить величину КПЕР используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и суммах кредита 50000, 60000, 70000, 80000, 90000, 100000 руб. соответственно.
6. Затем составить сценарий, если величина процентной ставки изменится на 12%.
7. Подобрать параметр величины кредита, если выплата будет производиться 19 месяцев.

Вариант 6.

1. Определить величину ежемесячной амортизации имущества (АПЛ) при условии, что начальная стоимость его 40000р., а остаточная (в конце периода амортизации) 9000р.; амортизация имущества занимает период 2 года.
2. Вычислить эту величину (не меняя формулу) при условии, что остаточная амортизация равна 7000р.
3. Определить величину АПЛ используя таблицу подстановок:
4. при варьировании остаточной амортизации: 5000, 6000, 7000, 8000, 9000 и том же периоде;
5. при варьировании остаточной амортизации: 5000, 6000, 7000, 8000, 9000 и периоде 0,5; 1, 1,5; 2, 3 года соответственно.
6. Составить сценарий, если начальная стоимость имущества изменится на 55000р.
7. Подобрать параметр срока полной амортизации при условии, что ежемесячные отчисления составят 3500р.

Вариант 7.

1. Вычислить, на сколько снизится стоимость основных фондов, рассчитанная по методу двойной амортизации (ДДОБ), если начальная стоимость имущества 80000р., а в конце периода эксплуатации 10000р. Время эксплуатации считать равным 17 месяцев, период, для которого вычисляется амортизация, равным10 месяцев.
2. Вычислить эту величину (не меняя формулу) при условии, что остаточная стоимость имущества равна 7000р.
3. Определить величину ДДОБ спользуя таблицу подстановок:
4. при варьировании начальной стоимости: 50000, 60000, 70000, 90000, 110000 и том же периоде амортизации;
5. при варьировании начальной стоимости: 50000, 60000, 70000, 90000, 110000 и периоде 5; 6, 7, 8, 9 месяцев соответственно.
6. Составить сценарий, если величина начальной стоимости изменится на 75000р.
7. Подобрать параметр срока эксплуатации, если стоимость основных фондов снизится на 2500р.

Вариант 8.

1. Вычислить величину ежемесячной выплаты (ПЛТ) фирмой, взявшей кредит в размере 90000р. со ставкой 7% на период, равный 1 году и будущей стоимостью 9000р.
2. После, вычислить эту величину (не меняя формулу) при условии, что ставка процента изменится на 10%.
3. Определить величину ежемесячной выплаты (ПЛТ) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и суммах кредита 70000, 80000, 90000, 100000, 120000 руб. соответственно.
6. Составить сценарий, если величина кредита изменится на 80000р., а срок на 2 года.
7. Подобрать параметр срока, на который был взят кредит, если ежемесячные выплаты составят 5000р.

Вариант 9.

1. Вычислить величину всех выплат (БС) фирмой, взявшей кредит в размере 67000р., сроком на 3 года, с ежемесячной выплатой 7000р. и годовой ставкой процента, равной 4,5%.
2. Вычислить эту величину (не меняя формулу) при условии, что величина процентной ставки изменится на 6%.
3. Определить величину БС используя таблицу подстановок:
4. при процентных ставках 5%, 6%, 7% , 8% и 9% годовых;
5. при процентной ставке 5%, 6%, 7% , 8% и 9% годовых, и суммах кредита 70000, 80000, 90000, 100000, 120000 руб. соответственно.
6. После, составить сценарий, если величина кредита изменится на 59000р.
7. Подобрать параметр ежемесячной выплаты, если величина всех выплат составит 103000р.

Вариант 10.

1. Вычислить общее количество периодов выплаты (КПЕР) фирмой, взявшей кредит в размере 93000р. Ставка процента постоянна и равна 6,5%. Ежемесячные выплаты фирмой также постоянны и равны 6500р.
2. Вычислить эту величину (не меняя формулу) при условии, что величина кредита изменится на 80000р.
3. Определить величину КПЕР используя таблицу подстановок:
4. при процентных ставках 5%, 6%,7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и суммах кредита 100000,90000, 80000, 70000, 60000, 50000 руб. соответственно.
6. Затем составить сценарий (условие п.1), если величина процентной ставки изменится на 9%, а ежемесячная выплата 4500р.
7. Подобрать параметр величины кредита, если выплата будет производиться 2 года.

Вариант 11.

1. Определить величину ежемесячной амортизации имущества (АПЛ) при условии, что начальная стоимость его 100000р., а остаточная (в конце периода амортизации) 10000р.; амортизация имущества занимает период 4 года.
2. Затем вычислить эту величину (не меняя формулу) при условии, что остаточная амортизация равна 12000р.
3. Определить величину АПЛ используя таблицу подстановок:
4. при варьировании остаточной амортизации: 5000, 6000, 7000, 8000, 9000 и том же периоде;
5. при варьировании остаточной амортизации: 5000, 6000, 7000, 8000, 10000 и периоде 1, 2, 3, 3,5 и 4 года соответственно.
6. После, составить сценарий, если начальная стоимость имущества изменится на 97000р.
7. Подобрать параметр срока полной амортизации при условии, что ежемесячные отчисления составят 7500р.

Вариант 12.

1. Вычислить, на сколько снизится стоимость основных фондов, рассчитанная по методу двойной амортизации (ДДОБ), если начальная стоимость имущества 123000р., а в конце периода эксплуатации 9000р. Время эксплуатации считать равным 13 месяцев, период, для которого вычисляется амортизация, равным8 месяцев.
2. После, вычислить эту величину (не меняя формулу) при условии, что остаточная стоимость имущества равна 9500р.
3. Определить величину ДДОБ используя таблицу подстановок:
4. при изменении времени эксплуатации: 7, 6, 5, 4, 3 года;
5. при изменении времени эксплуатации: 7, 6, 5, 4, 3 года; и начальной стоимости 70000, 60000, 50000, 40000, 30000 руб. соответственно.
6. Затем составить сценарий, если величина начальной стоимости изменится на 115000р.
7. Подобрать параметр срока эксплуатации, если стоимость основных фондов снизится на 5500р.

Вариант 13.

1. Вычислить величину ежемесячной выплаты (ПЛТ) фирмой, взявшей кредит в размере 74000р. со ставкой 8% годовых на период, равный 5 лет и будущей стоимостью 5000р.
2. После, вычислить эту величину (не меняя формулу) при условии, что ставка процента изменится на 14%.
3. Определить величину ежемесячной выплаты (ПЛТ) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% ,10%, 12% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10%, 12% годовых, и сроке кредита 3, 4, 5, 6, 7, 8 лет соответственно.
6. Затем составить сценарий, если величина кредита изменится на 87000р.
7. Подобрать параметр срока, на который был взят кредит, если ежемесячные выплаты составят 7500р.

Вариант 14.

1. Вычислить величину всех выплат (БС) фирмой, взявшей кредит в размере 77000р., сроком на 2 года, с ежемесячной выплатой 9000р. и годовой ставкой процента, равной 7,5%.
2. Затем, вычислить эту величину (не меняя формулу) при условии, что величина процентной ставки изменится на 9%.
3. Определить, используя таблицу подстановок:
4. величину всех выплат (БС) при процентных ставках 7%, 8% , 9% и 10% годовых;
5. величину всех выплат (БС) при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, а сроке займа 5, 7, 8, 9, 10 лет соответственно.
6. Составить сценарий, если величина кредита изменится на 86000р.
7. Подобрать параметр ежемесячной выплаты, если величина всех выплат составит 120000р.

Вариант 15.

1. Вычислить общее количество периодов выплаты (КПЕР) фирмой, взявшей кредит в размере 113000р. Ставка процента постоянна и равна 10%. Ежемесячные выплаты фирмой также постоянны и равны 8500р.
2. Вычислить эту величину (не меняя формулу) при условии, что величина кредита изменится на 100000р.
3. Определить общее количество периодов выплаты (КПЕР), используя таблицу подстановок:
4. при процентных ставках 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и сумме кредита 50000, 70000, 80000, 90000, и 100000 руб. соответственно.
6. Составить сценарий, если величина процентной ставки изменится на 8,5%, а сумма кредита на 85000 руб. Сохранить отчет типа структура.
7. Подобрать параметр величины кредита, если выплата будет производиться 4 года.

Вариант 16.

1. Вычислить величину ежемесячной выплаты (ПЛТ) фирмой, взявшей кредит в размере 80000р. со ставкой 8% годовых сроком на 6 лет и будущей стоимостью 8000р.
2. Вычислить эту величину (не меняя формулу) при условии, что ставка процента изменится на 12%.
3. Определить величину ежемесячной выплаты (ПЛТ) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и сроком на 5, 7, 8, 9, 10 лет соответственно.
6. Составить сценарий, если величина кредита изменится на 120000р, а срок на 10 лет.
7. Подобрать параметр срока, на который был взят кредит, если ежемесячные выплаты составят 7000р.

Вариант 17.

1. Вычислить величину всех выплат (БС) фирмой, взявшей кредит в размере 65000р., сроком на 3 года, с ежемесячной выплатой 4000р. и годовой ставкой процента, равной 6,5%.
2. Затем, вычислить эту величину (не меняя формулу) при условии, что величина процентной ставки изменится на 5%.
3. Определить величину всех выплат (БС) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и суммах кредита 50000, 60000, 70000, 80000, 90000, 100000 руб. соответственно.
6. Составить сценарий, если величина кредита изменится на 70000р., а срок на 5 лет.
7. Подобрать параметр ежемесячной выплаты, если величина всех выплат составит 95000р.

Вариант 18.

1. Определить величину ежемесячной амортизации имущества (АПЛ) при условии, что начальная стоимость его 90000р., а остаточная (в конце периода амортизации) 9000р.; амортизация имущества занимает период 5 лет.
2. Затем вычислить эту величину (не меняя формулу) при условии, что остаточная амортизация равна 10000р.
3. Определить величину АПЛ используя таблицу подстановок:
4. при варьировании остаточной амортизации: 5000, 6000, 7000, 8000, 9000 и том же периоде;
5. при варьировании остаточной амортизации: 5000, 6000, 7000, 8000, 10000 и периоде 1, 2, 3, 3,5 и 4 года соответственно.
6. После, составить сценарий, если начальная стоимость имущества изменится на 127000р., а период на 7 лет
7. Подобрать параметр срока полной амортизации при условии, что ежемесячные отчисления составят 7500р.

Вариант 19.

1. Вычислить величину ежемесячной выплаты (ПЛТ) фирмой, взявшей кредит в размере 60000р. со ставкой 6% годовых сроком на 4 года и будущей стоимостью 6000р.
2. Вычислить эту величину (не меняя формулу) при условии, что ставка процента изменится на 7%.
3. Определить величину ежемесячной выплаты (ПЛТ) используя таблицу подстановок:
4. при процентных ставках 5%, 7%, 8% , 9% и 10% годовых;
5. при процентной ставке 5%, 7%, 8%, 9%, 10% годовых, и сроком на 5, 7, 8, 9, 10 лет соответственно.
6. Составить сценарий, если величина кредита изменится на 80000р, а срок на 5 лет.
7. Подобрать параметр срока, на который был взят кредит, если ежемесячные выплаты составят 2000р.

Вариант 20.

1. Определить, на сколько снизится стоимость имущества (ДДОБ) на заданный период, используя метод двойной амортизации,если начальная стоимость имущества 120000р., а в конце периода эксплуатации 12000р. Время эксплуатации считать равным 26 месяцев, период, для которого вычисляется амортизация, равным 12 месяцев.
2. Вычислить эту величину (не меняя формулу) при условии, что остаточная стоимость имущества равна 9000р.
3. Определить величину ДДОБ используя таблицу подстановок:
4. при изменении времени эксплуатации: 7, 6, 5, 4, 3 года;
5. при изменении времени эксплуатации: 7, 6, 5, 4, 3 года и варьировании начальной стоимости: 70000, 80000, 90000, 100000, 110000 соответственно.
6. Составить сценарий, если величина начальной стоимости изменится на 95000р.
7. Подобрать параметр срока эксплуатации, если стоимость имущества снизится до 7000р.

Контрольные вопросы

1. Для чего предназначены функции: ПЛТ; БС; ПС; КПЕР и СТАВКА? Поясните синтаксис перечисленных функций.
2. Назначение и способы анализа “Что если”?
3. Для чего предназначена “Таблица подстановок”, опишите технологию ее применение для функций с одной переменной и для функций с двумя переменными?
4. Что такое сценарий, как его создать, просмотреть, изменить, получить итоговый отчет на отдельном листе?
5. Сущность операции Подбор параметра, как она выполняется?

Ставка.

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

Синтаксис.   СТАВКА (кпер, выплата, ПС, БС,
тип, предположение).

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

Рассмотрим варианты практического применения этой функции

1)  
Допустим, необходимо рассчитать процентную
ставку при известной текущей стоимости ПС, будущей
стоимости БС, числе периодов КПЕР. Тогда
формула в EXCEL в общем виде записывается так:

СТАВКА(кпер, , ПС,
БС, , предположение).

2)  
В случае фик­сированных обязательных или
обычных периодических платежей процентную ставку за расчетный период
удобнее вычислять с помощью функции:

СТАВКА(кпер,
выплата,, БС, тип, предположение).

3)  
Расчет процентной ставки по займу размером
ПС при равномерном погашении обычными периодическими
платежами, при условии, что заем полностью погашается, ве­дется по
формуле:

СТАВКА(кпер,
выплата, ПС, ,, предположение).

Примеры.

Задание 1.Предположим, что компании потребуется 100 тыс. руб. через 2 года.
Ком­пания готова вложить 5 тыс. руб. сразу и по 2,5 тыс. руб. каждый
последующий месяц. Каким должен быть процент на инвестированные
средства, чтобы получить необходимую сумму в конце второго года.

Решение.
В этой задаче сумма 100 тыс. руб. (аргумент БС функции СТАВКА) форми­руется за счет приведения к будущему моменту
начального вклада размером 5 тыс. руб. (аргумент ПС) и
фиксированных ежемесячных выплат (аргумент выплата). Используем функцию:

=СТАВКА(24, -2,5, -5,
100)= 3,28% (рисунок 51).

Ежемесячная процентная ставка составит 3,28%, годовая –
12*3,28%=39,36%

Рисунок 51 – Применение функции СТАВКА

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

Решение. Будущее значение ежемесячных выплат по 250 руб. должно
составить че­рез 4 года сумму займа с процентами. Ежемесячная ставка
процента должна составлять

СТАВКА(48,
-250, 7000) =2,46%,

Годовая процентная ставка составит 2,46% * 12 = 29,5%,

q 
Расчет периодических платежей. Функция ПЛТ.

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

Синтаксис   ПЛТ (ставка, кпер, ПС,
БС, тип).

Функция ПЛТ применяется в следующих
расчетах

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

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

2. Предположим, рассчитываются равные периодические
платежи по займу вели­чиной НЗ, необходимые для полного погашения
этого займа через КПЕР число периодов. Текущая стоимость
этих выплат должна равняться текущей сумме займа. Расчет в
EXCEL выполняется по формуле;

ПЛТ(ставка,
кпер, ПС,, тип).

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

ПЛТ(ставка,
кпер, ПС), так как аргумент тип – 0,

Если заем погашается не полностью, то есть его будущее
значение не равно 0, то следует указать аргумент БС,
который равен непогашенному остатку займа после всех вы­плат.

Примеры.

Задание 1. Клиенту банка необходимо накопить 200
тыс. руб. за 2 года. Клиент обязуется вносить в начале каждого месяца
постоянную сумму под 9% годовых. Какой должна быть эта сумма?

Для
определения ежемесячных выплат применяется функция ПЛТ с
аргументами: Ставка = 9%/12 (ставка процента за месяц); Кпер= 2*12 = 24 (общее число месяцев начисления процентов);

Тогда
величина ежемесячных выплат равна:

= ПЛТ
(9%/12; 24; ; 200; 1) = – 7,58 тыс. руб.

Результат
со знаком «минус», так как 7,58 тыс. руб. клиент ежемесячно вносит в
банк. Иллюстрация решения задачи приведена на рисунке 52.

Рисунок
52 – Иллюстрация применения функции ПЛТ

 Задание 2 Предположим, что
необходимо накопить 4000 руб. за 3 года, откладывая постоянную сумму в
конце каждого месяца. Какой должна быть эта сумма, если норма процента
по вкладу составляет 12% годовых.

Решение.
Общее число периодов начисления процентов кпер =3*12, ставка = 12% / 12.
Аргумент тип = 0, т.к. это вклады постнумерандо. Величина ежемесячных
выплат бу­дет равна:

ПЛТ(12%/12,
12*3„4000)=-92,86руб.

Задание 3.
Допустим, банк выдал ссуду 200 тыс. руб. на 4 года под 18% годовых.
Ссуда выдана в начале года, а погашение начинается в конце года
одинаковыми платежа­ми. Определить размер ежегодного погашения ссуды

Решение. Ежегодные
платежи составят ПЛТ(18%, 4, -200) = 74,35 тыс. руб.

БС?

ПС?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ:

Задачи 1 – 15

Формула расчета доли

Расчет доли часто необходим в бухгалтерском и финансовом учете, где необходимо определить долю тех или иных видов активов по отношению к суммарным. На рисунке ниже приведен пример и бухгалтерские данные по предприятию ОАО «АЛРОСА».

Пример задачи. Необходимо рассчитать долю «Запасов» в структуре «Активов» предприятия. Для этого воспользуемся формулой:

Доля запасов в Активах =B6/B7

Для того чтобы в ячейке полученные доли имею процентный вид можно воспользоваться сочетанием клавиш «Ctrl» «Shift» «%».

Формула расчета очищенной стоимости капитала (продукции)

Пример задачи. Необходимо рассчитать начальную стоимость продукции без НДС (налог на добавленную стоимость), текущая стоимость продукции составляет 200000 руб., процентная ставка налога 18%. Формула расчета процентов следующая:

Стоимость продукции без НДС=B6/(1 B7)

Формула расчета простых процентов по банковскому вкладу

При использовании простых процентов выплаты по вкладу осуществляются только в конце срока (периода) размещения.

Пример задачи. Требуется рассчитать размер выплат по банковскому вкладу, на который начисляется простые проценты. Размер вклада составляет 150000 руб., годовая процентная ставка по вкладу равна 12% (за 365 дней), период размещения вклада составляет 300 дней. Формула расчета в Excel следующая:

Размер банковского вклада на конец периода размещения =(1 B8*300/365)*B6

Формула расчета процентов. базовые понятия

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

• Капитал (англ.Capital,Principal) — является базой относительно которого вычисляют процент.
• Частота начисления процентов — период выплат процентов на капитал.
• Процентная ставка (англ.Rate) — размер процента или доля капитала, который будет выплачен.
• Период вложения (англ.Period) — временной интервал передачи капитала банку или другому финансовому институту.

Итак, рассмотрим различные эконометрические задачи с процентами.

Формула расчета размера капитала с процентами

Пример задачи. Требуется рассчитать итоговую стоимость товара с НДС (налог на добавленную стоимость), тогда как стоимость товара без НДС составляет 10000 руб., процентная ставка налога равна 18%. Формула расчета стоимости товара с учетом НДС рассчитывается по формуле:

Стоимость товара с учетом НДС =B6*(1 B7)

Формула расчета размера процента от капитала

Рассмотрим вторую постоянно встречающуюся экономическую задачу: расчет абсолютного значения процента по капиталу.

Пример задачи. Необходимо определить размер выплаты банка ОАО «Альфа-банк» по вкладу в размере 100000 руб. с процентной ставкой 15%. Формула расчета размера выплат будет иметь следующий вид:

Выплаты по вкладу в банке =B6*B7

Формула расчета сложных процентов по банковскому вкладу

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

Пример задачи. Необходимо рассчитать размер банковского вклада, который был размещен по сложный процент. Первоначальный размер депозита составляет 100000 руб., годовая процентная ставка равна 14%, период начисления процентов — каждые 4 месяца, срок размещения вклада 1 год.

Формула расчета сложных процентов:

Размер вклада со сложными процентами на конец года =B6*(1 B8*B9/B7)^4

Формула уменьшения размера капитала на процент

Пример задачи. Необходимо рассчитать размер капитала после вычета налога на прибыль, процентная ставка налога равна 24%, текущее значение капитала равно 50000 руб. Формула расчета остатка капитала после вычета налога следующая:

Остаток капитала =B6-B6*B7

Функции по расчету амортизации: aпл, асч и ддоб

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

Функция АПЛ (SLN) возвращает величину амортизации имущества за один период времени, используя метод равномерной амортизации.

Синтаксис:

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

которая возвращает значение 1000 р.

Функция АСЧ(SYD) возвращает годовую амортизацию имущества для указанного периода.

При расчете предыдущего примера получим:

которая возвращает значение 1666.67р.

которая возвращает 333. 33 р.

Функция ДДОБ (DDB) возвращает величину амортизации имущества для указанного периода, используя метод двукратного (или k-кратного) учета амортизации.

Синтаксис:

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

В примере с компьютером по методу двукратной амортизации она составит:

Примечание

В заключение попытаемся разобраться, как работают функции АПЛ, АСЧ, ДОБ и ДДОБ.

Проще всего дело обстоит с функцией АПЛ. Она возвращает одну и ту же амортизацию за каждый период.

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

Значения функции ДДОБ изменяются также по экспоненциальному закону. Но коэффициент этой геометрической прогрессии не вычисляется, а является параметром.

Функция плт

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

Синтаксис:

Функция ПЛТ может быть использована для анализа всевозможных ссуд. Необходимым условием является непротиворечивость аргументов функции.

Пример 1. Предположим, что нужно воспользоваться 9-процентной 15-летней ссудой. Объем ссуды составляет 150 000 000 рублей. C помощью Мастера функций можно определить величины ежемесячных выплат. Предварительно следует привести все другие значения к месячной норме.

Ввести таблицу (рис. 9.1
рис.
9.1 ), начиная с ячейки А1:

В ячейки В 3 и В 4 ввести соответствующие формулы.

Процентная ставка (СТАВКА) – годовая, поэтому для получения месячной ставки (Удельная ставка) соответствующее значение делится на 12 (0,09/12).

Срок действия ссуды – 15 лет, поэтому с учетом 12 платежей год общее количество месячных выплат (КПЕР) составит 12х15.

Для ячейки В6 пошаговыми действиями Мастера функций выполните настройку функции ПЛТ. Для вызова Мастера функций необходимо выбрать команду Вставить функцию (значок fx) в меню Формулы.

После этого в поле Значение диалогового окна Мастера функций вы увидите сумму ежемесячного взноса. А после нажатия на кнопку Готово результат отобразится в ячейке.

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

Функция пс

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

ПС (PV )– возвращает текущий объем вклада. Текущий объем -это общая сумма, которую составят будущие платежи. Например, когда вы берете взаймы деньги, заимствованная сумма и есть текущий объем для заимодавца.

Синтаксис:

Например, определите необходимую сумму текущего вклада в банк, чтобы через пять лет он достиг 5000 руб. при 20% годовых и ежегодном начислении процентов в конце года. Синтаксис: ПС (20%, 5, 5000). Результат 2009,39.

Эффект

Оператор ЭФФЕКТ ведет расчет фактической (или эффективной) процентной ставки. У этой функции всего два аргумента: количество периодов в году, для которых применяется начисление процентов, а также номинальная ставка. Синтаксис её выглядит так:

=ЭФФЕКТ(Ном_ставка;Кол_пер)

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