Laboratornye_raboty_semestr2_modul2

41

1.Открыть свою папку и в ней создать новый файл Microsoft Excel. Назвать новый файл «Закон распределения».

2.Создать новый документ Microsoft Word, назвать его «Табличные данные». Открыть и вставить в него файл, полученный в результате выполнения labrab2.exe, с помощью команды Вставка\Файл.

3.Выделить весь текст и преобразовать в таблицу из одного столбца и 500 строк командой Таблица\Преобразовать.

4.Заменить во всем тексте десятичную точку на десятичную запятую с помощью команды Правка\Заменить. Сохранить документ.

5.Скопировать таблицу в буфер обмена. Для этого выделить целиком таблицу: активизировать любую ячейку таблицы и выполнить команду

Таблица\Выделить таблицу, а затем вызвать команду.

6.Открыть лист Excel «Закон распределения» и, выделив первую ячейку на первом листе, вставить из буфера скопированные ячейки.

7.Значения, которые находятся во втором столбце перенести на лист 2.

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

Например: =ДИСП(A1:A500). Смотри образец!

9.Разбить диапазон возможных значений на 20 интервалов (для первого столбца). Для этого сначала вычисляем шаг разбиения по формуле (максимумминимум)/20, где вместо максимума и минимума нужно подставить ссылки на соответствующие ячейки второго столбца. Затем в третьем столбце рассчитываем границы интервалов. Первая граница – наш минимум, вторая – минимум + шаг, третья – вторая + шаг и т. д. Всего 21 граница. Смотри

образец!

10.В четвертом столбце 21 раз подсчитываем число элементов выборки, не превышающих данный предел (для каждой границы между интервалами) с помощью статистической функции ЧАСТОТА.

42

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

12.В шестом столбце 20 раз нормируем число попаданий, деля его на объем выборки, чтобы получить вероятность попадания в данный интервал.

13.По данным последнего столбца строим график распределения вероятности. Границы интервалов должны стать подписями оси X. Название графика –

плотность распределения, оси X – значения, Y – вероятность.

14.Провести такие же вычисления для значений второго листа, только разбить его на 10 интервалов

15.Сохранить документ. Образец написания формул.

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

Расчет сложных процентов с использованием финансовых функций MS EXCEL

I.Финансовая функция ПЛТ

Функция ПЛТ вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке. Синтаксис:

ПЛТ(ставка; кпер; нз; бз; тип)

Аргументы:

ставка Процентная ставка за период кпер Общее число периодов выплат

нз Текущее значение, т. е. общая сумма, которую составят будущие платежи

43

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

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

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

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

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

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

II.Финансовая функция ЧПС

44

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

ЧПС(ставка; 1-е значение; 2-е значение;…)

Аргументы:
Ставка	Процентная ставка за период
1-е значение, 2-е значение		От 1 до 29 аргументов, представляющих
		расходы и доходы.

Рассмотрим следующую задачу: Вас просят дать в долг 10000грн. и обещают вернуть через год 2000грн., через два года – 4000грн., через три – 7000грн. При какой годовой процентной ставке эта сделка выгодна? В приведенном на Рис.17 расчете в ячейку В7 введена формула: =ЧПС(B6;B2:B4). Для автоматизации составления таблицы в ячейку С5 введена формула:

=ЕСЛИ(B5=1;"год";ЕСЛИ(И(B5>=2;B5<=4);"года";"лет")).

Рис.17 Расчет годовой процентной ставки c помощью функции ЧПС

Для определения годовой учетной ставки воспользуемся инструментом Подбор параметра (Рис.18).

Рис.18 Диалоговое окно Подбор параметра при расчете годовой процентной ставки

45

Подбор параметра определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000грн. Результат вычисления выводится в ячейку В6: годовая учетная ставка должна составлять 11,79%

III.Финансовая функция ВСД

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

Синтаксис:

ВСД (значения; прогноз)

Аргументы:

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

прогноз Величина, о которой предполагается, что она близка к результату ВСД

Определим годовую процентную ставку с помощью функции ВСД для данных из предыдущего примера. Для этого в ячейку В1 вместо 10000 надо ввести значение (–10000), а в ячейку В6 – функцию ВСД(В1:B4), которая и найдет минимальную годовую учетную ставку (Рис.19 и Рис.20).

Рис.19 Определение годовой процентной ставки с использованием функции ВСД

46

Рис.20 Формулы для определения годовой процентной ставки

Задание

1.Вычислить n-годичную ипотечную ссуду для покупки квартиры за P грн. с годовой ставкой i% и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат.

Вариант	n	P	i	A	Вариант	n	P	i	A
1	7	170000	5	10	18	7	280000	12	18
2	8	200000	6	10	19	10	250000	9	30
3	9	220000	7	20	20	12	350000	12	20
4	10	300000	8	20	21	10	420000	13	30
5	11	350000	9	15	22	11	460000	14	25
6	7	210000	10	15	23	12	500000	12	30
7	8	250000	11	30	24	10	330000	12	15
8	9	310000	12	30	25	15	400000	6	30
9	10	320000	13	25	26	20	480000	10	24
10	11	360000	14	25	27	14	380000	11	20
11	4	270000	7	15	28	15	360000	10	30
12	12	450000	11	18	29	12	280000	12	28
13	18	250000	9	12	30	15	500000	10	25
14	10	200000	8	15	31	8	250000	11	30
15	15	420000	11	20	32	7	210000	10	15
16	14	300000	8	18	33	11	360000	14	25
17	15	450000	10	15	34	18	250000	9	12

2.Вас просят дать в долг Р грн. и обещают вернуть P1 грн. через год, P2 грн. –

через два года и т.д., наконец, Pn грн. – через n лет. При какой годовой процентной ставке эта сделка имеет смысл? Для вычислений воспользоваться функциями ЧПС и ВСД.

Вариант	n	P	P1	P2	P3	P4	P5
1	3	17000	5000	7000	8000

47

Вариант	n	P	P1	P2	P3	P4	P5
2	4	20000	6000	6000	9000	7000
3	5	22000	5000	8000	8000	7000	5000
4	3	30000	5000	10000	18000
5	4	35000	5000	9000	10000	18000
6	5	21000	4000	5000	8000	10000	11000
7	3	25000	8000	9000	10000
8	4	31000	9000	10000	10000	15000
9	5	32000	8000	10000	10000	10000	11000
10	3	36000	10000	15000	21000
11	3	27000	10000	17000	8000
12	3	20000	6000	10000	10000
13	5	32000	5000	10000	14000	10000	10000
14	4	30000	6000	8000	10000	10000
15	5	35000	5000	10000	15000	12000	8000
16	3	20000	6000	10000	11000
17	4	36000	5000	10000	12000	18000
18	5	40000	1000	16000	15000	12000	10000
19	3	25000	12000	10000	8000
20	4	30000	9000	8000	12000	14000
21	5	35000	8000	8000	10000	12000	10000
22	3	26000	10000	12000	10000
23	4	20000	5000	8000	10000	5000
24	3	22000	7000	10000	10000
25	4	24000	6000	6000	9000	10000
26	5	25000	6000	10000	11000	5000	3000
27	4	36000	10000	12000	15000	10000
28	5	40000	10000	15000	10000	10000	5000
29	3	24000	8000	12000	10000
30	4	32000	10000	10000	15000	5000
31	4	35000	5000	9000	10000	18000
32	5	22000	5000	8000	8000	7000	5000
33	3	20000	6000	10000	11000
34	4	30000	6000	8000	10000	10000

48

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

Финансовый анализ в MS EXCEL

I.Функция ПРПЛТ

Функция ПЛТ возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки. Синтаксис:

ПРПЛТ(ставка; период; кпер; нз; бз; тип)

Аргументы функции ПРПЛТ:

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

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100000 грн. на срок 5 лет при годовой ставке 2% (Рис.21).

49

Рис.21 Вычисление основных платежей и платы по процентам

Ежегодная плата вычисляется в ячейке ВЗ по формуле:

=ПЛТ(процент; срок; -размер_ссуды),

где ячейки Bl, В2 и В4 имеют имена: процент, срок и размер_ссуды,

соответственно. Присвоение имени ячейке осуществляется с помощью команды Вставка/Имя/Присвоить. Плату по процентам можно получить с помощью функции ПРПЛТ, используя формулу:

=ПРПЛТ(Процент;A7;Срок;-Размер_ссуды)

Основная плата в ячейке С7 вычисляется по формуле:

=ежегодная_плата-В7,

где ежегодная_плата – имя ячейки ВЗ. Остаток долга в ячейке D7 вычисляется по формуле:

=D6-C7

Ячейки C8:D11 заполняются с помощью маркера заполнения после выделения диапазона D7:D7.

II.Финансовая функция БС

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

Синтаксис:

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

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

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

Приведем пример использования функции БС. Предположим, вы хотите резервировать деньги для специального проекта, который будет осуществлен через год. Предположим, вы собираетесь вложить 1000 грн. при годовой ставке 6%. Вы собираетесь вкладывать по 100 грн. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев?

Рис.22 Пример использования функции БЗ

С помощью формулы (Рис.22):

=БС(B2/12;B3;-B4;-B1;1)

получаем ответ: 2301,4 грн.

III.Финансовая функция КПЕР

Функция КПЕР вычисляет общее количество периодов выплаты данного вклада на основе периодических постоянных выплат и постоянной процентной ставки. Синтаксис:

КПЕР(ставка; выплата; нз; бз; тип)

Аргументы:

ставка Процентная ставка за период выплата Величина постоянных периодических платежей

нз Текущее значение, т. е. общая сумма, которую составят будущие платежи