Размещено на http://www.allbest.ru/

Введение

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

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

В данной работе я попытаюсь подробно исследовать финансовую функцию КПЕР, для чего мне потребуется:

· описать категорию функций «Финансовые функции», к которой относится функция КПЕР, чтобы определить какие из них могут быть связаны с ней;

· составить подробный анализ экономической проблемы, для решения которой используется функция КПЕР;

· составить список специальных терминов и формул с их описанием, знание которых необходимо для понимания работы функции КПЕР;

· составить подробное описание работы функции КПЕР и всех ее аргументов;

· предложить набор исходных данных, которые будут использованы для демонстрации работы функции КПЕР;

· предоставить описание примеров возможного использования функции КПЕР.

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

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

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

· функции анализа кредитов и вкладов;

· функции для работы с ценными бумагами;

· функции для расчета амортизации.

Функция КПЕР относится к группе функций анализа кредитов и вкладов.

1.1 Функции анализа кредитов и вкладов

Движение денежных средств можно представить численным рядом из последовательности платежей, распределенным во времени. Такой ряд называется потоком платежей (cash fiow, CF).

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

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

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

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

Названия и краткое описание функций, входящих в группу «финансовых» указаны в следующей таблице:

Таблица 1

БЗРАСПИС	Возвращает будущую стоимость первоначальной основной суммы после начисления ряда сложных процентов.
БС	Возвращает будущую стоимость инвестиции.
ВСД	Возвращает внутреннюю ставку доходности для ряда потоков денежных средств.
КПЕР	Возвращает общее количество периодов выплаты для данного вклада.
МВСД	Возвращает внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разные значения ставки.
НОМИНАЛ	Возвращает номинальную годовую процентную ставку.
ОБЩДОХОД	Возвращает общую выплату по займу между двумя периодами.
ОБЩПЛАТ	Возвращает общую выплату, произведенную между двумя периодическими выплатами.
ОСПЛТ	Возвращает величину выплат в погашение основной суммы по инвестиции за заданный период.
ПЛТ	Возвращает величину выплаты за один период аннуитета.
ПРОЦПЛАТ	Вычисляет выплаты за указанный период инвестиции.
ПРПЛТ	Возвращает величину выплаты прибыли на вложения за данный период.
ПС	Возвращает приведенную (к текущему моменту) стоимость инвестиции.
СТАВКА	Возвращает процентную ставку по аннуитету за один период.
ЧИСТВНДОХ	Возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер.
ЧИСТНЗ	Возвращает чистую приведенную стоимость для денежных потоков, которые не обязательно являются периодическими.
ЧПС	Возвращает чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.
ЭФФЕКТ	Возвращает действующие ежегодные процентные ставки.

1.2 Функции, применяемые для вычисления параметров ссуды

Параметры ссуды:

· объем ссуды;

· процентная ставка;

· количество выплат;

· периодичность выплат.

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

Для этого используются шесть функций: ПЛТ, ОСПЛТ, ПРПЛТ, СТАВКА, КПЕР и ПС.

1.2.1 Функция ПЛТ

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

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

Следующая формула возвращает объем ежемесячных выплат по ссуде, размер которой 50 000 руб., а процентная ставка составляет 6% годовых. Ссуда взята на срок четыре года (48 месяцев).

=ПЛТ(0,06/12;48;-50000)

Вычислив формулу, получим, что объем ежемесячных выплат по ссуде равен 1 174,25 руб. Обратите внимание на то, что третий аргумент (пс, приведенная стоимость) отрицательный, это указывает на то, что деньги были взяты в долг.

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

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

ОСПЛТ(ставка;период;кпер;пс;бс;тип)

Следующая формула возвращает основную часть выплаты за первый месяц по ссуде 50 000 руб., взятой под 6% годовых. Ссуда взята на срок четыре года (48 месяцев).

ОСПЛТ(0,06/12;1;48;50000)

Вычислив формулу, получим, что основная часть первой выплаты равна 924,25 руб., или приблизительно 78,7% от общей суммы выплаты. Если в качестве второго аргумента ввести число 48 (для вычисления основной части последней выплаты), то формула вернет 1 168,41 руб., т.е. приблизительно 99,5% от общей суммы выплаты. Чтобы вычислить кумулятивную основную сумму, выплаченную между любыми двумя определенными периодами выплат, можно воспользоваться функцией ОБЩДОХОД. Функция содержит два дополнительных аргумента: нач_период и кон_период. В версиях Excel, предшествовавших Excel 2007, функция ОБЩДОХОД становилась доступной только после установки надстройки Пакет анализа.

1.2.3 Функция ПРПЛТ

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

ПРПЛТ (ставка; период; кпер; пс; бс; тип)

Формула, приведенная ниже, вычисляет объем выплат по процентам для первой выплаты по ссуде размером 50 000 руб. с процентной ставкой 6% годовых. Ссуда взята на 4 года (48 месяцев).

=ПРПЛТ (0,06/12;1;48;-50000)

После вычисления формулы получим, что сумма выплат по процентам составляет 250,00 руб. Но объем выплат по процентам за последний период будет всего 5,84 руб. Чтобы вычислить кумулятивную сумму выплат по процентам между двумя периодами, следует воспользоваться функцией ОБЩПЛАТ. Функция содержит два дополнительных аргумента: нач_период и кон_период.

1.2.4 Функция СТАВКА

Позволяет вычислить периодичность, с которой начисляются проценты по ссуде по заданным общему количеству периодов выплат, размеру выплат за один период и размеру ссуды. Функция СТАВКА имеет следующий синтаксис:

СТАВКА (кпер; плт; пс; бс; тип; предположение)

Следующая формула вычисляет годовую процентную ставку для ссуды размером 50 000 руб., взятой на 48 месяцев. Объем выплат за один период составляет 1 174,25 руб.

=СТАВКА(48;1174,25;-50000)*12

Формула вернет 6,00%. Заметьте, что результат вычисления функции умножается на 12 -- функция возвращает процентную ставку за период, поэтому, чтобы получить годовую процентную ставку, нужно умножить полученное значение на количество периодов выплат в году (в примере -- на 12).

1.2.5 Функция ПС

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

ПС (ставка; кпер; плт; бс; тип)

Следующая формула возвращает основной объем ссуды, взятой на 48 месяцев под 6% годовых. Объем выплаты равен 1 174,25 руб.

=ПС(0,06/12;48;-1174,25)

Формула вернет 49 999,94 руб. Поскольку объем выплаты указан с точностью до одной копейки, результат оказался с погрешностью в 0,06 руб.

финансовый функция ссуда кредит

2. Анализ экономической проблемы

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

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

3. Терминология

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

Приведенная стоимость - это основная (капитальная) сумма. Если на депозит в банке вкладывается 50 000 руб., то эта величина представляет собой капитал или приведенную стоимость вложенных денег. Если берется ссуда размером 150 000 руб. на приобретение автомобиля, то данная сумма будет основной или приведенной стоимостью ссуды. Приведенная стоимость может быть как положительной, так и отрицательной.

Будущая стоимость представляет собой сумму приведенной стоимости и начисленным по ней процентам. Если на депозитный счет в банке вкладывается 50 000 руб. на пять лет под 6% годовых, то в конце срока можно будет получить 63 123,80 руб. Последняя сумма будет будущей стоимостью инвестиции. Если же берется ссуда на три года на покупку автомобиля в размере 150 000 руб. под 7% годовых, то в конце срока нужно будет выплатить 166 731,60 руб. Иными словами, нужно будет вернуть основную сумму плюс проценты. Будущая стоимость, в зависимости от перспективы (кредитор или заемщик), может быть положительной или отрицательной.

Взнос - может быть либо капитал, либо капитал и начисленные на него проценты. Если каждый месяц вкладывается 1 000 руб. на депозитный счет, то 1 000 руб. -- это взнос. Если для погашения ссуды ежемесячный взнос составляет 8 250 руб., то он состоит из основной суммы и начисленных процентов.

Процентная ставка - часть основной суммы (в процентах), начисляемая за определенный период (как правило, за год). Например, деньги вложены на депозит с процентной ставкой 5,5% годовых. Или процентная ставка ссуды составляет 7,75% в год.

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

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

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

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

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

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

Банковская ссуда - сумма денежных средств, предоставляемая предприятию банком в порядке кредитования.

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

Поток платежей - это распределенная во времени последовательность платежей.

Элементарные потоки платежей - состоят из одной выплаты и последующего поступления либо разового поступления с последующей выплатой, разделенных n - периодами времени.

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

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

4. Описание функции КПЕР

Функция КПЕР возвращает общее количество выплат по ссуде по заданным объему ссуды, процентной ставке и объему одной выплаты. Функция имеет следующий синтаксис:

КПЕР (ставка; плт; пс; бс; тип)

Формула, приведенная ниже, вычисляет количество выплат по ссуде размером 50 000 руб. и объему выплаты, равным 1 174,25 руб. Ссуда взята под 6% годовых.

=КПЕР (0,06/12;1174,25;-50000)

С небольшой погрешностью (меньше 0,0001) функция вернет число 48. Это значит, что всего будет произведено 48 выплат (за 48 месяцев). Полученный результат оказался неточным из-за того, что объем выплат указан с точностью до одной копейки, т.е. округлен.

4.1 Аргументы функции КПЕР

4.1.1 СТАВКА

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

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

4.1.2 ПЛТ

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

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

4.1.3 ПС

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

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

4.1.4 БС

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

Примечание: если аргумент "бс" опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).

4.1.5 ТИП

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

Примечание: аргумент равен нулю, если выплата производится в конце периода, и единице -- если в начале. «Тип» по умолчанию равен нулю.

4.2 Формула функции КПЕР

где FV - будущая стоимость,

PV - текущая стоимость,

r - ставка.

5. Исходные данные

финансовый ссуда кредит

Для демонстрации работы изучаемой мною функции КПЕР нужен следующий набор исходных данных.

Во-первых, для работы функции нам необходима программа Microsoft Excel, в которой осуществляется ее действие.

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

Рис. 1: окно «Мастера функций»

После этого нужно выбрать ячейку, в которой будет отображаться результат вычисления функции, щелкнув по ней и запустить «Мастера функций», выбрав команду «Вставить функцию» на ленте «Формулы» или активизировав комбинацию клавиш <Shift+F3>. Также можно щелкнуть на кнопке вызова функций , находящейся на строке формул. Появится окно, представленное на рисунке 1.



Рис. 2: выбор функции КПЕР

Затем, из списка «Категория» выбираем «Финансовые», после чего появится список финансовых функций, к которому и относится изучаемая мной функция КПЕР. Ее можно найти, прокрутив бегунок в правой части окна «Мастера функций», и выбрать, щелкнув по ней (Рис. 2).

После выбора функции КПЕР откроется окно, в графы которого нужно ввести необходимые для расчета функции значения. Для этого нужно щелкнуть по ячейке, в которой находится соответствующий аргумент. Тогда в графах окна функции будут появляться номера ячеек с аргументами. А в строке формул появится формула следующего вида: =КПЕР(B1;B2;B3). Окно «Аргументов функции» показано на рисунке 3.

Рис. 3: окно «Аргументы функции»

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

6. Примеры использования функции КПЕР

6.1 Пример 1

6.1.1 Дано

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

6.1.2 Решение задачи

При ежемесячном начислении процентов ставка процента за период начисления равна 6%/12. Чтобы определить общее число периодов выплат для единой суммы вклада, воспользуемся функцией КПЕР со следующими аргументами:

· ставка = 6%/12;

· пс = -60000;

· бс = 120000.

Значением функции КПЕР является число периодов, необходимое для проведения операции, в данном случае - число месяцев. В результате мы получим число периодов, равное 138,97. То есть почти 139 месяцев.

Для нахождения числа лет полученный результат разделим на 12. В строке формул запишем =139/12, и получим 11,58. То есть около 11,6 лет.

Иллюстрацию к задаче можно рассмотреть на рисунке 4.



Рис. 4: иллюстрация к примеру 1

6.2 Пример 2

Дано

Была взята ссуда на сумму 300 000 руб. под 17% годовых. Объем каждой выплаты равен 7000 руб. Нужно рассчитать количество лет, необходимых для погашения ссуды.

Решение задачи

При ежемесячной плате ставка процента за период равна 17%/12. Чтобы определить общее число периодов выплат по ссуде, воспользуемся функцией КПЕР со следующими аргументами:

· ставка = 17%/12;

· плт = 7 000;

· пс = -300 000.

Значением функции КПЕР является число периодов, необходимое для погашения ссуды, в данном случае - число месяцев. В результате мы получим число периодов, равное 66,42. То есть около 66,4 месяцев.

Для нахождения числа лет полученный результат разделим на 12. В строке формул запишем =66,4/12, и получим 5,53. То есть около 5,5 лет.

Рис. 5: иллюстрация к примеру 2

Иллюстрацию к задаче можно рассмотреть на рисунке 5.

6.3 Пример 3

Дано

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

Решение задачи

Для этого возьмем имеющиеся данные и составим таблицы зависимости. В таблице зависимости числа периодов от изменения процентной ставки (Рис. 6) мы берем 15 различных значений ставки. Значения платежа и приведенной стоимости остаются неизменными в ячейках A5 и B5 соответственно. Для того, чтобы правильно рассчитать все значения по формуле КПЕР, нужно в строке формул для ячейки B8 изменить аргументы платежа и приведенной стоимости таким образом, чтобы их значения были закреплены в ячейках A5 и B5. Для этого рядом с цифрой 5, означающей строку, где находятся значения аргументов, нужно поставить значок доллара ($), нажав при этом необходимое количество раз клавишу F4 или просто приписав значок перед цифрой. После этого достаточно потянуть за правый нижний угол ячейку с формулой, чтобы заполнить все 15 ячеек таблицы.

Аналогично выполним вычисления зависимости числа периодов от изменения выплаты, производимой в каждый период, результаты которых можно увидеть на рисунке 7, вычисления зависимости числа периодов от изменения общей суммы, результаты которых показаны на рисунке 8.

Рис. 6: зависимость числа периодов от изменения процентной ставки

Рис. 7: зависимость числа периодов от изменения выплаты, производимой в каждый период



Рис. 8: зависимость числа периодов от изменения общей суммы

Примеры можно подробно рассмотреть в файле «Примеры применения функции КПЕР.xlsx»

Список используемой литературы

1. Экономическая информатика; Чистов Д.В.; М.: издательство «КноРус»; 2012г.

2. Практикум по информатике; Землянский А.А. , Кретова Г.А., Стратонович Ю.Р.; М.: издательство «Колос», 2003г.

3. Практикум по основам информатики и вычислительной техники. Учебное пособие для учреждений начального профессионального образования. Гриф МО РФ; Красникова Н.Е. , Силакова Л.А. , Уваров В.М.; М.: издательство «ACADEMIA»; 2012г.

4. Информационные технологии управления; Вертакова Ю.В., Венделева М.А.; М.: издательство «Юрайт»; 2012г.

Размещено на Allbest.ru