Лабораторная работа № 7. Финансовые функции Excel – 1

Цель работы: ознакомление с финансовыми функциями расчета амортизационных отчислений, рентных платежей и денежных потоков. (4 часа.)

Финансовые функции являются решениями какой-то частной финансовой задачи. Функция является частью формулы. Как и формула, функция начинается знаком равенства и имеет следующий вид:

= ИМЯФУНКЦИИ(Аргументы).

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

Функции для расчета амортизационных отчислений

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

Приобретенное имущество амортизируется на протяжении определенного периода, который называется сроком амортизации. В программе он должен быть указан в аргументе, для обозначения которого используются названия Время_эксплуатации, Жизнь, Время_амортизации. Стоимость в конце срока амортизации – это остаточная (ликвидационная) стоимость. Хотя эта стоимость и не является ликвидной, ее следует указывать в аргументе Ликвидная_стоимость, Остаточная_стоимость, Ост_стоимость. Расчет амортизационных отчислений производится для определенного периода, задаваемого аргументом Период.

Рассмотрим пример составления плана амортизации с использованием функций АМР и ДОБ. Более подробно финансовые функции рассмотрены в учебном пособии [1].

Аргументы функций имеют следующий смысл:

Стоимость – начальная стоимость имущества.

Остаточная_стоимость – остаточная стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества).

Время_эксплуатации – количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).

Остаток – остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества).

Нач_стоимость – начальная стоимость имущества.

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

Месяц – количество месяцев в первом году. Если аргумент месяц опущен, то предполагается, что он равен 12.

АМР

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

Синтаксис:

АМР(стоимость; остаток; период)

Пример.

Предположим Вы купили за 30 000 руб. грузовик, который имеет срок эксплуатации 10 лет, после чего оценивается в 7 500 руб. Снижение стоимости для каждого года эксплуатации составит:

АМР(30000; 7500; 10) равняется 2 250 руб.

ДОБ

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

Синтаксис:

ДОБ(нач_стоимость; ост_стоимость; время_эксплуатации; период; месяц)

Замечания.

Метод постоянного учета амортизации вычисляет амортизацию, используя фиксированную процентную ставку. ДОБ использует следующие формулы для вычисления амортизации за период:

(нач_стоимость – суммарная амортизация за предшествующие периоды) * ставка

где: ставка = 1 – ((ост_стоимость/нач_стоимость) ^ (1 / время_эксплуатации)), округленное до трех десятичных знаков после запятой.

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

нач_стоимость * ставка * месяц / 12.

Для последнего периода ДОБ использует такую формулу:

((нач_стоимость - суммарная амортизация за предшествующие периоды) * ставка * (12 - месяц)) / 12.

Примеры.

Предположим, что предприятие приобрело новое оборудование. Оборудование стоит 1000000 руб. и имеет срок эксплуатации шесть лет. Остаточная стоимость оборудования 100000 руб. Следующие примеры показывают величину амортизации за время эксплуатации оборудования. Результаты округлены до целых.

ДОБ(1000000;100000;6;1;7) равняется 186 083 руб.

ДОБ(1000000;100000;6;2;7) равняется 259 639 руб.

ДОБ(1000000;100000;6;3;7) равняется 176 814 руб.

ДОБ(1000000;100000;6;4;7) равняется 120 411 руб.

ДОБ(1000000;100000;6;5;7) равняется 82 000 руб.

ДОБ(1000000;100000;6;6;7) равняется 55 842 руб.

ДОБ(1000000;100000;6;7;7) равняется 15 845 руб.

Допустим, что предприятие приобрело оборудование стоимостью 120000 руб. Срок амортизации оборудования 15 лет, после чего его ликвидационная стоимость будет составлять 1000 руб.

Введите в верхней части рабочего листа название таблицы – Начисление амортизации – и следующие текстовые метки строк в ячейки A3 – A6:

Начальная стоимость

Срок амортизации

Максимальная норма списания

Остаточная стоимость после.

На следующем этапе уменьшите ширину столбца B до 2,00, после чего задайте в ячейке B6 формулу:

=$D$4

и в заключение введите в ячейке C6 слово лет. Вышеописанная операция поз-

Рис. 7.1

воляет «автоматизировать» вставку значения количества лет после указания цифры в ячейке D4. Максимальная норма списания для оборудования, срок амортизации которого составляет 15 лет, равняется 20%. Поэтому укажите в ячейке D5 значение 0,2 и сформатируйте ячейку процентным стилем. Присвойте рабочему листу имя Данные. После ввода данных задачи получаем таблицу, представленную на рис. 7.1. Далее займемся расчетом амортизационных отчислений с помощью метода линейного списания и геометрически-дегрес- сивного метода, а также попробуем определить оптимальный план начисления износа.

Второму рабочему листу присвоим имя Расчет. В столбце B будет находится год, для которого следует определить амортизацию, поэтому в ячейке B5 укажите слово Год, в первых двух ячейках введите значения 1 и 2 для двух первых лет, выделите обе ячейки, поместите курсор мыши на маркере заполнения и используйте функцию автозаполнения для ввода остальных значений (до 15). В ячейках D5, F5, H5, J5 введите текстовые метки столбцов:

Балансовая стоимость

Линейное списание

Дегрессивный метод

Износ

Столбцы C, E, G и I будут служить своеобразным «декоративным» оформлением, для этого уменьшите их ширину до 1,43, ориентируясь по содержимому поля в левой части строки формул.

Перейдем к заданию балансовой стоимости оборудования. Значение в ячейке D6 соответствует первоначальной стоимости оборудования, поэтому в этой ячейке можно указать формулу:

=Данные!$D$3.

Тем самым значение из ячейки D3 рабочего листа Данные будет представлено в ячейке D6. В дальнейшем, при изменении исходных данных примера, необходимые изменения следует выполнять только в листе Данные.

Далее, поместим указатель ячейки на ячейку F6 и активизируем Мастера функций (команда Функция меню Вставка или Мастер функций панели инструментов Стандартная). Выберете функцию АМР и нажмите кнопку Далее для перехода в диалоговое окно задания аргументов.

Рис. 7.2

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

=АМР(D6;Данные!$D$6;Данные!$D$4-B6+1).

В качестве первого аргумента следует указать значение для соответствующего года из столбца Балансовая стоимость, остаточная стоимость будет взята из листа Данные. Срок амортизации оборудования, уменьшающийся каждый раз на один год по мере начисления износа, следует записать в виде Данные!$D$4-B6+1.

Теперь в ячейке H6 следует указать формулу для расчета амортизации с помощью геометрически-дегрессивного метода, при этом необходимо, чтобы программа одновременно проверяла, не превышает ли найденное значение 20% балансовой стоимости. Поэтому в ячейке H6 поместим формулу:

=ЕСЛИ(ДОБ(D6;Данные!$D$6;Данные!$D$4-B6+1;1)/D6 < Данные! $D$5;ДОБ(D6;Данные!$D$6;Данные!$D$4-B6+1;1);D6*Данные! $D$5).

В логической функции ЕСЛИ необходимо проверить на истинность выражение: превышает ли результат вычисления функции ДОБ 20% балансовой стоимости или нет. Для образования вложенной функции следует нажать кнопку с литерой f_x рядом с полем ввода аргумента. Если выражение истинно, то в ячейке должен быть представлен результат вычисления функции ДОБ. В противном случае следует указать максимально возможное значение, которое равняется произведению балансовой стоимости и максимальной нормы списания. Первые три аргумента соответствуют трем аргументам функции АМР.

Таким образом, функции для расчета амортизации с помощью метода линейного списания и геометрически-дегрессивного метода введены. Теперь следует в столбце Износ представить большую величину амортизации. Для этого следует воспользоваться логической функцией ЕСЛИ и вставить ее в ячейку J6 в виде:

=ЕСЛИ(F6>H6;F6;H6).

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

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

=D6-J6.

Итак, ввод необходимых для вычислений формул завершен. Осталось только с помощью функции автоматического заполнения скопировать формулы в расположенные ниже ячейки. Для этого помечаем ячейки D7 – J7, помещаем мышь в квадратик копирования и протягиваем мышь до 20-ой строки. В ячейке D21 укажем остаточную стоимость оборудования:

=Данные!$D$6.

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

В результате всех этих действий получим таблицу в рабочем листе Расчет, приведенную на рис. 7.2. Как видно из таблицы, переход на линейное списание происходит после одиннадцати лет начисления износа с помощью геометрически-дегрессивного метода.

Представим процесс начисления амортизации графически.

Рис. 7.3

Для этого выделим ячейки B5 – B20, F5 – F20, H5 – H20, J5 – J20 и активизируем Мастер диаграмм посредством выбора команды Диаграмма/На новом листе меню Вставка. Программа автоматически вставит в рабочую книгу новый лист под названием Диаграмма 1 (лист диаграмм), а на экране появится первое диалоговое окно Мастера диаграмм. Подтвердите предлагаемый программой выбор ячеек содержащих данные для построения диаграммы (это ячейки, которые были выбраны нами), нажатием кнопки Далее.

Во втором диалоговом окне Мастера диаграмм выберете тип диаграммы График и нажмите кнопку Далее.

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

В последнем диалоговом окне введите название диаграммы – Расчет амортизации, а также названия по оси категорий (X) – Год, по оси (Y) – Величина амортизации. Нажатием кнопки Готово завершается процесс построения диаграммы (рис. 7.3).

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

Аргументы ниже приведенных функций имеют следующий смысл.

Бз – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Если бз опущено, оно полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если Вы хотите накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Вы можете сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.

Значение1, значение2, ... – от 1 до 29 аргументов, представляющих расходы и доходы. Значение1, значение2, ... должны быть равномерно распределены по времени и осуществляться в конце каждого периода.

Кпер – общее число периодов выплат годовой ренты. Например, если Вы получили ссуду на 4 года под автомобиль и делаете ежемесячные платежи, то Ваша ссуда имеет 4*12 (или 48) периодов. Вы должны ввести число 48 в формулу в качестве значения аргумента кпер.

Нз – текущая стоимость, или общая сумма всех будущих платежей с настоящего момента. Если аргумент нз опущен, то он полагается равным 0.

Ставка – процентная ставка за период. Например, если Вы получили ссуду под автомобиль под 10% годовых и делаете ежемесячные выплаты, то процентная ставка за месяц составит 10%/12, или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

Тип – число 0 или 1, обозначающее, когда должна производится выплата. Если аргумент тип опущен, то он полагается равным 0.

Тип Когда нужно платить

0 В конце периода

1 В начале периода

НПЗ

Возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации. Чистый текущий объем вклада – это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения).

Синтаксис:

НПЗ(ставка; значение1; значение2; ...)

НПЗ использует порядок аргументов значение1, значение2, ... для определения порядка поступлений и платежей. Убедитесь в том, что Ваши платежи и поступления введены в правильном порядке.

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

Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, тексты или значения ошибок в массиве или ссылке игнорируются.

Замечания.

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

Если n – это количество денежных взносов в списке значений, то формула для функции НПЗ имеет вид:

НПЗ =.

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

Примеры.

Рассмотрим инвестицию, при которой Вы выплачиваете 10 000 рублей через год после сегодняшнего дня и получаете годовые доходы 3 000 руб., 4200 руб., 6 800 руб. в последующие три года. Предположим, что учетная ставка составляет 10 процентов, в таком случае чистый текущий объем инвестиции составит:

НПЗ(10%; –10000; 3000; 4200; 6800) равняется 1188,44 руб.

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

Рассмотрим инвестиции, которые начинаются в начале первого периода. Допустим, Вы интересуетесь покупкой обувного магазина. Стоимость предприятия – 40 000 руб. и Вы ожидаете получить следующие доходы за первые пять лет: 8 000 руб., 9 200 руб., 10 000 руб., 12 000 руб. и 14 500 руб. Годовая учетная ставка равна 8%. Она может представлять степень инфляции или учетную ставку конкурирующих инвестиций.

Если стоимость и доходы от обувного магазина введены в ячейки от B1 до B6 соответственно, то чистый текущий объем инвестиции в обувной магазин составит:

НПЗ(8%; B2:B6)+B1, что равняется 1922,06.

В предыдущем примере начальная цена 40 000 руб. не включалась в список значений, поскольку выплата пришлась на начало периода.

Предположим, что на шестой год Ваш магазин потерпел крах, и Вы предполагаете убыток в 9 000 руб. для шестого года. Чистый текущий объем инвестиции в обувной магазин после шести лет составит:

НПЗ(8%; B2:B6; -9000)+B1, что равняется – 3749,47.

Рассмотрим пример использования функции НПЗ.

Предположим, что стоит задача наладить производство товаров, на которые имеется спрос. При этом предстоит выбрать один из трех альтернативных проектов. Первоначальные инвестиции при реализации первого проекта составляют 500000 руб., второго – 400000 руб. И третьего – 700000 руб.; при этом сроки службы оборудования (период инвестиции) также различаются. Все эти значения будут введены в таблицу с исходными данными. Задача заключается в определении наиболее выгодного инвестиционного проекта.

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

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

По строкам представим исходные данные и итоги для отдельных проектов: в ячейках B5, B7 и B9 следует указать текстовые метки строк Проект 1, Проект 2 и Проект 3. В строке 3 будут отображены метки столбцов, поэтому введем в ячейки D3, E3, F3, H3 и I3 соответственно:

Инвестиция

Срок (лет)

Ставка (%)

Текущая стоимость

Чистая стоимость

Рис. 7.4

В столбце D укажите размер первоначальной инвестиции. Введите значения 500000, 400000 и 700000 для трех проектов и отформатируйте ячейки денежным стилем. Дополнительные инвестиции будут учитываться в балансе денежных потоков. Столбец Срок (лет) должен содержать данные о периоде инвестиции – 5, 5 и 8 лет соответственно. Зададим в столбце Ставка (%) для первых двух проектов процентную ставку 13%, а для третьего – 11%. Отформатируйте ячейки процентным стилем. Основные исходные данные введены, присвоим первому рабочему листу имя Выбор проекта. В результате получаем таблицу, приведенную на рис. 7.4.

Присвоим второму рабочему листу имя Проект 1. Одноименный заголовок таблицы введите и в ячейку A1. В строке 3 укажите в ячейках B3, D3, F3 и E3 текстовые метки столбцов:

Год

Баланс денежных потоков

Текущая стоимость

Текущая стоимость (Итог).

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

Рис. 7.5

В ячейках B4 – B8 укажите значения от 1 до 5. Далее введите предполагаемые значения для денежных потоков в ячейки D4 – D8. Следует помнить, что отрицательное сальдо денежных потоков (выплаты в течение периода превышают поступления) следует указывать со знаком минус.

Теперь определим текущую стоимость денежных потоков для каждого года. Для этого в ячейку E4 введите формулу:

=D4*(1 + Выбор проекта!$F$5)^(–B4)

и скопируйте ее в ячейки E5 – E8. В столбце F значение текущей стоимости инвестиции будет представлено нарастающим итогом. В ячейке F4 с помощью формулы

= E4

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

= F4 + E5

и скопируйте ее в ячейки F6 – F8. В ячейке F8 представлено значение текущей стоимости инвестиций, которое должно совпасть со значением в ячейке H5 в рабочем листе Выбор проекта, рассчитанном с помощью функции НПЗ. В результате все этих действий получаем таблицу, представленную на рис. 7.5.

Рис. 7.6

Рис. 7.7

Перейдем в третий рабочий лист и назовем его Проект 2. Чтобы не повторять всех аналогичных действий, скопируем содержимое рабочего листа Проект 1 и выполним необходимую редакцию содержимого. Отредактируем название таблицы (ячейка A1) и формулу в ячейке E4 (ту ее часть, которая касается процентной ставки (Выбор проекта!$F$7)). В результате получаем таблицу, представленную на рис. 7.6.

Те же самые операции следует выполнить для четвертого рабочего листа, который называется Проект 3. Здесь только необходимо добавить данные для трех дополнительных лет (см. рис. 7.7). Следует не забыть указать правильный адрес ячейки для значения процентной ставки (Выбор проекта!$F$9).

Перейдем к рабочему листу Выбор проекта для завершения расчетов и определения наиболее выгодного проекта. В этом рабочем листе текущее значение инвестиции будем определять с помощью функции НПЗ. Поместим указатель ячейки на ячейку H5 и активизируем Мастер функций, выберем функцию НПЗ и зададим ее аргументы следующим образом:

= НПЗ (F5; Проект1!D4:D8).

В ячейках H7 и H9 укажите формулы:

= НПЗ (F7; Проект1!D4:D8)

и

= НПЗ (F9; Проект1!D4:D11).

Рис. 7.8

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

= H5 – D5

и скопируем ее в ячейки I7 и I9. Результаты вычислений приведены в таблице на рис. 7.8.

Анализ столбца Чистая стоимость показывает, что чистая стоимость третьего проекта больше, чем у двух оставшихся. Следовательно, наиболее перспективным является реализация третьего проекта.

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

ППЛАТ – возвращает величину выплаты за один период годовой ренты на основе постоянных выплат и постоянной процентной ставки.

Синтаксис:

ППЛАТ(ставка; кпер; нз; бз; тип)

Более подробное описание аргументов функции ППЛАТ см. в описании функции ПЗ.

Замечания.

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

Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ППЛАТ значение на кпер.

Примеры.

Следующая макроформула возвращает ежемесячные выплаты по займу в 10000 руб. и годовой процентной ставке 8%, которые можно выплачивать в течение 10 месяцев:

ППЛАТ(8%/12; 10; 10000) равняется – 1037,03 руб.

Для того же займа, если выплаты должны делаться в начале периода, то выплата составит:

ППЛАТ(8%/12; 10; 10000; 0; 1) равняется – 1030,16 руб.

Следующая макроформула возвращает сумму, которую необходимо выплачивать Вам каждый месяц, если Вы дали взаймы 5000 руб. под 12% годовых и хотите получить назад деньги за пять месяцев:

ППЛАТ(12%/12; 5; -5000) равняется 1030,20 руб.

Предположим, что Вы хотите накопить 50000 руб. за 18 лет, накапливая постоянную сумму каждый месяц. Если предположить, что Вы сможете обеспечить 6% годовых на Ваши накопления, то можно использовать функцию ППЛАТ, чтобы определить, сколько нужно откладывать каждый месяц:

ППЛАТ(6%/12; 18*12; 0; 50000) равняется – 129,08 руб.

Если Вы платите 129,08 руб. с 6% накоплением в течение 18 лет, Вы получите 50000 руб.

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

В ячейках B7, B9, B11 рабочей книги вводится соответственно Банк 1, Банк 2 и Банк 3. В строке 5 будут представлены текстовые метки столбцов. Введите в ячейки C5, D5, E5, F5, G5, I5, J5, K5, L5 следующие текстовые метки столбцов: Объем кредита, Выдача (%), Плата за оформление, Ставка (%), Срок (лет), Получено, Дизажио, Выплата (год), Выплата/Получено.

В первом столбце будет представлен объем кредита. Укажем во всех трех случаях объем кредита в 200000 р. Однако представление кредита еще не означает, что будет получена вся сумма. Кое-что (дизажио) необходимо оставить в банке, как плату за обработку и за повышенный риск, на который идет банк. Укажем в ячейках D7, D9 и D11 значения 0,95, 0,96 и 0,965 и сформатируем ячейки процентным стилем. Кроме того, следует оплатить издержки, возникающие при оформлении кредита, которые в нашем примере составляют 300, 250 и 350 р. В столбце Ставка следует ввести значения процентных ставок, по которым банки готовы предоставить кредит: 0,12, 0,135 и 0,142, – а затем сформатировать ячейки столбца процентным стилем. Столбец Срок должен содержать значения количества лет, на которые предоставлен кредит – 5 лет. Этим завершается ввод исходных данных в таблицу. В результате получаем таблицу, приведенную на рис. 7.6.

Теперь необходимо произвести расчеты.

Сначала определим в столбце J7 размер дизажио для первого варианта с помощью формулы:

=C7*(1 – D7).

Для определения полученной суммы следует вычесть из объема кредита дизажио и плату за оформление, поэтому в ячейке I7 следует задать формулу:

= C7 – J7 – E7.

Рис. 7.9

Теперь можно приступить к расчету годового платежа по кредиту, который будет включать в себя как погашение основного долга, так и процентные платежи. Поместим указатель ячейки на ячейку K7 и введем функцию ППЛАТ с обязательными аргументами:

=ППЛАТ(F7; G7; -C7).

Остается определить в ячейке L7 (предварительно сформатированной в процентном стиле) отношение годовой выплаты к полученной сумме с помощью формулы:

= K7/I7.

Скопируем формулы из I7, J7, K7, L7 в расположенные ниже ячейки, в которых будут произведены вычисления для предложений кредита второго и третьего банков. Все результаты имеются на рис. 7.9. Присвоим этому рабочему листу имя Кредит.

Предположим, что было принято решение остановится на кредите, предложенном первым банком (более низкий годовой процент). В пользу первого банка говорит также более выгодное отношение Выплата/Получено (столбец L). Посмотрим во втором рабочем листе, как будет протекать погашение кредита.

Перейдем во второй рабочий лист и присвоим ему имя Погашение. Вводим в ячейки B4,C4, D4 и E4 следующие текстовые метки столбцов: Год, Погашение долга, Проценты и Остаток. В первом столбе в ячейках B6 – B10 будут представлены значения периодов выплаты – от 1 до 5. Ячейки C6 – C10 должны содержать суммы – части годового платежа, которые будут идти на погашение основного долга, ячейки D6 – D10 – значения выплачиваемых процентов, а ячейки E6 – E10 – значения остатка основного долга.

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

=Кредит!$C$7*Кредит!$F$7.

Рис 7.10

Часть годового платежа, которая в первый год уйдет на погашение основного долга, составит (ячейка C6):

=Кредит!$K$7–D6.

Остаток долга в конце первого года рассчитаем по формуле (ячейка E6):

= Кредит!$C$7-$C$6.

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

=E6*Кредит!$F$7.

Эту формулу следует скопировать в ячейки и для того, чтобы определить процентные платежи для следующих лет. Также можно скопировать формулу определения суммы погашения основного долга, заданную для первого года, в ячейках C7 – C10. В заключение в ячейке E7 следует указать формулу определения остатка основного долга:

=E6-C7

и скопировать эту формулу в ячейки E8 – E10. В итоге получаем таблицу, приведенную на рис. 7.10.

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