Практическая работа № 5.
Тема: MS Excel. Использование основных финансовых и текстовых функций, функций даты и времени.
Цель: Научиться выполнять вычисления с использованием функций.
Время: 60 мин.
Задание: Изучите основные функции Excel по заданию, описанному ниже.
Литература:
Последовательность выполнения работы:
I. Использование основных финансовых функций
1. Включите компьютер, войдите в систему и запустите табличный процессор Microsoft Excel.
2. Присвойте первому листу имя «Фин. ф-ции», сразу сохраните рабочую книгу в папке «Мои документы» под именем «пр10» (пр10.xls).
3. С помощью финансовых функций Excel решите задачу:
Клиент «ПУМБ» открыл депозит «Накопительный» сроком на 18 месяцев, сделав первоначальный вклад 5000 грн. В конце каждого месяца он пополняет депозит на 1000 грн. Банк начисляет ежемесячно сложные проценты по номинальной ставке 14,3% годовых. Какая сумма накопится на счёте к концу срока при сохранении на это время всех указанных условий без изменения? Выгоднее ли будет вклачику, если при том же пополнении банк будет начислять простые проценты по ставке 15,3%?
Результаты расчётов отразите в отчёте.
- Для решения этой задачи воспользуемся функцией БС (возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки):
Синтаксис: БС(ставка,кпер,плт,[пс],[тип])
Функция БС имеет аргументы, указанные ниже:
Ставка — обязательный аргумент. Процентная ставка за период.
Кпер — обязательный аргумент. Общее количество периодов платежей по аннуитету.
Плт — обязательный аргумент. Выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент "плт" состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент "пс" является обязательным.
Пс — обязательный аргумент. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент "пс" опущен, предполагается значение 0. В этом случае аргумент "плт" является обязательным.
Тип — необязательный аргумент. Число 0 или 1, обозначающее срок выплаты. Если аргумент "тип" опущен, предполагается значение 0.
| Тип | Выплата |
| В конце периода | |
| В начале периода |
| Данные | Описание |
| 14,3% | Годовая процентная ставка (ставка*12) |
| Количество платежей (кпер) | |
| -1000 | Объём платежей (плт) |
| =-(5000+5000*14,3%/12) | Стоимость на текущий момент (пс) |
| Платежи осуществляются в начале периода (тип) | |
| Формула | Описание (результат) |
| =БС(A2/12; A3; A4; A5; A6) | Будущая стоимость инвестиции в соответствии с приведенными выше условиями |
- Скопируйте эту таблицу, поясняющую, как применять функцию БС, на лист рабочей книги Excel. Для правильной демонстрации примера необходимо вставить его в ячейку A1 листа.
- !!! В Excel переключение между режимами «формулы» - «значения» осуществляется сочетанием клавиш CTRL+` (тупое ударение) (CTRL + ~ или CTRL + Ё) или на вкладке Формулы в группе Проверить формулы нажмите кнопку Показывать формулы. Проверьте правильность скопированной формулы, убедитесь, что аргументами функции являются правильные адреса ячеек.
- Обратите внимание, что «текущий момент» начинается не тогда, когда вкладчик положил деньги в банк, а тогда, когда он начал делать регулярные вложения (к тому времени уже «набежали» проценты на первоначальный взнос) и количество платежей за полтора года будет равно 17-и!
- Для того, чтобы лучше понять механизм действия формулы, составьте таблицу, поясняющую начисление сложных процентов и рост вклада:
 | |||||
 | |||||
 | |||||
| Месяц | Вклад (пополнения) | Остаток по вкладу | процент (за месяц) |
| 59,58333 | |||
| 6059,58333 | 72,21003 | ||
| 7131,79337 | 84,9872 | ||
| 8216,78057 | 97,91664 | ||
| 9314,69721 | 111,0001 | ||
| 10425,6973 | 124,2396 | ||
| 11549,9369 | 137,6367 | ||
| 12687,5737 | 151,1936 | ||
| 13838,7672 | 164,912 | ||
| 15003,6792 | 178,7938 | ||
| 16182,4731 | 192,8411 | ||
| 17375,3142 | 207,0558 | ||
| 18582,37 | 221,4399 | ||
| 19803,8099 | 235,9954 | ||
| 21039,8053 | 250,7243 | ||
| 22290,5297 | 265,6288 | ||
| 23556,1585 | 280,7109 | ||
| 24836,8694 | 295,9727 | ||
| 25132,8421 |
В третьем и четвёртом столбце должны быть формулы – введите их самостоятельно (для уровня «Высокий», т.е. на 10 -12 баллов). Когда введёте формулы для третьего месяца, выделите эти 2 ячейки и с помощью маркера автозаполнения протягиванием скопируйте их в оставшиеся ячейки до конца таблицы.
Это другой вариант представления механизма сложных процентов с пополнением вклада.
- Для сравнения ставок воспользуемся функцией ЭФФЕКТ():
| Данные | Описание |
| 0,143 | Номинальная годовая процентная ставка |
| Количество периодов в году, за которые начисляются сложные проценты | |
| Формула | Описание (результат) |
| =ЭФФЕКТ(A2;A3)*100% | Фактическая процентная ставка в соответствии с приведенными выше условиями |
4. Какую сумму родители должны каждый месяц, начиная от рождения ребёнка, перечислять на счёт в банке, чтобы к 18-летию ребёнка накопить 100000 гривен? Годовая процентная ставка – 13%.
- Используйте функцию ПЛТ:
| Данные | Описание |
| 0,13 | Годовая процентная ставка |
| Предполагаемое число лет хранения сбережений | |
| Требуемый объем сбережений через 18 лет | |
| Формула | Описание (результат) |
| =-ПЛТ(A2/12; A3*12; 0; A4) | Необходимая сумма месячного платежа для получения 100000 в конце восемнадцатилетнего периода |
Знак «-» указывает на то, что банк отдаёт эту сумму, а не получает