Тема: Условны функции, условное форматирование

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

Финансовые функции МS Ехсеl.

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

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

I. Условные функции и условное форматирование.

Задание №1

Создать таблицу «Календарь погоды» по предложенному образцу и продолжить заполнение таблицы до конца месяца. На основании данных выполнить следующие расчёты:

1. Вычислить среднемесячную температуру.

2. Подсчитать количество дней, когда шёл снег

=СЧЁТЕСЛИ(B5:AC5;"С")

3. Подсчитать количество дней, когда шёл дождь

4. Подсчитать количество дней, когда не было осадков.

Задание №3

1. В группе в конце семестра были проведены тесты по четырем предметам: экономике, математике, КИТ и русскому языку.

2. По результатам тестирования проводится отбор для подготовки к олимпиаде. Зачисляются те, у кого общий балл не ниже 30, а суммарный балл по КИТ и экономике больше 16. В Отметке о зачислении должна быть запись да или нет.

3. В ячейку F5 внести формулу: =ЕСЛИ(И(СУММ(В5:Е5)>=30; C5 + D5 >16);” да”; ” нет”).

4. С помощью условного форматирования красным цветом отметить учащихся, прошедших отбор. Для этого:

1) выделить столбец F;

2) в главном меню выбрать пункт Формат, затем команду Условноеформатирование;

3) В диалоговом окне Условное форматирование в группе Условие 1 установить параметры в соответствии с образцом:

В этом же окне нажать кнопку Формати установить цвет шрифта (красный).

Задание №3

1. Объявлен набор в школу моделей. Составлен список претендентов:

2. К претендентам предъявляются следующие требования:

для мужчин – рост не ниже 185 см, вес не более 75 кг;

для женщин – рост не ниже 175 см, вес не более 55 кг.

Набирается молодежь не старше 25 лет.

заполнить столбец Принят словами да, нет.

3. Выделить сиреневым цветом тех, кто принят в школу.

II. Финансовые функции используют для решения задач плани­рования финансовой деятельности, определения прибылей, анализа выгодности капиталовложений, кредитно-инвестиционной политики и т.п. Инвестицией называют вкладывание денег в некоторый бизнес на определенных условиях. Заем в банке называется кредитом, а взнос на банковский счет — депо­зитом. Поступление денег от бизнеса называют рентой.

Основные параметры финансовых функций и их сокра­щенные названия:

процентная ставка (ПС) выражается в процентах и может быть суточной, месячной, годовой и т.п.;

количество периодов (КП) продолжительностью сутки, месяц, год;

периодическая выплата (ПВ) — сумма, выплачиваемая клиентом на протяжении установленного периода (это отрицате­льное число), или сумма, получаемая клиентом на протяжении каждого периода (это положительное число);

сумма взноса (СВ) — сумма инвестиции, капиталовло­жения, начального взноса (это отрицательное число или ноль);

тип операции (Т) — число 0, если выплата осуществляется в конце каждого периода, и число 1, если в начале.

Различают кредитную и депозитную процентные ставки. Кредитная ставка выше депозитной. Процентная ставка должна быть согласована с продолжительностью периода, например, годовая ставка 60% равна месячной ставке 5%. В этой работе считается, что месячная депозитная ставка — 5%, а кредитная — 6%

1. Функция для определения будущей стоимости сегодняшних инвестиций. Функция имеет вид БС(ПС; КП; ПВ; СВ; Т).

Если параметр равен 0, его можно не указывать. Если пара­метр пропускают в середине списка параметров, то нужно за­писать два разделителя рядом (в этом случае ;).

Задание №1

Инвестор вкладывает в бизнес 2 000 руб (или открывает на эту сумму счет в банке) на условиях 5% ставки прибыли ежемесячно. Какая стоимость инвестиции через 36 месяцев?

Решение задачи дает такая формула:

=БС(5%; 36;;-2000)

Ответ: 11 583,63 руб.

Задание № 2.

Клиент открывает счет в банке на условиях 5% ставки прибыли ежемесячно, кладет на счет 2 000 руб и планирует в начале каждого месяца забирать со счета 100 руб. Какая сумма будет на счету через 36 месяцев?

Решение: =БС(5%; 36; 100; -2000; 1)

Ответ: 1 520,82 руб.

Задание №3

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

=БС(5%; 36; -100; -2000; 1)

Ответ: 21 646,45 руб.

2. Функции для определения выплат по погашению займа.

Введем новые термины и их сокращенные названия:

· номер периода (НП);

· сумма займа (СЗ);

· конечное значение займа (КЗ).

Функция ПЛТпредназначена для определения суммы периодических выплат погашения долга и имеет вид ПЛТ(ПС; КП; СЗ; КЗ; Т).Такая выплата состоит из двух частей, вычисляемых с помощью двух функций ПРОЦПЛАТ и ОСПЛТ, а именно:

а) выплата по процентам ПРОЦПЛАТ(ПС; НП; КП; СЗ; КЗ; Т);

б) основная выплата ОСПЛТ(ПС; НП; КП; СЗ; КЗ; Т).

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

Задание №4

Бизнесмен взял в банке кредит на сумму 2 000 руб сроком на 12 месяцев при условии ежемесячного погашения займа и месячной ставки кредита 6%. Определить величину ежемесячных выплат и ее составные в конце первого месяца.

= ПЛТ(6% ; 12; 2000). Ответ: -238,55 руб.

= ПРОЦПЛАТ(6%; 1; 12; 2000). Ответ: -110,00 руб.

= ОСПЛТ6%; 1; 12; 2000). Ответ: -118,55 руб.

Рассмотрим функцию КПЕР,вычисляющую количество периодов для погашения суммы займа, предоставленной под некоторую процентную ставку при условии заранее заданной суммы периодических выплат: КПЕР(ПС; ПВ; СЗ; КЗ; Т).

Задание №5 (задача 6)

Заем 2 000 руб берут при условии возвращения в конце каждого месяца 200 руб и процентной ставки 6%. Сколько месяцев нужно для возвращения займа?

=КПЕР(6%; -200; 2000). Ответ: 15,73 месяца.

Функция СТАВКА(КП; ПВ; СЗ; КЗ; Т; начальное приближе­ние)определяет выгодность предоставления займа, т.е. реальную процентную ставку от предоставления займа на определенную сумму при условии фиксированных периодических выплат на протяжении нескольких периодов. Здесь нужно задать некоторое начальное приближение к искомой процентной ставке, например 0,1 (10%).

Задание №6 (задача 7)

Бизнесмен обращается в банк за займом (кредитом) на сумму 2 000 руб на 12 месяцев при условии пе­риодических выплат 200 руб в конце каждого месяца на про­тяжении года. Определить процентную ставку займа. =СТАВКА(12; -200; 2000; 0; 0; 0,1)

Ответ: 3%. Такой кредит для банка невыгодный, если ме­сячная депозитная процентная ставка, например, 5% . Банк кре­дит не даст.