Laboratornye_raboty_semestr2_modul2

51

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

Например, если вы берете в долг 1000 грн. при годовой ставке 1% и собираетесь выплачивать по 100 грн. в год, то число выплат (Рис.23) вычисляется следующим образом:

=КПЕР(B2;-B3;B1)

Рис.23 Вычисление количества периодов выплат

В результате получаем ответ: 11.

IV. Финансовая функция СТАВКА

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

Синтаксис:

СТАВКА(кпер; выплата; нз; бз; тип; нач_прибл)

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

52

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

нач_прибл Предполагаемая величина нормы. Если нач_прибл опущено, оно полагается равным 10%.

Рассмотрим пример использования функции СТАВКА: определить процентную ставку для четырехлетнего займа размером в 8000 грн. с ежемесячной выплатой 200 грн. (Рис.24). Для этого используется формула:

=СТАВКА(B2;-B3;B1)

Рис.24 Вычисление процентной ставки

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

Задание

1.Используя функции ПРПЛТ и ПЛТ, вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды Р грн. при годовой ставке i% на срок n лет.

Вариант	N	Р	i	Вариант	n	Р	i
1	7	170000	5	18	10	370000	13

53

Вариант	N	Р	i	Вариант	n	Р	i
2	8	200000	6	19	11	330000	12
3	9	220000	7	20	7	290000	10
4	10	300000	8	21	9	380000	5
5	11	350000	9	22	12	270000	11
6	7	210000	10	23	6	190000	7
7	8	250000	11	24	8	220000	13
8	9	310000	12	25	10	250000	8
9	10	320000	13	26	6	200000	12
10	11	360000	14	27	9	300000	11
11	8	220000	10	28	7	250000	5
12	7	150000	8	29	8	320000	9
13	10	280000	12	30	10	380000	12
14	12	430000	10	31	11	360000	14
15	11	320000	7	32	10	280000	12
16	9	380000	10	33	8	200000	6
17	8	250000	7	34	9	380000	10

2.Вы берете в долг Р грн. при годовой ставке i% и собираетесь отдавать А грн. в год. Сколько лет займут выплаты?

Вариант	А	Р	i	Вариант	А	Р	i
1	200	1000	2	18	850	4700	10
2	190	1700	3	19	225	5300	4
3	178	3100	4	20	185	4500	3
4	164	5900	2	21	240	3350	5
5	146	6190	2	22	215	2300	6
6	243	6509	3	23	278	2350	8
7	320	6860	4	24	240	3600	5
8	423	7246	5	25	180	2700	6
9	521	7670	6	26	220	3800	3
10	711	8138	7	27	175	2900	5
11	120	1000	6	28	475	3700	8
12	750	3350	10	29	180	2400	5
13	250	1800	7	30	230	3700	4

54

Вариант	А	Р	i	Вариант	А	Р	i
14	130	2100	5	31	245	6600	6
15	580	6700	3	32	525	7750	4
16	210	3700	4	33	850	4800	9
17	275	3800	7	34	175	3400	5

3.Вы собираетесь вкладывать по А грн. в течение n лет при годовой ставке i%. Сколько денег будет на счете через n лет?

Вариант	А	n	i	Вариант	А	n	i
1	200	10	2	18	210	10	4
2	190	11	3	19	175	9	7
3	178	12	4	20	235	8	10
4	164	13	2	21	225	7	4
5	146	14	2	22	185	10	3
6	243	8	3	23	140	9	5
7	320	9	4	24	215	13	6
8	423	10	5	25	178	10	8
9	521	11	6	26	240	12	5
10	711	12	7	27	180	9	6
11	120	14	6	28	220	14	3
12	150	9	10	29	175	8	5
13	250	11	7	30	205	11	8
14	130	13	5	31	180	17	5
15	180	12	3	32	230	10	4
16	175	13	5	33	220	9	8
17	220	12	6	34	164	13	2

4.Определить %-ю ставку для n-летнего займа в Р грн. с ежемесячной выплатой в А грн.

Вариант	А	Р	n	Вариант	А	Р	n
1	150	1000	2	18	270	7500	7
2	190	1700	3	19	340	6000	5
3	178	3100	4	20	620	32000	20

55

Вариант	А	Р	n	Вариант	А	Р	n
4	164	5900	7	21	370	18000	11
5	146	6190	10	22	620	21000	13
6	240	6500	8	23	430	16200	9
7	320	6860	4	24	550	25000	12
8	423	7200	5	25	240	12500	8
9	520	7670	6	26	400	16800	18
10	710	8138	7	27	350	9500	9
11	250	4500	12	28	480	8500	15
12	340	6700	8	29	800	18000	20
13	280	7500	9	30	360	25000	12
14	400	10000	15	31	750	45000	18
15	420	8800	11	32	520	12500	14
16	250	5200	15	33	710	8138	7
17	170	3500	5	34	340	6700	8

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

Использование сценариев и таблиц подстановки для прогнозирования

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

Порядок действий при создании сценария:

1.Команда Сервис/Сценарии.

2.Кнопка Добавить.

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

4.Ввести ссылки на ячейки, которые необходимо изменить, в поле Изменяемые ячейки.

5.Установить необходимые флажки в наборе флажков Защита.

6.Нажать кнопку OK.

7.Ввести необходимые значения в диалоговом окне Значения ячеек сценария.

8.Чтобы создать сценарий, нажать кнопку OK.

56

9.Для создания дополнительных сценариев нажать кнопку Добавить, а затем повторить шаги с 3 по 7. После завершения создания сценариев нажать кнопку OK, а затем – кнопку Закрыть

Рассмотрим задачу использования сценария для определения выгодности сделки. Допустим, у вас просят в долг 10000 грн. и обещают возвращать по 2000 грн. в течение 6 лет. При какой годовой процентной ставке будет выгодна эта сделка?

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

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

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

производится в конце периода, если 1 – в начале периода В приводимом расчете в ячейку В5 введена формула (Рис.25)

=ПС(В4;В2;-ВЗ)

В ячейку В4 вводится произвольная процентная ставка, например 7%.

Рис.25 Расчет эффективности капиталовложений

57

Кроме того, для автоматизации составления таблицы в ячейки С2 и В6 введены формулы:

=ЕСЛИ(B2=1;"год";ЕСЛИ(И(B2>=2;B2<=4);"года";"лет")) =ЕСЛИ(B1<B5;"Выгодно дать деньги в долг";ЕСЛИ(B1=B5;"Варианты равносильны";"Выгодно положить деньги под проценты"))

Команда Сервис/Сценарии предоставляет возможность проанализировать ситуацию для нескольких возможных вариантов параметров с одновременным автоматизированным составлением отчета. Рассмотрим способ применения этой команды для следующих процентных ставок: 6, 5, и 8. Для этого необходимо выбрать команду Сервис/Сценарии. В открывшемся диалоговом окне Диспетчер сценариев для создания первого сценария нажмите кнопку Добавить (Рис.26).

Рис.26 Диалоговое окно Диспетчер сценариев

Рис.27 Диалоговое окно Изменение сценария

В диалоговом окне Изменение сценария в поле Название сценария введите,

например ПЗ1, а в поле Изменяемые ячейки – ссылку на ячейку В4, в которую вводится значение процентной ставки (Рис.27). После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария, в поле которого введите значение параметра для первого сценария (Рис.28).

58

Рис.28 Диалоговое окно Значения ячеек сценария

С помощью кнопки Добавить последовательно создайте нужное число сценариев. После этого диалоговое окно Диспетчер сценариев будет иметь вид, представленный на Рис.29. С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки Отчет открывает диалоговое окно Отчет по сценарию (Рис.30).

Рис.29 Вывод сценариев на рабочий лист с помощью диалогового окна

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

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

Структура.

59

Рис.31 Отчет по сценарию типа Структура

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

Как видно из отчета по сценарию при годовой процентной ставке 5% выгодно дать деньги в долг.

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

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

1.Следует сформировать таблицу подстановки.

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

3.В отдельный столбец или в отдельную строку нужно ввести список значений, которые следует подставлять в ячейку ввода.

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

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

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

7.Команда Данные/Таблица подстановки.

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

60

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

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

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

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

2.В тот же столбец ниже формулы ввести значения подстановки для первой переменной. Значения подстановки для второй переменной вводятся в строку правее формулы.

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

4.Команда Данные/Таблица подстановки.

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

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

I.Использование таблицы подстановки с одной переменной

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

Исходные данные и формулы для расчета суммы вклада и коэффициента наращивания приведены на Рис.32. Для вычисления суммы вклада используется финансовая функция БС:

=БС(B3;B2;0;-B1;0),

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

=B5/B1.