Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

– в D4 введено произвольное начальное число;

– в С8 введена формула =ЧПС(C2;C5:C7;

– в D8 введена формула =ЧПС(D2;D5:D7);

– в С9 введена формула = –C8/C4;

– в D9 введена формула = –D8/D4;

– в С10 введена формула =C9 – D9.

Для решения задачи необходимо подобрать такое значение в ячейке D4, чтобы индексы рентабельности проектов сравнялись, т.е. в ячейке С10 должен получиться ноль.

Для автоматического подбора:

– курсор устанавливаем в С10;

– вызываем средство «Подбор параметра» (Сервис > Подбор параметра);

– в поле «Установить в ячейке» указать С10;

– в поле «Значение» указать 0;

– в поле «Изменяя значение в ячейке» указать D4.

После нажатия кнопки «OK» в ячейке D4 должно получиться значение 3307,16.

Пример 3.

Папа-нефтяник отправил своего сына учиться и для его материального обеспечения положил в банк некую сумму. По условиям договора сын имеет право в течение всех пяти лет учебы ежемесячно снимать со счета по 20000 руб. Кроме того, договор составлен так, что после снятия последней суммы (в конце пятого года обучения) на счете должна остаться сумма, равная начальному значению вклада. Определить величину начального вклада, если годовая ставка равна 10%.
Поскольку финансовые функции являются взаимообратными, то решать данную задачу можно используя практически любую из них. Рассмотрим метод с использованием функции ПЛТ.

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

	A	B	C	D
1
2		Начальный вклад
3		Ставка
4		Время
5		Периодичность
6		Конечный вклад
7		Платежи
8

---

– в ячейку C2 вводится произвольное отрицательное число;

– в ячейки C3, C4 и C5 вводятся заданные в условии исходные данные;

– в ячейку C6 вводится формула =–C2;

– в ячейку C7 вводится формула =ПЛТ(C3/C5;C4*C5;C2;C6).

	A	B	C	D
1
2		Начальный вклад	–1000
3		Ставка	10%
4		Время	5
5		Периодичность	12
6		Конечный вклад	1000
7		Платежи	8,33
8

---

При этом в C7 появится число 8,33.

Но нам необходимо, чтобы величина платежа была равна 20000.

Обратите внимание на то, что введенные в C6 и C7 формулы зависят от величины начального вклада. Потому меняя эту величину вручную можно попытаться подобрать ее так, чтобы в С7 получилось 20000.

Для автоматического подбора:

– курсор устанавливаем в С7;

– вызываем средство «Подбор параметра» (Сервис > Подбор параметра);

– в поле «Установить в ячейке» указать С7;

– в поле «Значение» указать 20000;

– в поле «Изменяя значение в ячейке» указать С2.

После нажатия кнопки «OK» в ячейке С2 должно получиться –2400000, а в С7 – 20000.
Пример 4.

Кредит в 100000 у.е. выдан на 4 года под 18% годовых при условии, что каждая последующая возвращаемая (1 раз в году) сумма на 2000 больше предыдущей. Найти возвращаемые суммы, если к концу 4 года кредит должен быть погашен полностью.

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

	A	B	C	D	E	F
1
2			Ставка	18%
3
4		Время	Долг	Выплата	Остаток
5		0	100000		100000
6		1	118000	30000	88000
7		2	103840	32000	71840
8		3	84771,2	34000	50771,2
9		4	59910,02	36000	23910,02
10

---

Механизм погашения долга выглядит следующим образом:

– в конце первого года на остаток долга начисляются проценты и затем, возвращается часть долга;

– в конце второго года на остаток долга начисляются проценты и затем, возвращается часть долга, на 2000 большая, чем в первом году. И т.д.;

Для реализации расчетов в ячейки введено следующее:

– в D6 введена произвольная начальная сумма;

– в C6 введена формула = E5 * 1,18;

– в E6 введена формула = C6 – D6;

– в D7 введена формула = D6 + 2000.

Затем все указанные формулы скопированы вниз по столбцам до 4 года включительно.

Как следует из полученного расчета, мы не угадали величину начальной суммы выплат (введенную в D6), поскольку остаток вклада в конце четвертого года не равен 0.

Для того, чтобы подобрать ее:

– курсор устанавливаем в E9;

– выполняем команды Сервис > Подбор параметра;

– в появившемся окне

– в поле «Установить в ячейке» указываем E9;

– в поле «Значение» указываем 0;

– в поле «Изменяя значение в ячейке» указываем D6.

После нажатия кнопки «OK» в ячейке D6 должно получиться 34584,47, а в C9 – 0.
Пример 5. Найти дисконтированный срок окупаемости (DPP) инвестиционного проекта, у которого первоначальные затраты составляют 100 тыс. руб., а ожидаемые доходы представлены в табл. 6.8.

Таблица 6.8

Годы	1	2	3	4	5	6
Поступление наличности, тыс. р.	25	30	35	40	45	50

Ставка дисконтирования равна 10%.
Решение. Построим расчетную таблицу:

	A	B	C	D	E
1	Расчет дисконтированного срока окупаемости
2	Ставка	0,1
3	Год	Сумма Pk	(1+i)k	Дисконтированные суммы Pk/ (1+i)k	Дисконтированный доход нарастающим итогом
4	0	-100	1	-100	-100
5	1	25	1,1	22,73	-77,27
6	2	30	1,21	24,79	-52,48
7	3	35	1,331	26,30	-26,18
8	4	40	1,464	27,32	1,14
9	5	45	1,611	27,94	29,08
10	6	50	1,772	28,22	57,30
11			DPP	3,958

---

В столбцах C, D, E введем формулы:

Ячейка	Формула	Примечание
С4	=(1+$B$2)^A4	копируем вниз до строки 11
D4	=B4/C4
E4	=D4
E5	=E4+D5

В столбце E последовательно прослеживается изменение суммы дисконтированных доходов. Последнее отрицательное значение этой суммы получено в ячейке E7 (-26,18), после чего накопленный доход становится положительным. Таким образом, данный инвестиционный проект окупится в четвертом году. Чтобы найти долю четвертого года, которая для этого понадобится, необходимо поделить сумму, которую осталось компенсировать (26,18) на дисконтированный доход четвертого года (27,32). Подводя итог вышесказанному, найдем в ячейке D11 значение дисконтированного срока окупаемостиc помощью формулы:

=A7-E7/D8.

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

3. 
   Варианты заданий
   

Внимание!

В некоторых вариантах начальные значения подбираемых параметров необходимо указывать как можно ближе к ожидаемым значениям.
1. Даны 2 проекта, рассчитанные на 3 года, при норме дисконта 10%. Какой должна быть первоначальная сумма во втором проекте, если у второго проекта величина NPV вдвое выше, чем у первого?

Период	0	1	2	3
Проект А	–800	500	200	1880
Проект В	?	3500	4500	2000

2. Кредит в 200000 д.е. выдан на 6 лет под 25% годовых при условии, что каждая последующая возвращаемая (1 раз в году) сумма на 15000 меньше предыдущей. Найти возвращаемые суммы, если к концу 6 года кредит должен быть погашен полностью.

---