Практическая работа №3. Финансовые функции в MS Excel 2010. Определение платежа по сложным процентам, выплат основной суммы и суммы ежегодного платежа.

Цель работы. Решение экономических задач определения платежа по сложным процентам, выплат основной суммы и суммы ежегодного платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки в табличном процессоре MS Excel 2010.

Ход работы:

1. Изучить теоретическую часть.

2. Выполнить задание практической части.

3. Представить файл для проверки преподавателю.

Теоретическая часть Определение платежа по сложным процентам

Функция ПРПЛТ служит для расчета суммы платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

= ПРПЛТ (Ставка; Период; Кпер; Пс; Бс),

где Ставка – процентная ставка за период;

Период – период, для которого нужно определить сумму выплаты; должен быть в диапазоне от 1 до Кпер;

Кпер – общее число периодов выплат инвестиции;

Пс – приведенная (нынешняя) стоимость, или общая сумма, равноценная на данный момент серии будущих выплат;

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

Определение выплат основной суммы

Функция ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставке.

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

Аргументы функции ОСПЛТ аналогичны аргументам фукции ПРПЛТ.

Определение суммы ежегодного платежа

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

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

Функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

= ПЛТ (Ставка; Кпер; Пс; Бс; Тип),

где Ставка – процентная ставка за период займа;

Кпер – общее число периодов выплат по займу;

Пс – приведенная (нынешняя) стоимость, или общая сумма, на настоящий момент равноценная серии будущих выплат;

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

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

Определение суммы платежей по процентам за весь период

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

= ОБЩПЛАТ (Ставка; Кол_пер; Нз; Нач_период; Кон_период; Тип),

где Ставка – процентная ставка;

Кпер – общее число периодов выплат;

Нз – текущая стоимость инвестиций;

Нач_период – номер первого периода, включенного в вычисления;

Кон_период – номер последнего периода, включенного в вычисления;

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

Практическая часть

Задание 1. Рассчитать платеж по сложным процентам по шестигодичному займу в 10000 руб. из расчета 10 % годовых. Какую часть основного платежа занимают выплаты основной суммы, а какую выплаты процентов. Построить гистограмму соотношения выплат по процентам и основной суммы на протяжении всего периода выплат.

Для этого выполните действия:

1. Запустите Excel. Сохраните файл под именем «Ваша фамилия3», например, Иванов3.

2. Переименуйте Лист1, дав ему имя Задание1.

3. Составьте таблицу, представленну на рис. 3.1.

Рис. 3.1 Таблица для рассчета платежа по сложным процентам

4. Рассчитайте размер ежегодного платежа (столбец С). Установите курсор в ячейку С4. Осуществите вызов финансовой функции ПЛТ. В диалоговое окно для ввода аргументов введите: Ставка – ежегодный процент – 10 %; Кпер – 6, так как процент начисляется ежегодно; ПС – сумма займа -10000 руб. (значение представляем отрицательным числом, так как займ клиента для банка является расходом денежных средств) (рис. 3.2).

Рис. 3.2 Окно Аргументы функции ПЛТ

Таким образом, функция для вычисления ежегодного платежа имеет вид: = ПЛТ (10 %; 6; -10000; 0; 0).

5. Нажмите кнопку ОК для запуска расчета значения встроенной функции ПЛТ.

6. Скопируется формулы из С4 в ячейки С5:С9. Значение платежа на протяжении всего периода выплат остается неизменным 2296,07 руб.

7. Рассчитайте размер выплат основной суммы (столбец D). Установите курсор в ячейку D4. Осуществите вызов финансовой функции ОСПЛТ. В диалоговое окно введите значения аргументов (рис. 3.3). При расчете следует обратить внимание на разницу аргументов функции ОСПЛТ: Кпер – общее число периодов выплат (в задании – 6 лет) и Период, для которого нужно определить сумму выплаты (для первого года 1, для второго 2 и т.д).

Рис. 3.3 Окно Аргументы функции ОСПЛТ

8. Нажмите кнопку ОК для запуска расчета значения встроенной функции ОСПЛТ. Скопируется формулы из D4 в ячейки D5:D9.

9. Рассчитайте размер выплат по процентам (столбец E). Установите курсор в ячейку E4. Осуществите вызов финансовой функции ПРПЛТ. В диалоговое окно введите значения аргументов (рис. 3.4).

Рис. 3.4 Окно Аргументы функции ПРПЛТ

10. Выполните проверку – сумма выплат по процентам и выплат основной суммы для каждого года (столбец F) составляет ежегодный платеж (столбец C). Для этого в ячейку F4 вставьте =D4+E4 и скопируйте ее в ячейки F5: F9.

11. Для нахождения баланса на конец года (столбец G) найдите разницу баланса на начало года (столбец B) и выплатой основной суммы (столбец D). В ячейку G4 вставьте =B4–D4 (получите значение 8703,93руб.) и скопируйте ее в ячейки G5: G9.

12. Так как выплата процентов и основной суммы рассчитываются из остатка суммы, то баланс на конец первого года служит балансом на начало второго года и так далее. Для заполнения ячейки баланса на начало второго года в ячейку B5 вставьте формулу = G4 и скопируйте ее в ячейки B6: B9.

13. Введите в ячейку B10 – «Итог:», в C10 формулу =СУММ(C4:C9). Cкопируйте формулу из ячейки C10 в D10:F10. Произведите заливку ячеек таблицы как показано на рис. 3.5.

Рис. 3.5 Рассчет платежа по сложным процентам

Платеж по сложным процентам по шестигодичному займу в 10000 руб. из расчета 10 % годовых составляет 13776, 44 руб., где выплаты основной суммы занимают 10000 руб., а выплаты процентов – 3776,44 руб.

14. Постройте гистограмму по диапазону ячеек D3:E9, которая наглядно отразит соотношение по годам выплат основной суммы и выплат по процентам (рис. 3.6).

Рис. 3.6 Диаграмма соотношения выплат по процентам и основной суммы

Задание 2. Покупка компьютера осуществялется в кредит и необходимо полностью рассчитаться за него в течении 1 года или быстрее. Сумма кредита – 25000 руб.; срок кредитования – до 12 месяцев; первоначальный взнос и процентная ставка годовых, в процентах от суммы кредита – заданы кредитными планами (рис. 3.7). Определить: величину первоначального взноса; сумму переплаты за пользование кредитом; ежемесячный платеж по кредиту.

Для этого выполните действия:

1. Прейдите на Лист2, дав ему имя Задание2.

2. Составьте таблицу, представленну на рис. 3.7.

Рис. 3.7 Таблица Выбор кредита

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

Сумма первоначального взноса (Столбец G):

Сумма_первоначального_взноса = Стоимость * Первоначальный_взнос

Для ячейки G4 формула =C4 * E4

Сумма кредита (Столбец H):

Сумма_кредита = Стоимость – Сумма_первоначального_взноса

Для ячейки H4 формула =C4 – G4

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

Для ячейки I4 формула = - ПЛТ(F4/12;D4;H4;0;0)

Переплата (Столбец J):

Переплата = Ежемесячный_платеж * Срок_кредита – Сумма_кредита

Для ячейки J4 формула =I4 * D4 – H4

Переплата (Столбец K):

Общая_сумма = Сумма_кредита + Переплата

Для ячейки K4 формула =H4 + J4

Результаты вычислений представлены на рис. 3.8. Они позволяют выбрать приемлемый вариант кредитования, исходя из: денежных средст, которые покупатель готов внести в качестве первоначального взноса; величины ежемесячного платежа; переплаты, которую покупатель готов отдать банку за пользование кредитом; срока кредитования.

Рис. 3.8 Таблица Выбор кредита (результаты расчетов)

Задание 3. Банк выдал кредит в сумме 650 000 на шесть лет под 9% годовых. Погашение кредита должно производиться равными ежегодными выплатами в конце каждого года, включающими погашение основного долга и процентные платежи. Начисление процентов производится раз в год. Определить ежегодные выплаты по займу, платежи по процентам за третий год, основные платежи за четвертый год, сумму платежей по процентам за весь период. Ответ округлите до копеек.

Для этого выполните действия:

1. Прейдите на Лист2, дав ему имя Задание3.

2. Составьте таблицу, представленну на рис. 3.9.

Рис. 3.9 Таблица для рассчета платежей по кредиту

3. Рассчитайте размер ежегодных выплат по займу. Установите курсор в ячейку B4. Осуществите вызов финансовой функции ПЛТ. В диалоговое окно введите значения аргументов (рис. 3.10). Нажмите кнопку ОК для запуска расчета значения встроенной функции ПЛТ.

Рис. 3.10 Окно Аргументы функции ПЛТ

4. Рассчитайте размер платежей по процентам за третий год. Установите курсор в ячейку B5. Осуществите вызов финансовой функции ПРПЛТ. В диалоговое окно введите значения аргументов (рис. 3.11).

Рис. 3.11 Окно Аргументы функции ПРПЛТ

5. Рассчитайте размер основных платежей за четвертый год. Установите курсор в ячейку B6. Осуществите вызов финансовой функции ОСПЛТ. В диалоговое окно введите значения аргументов (рис. 3.12).

Рис. 3.12 Окно Аргументы функции ОСПЛТ

6. Рассчитайте сумму платежей по процентам за весь период. В ячейку B7 введите формулу =ОБЩПЛАТ(B2;B3;B1;1;6;0).

Таким образом, получено:

• ежегодные выплаты по займу составят 14 489, 79 руб.;

• платежи по процентам за третий год составят 4 224,86 руб.;

• основные платежи за четвертый год составят 11 188,77 руб.;

• сумма платежей по процентам за весь период 21 938,72 руб.

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

7. Сохраните файл и закройте MS Excel. Представьте файл для проверки преподавателю.