Практическое занятие № 28

Финансовые, текстовые, логические функции. Функции даты и времени

Цель занятия:

сформировать умения и навыки по работе с функциями

Примеры использования функций MS Excel в экономических расчётах

Задание 1

Постановка задачи

Таблица расчета процентов по вкладу

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

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

● первоначальную сумму вклада;

● начальное значение процентной ставки по депозиту и шаг ее изменения;

● начальное значение периода времени и шаг его изменения.

Процентные ставки будут располагаться в столбце Процент области вычислений, периоды времени — в строке, озаглавленной как Годы. В области вычислений должны отображаться суммы, величина которых зависит от срока размещения вклада и процентной ставки (рис. 3.1 и 3.2).

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

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

P1= P0*( 1+г)^{^}n,

где РО — сумма, размещенная на депозите, r — ставка по депозиту, п — число периодов (лет).

Рис. 3.1 - Фрагмент таблицы для расчета суммы на депозите (с формулами)

Рис.3.2 - Таблица для расчёта суммы на депозите (с числовыми значениями)

У вас не возникнет проблем с оформлением столбцов и строк, содержащих исходные данные для расчета. Вам требуется вставить две строки (после строк 2 и 8) и ввести имена и значения параметров в область ввода. А вот процесс создания основной расчетной формулы мы опишем более подробно. Вы­делите диапазон В10:К19 и введите в ячейку В10 формулу для расчета, выполнив следующие действия:

1. Введите знак равенства, выделите ячейку D2 и нажатиями функциональной клавиши [F4] задайте абсолютную ссылку.

2. Введите знак «*» (умножить), круглую открывающую скобку, цифру 1 и знак «+».

3. Выделите ячейку А10 и три раза нажмите функциональную клавишу [F4] (бу­дет создана абсолютная ссылка на имя столбца), затем введите круглую за­крывающую скобку.

4. Переключитесь на английский шрифт и введите знак возведения в степень «^{^}» путем нажатия комбинации клавиш [Shift+6],

5. Выделите ячейку В9 и дважды нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на номер строки). Затем введите круглую откры­вающую скобку.

6. Завершите ввод формулы нажатием комбинации клавиш [Ctrl+Enter].

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

Задание 2

Выбор формата представления для процентных ставок

Особое внимание следует уделить числовому форматированию ячеек. В частно­сти, ячейки с процентными ставками и ячейки, в которых задаются шаг изме­нения и начальное значение процентной ставки, отформатируйте как процент­ные. Насколько это важно, вы поймете из приведенного ниже примера.

ПРИМЕР

Бухгалтер одного из предприятий при расчете начислений в один из обязатель­ных фондов перепутал ставку 0,06% со ставкой 0,06 и в течение года перевыпол­нил план по данному сбору на 99 лет вперед. А по налогу на прибыль заработал пеню.

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

1. Выделите форматируемую область, нажмите правую кнопку мыши и выберите в контекстном меню команду Формат ячеек.

2. В диалоговом окне Формат ячеек перейдите на вкладку Число. В списке Чи­словые форматы выделите элемент Процентный (рис. 3.3), задайте необходи­мое число десятичных знаков (например, 2) и нажмите кнопку ОК.

Рис.3.3 - Диалоговое окно Формат ячеек, вкладка Число при выборе процентного формата

Таблица определения влияния инфляции на стоимость денег

Действительная стоимость денег зависит от инфляции и определяется по сле­дующей формуле:

P1= P0: (1+j)^n,

где j — процент инфляции за период (например, за год), п — число периодов.

Она подобна приведенной выше формуле определения суммы денег на депозит­ном счету. Ввод формулы в таблицу производится аналогичным образом. Отли­чие состоит лишь в том, что вместо знака умножения в данном случае применя­ется знак деления. Новую таблицу нетрудно создать на основе предыдущей. Для этого, выделив диапазон В10:К19, отредактируйте формулу и нажмите комбина­цию клавиш [Ctrl+Enter].

Рис. 3.4 – Таблица расчёта реальной стоимости денег с возможностью изменения суммы и процента инфляции

Таблица определения реальной стоимости денег

Давайте усложним нашу задачу, объединив две предыдущие формулы. Напом­ним, что первая формула необходима для расчета суммы денег на депозите, а вторая — для определения влияния инфляции на стоимость денег. В результате их объединения мы получим формулу для вычисления действительной стоимости денег:

FV = P0 x (1+r) ^n: (1+j) ^n

Для решения этой задачи требуется усложнить таблицу, представленную на рис. 3.3. В области ввода необходимо определить значения ячеек ЕЗ и Е5. Первая ячейка должна содержать начальное значение уровня инфляции, а вторая — шаг изме­нения инфляции.

В области вычислений следует добавить ячейки, в которые будут занесены про­центы инфляции. Но поскольку в таблице имеются объединенные ячейки, ни одним из описанных ранее приемов перемещения таблицы воспользоваться нельзя. При попытке их применения Excel выдаст сообщение об ошибке (рис. 3.5).

Рис. 3.5 - Окно с предупреждением о невозможности изменения части объединенных ячеек

Поэтому сначала выделите строки 7:19 и, вызвав диалоговое окно Формат ячеек, отмените опцию Объединение ячеек, а затем выполните следующее:

1.Выделите область А7:А19 и задайте команду Копировать.

2.Переместите табличный курсор в ячейку В7 и щелчком правой кнопки мыши вызовите контекстное меню.

3.Активизируйте в нем команду Добавить скопированные ячейки.

В диалоговом окне Вставка скопированных ячеек (рис. 3.6) отметьте переклю­чатель диапазон, со сдвигом вправо и нажмите кнопку ОК.

Рис. 3.6 - Диалоговое окно Вставка скопированных ячеек

После вставки скопированных ячеек отформатируйте таблицу, а затем, восполь­зовавшись методом заполнения ячеек одинаковой информацией, отредактируйте формулу в ячейках В11:В 19 следующим образом: =В10+$Е$5.

Теперь измените формулу, которая находится в области вычислений таблицы. Вы­делив диапазон C10:L19, щелкните мышью в строке формул (рис. 3.7) и в формуле

=$D$2*(1+$А10)^{^}С$9 скопируйте фрагмент (1+$А10)^{^}С$9.

Затем, нажав клавишу [End], переместите курсор в конец формулы, введите с клавиатуры знак деления «/» и вызовите команду Вставить. В появившемся фраг­менте формулы замените посредством клавиатуры адрес $А10 адресом $В10.

В результате у вас должна получиться такая формула:

=$D$2 *(1 + $А10)^{^}С$9/(1 + $В10)^{^}С$9.

Рис. 3.7 – Выделение фрагмента формулы в строке формул

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

Перемещать курсор по строке формул в режиме редактирования можно не только посредством мыши. Вы можете использовать клавиши, которые применяются для пе­ремещения по рабочему листу и выделения диапазонов ячеек. В частности, для вы­деления указанного фрагмента в строке формул достаточно поместить курсор в ко­нец этой строки и нажать комбинацию клавиш [Shift+<~].

Рис. 3.8 - Фрагмент таблицы для расчета реальной стоимости денежных средств на депозите (с формулами)

На рис. 3.8 изображен фрагмент таблицы с формулами для расчета реальной стоимости денежных средств, размещенных на депозите, с учетом инфляции, а на рис. 3.9 — та же таблица с числовыми значениями. В таблице, представленной на рис. 3.9, начальная ставка процента по депозиту равна 1%, шаг изменения процента по депозиту — 1% за период. Начальный процент инфляции — 3%, а шаг изменения процента инфляции — 0,5% за период.

Рис 3.9 - Таблица для расчета реальной стоимости денежных средств на депозите (с числовыми значениями)

Задание 3

Модуль расчета реальной стоимости денег

Все расчеты, которые производятся в таблице на рис. 3.9, можно выполнить при помощи небольшого модуля, размером пять на две ячейки (рис. 3.10 и 3.11). Задавая для него входные данные, вы получите те же результаты, что и в ранее созданной таблице. Этот модуль можно вставлять в более сложные таблицы.

Входные данные располагаются в ячейках модуля следующим образом: В1 — на­чальная сумма, размещенная на депозите; В2 — процентная ставка по депозиту; ВЗ — уровень инфляции; В4 — период, на который производится размещение денежных средств (в годах). Определение реальной стоимости денежных средств через указанный период выполняется в ячейке В5, называемой выходом модуля. Здесь содержится формула

=В1*(1+В2)^{^}В4/(1+ВЗ)^В4

Если вы будете применять вычисленное значение в последующих расчетах, его необходимо округлить, воспользовавшись функцией ОКРУГЛ. Для этого нужно за­дать формулу расчета денежных средств в качестве первого аргумента функции ОК­РУГЛ:

=ОКРУГЛ(В1*(1+В2)^{^}В4/(1+ВЗ)^{^}В4;2)

Рис. 3.10 – Модуль для расчёта реальной стоимости денежных средств на депозите (с формулами)

Рис. 3.11 – Модуль для расчёта реальной стоимости денежных средств на депозите (с числовыми значениями)