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

Московский технический университет связи и информатики

_____

Кафедра информационных технологий в экономике и управлении

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

 

Москва 2001
Содержание

ВВЕДЕНИЕ............................................................................................................................................................ 3

Денежные расчеты по вкладам и кредитам в EXCEL................................................. 3

1. Функция БЗ............................................................................................................................................ 4

2. Функция НПЗ......................................................................................................................................... 4

3. Функция ПЗ............................................................................................................................................ 5

4. Функция ВНДОХ.................................................................................................................................. 6

5. Функция МВСД.................................................................................................................................... 6

Процентные расчеты по вкладам и кредитам................................................................ 7

1. Функция НОРМА.................................................................................................................................. 7

2. Функция КПЕР...................................................................................................................................... 7

3. Функция ОСНПЛАТ............................................................................................................................ 8

4. Функция ПЛПРОЦ................................................................................................................................ 8

5. Функция ППЛАТ................................................................................................................................... 9

Расчет амортизации имущества в EXCEL............................................................................ 9

1. Функция АМГД.................................................................................................................................. 10

2. Функция АМР...................................................................................................................................... 11

3. Функция ДДОБ................................................................................................................................... 11

4. Функция ДОБ...................................................................................................................................... 13

5. Функция ПДОБ................................................................................................................................... 13

Задачи................................................................................................................................................................. 16

ВВЕДЕНИЕ

 

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

1. Информационные функции

2. Логические функции

3. Текстовые функции

4. Функции базы данных

5. Функции просмотра и ссылки

6. Статистические функции

7. Математические и тригонометрические функции

8. Функции даты и времени

9. Финансовые функции

 

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

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

Запуск мастера функций:

· меню ВСТАВКА ® ФУНКЦИЯ;

· выбрать категорию функции, затем саму функцию;

· щелкнуть по кнопке ШАГ и ввести значения аргументов (значения, которые вводить не нужно, можно пропускать);

· щелкнуть по кнопке ЗАКОНЧИТЬ.

 

 

Excel в типовой установке насчитывает 15 финансовых функций, которые условно можно разделить на три группы:

1. Денежные расчеты по вкладам и кредитам

2. Процентные расчеты по вкладам

3. Расчет амортизации имущества

 

Денежные расчеты по вкладам и кредитам в EXCEL

 

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

 

Введем обозначения аргументов:

 

С – ставка (процент) по вкладу или кредиту, может вводиться в ячейку в виде 10% либо 0,1;

КП – количество периодов (лет, месяцев дней), на которые делается вклад или выдается кредит;

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

НЗ – начальное значение (исходная величина) вклада или кредита;

БЗ – будущее значение (ожидаемый доход) по вкладу или кредиту;

БП – номер периода (из интервала от 1 до КП), для которого производятся вычисления;

ТИП – логическая величина, имеющая значение ЛОЖЬ, если выплаты производятся в конце периода (месяца, дня, года), и ИСТИНА, если в начале. По умолчанию всегда принимается значение ЛОЖЬ (в конце периода).

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

 

Функция БЗ

 

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

БЗ (С; КП; П; [НЗ;] – ИП])

Пример: Пусть мы вложили 1 млн. руб. на 5 лет под 10% годовых, при этом каждый год будем снимать 100 000 р.

Внесем эти данные в таблицу:

 

	А	В	С	D	E
	10%		+100 000	-1 000000

 

Введем в Е1 формулу: = БЗ(А1;В1;С1;D1), получим 1000000,00 руб. Именно эта сумма остается на счете через 5 лет. Значение П и НЗ в данном случае взято отрицательным, т.к. означают платежи (уменьшение доходов).

 

Функция НПЗ

 

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

НПЗ(С;З1;[З2;…;ЗN]),

где З1,…,ЗN – выплаты (отрицательные) и поступления (положительные) числа, которые обязательно должны осуществляться в конце соответствующего периода.

Пример 1: Некто собирается в конце года взять кредит в банке в 10 000000 руб. на 2 года. Деньги он собирается вложить в акции, по которым гарантирован доход 10%. В первый год он должен вернуть банку 4 000000 руб. а во второй -6 000000 руб. (беспроцентный кредит). Определить, что он будет иметь от этой операции.

Введем исходные данные в таблицу:

 

	А	В	С	D	E
	10%	10 000000	-4 000000	-6 000000

 

Введем в Е1 формулу: = НПЗ(А1;В1;С1;D1), получим: 1 277235,16 руб.

 

Ясно, что такое вложение выгодно, т.к. сегодняшний доход равен полученной сумме. Значение З1 взято положительным, а З2 и З3 – отрицательными, т.к. расчет ведется относительно клиента.

 

Пример 2: В условиях предыдущего примера введем увеличение платы за кредит – в 1-ый год – 5 000000 руб., во второй – 5 000000 руб.

Введем исходные данные в таблицу:

 

	А	В	С	D	E
	10%	10 000000	-5 000000	-5 000000

 

Введем в Е1 формулу: = НПЗ(А1;В1;С1;D1), получим: -1 051840,72 руб.

Данное вложение невыгодно, т.к. указанная сумма равнозначна текущему убытку (выплате). Т.е. получаемые доходы не покроют необходимых выплат.

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

 

Пример 3: В условиях примера 1 изменим время выдачи кредита. Пусть кредит берется сейчас.

Введем исходные данные в таблицу:

 

	А	В	С	D	E
	10%	10 000000	-4 000000	-6 000000

 

Введем в Е1 формулу: = НПЗ(А1;С1;D1)+В1, получим: 1 404598,68 руб., т.е. данные условия вложения также выгодны.

 

Пример 4: При оценке будущих доходов в качестве ставки может выступить также уровень инфляции. Пусть некто собирается вложить в конце года 5 млн. руб., чтобы через год получить доход в 7 млн. руб. Определить выгодно ли это, если степень инфляции 10%.

Введем данные в таблицу:

 

	А	В	С	D
	10%	-5 000000	7 000000

 

Введем в D1 формулу: = НПЗ(А1;В1;С1), получим: 1 239669,42 руб., т.е. вложение выгодно.

 

Функция ПЗ

 

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

ПЗ(С;КП;П;[БЗ;ТИП])

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

Пример: Пусть банк выдает кредиты на 5 лет с ежегодной суммой возврата в 1 млн. руб. Деньги кредита, будучи вложены, принесут годовой доход в 10%. На какую максимальную сумму может быть предоставлен такой кредит?

Введем исходные данные в таблицу:

 

	А	В	С	D
	10%		1 000000

 

Введем в D1 формулу: = ПЗ(А1;В1;С1), получим: -3 790786,77 руб., т.е. сегодня, выдав эту сумму, мы гарантируем ее возврат с процентами на наших условиях, если доход 10%. Результат получился отрицательным, т.к. означает платеж банка. Функции БЗ и ПЗ взаимообратны. Если БЗ(х)=у, то ПЗ(у)=х. Так, например, введем в формулу: =БЗ(А1;В1;С1; - 3790786,77), получим ответ 0,00 руб.

 

Функция ВНДОХ

 

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

ВНДОХ( ЗНАЧЕНИЯ: [ПРОГНОЗ]),

где ЗНАЧЕНИЯ – это значения числовых величин, означающих сущность денежных операций (положительные значения означают платежи, отрицательные – выплаты). Значения обязательно должны содержать хотя бы один положительный и один отрицательный элемент. В данной функции в качестве значений не могут выступать числа, они должны быть обязательно ссылками на имена ячеек. ПРОГНОЗ – это предполагаемая величина результата. Если ПРОГНОЗ опущен, то он предполагается равным 10%. Если функция с введенным значением ПРОГНОЗ или со значением по умолчанию (=10%) дает результат ОШИБКА, то необходимо изменить значение ячейки ПРОГНОЗ.

Пример: Пусть некто вложил в дело в 1-ый год 1 млн. руб. и во 2-ой год – 1,5 млн. руб. В первый год был получен доход в 2,5 млн. руб., во 2-ой год – 3 млн. руб. и в третий – 4 млн. руб. Определить внутреннюю скорость оборота.

Введем данные в таблицу:

 

	A	B	C	D	E	F
	-1000000		-1500000

 

Введем в F1 формулу: = ВНДОХ(А1:Е1), получим результат 160%

 

Функция МВСД

 

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

МВСД(В1;В2;[В3;…;ВN]ФС;РС),

где В1;…;ВN – это выплаты или доходы. Выплаты имеют отрицательные значения , доходы – положительные. В1 и В2 должны быть обязательно одно положительным, другое – отрицательным. ФС – норма прибыли за деньги, находящиеся в наличном обороте.

Пример: Возьмем кредит в 1000000 руб. под 10% годовых (доход), прибавим к ним 200000 руб. и сумму (1200000) вложим в дело с доходностью в 15% (расход). Определить скорость оборота денежных средств.

 

Введем данные в таблицу:

 

	A	B	C	D	E
		-1200000	10%	15%

 

Введем в Е1 формулу: = МВСД(А1;В1; С1;D1), получим ответ 20%

 

В данной функции также в качестве В1;…;ВN вводятся не числа, а имена ячеек.

 

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

 

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

 

Различают следующие функции:

 

Функция НОРМА

 

Вычисляет процентную ставку по вкладу или кредиту. Функция имеет формат:

НОРМА(КП;П;НЗ;[БЗ;ТИП;НП]),

где НП – это начальное приближение, по умолчанию принимается равным 10%. Если функция выдает результат ОШИБКА, то необходимо изменить начальное приближение.

Пример: Банк принимает вклады в 1 млн. руб. на 5 лет с правом ежегодного снятия 500000 руб. Через 5 лет вклад закрывается(на счете остается 0). Определить процентную ставку по данному вкладу.

Введем данные в таблицу:

 

	A	B	C	D
		-500000

 

Значение в ячейке В1 отрицательное, т.к. означает расход.

Введем в D1 формулу: =НОРМА(А1;В1;С1), получим результат 41%

 

Функция КПЕР

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

КПЕР(С;П;НЗ;[БЗ;ТИП])

Значение БЗ по умолчанию принимается = 0.

Пример: Положим в банк 1 млн. руб. под 10% годовых. Ежегодно будем снимать 200000 руб. На сколько лет должно хватить этого вклада (т.е. когда на счету ничего не останется?)

Введем данные в таблицу:

 

	A	B	C	D
	10%	-200000

 

Т.к. расчет ведется относительно банка, то В1 взято отрицательным, а С1 – положительным.

Введем в D1 формулу: = КПЕР(А1;В1;С1), получим: 7,272, т.е.~ 7 лет.

 

Функция ОСНПЛАТ

 

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

ОСНПЛАТ(С;ВП;КП;НЗ;[БЗ;ТИП]).

По умолчанию принимается БЗ равным 0.

Пример: Возьмем кредит на 2 года под 10% годовых. Сколько необходимо выплатить основного платежа за 1-ый год и 2-ой год?

Введем данные в таблицу:

 

	A	B	C	D	E
	10%
	10%

 

Введем в Е1 формулу: = ОСНПЛАТ(А1;В1;С1;D1), получим: - 476190,48 руб.

Введем в Е2 формулу: = ОСНПЛАТ(А2;В2;С2;D2), получим: - -523809,52 руб.

Результаты получились отрицательными, т.к. означают платежи.

 

Функция ПЛПРОЦ

 

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

ПЛПРОЦ(С;ВП;КП;НЗ;[БЗ;ТИП])

По умолчанию принимается значение В3=0

Пример: В условиях примера по функции ОСНПЛАТ вычислить плату по процентам за 1-й и 2-й год.

Введем данные в таблицу:

 

	A	B	C	D	E
	10%
	10%

 

Введем в Е1 формулу: = ПЛПРОЦ(А1;В1;С1;D1), получим: - -100000 руб.

Введем в Е2 формулу: = ПЛПРОЦ(А2;В2;С2;D2), получим: - -52380,95 руб.

Функция ППЛАТ

 

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

ППЛАТ(С;КП;НЗ;[БЗ;ТИП])

Значение БЗ по умолчанию равно 0.

Пример: В условиях примеров функций ОСНПЛАТ и ПЛПРОЦ определить ежегодную выплату по кредиту.

Введем данные в таблицу:

 

	A	B	C	D
	10%

 

Введем в D1 формулу: = ППЛАТ(А1;В1;С1), получим: - -576190,48 руб.

 

Как видно из примеров на функции ОСНПЛАТ, ППЛАТ и ПЛПРОЦ, ежегодная выплата по вкладу или кредиту представляет собой фиксированную сумму, однако составляющие этой суммы год от года меняются: основная плата растет, а плата по процентам уменьшается. Поэтому для каждого конкретного периода можно записать:

ОСНПЛАТ + ПЛПРОЦ = ППЛАТ

Примечание:

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