Финансовые расчеты с применением MS Excel
СОДЕРЖАНИЕ: Оглавление Оглавление 2 Введение 3 1. Теоретическая часть 4 1.1 Особенности использования финансовых функций в MS Excel 4 2. Практическая часть 8 2.1 Постановка задачи: 8Оглавление
1.1 Особенности использования финансовых функций в MSExcel4
1.2 Технология работы и виды финансовых функций в области кредитования в MSExcel5
2.2 Решение поставленных задач.10
Введение
В настоящее время трудно переоценить роль специалиста по финансовому анализу деятельности предприятия. Финансы являются «кровью» предприятия. Именно в деньгах оцениваются проданные товары и оказанные клиентам услуги. Именно деньги являются универсальным измерителем необходимых предприятию ресурсов – сырья и материалов, станков, человеческих ресурсов, информации и т.д. поэтому планирование и прогнозирование, контроль и оптимизация финансовых потоков являются жизненно важными задачами финансовой службы. (Л.А., 2006)
Финансовые функции применяются при планировании и анализе финансово-хозяйственной деятельности предприятия, а также при решении задач, связанных с инвестированием средств.
Данная работа посвящена рассмотрению различных задач связанных с расчетами по кредитам и вкладам в банки. В теоретической части рассмотрены особенности использования финансовых функций в MSExcel, а также описаны виды финансовых функций для расчетов в области кредитования в MSExcel.
В практической части рассмотрены различные задачи по кредитным вычислениям, с применением различных функций.
1. Теоретическая часть
1.1 Особенности использования финансовых функций в MSExcel
Сегодня нельзя всерьез претендовать на работу экономиста, менеджера, бухгалтера, финансиста, специалиста по ценным бумагам и т.п., если не уметь обращаться с компьютером. Умение работы с компьютером предполагает прежде всего знание текстовых процессоров, электронных таблиц, системы управления базами данных и систем для работы с графикой.
EXCEL является одной из самых популярных программ работающих в операционной среде Windows, поскольку объединяет возможности графического и текстового редактора с мощной математической поддержкой.
Функции EXCEL используют базовые модели финансовых операций, базирующиеся на математическом аппарате методов финансово-экономических расчетов. Использование возможностей компьютера и табличного процессора EXCEL позволяет облегчить выполнение расчетов и представить их в удобной для пользователя форме.
Финансовые функции EXCEL предназначены для проведения финансово-коммерческих расчетов по кредитам и займам, финансово-инвестиционного анализа, ценным бумагам.
Однако для ряда пользователей существуют трудности при использовании финансовых функций в среде EXCEL, поскольку синтаксис пакета использует иные обозначения основных понятий финансовых операций, нежели в классических расчетах. (Пикуза В., 2004)
На основной панели инструментов имеется кнопка Мастер функций, с помощью которой открывается диалоговое окно Диспетчера функций. Оно организовано по тематическому принципу. Выбрав в списке тематическую группу Финансовые, получите полный перечень списка имен функций, содержащихся в данной группе. Когда курсор стоит на имени функции, в нижней части окна приводится краткая характеристика функции и синтаксис. Вызов функции осуществляется двойным щелчком на ее имени или нажатием кнопки Далее в диалоговом окне Диспетчера функций. Диалоговое окно Ввода аргументов функции для каждой финансовой функции регламентировано по составу и формату значений перечня аргументов.
При работе с финансовыми функциями необходимо учитывать специфику задания значения аргументов:
· можно вводить как сами значения аргументов, так и ссылки на адреса ячеек;
· все расходы денежных средств (платежи) представляются отрицательными числами, а все поступления денежных средств – положительными числами;
· процентная ставка вводится с использованием знака %;
· все даты как аргументы функций имеют числовой формат.
Функции, обслуживающие расчеты по операциям наращения позволяют рассчитать будущую стоимость разовой суммы по простым и сложным процентам, а также будущее значение потока платежей, как на основе постоянной процентной ставки, так и на основе переменной процентной ставки. (К., 2001)
Методика использования финансовых функций требует соблюдения определенной технологии.
1.2 Технология работы и виды финансовых функций в области кредитования в MS Excel
Технология работы с финансовыми функциями на рабочих листах Excel в целом не отличается от работы с другими функциями:
1) подготовка исходных значений основных аргументов функции;
2) для расчета финансовой функции курсор устанавливается в нужную ячейку и вызывается с панели задач Диспетчер функций;
3) из появившегося списка выбираем в разделе финансовых функций необходимую;
4) вводим аргументы функций;
5) получаем результат.
К основным финансовым функциям в Excel в области расчетов кредитования удобно использовать: ПС(), ПЛТ(), ОСПЛТ(), ПРПЛТ(), КПЕР(), БС(), СТАВКА().
Назначение финансовых функций представлено в таблице 1.1. (Куприянова А.В., 2007)
Таблица 1.1 Назначение финансовых функций
| Название функции | Аргументы | Назначение |
| БС (ранее БЗ) | БС(ставка;кпер;плт;пс;[тип]) | Рассчитывает будущую стоимость периодических постоянных платежей и будущее значение вклада (или займа) на основе постоянной процентной ставки |
| ПС (ранее ПЗ) | ПС(ставка;кпер;плт;бс;[тип]) | Предназначена для расчета текущей стоимости, как единой суммы вклада (займа), так и будущих фиксированных периодических платежей |
| КПЕР | КПЕР(ставка;плт;пс;бс;[тип]) | Вычисляет количество периодов начисления процентов исходя из известных величин ставки, платежа, и суммы займа (вклада) |
| ПЛТ | ПЛТ(ставка;кпер;пс;бс;[тип]) | Позволяет рассчитать сумму постоянных периодических платежей, необходимых для равномерного погашения займа, при известных сумме займа, ставке процентов и сроках, на который он выдан |
| ПРПЛТ | ПРПЛТ(ставка;период;кпер;пс;бс) | Возвращает сумму платежей процентов по инвестиции за данный период, на основе постоянства сумм периодических платежей и постоянства процентной ставки |
| ОСПЛТ | ОСПЛТ(ставка;период;кпер;пс;бс) | Возвращает величину платежа в погашение основной суммы по инвестиции за данный период и на основании постоянства периодических платежей и процентной ставки. |
| СТАВКА | СТАВКА(кпер;плт;пс;бс;[тип]) | Вычисляет процентную ставку, которая в зависимости от условий операции может выступать либо в качестве цены, либо в качестве нормы ее рентабельности |
Как видно из таблицы, практически все функции содержат одинаковый набор аргументов:
Ставка – процентная ставка за период (норма доходности или цена заемных средств – r)
Кпер – срок (число периодов n) процедения операции.
Плт – выплата производимая каждый период и не меняющаяся за все время выплаты ренты.
Пс – это приведенная к текущему моменту стоимость, или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент ПС опущен, то он полагается равным 0. В этом случае должно быть указано значение аргумента Плт.
Бс – требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (например будущая стоимость займа равна 0)
[тип] – число 0 или 1, обозначающее когда должна производится выплата (1 – начало периода (обычная рента или пренумерандо), 0 – конец периода (постнумерандо)).
Как видно во многом функции перекрещиваются между собой, таким образом в решение одной финансовой задачи по расчету к примеру платежей по кредиту может использоваться несколько функций. (Мак-Федрис, 2006)
2. Практическая часть
2.1 Постановка задачи:
Необходимо на практике изучить финансовые функция для расчетов по кредитам: ПС(), БС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), КПЕР.
Для этого необходимо решить следующие задачи:
1) Рассчитать аннуитетные платежи по кредиту суммой 250 000 рублей, сроком на 1 год и под 17% годовых. Составить график платежей, с подробным описанием платежей непосредственно по кредиту, по процентам и оставшейся суммой платежа. (Использование функций ПС(), ПЛТ(), ПРПЛТ(), ОСПЛТ()).
2) Рассчитать сумму ежемесячного вложения под 10% годовых, которое через 15 лет составит сумму вклада в 50000 рублей. Выплата производится в начале периода. (Использование функции ПЛТ()).
3) Рассчитать сумму ежемесячного вложения под 10% годовых, которое через 15 лет составит сумму вклада 50000 рублей, при первоначальном взносе 1000 рублей. (Использование функции ПЛТ()).
4) Рассчитать величину вложений под 18 % годовых, которые будут приносить ежегодно в течение 5 лет 20 000 рублей. (Использование функции ПС()).
5) Рассчитать величину первоначальных вложений, под 15% годовых, которое через 10 лет принесет доход 100000 рублей, при условии внесении раз в год на счет 2000 рублей. (Использование функции ПС()).
6) Вычислить выплаты по процентам за первый месяц для трехгодичного займа в 100 000 рублей из расчета 10% годовых. (Использование функции ПРПЛТ()).
7) Вычислить доход за последний год от трехгодичного займа в 100000 рублей из расчета 10% годовых при ежегодных выплатах. (Использование функции ПРПЛТ()).
8) Вклад размером в 5000 рублей положен с 10.01.2010 по 03.04.2010 под 20% годовых. Найти величину капитала на 03.04.2010 при начислении простых процентов. (Использование функции БС()).
9) Определить сумму капитала, если изначально вложена сумма в размере 10 000 рублей, в банк на 3 года под 15% годовых, далее в течение всего периода раз в месяц вносится сумма 1000 рублей. Проценты начисляются раз в месяц, в начале. (Использование функции БС()).
10) Определить будущую стоимость капитала 15000 рублей, помещенных в банк под 18% годовых, сроком на 5 лет. Проценты начисляются раз в квартал. (Использование функции БС()).
11) Взята сумма в размере 90000 рублей сроком на 2 года под 15% годовых. Рассчитать сумму остаточных платежей для каждого года займа. (Использование функции ОСПЛТ()).
12) С кредитно-дебетовой карты взята сумма в размере 70000 рублей сроком на 3 года под 17% годовых. Рассчитать сумму остаточных платежей для каждого квартала займа, при условии, что конец периода на счету должна быть накоплена сумма 8000 рублей. (Использование функции ОСПЛТ()).
13) Рассчитать через сколько лет сумма вклада в размере 15 000 рублей достигнет 50000 рублей, при процентной ставке 15% годовых. (Использование функции КПЕР()).
14) Начиная с 30 лет каждый год на счет в банк вносится 1000 рублей. К какому возрасту человек станет миллионером, при условии, что процентная ставка равна 18% годовых. (Использование функции КПЕР()).
15) Рассчитать через сколько лет произойдет полное погашение займа размером 2500000 рублей, если выплаты 50000 рублей производятся в конце каждого квартала, а процентная ставка равна 17% годовых. (Использование функции КПЕР()).
2.2 Решение поставленных задач.
Для решение поставленных задач используются функции ПС(), БС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), КПЕР.
Алгоритм решения задач:
1) Внесение исходных данных;
2) Ввод функции с аргументными значениями;
3) Получение результата.
Задача 1.
Исходные данные:
| Сумма кредита | 250000 |
| Срок кредита, лет | 1 |
| Процент | 17% |
Для решения поставленной задачи использовались функции ЕСЛИ(), ПС(), ПЛТ(), ПРПЛТ(), ОСПЛТ(), СУММ().
Ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Процент/12;Срок кредита*12;Сумма кредита;;)
Аннуитет, платежи по кредиту, по процентам и остаток суммы задолженности рассчитывается по одинаковой формуле, с изменением № месяца, для которого производится расчет.
Формула расчета Аннуитета =ЕСЛИ(№ месяцаСрок кредита*12;0;Ежемесячный платеж)
Платежи по кредиту рассчитываются по формуле =ЕСЛИ(№ месяцаСрок кредита *12;0;ОСПЛТ(Процент/12;№месяца;Срок кредита*12;Сумма кредита)).
Процентные платежи рассчитываются по формуле ЕСЛИ(№месяцаСрок кредита*12;0;ПРПЛТ(Процент/12;№месяца;срок кредита*12;сумма кредита)).
Остаток суммы задолженности рассчитывается по формуле =ЕСЛИ(№месяцаСрок кредита*12;0;ПС(Процент/12;(Срок кредита *12)-№месяца; Ежемесячный платеж)).
Общая сумма процентов рассчитывается путем суммирований данных из столбца Проценты.
Мес. – рассчитывается путем умножения срока кредита (лет) на 12 месяцев.
Сумма аннуитета рассчитывается путем умножения суммы ежемесячного платежа на количество месяцев.
Результатом проведения вышеуказанных расчетом получаем график платежей.
Таблица 2.1 Решение задачи 1
| Сумма кредита | 250000 | ||||
| Срок кредита, лет | 1 | мес | Сумм. Аннуитет | Кредит | Проценты |
| Процент | 17% | 12 | -273 614,26р. | -250 000,00р. | -23 614,26р. |
| Ежемесячный платеж | -22 801,19р. | ||||
| № месяца | Аннуитет | Кредит | Проценты | Остаток СЗ | |
| 1 | -22 801,19р. | -19 259,52р. | -3 541,67р. | 230 740,48р. | |
| 2 | -22 801,19р. | -19 532,36р. | -3 268,82р. | 211 208,11р. | |
| 3 | -22 801,19р. | -19 809,07р. | -2 992,11р. | 191 399,04р. | |
| 4 | -22 801,19р. | -20 089,70р. | -2 711,49р. | 171 309,34р. | |
| 5 | -22 801,19р. | -20 374,31р. | -2 426,88р. | 150 935,03р. | |
| 6 | -22 801,19р. | -20 662,94р. | -2 138,25р. | 130 272,09р. | |
| 7 | -22 801,19р. | -20 955,67р. | -1 845,52р. | 109 316,43р. | |
| 8 | -22 801,19р. | -21 252,54р. | -1 548,65р. | 88 063,89р. | |
| 9 | -22 801,19р. | -21 553,62р. | -1 247,57р. | 66 510,27р. | |
| 10 | -22 801,19р. | -21 858,96р. | -942,23р. | 44 651,31р. | |
| 11 | -22 801,19р. | -22 168,63р. | -632,56р. | 22 482,68р. | |
| 12 | -22 801,19р. | -22 482,68р. | -318,50р. | 0,00р. |
Задача 2.
Исходные данные:
Годовая процентная ставка – 10%
Число лет хранения – 15
Необходимая величина сбережений – 50 000 руб.
Сумма ежемесячного платежа рассчитывается по формуле =ПЛТ(Процентная ставка/12;Число лет*12;;Необходимая сумма сбережений; момент выплаты) = ПЛТ(10%/12;15*12;;50000;1) = -119,64 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
При изменении момента выплаты на конец периода сумма несколько измениться = ПЛТ(10%/12;15*12;;50000;0) = -120,64 руб.
Задача 3.
Исходные данные:
Годовая процентная ставка – 10%
Число лет хранения – 15 лет
Необходимая величина сбережений – 50 000 руб.
Начальный взнос – 10 000 руб.
Сумма ежемесячного платежа рассчитывается по формуле =ПЛТ(Процент/12;Число лет*12;начальный взнос;необходимая величина сбережений)= ПЛТ(10%/12;15*12;10000;50000)=-228,10 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 4.
Исходные данные:
Ежегодный доход – 20 000 руб.
Процентная ставка – 18%
Число лет – 5
Величина вложений рассчитывается с помощью формулы =ПС(Ставка;Число лет;Ежегодный доход)=ПС(18%;5;20000)=-62 543,42 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 5.
Исходные данные:
Процентная ставка – 15%
Число лет – 10
Итоговый доход - 100 000 руб.
Ежегодный взнос – 2000 руб
Величина первоначальных вложений рассчитывается по формуле =ПС(Процент;Число лет;Ежегодный взнос;Итоговый доход)=ПС(15%;10;-2000;100000)=-14 680,933 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 6.
Исходные данные:
Процентная ставка – 10%
Месяц – 1
Срок кредита – 3 года
Сумма кредита – 100 000 руб.
Выплаты по процентам рассчитываются по формуле =ПРПЛТ(процентная ставка/12;Период (Месяц);Срок кредита*12;сумма кредита)= ПРПЛТ(10%/12;1;3*12;100000)= -833,33 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
Задача 7.
Исходные данные:
Процентная ставка – 10%
Срок кредита – 3 года
Момент дохода – 3-й год
Сумма кредита – 100000 руб.
Выплаты по процентам рассчитываются по формуле =ПРПЛТ(Ставка;Момент дохода;Срок кредита; Сумма кредита)=ПРПЛТ(10%;3;3;100000) = -3 655,59 руб.
Отрицательная сумма получилась, потому, что данную сумму необходимо платить.
В случае если не указан, хотя бы один из обязательных аргументов расчет будет невозможен.
Задача 8.
Исходные данные:
Процентная ставка – 30%
Вклад – 5000 рублей
Дата открытия вклада – 10.01.2010
Дата закрытия – 04.03.2010
Количество расчетных периодов – 1
Расчет суммы капитала производится через формулу =БС((Дата закрытия-Дата открытия)/360*Процент;Количество периодов;;вклад) = =БС((53)/360*30%;1;;-5000)= 5220,833.
В данном случае заполнение аргумента Плт не обязательно, т.к. заполняется необязательный аргумент Пс.
Задача 9.
Исходные данные:
Процентная ставка – 15%
Вклад – 10 000 руб.
Количество периодов – 3 года
Ежемесячные вклады – 1000 рублей.
Расчет накопленного капитала производится через формулу =БС(Процентная ставка/12;Количество периодов*12;Ежемесячный вклад; Первоначальный вклад; момент выплаты)= БС(15%/12;3*12;-1000;-10000;1)= 61318,89
Задача 10.
Исходные данные:
Процентная ставка – 18%
Вклад – 15 000 руб.
Количество периодов – 5 лет.
Расчет накопленного капитала производится через формулу =БС(Процентная ставка/4;Количество периодов*4;;вклад)= БС(18%/4;5*4;;-15000)=36 175,71 руб.
Задача 11.
Исходные данные:
Ссуда – 90 000 руб.
Процентная ставка – 15% годовых
Срок 2 года.
Расчет суммы остаточных платежей для каждого года производится по формуле =ОСПЛТ(Процентная ставка;Период;Ссуда;Срок кредита).
Для каждого периода в расчетной формуле меняется номер периода.
Таким образом для 1-го года остаточная сумма платежа равна =ОСПЛТ(15%;1;90000;2)=-41 860,47 руб.
Для 2-го года:
=ОСПЛТ(15;2;90000;2)=-48 139,53 руб.
Суммы отрицательны, т.к. их необходимо платить.
Задача 12.
Исходные данные:
Ссуда – 70 000 рублей
Процентная ставка – 17%
Срок кредита – 3 года
Накопления на счету – 8000 рублей.
Расчет платежей производится по формуле = ОСПЛТпроцентная ставка/4;период;срок кредита *4;ссуда;накопления).
Для каждого квартала изменяется только № периода. Результатом вычислений является таблица 2.2
Таблица 2.2 – Результаты расчетов по задаче 12
| Ссуда | 70 000,00р. |
| Процентная ставка | 17% |
| Срок кредита | 3 |
| Период | Платеж |
| 1 | -5 117,07р. |
| 2 | -5 334,55р. |
| 3 | -5 561,27р. |
| 4 | -5 797,62р. |
| 5 | -6 044,02р. |
| 6 | -6 300,89р. |
| 7 | -6 568,68р. |
| 8 | -6 847,85р. |
| 9 | -7 138,88р. |
| 10 | -7 442,28р. |
| 11 | -7 758,58р. |
| 12 | -8 088,32р. |
| Итого | -78 000,00р. |
| Накопления | 8 000,00р. |
Задача 13.
Исходные данные:
Начальный платеж – 15 000 руб.
Необходимая сумма накоплений – 50 000 руб.
Процентная ставка – 15:
Срок накопления необходимых денежных средств рассчитывается по формуле =КПЕР(Процентная ставка;; Первоначальный платеж; Необходимая сумма)= КПЕР(15;;-15000;50000)=8,61 лет.
Не обязательно заполнение аргумента Плт при заполненном аргументе Бс.
Задача 14.
Исходные данные:
Ежемесячный платеж – 1000 рублей
Процентная ставка – 18% годовых
Необходимая сумма на счету – 1 000 000 руб.
Срок накопления необходимых денежных средств рассчитывается через формулу =КПЕР(процентная ставка/12;-ежемесячный платеж;; необходимая сумма на счету; момент выплаты)/12= КПЕР(18%/12;-1000;;1000000;0)/12=15,52 лет.
Таким образом возраст человека будет составлять = 30+15,52 = 45,52 лет.
Не обязательно заполнение аргумента Пс при заполненном аргументе Бс.
Задача 15.
Исходные данные:
Займ – 250 000 руб.
Ежеквартальные выплаты – 50 000 руб.
Процентная ставка – 17% годовых.
Срок погашение займа рассчитывается через формулу =КПЕР(процентная ставка/4;ежеквартальные выплаты; займ)/4 = КПЕР(17%/4;-50000;250000)/4=1,43 года.
Выводы:
Курсовой проект основывается на условных примерах, которые не отражает всех особенностей начисления кредитных платежей, вычисления сроков и т.п. Однако выполнение данного курсового проекта позволило ознакомиться и научиться применять все необходимые для современного финансиста-экономиста инструменты обработки финансовой информации.
В работе рассмотрены теоретические и практические основы использования финансовых функций, их возможности и способы использования
В практической части работы представлены 15 задач по использованию финансовых функций Excel при расчетах по кредитам. Рассмотрены различные варианты задач, а также способы заполнения аргументов функций (обязательные и необязательные аргументы).
Список литературы:
1) К., Карлберг (2001). Бизнес-анализ с помощью MS EXcel 2000. стр. 250.
2) Куприянова А.В., М. М. (2007). Вычисления и расчеты в Excel 2003 изд.2 Компьютерная шпаргалка. стр. 80.
3) Л.А., Левин (2006). Финансовая математика в MS EXCEL - Учебное-методическое пособие. стр. 111-5.
4) Мак-Федрис, П. (2006). Формулы и функции в Microsoft Excel 2003. стр. 576.
5) Пикуза В., Г. А. (2004). Экономические и финансовые расчеты в Excel. Самоучитель. стр. 397.