Финансовые вычисления в MS Excel с помощью встроенных функций.

Лабораторная работа № 12

Финансовые вычисления в MS Excel с помощью встроенных функций.

1. Создайте на диске D: папку LabExcel или откройте её, если она уже есть.

2. Запустите программу «Microsoft Excel» (меню Пуск/Программы/Microsoft Excel).

3. Создайте новую книгу с именем ФинФормулы2 и сохраните ее в папке LabExcel.

4. Создайте новый лист и назовите его "ПроцПЛТ". На этом листе составьте амортизационный план погашения кредита, если сумма взятого кредита – (2400+ХХ) н.д.е., срок, на который был взят кредит – 6 месяцев, процентная ставка – 15 % (ХХ - номер студента в группе), дата выдачи кредита - текущая дата.

Для этого составляем таблицу, как показано на рис.1:

Рис.1 – Таблица для расчета амортизационного плана погашения кредита.

Величина ежемесячных выплат по кредиту (столбец D) вычисляется по формуле:

=

или (в нашем случае кредит выдавался на 6 месяцев)

=$G$2/6.

Величина долга, оставшегося после выплаты очередного платежа (столбец В), может быть вычислена по формуле:

= Сумма оставшегося кредита – величина ежемесячных выплат по кредиту

Величина процентного платежа меняется в зависимости от количества прошедших месяцев с момента взятия кредита, и может быть вычислена так как в Лабораторной работе №11 (по формулам процентного платежа), так и встроенными средствами MS Excel, в частности, с помощью функции ПРПЛТ (в версии MS Excel 97 - ПЛПРОЦ).

Функция ПРПЛТ (ставка; период; кпер; нз; бз; тип) возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставки. Здесь:

Ставка – процентная ставка за период, например, для ежемесячных выплат при годовой процентной ставке 15%.

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

Кпер – общее число периодов выплат, например, 6, если кредит был взят на 6 месяцев.

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

Бз – будущая стоимость или баланс наличности, который нужно достичь после последней выплаты. Для кредита это – 0 (т.е. ничего не нужно платить кредитору).

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

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

Эта функция применяется таким образом:

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

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

=ПРПЛТ($I$2/12;A4;6;-$H$2;0),

где:

в $I$2 – годовой процент, поделенный на 12, т.к. выплата процентов выполняется ежемесячно,

в A4 – номер месяца (3, в данном случае),

6 – количество периодов выплат (месяцев, в нашем случае, т.к. годовой процент был поделен на 12, $I$2/12),

в $H$2 – сумма кредита (перед $H$2 стоит знак «минус», т.к. мы должны банку эту сумму),

0 означает, что выплаты проводятся в конце периода (в нашем случае, в последний день месяца).

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

Функция ПЛТ(ставка;кпер;нз;бз;тип) вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки. Эта величина равна среднему взносу за один период, и одинакова для всех периодов, т.к. учитывает процентные платежи за все число периодов выплат. Смысл аргументов ПЛТ такой же, что и для ПРПЛТ.

6. Сохраните книгу и пригласите преподавателя.

7. Создайте новый лист и назовите его “КредитПарам”.

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

Предположим, на вашей кредитной карте установлена процентная ставка 12 % годовых на остаток. Сейчас на карте отрицательный остаток: -1000 грн. У вас появилась возможность равномерно погашать этот остаток равными долями, например, по 100 грн. в месяц, и в дальнейшем продолжать пополнять карту так же на сумму 100 грн в месяц. После скольких платежей на вашей карте появится сумма 5000 грн?

Для решения подобной задачи построим таблицу как на Рис. 2:

Рис. 2 – Таблица для расчета количества периодов выплат

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

=КПЕР(ежемесячная процентная ставка; выплата за 1 период; текущая стоимость; будущая стоимость;тип платежа)

Или:

=КПЕР(A2; A3; A4; A5; 1),

где тип платежа выбран равным 1, что означает, что платеж будет выполняться в начале месяца

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

Рис. 3 – Таблица для расчета процентной ставки

Для этого используем функцию СТАВКА:

= СТАВКА(срок займа в годах; ежемесячная сумма платежа;сумма займа)

или

=СТАВКА(A2*12; A3; A4)

Так мы получаем ежемесячную процентную ставку (в нашем случае – 1 %) Чтоб узнать годовую процентную ставку, домножим на 12. Ответ показан в ячейке A7.

Последняя задача из этой серии связана с оценкой будущего размера вклада.

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

На рис. 4 показана таблица для расчета размера вклада через 1 месяц после размещения 500 грн. на депозите с ежемесячным платежом 45 грн.

Рис. 4 – Расчет будущего размера вклада

Функция, которая вычислит будущий размер вклада, называется БС (будущая стоимость):

=БС(годовая процентная ставка/12; количество уже совершенных платежей; размер одного платежа; текущее значение вклада; тип платежа)

или

=БС(A2/12; A3; A4; A5; A6)

8. Сохраните книгу и пригласите преподавателя.

9. Создайте новый лист и назовите его “ИнвестПарам”.

На этом листе мы будем оценивать эффективность инвестиций по сравнению с депозитами.

Второй класс задач, для которых в MS Excel разработаны встроенные функции, связан с оценкой эффективности инвестиции.

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

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

Рис. 5 – Оценка прибыльности инвестиции

Как видим из Рис. 5, инвестиции могут быть невыгодными до определенного времени.

Чтобы оценить это, воспользуемся функцией ВСД (внутренняя ставка доходности):

=ВСД(диапазон выплат по инвестиции)

Или, для оценки эффективности инвестции после 4-го года

=ВСД(A2:A6),

после 5-го года

=ВСД(A2:A7)

Т.е. инвестиция после 4-го года будет еще убыточной, и только после пяти лет инвестиция принесет прибыль.

Для того, чтобы явно увидеть насколько в денежном выражении отличается инвестиция с известным планом возврата средств от депозита с известной процентной ставкой, применяется функция ЧПС (чистая приведенная стоимость (см. рис. 6):

Рис. 6 – Чистая приведенная стоимость инвестиции по сравнению с депозитом

Функция имеет такие параметры:

=ЧПС(ставка; диапазон выплат по инвестиции)

или

=ЧПС(А2; А3:А8)

10. Сохраните книгу и пригласите преподавателя.

Дата добавления: 2015-11-04