Компьютерные технологии MS EXEL (стр. 2 из 4)

Нахождение корней нелинейных (трансцендентных) уравнений, используя инструмент «Подбор параметра»

Пример. Найти корни уравнения

Из рис.1 видно, что функция меняет знак между значениями Xдиапазона [3,2;3,3]. Значит, в этом диапазоне существует корень. В качестве начального приближения Xкорень берем ячейку F3=3, значение функции Y задаем в ячейке F4=3*F3-4*ln(F3)-5.

Теперь выберем команду Сервис, Подбор параметра и заполним диалоговое окно Подбор параметра. Заполнение окна смотрите на рис.1.

После нажатия кнопки OK средство Подбора параметров находит приближенное значение корня, которое помещает в ячейку F3, а корень-результат в ячейку F4(смотрите рис.2).

Рис.1

Рис.2

Варианты к заданию 3

№	Уравнения	Отрезок, содержащийкорень	Приближенное значение
1	1-x+sinx-ln(1+x)=0	[0; 1,5]	1,1474
2	3x-14+ex-e-x=0	[1; 3]	2,0692
3		[0; 1]	0,5768
4	x+cos(x0,52+2)=0	[0,5; 1]	0,9892
5	3ln2x+6lnx-5=0	[1; 3]	1,8832
6	sinx2+cosx2-10x=0	[0; 1]	0,1010
7	x2 - ln(1+x) - 3=0	[2; 3]	2,0267
8	2x*sinx - cosx=0	[0,4; 1]	0,6533
9		[-1; 0]	- 0,2877
10	lnx - x + 1,8=0	[2; 3]	2,8459
11		[0,2; 1]	0,5472
12		[1; 2]	1,0769
13		[1; 2]	1,2388
14		[0; 1]	0,4538
15	0,6*3x-2,3*x - 3=0	[2; 3]	2,4200
16		[2;3]	2,2985
17		[0; 2]	1,0001
18		[0,4; 1]	0,7376
19		[0; 0,85]	0,2624
20		[1; 2]	1,1183
21		[0; 0,8]	0,3333
22		[0; 1]	0,5629
23		[2; 4]	3,2300
24		[1; 2]	1,8756
25		[0; 1]	0,7672
26		[0; 1]	0,8814
27		[1; 3]	1,3749
28		[1,2; 2]	1,3077
29	ex+lnx-10x=0	[3; 4]	3,5265
30		[1; 2]	1,0804

Задание 4. Финансовый анализ в Excel

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

Пример 1

Постановка задачи. Вычислить финансовую функцию ППЛАТ(ПЛТ) расчета 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% от цены покупки и ежемесячной (ежегодной) выплате.

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

Синтаксис:

=ППЛАТ (ПЛТ) (ставка; кпер; ос; остаток; тип)

Аргументы:

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

кпер количество периодов выплат;

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

остаток Остаток или баланс наличности, который нужно достичь после последней выплаты. Если остаток опущен, то он полагается равным 0;

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

Очень важно быть последовательным в выборе единиц измерения для задания аргументов ставка и кпер. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента ставка используйте 12%/12, а для задания аргумента кпер — 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента ставка используйте 12%, а для задания аргумента кпер — 4.

Замечание. Обратите внимание, что в функциях, связанных с интервалами выплат, выплачиваемые вами деньги, такие как депозит на накопление, представляются отрицательным числом, а деньги, которые вы получаете, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000, если вы вкладчик, и аргументом 1000, если вы — представитель банка, т.е. что отдается банку-аргумент с минусом, при получении от банка-аргумент с плюсом.

Рабочий лист (рис.1) приведен в режиме отображения значений, а на рис.2-в режиме формул.

Рис.1.Расчет ипотечной ссуды

Рис. 2. Формулы для расчета ипотечной ссуды

Пример 2 расчета эффективности неравномерных капиталовложений с помощью функций НПЗ и инструмента Подбор параметра

Постановка задачи. Вас просят дать в долг 10000 руб. и обещают вернуть через год 2000руб., через два года— 4000руб., через три года— 7000 руб. Определить при какой годовой процентной ставке эта сделка выгодна? Для решения задачи будем использовать финансовую функцию НПЗ(ЧПС).

Функция НПЗ(ЧПС) возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных.

Синтаксис: НПЗ(ЧПС) (ставка; 1-е значение; 2-е значение; ...)

Аргументы: ставка Процентная ставка за период;

1-е значение, От 1 до 29 аргументов, представляющих расходы и доходы;

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

На рабочем листе (рис.3) введем исходные данные с пояснениями и расчетные формулы в следующей последовательности:

· Ввод текста и значений в диапазон A2:B6;

В ячейку C6 введем формулу

C6==ЕСЛИ(Вб=1;"год";ЕСЛИ(И(В6>=2;Вб<=4);"года";"лет"))

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

· В ячейку B8 введем формулу вычисления текущего вклада B8=НПЗ(ЧПС)(B7;B3;B5).

Ввод исходных данных завершен.

Рис.3.Расчет годовой процентной ставки

Далее выполняем команду Сервис, Подбор параметра и заполняем открывшееся диалоговое окно Подбор параметра, как показано на рис.4