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

ПРИМЕЧАНИЕ

Адресом объединенной ячейки станет адрес самой  левой из всех объединяемых.

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

Изменение начальных  параметров и шага расчета

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

Рис. 3.12. Таблица  умножения (с формулами; изменяются начальное значение и его шаг)

Введите в ячейку А8, которая является начальной ячейкой  ряда сомножителей 1, формулу  
=D2

Она задает ссылку на ячейку D2 из области ввода. В ячейку В7, которая начинает горизонтальный ряд сомножителей 2, введите формулу  
=D3

Теперь нам  необходимо отредактировать формулы  в диапазоне А9:А17 таким образом, чтобы они ссылались на ячейку области ввода, которая задает шаг  изменения сомножителя. Воспользуемся  для этого методом ввода значений в несколько ячеек одновременно:  
1. Выделите диапазон ячеек А9:А17 и для перехода в режим редактирования содержимого ячейки нажмите функциональную клавишу [F2]. В результате курсор окажется в конце формулы, находящейся в ячейке А9.  
2. Нажатием клавиши [Backspace] удалите цифру 1 в конце формулы.  
3. Выделите мышью ячейку D4 и, нажав функциональную клавишу [F4], задайте абсолютную ссылку.  
4. Нажмите комбинацию клавиш [Ctrl+Enter].

Аналогичные операции выполните для ячеек  диапазона С7:К7, которые принадлежат  ряду сомножителя 2. Однако не забудьте, что в них надо создать абсолютную ссылку на ячейку D5.

Для диапазонов ячеек, в которых находятся числа, задайте формат Числовые, вызвав диалоговое окно Формат ячеек и активизировав вкладку Число. Результаты представлены на рис. 3.12 и 3.13.

 

Рис. 3.13. Таблица  умножения (с результатами) 

Практическое применение таблицы 

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

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

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

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

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

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

Сумма вклада на конец периода рассчитывается по такой формуле:  
Р1= Р0*(1+r)/\n

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

 

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

 

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

Нам кажется, что у  вас не возникнет проблем с  оформлением столбцов и строк, содержащих исходные данные для расчета. Вам требуется вставить две строки (после строк 2 и 8) и ввести имена и значения параметров в область ввода. А вот процесс создания основной расчетной формулы мы опишем более подробно. Выделите диапазон В10:К19 и введите в ячейку В10 формулу для расчета, выполнив следующие действия:  
1. Введите знак равенства, выделите ячейку D2 и нажатиями функциональной клавиши [F4] задайте абсолютную ссылку.  
2. Введите знак "*" (умножить), круглую открывающую скобку, цифру 1 и знак "+".  
3. Выделите ячейку А10 и три раза нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на имя столбца), затем введите круглую закрывающую скобку.  
4. Переключитесь на английский шрифт и введите знак возведения в степень "/\" путем нажатия комбинации клавиш [Shift+6].  
5. Выделите ячейку В9 и дважды нажмите функциональную клавишу [F4] (будет создана абсолютная ссылка на номер строки). Затем введите круглую открывающую скобку.  
6. Завершите ввод формулы нажатием комбинации клавиш [Ctrl+Enter].

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

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

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

ПРИМЕР

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

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

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

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

Действительная стоимость  денег зависит от инфляции и определяется по следующей формуле:  
Р1= Р0 : (1+J)/\n

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

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

 

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

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

Давайте усложним нашу задачу, объединив две предыдущие формулы. Напомним, что первая формула необходима для расчета суммы денег на депозите, а вторая - для определения влияния инфляции на стоимость денег. В результате их объединения мы получим формулу для вычисления действительной стоимости денег:  
FV = Р0 х (1+r)/\n:(1+j)/\n

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

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

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

Поэтому сначала выделите строки 7:19 и, вызвав диалоговое окно Формат ячеек, отмените опцию Объединение ячеек, а затем выполните следующее:  
1. Выделите область А7:А19 и задайте команду Копировать.  
2. Переместите табличный курсор в ячейку В7 и щелчком правой кнопки мыши вызовите контекстное меню.  
3. Активизируйте в нем команду Добавить скопированные ячейки.  
4. В диалоговом окне Вставка скопированных ячеек (рис. 3.19) отметьте переключатель диапазон, со сдвигом вправо и нажмите кнопку ОК.

 

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

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

Теперь измените формулу, которая находится в  области вычислений таблицы. Выделив  диапазон C10:L19, щелкните мышью в строке формул (рис. 3.20) и в формуле  
=$D$2*(1+$А10)/\С$9

скопируйте  фрагмент  
(1+$A10)/\С$9

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

В результате у вас должна получиться такая  формула:  
=$D$2* (1 + $A10)/\C$9/(1 + $B10)/\C$9

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

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

ПРИМЕЧАНИЕ

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

 

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

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

 

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

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

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

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

Если вы будете применять вычисленное значение в последующих расчетах, его необходимо округлить, воспользовавшись функцией ОКРУГЛ. Для этого нужно задать формулу расчета денежных средств в качестве первого аргумента функции ОКРУГЛ:  
=ОКРУГЛ(В1*(1+В2)/\В4/(1+ВЗ)/\В4;2)

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

 

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

Ввод дат  в модуль