Финансовые расчеты в EXCEL

В Excel существует возможность  выполнять операции с датами. В  частности, даты можно автоматически  вычитать. Давайте применим эту возможность  в нашем модуле. Чтобы более точно определить период, добавим в модель две ячейки: дату размещения денежных средств на счету и дату закрытия депозита, а в ячейку В4, определяющую срок размещения, введем формулу  
=(Е2-Е1)/365

В этой формуле  одна дата вычитается из другой, а полученный результат делится на количество дней в году.

Формат ячеек, куда вводятся даты начала и конца  операции (ячейки В1 и В2 на рис. 3.26), необходимо привести в соответствие со своими требованиями. Для этого следует  вызвать диалоговое окно Формат ячеек, отобразить вкладку Число (рис. 3.27) и отметить в списке Числовые форматы элемент Дата. В списке Тип можно выбрать устраивающий вас формат.

Обратите  внимание, что даты в ячейках В1 и В2 таблицы на рис. 3.26 (с формулами) представлены в виде чисел. Это объясняется тем, что в Excel расчет дат ведется в днях, начиная от 1900 года. Такие числа показывают, сколько дней прошло от 01.01.1900 года до указанной даты.

Результат, полученный при вычитании дат (ячейка В6), преобразуем  в числовой формат. В противном случае, вычитая одну дату из другой, можно получить результат в формате дат (например, 05.00.00), который может быть истолкован не правильно.

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

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

Рис. 3.27. Диалоговое окно Формат ячеек, вкладка Число, числовой формат Дата

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

Предположим, что на конец каждого операционного  дня у вас на расчетном счете  присутствуют остатки денежных средств. Банк начисляет на них проценты из расчета 3% годовых. Датой поступления  процентов на расчетный счет является 26 число каждого месяца. Необходимо произвести расчет суммы начисленных процентов. Для этого составим таблицу, в которую внесем даты и суммы остатков на расчетном счете на конец каждого операционного дня. Предположим, это период с 27.09.2001 по 26.10.2001.

Столбец В  отведем для дат, столбец С - для остатков на расчетном счете, а столбец D - для определения суммы ежедневно начисляемого процента. Если ввести в ячейку ВЗ начальную дату, то даты в области В4:В32 можно будет разместить методом прогрессии. Однако лучше применить метод заполнения ячеек одинаковыми формулами и ввести в ячейку В4 формулу  
=ВЗ + 1

Изменение даты в ячейке ВЗ повлечет за собой соответствующее  изменение дат в области В4:В32. Дата в каждой расположенной ниже ячейке будет на день больше даты в  ячейке, находящейся выше. Это позволит нам в следующем месяце, изменив дату в ячейке ВЗ, изменить весь временной интервал. В ячейку С4 введите формулу =С3

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

Формула для  определения суммы процента довольно проста:  
=C4*$D$1/365

В данном случае сумма денежных средств на расчетном  счете на каждую конкретную дату (ячейка С4) умножается на процент банка (абсолютная ссылка на ячейку D1) и делится на количество дней в году (365).

Используемые  при этом расчетные формулы и  полученные числовые результаты приведены  на рис. 3.28 и 3.29.

Рис. 3.28. Таблица  для расчета суммы процентов  на остаток на расчетном счете  в банке (с формулами)

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

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

Рассмотрим еще  один пример. Предположим, что вы положили деньги на депозит под сложный  процент. Период капитализации - 1 день (то есть каждый день начисляется процент на основную сумму и на начисленные за предыдущее время проценты). Просчитаем, как будет расти сумма на депозите. Добавьте к предыдущей таблице одну строку для ввода первоначальной суммы депозита и немного измените формулу из таблицы, приведенной на рис. 3.28. В результате в ячейке В5 таблицы должна появиться следующая формула (рис. 3.30):  
=В4*(1+$В$2)/\(А5-А4)

Как видите, сумма на депозите (ячейка В4) умножается на ставку ежедневного сложного процента  
(1+$В$2)

предварительно  возведенную в степень, которая  равна количеству дней между двумя  датами:  
А5-А4

Формулы и  числовые примеры для этого модуля приведены на рис. 3.30 и 3.31.

Рис. 3.30. Таблица  для расчета сложного процента на сумму вклада при периоде капитализации 1 день (с формулами)

Рис. 3.31. Таблица  для расчета сложного процента на сумму вклада при периоде капитализации 1 день (с числовым примером) 

Модуль расчета  сложного процента на сумму вклада

Зачем нам нужна  такая большая таблица для  расчета суммы начисленного процента? Ведь если деньги будут положены в банк на год, то для отображения результатов нам потребуется 365 строк. Можно создать небольшой отдельный модуль занимающий диапазон ячеек А1:В5 (рис. 3.32 и 3.33), который выполняет те же функции, что и таблица, представленная на рис. 3.3!. Изменяя дату в ячейке В2, вы сможете видеть результат в ячейке В5, где находится следующая формула:  
=ВЗ*(1+В4)/\(В2-В1)

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

Рис. 3.32. Модуль для расчета сложного процента на сумму вклада при периоде капитализации 1 день (с формулами) 

Рис. 3.33. Модуль для расчета сложного процента на сумму вклада при периоде капитализации 1 день (с числовыми данными) 

Резюме

На практике часто  приходится сталкиваться с задачами, для решения которых требуется  выполнить однотипные расчеты для  разных наборов входных данных. Простейший пример - составление таблицы умножения. При решении таких задач оперируют наборами однородных входных данных и одинаковыми математическими формулами. Для быстрого выполнения подобных расчетов средствами Excel необходимо уметь создавать наборы данных и формулы с абсолютными и смешанными ссылками. Другими словами, сначала вы должны быстро определить, при помощи какой формулы или прогрессии создать набор входных данных, и составить формулу для расчета, а затем назначить ссылки в формуле таким образом, чтобы ее можно было разместить в области вычислений путем копирования.

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

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

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