Указание. Упражнения 2 и 3 выполните на новых листах.
ЕЗ/Е5
6. Выполните форматирование ячейки Е6 в процентном формате.
7. Введите в ячейку Е7, используя Мастер функций, формулу для вычисления значения периодического платежа
=ПЛТ(Е6; Е5; Е4)
Так как ПЛТ - финансовая функция, то ее результат будет представлен как денежная единица. Так как ПЛТ - финансовая функция, определяющая убыток, то ее значение будет представлено отрицательным числом.
8. Сохраните текущее состояние таблицы в личной папке, например, под именем credit (будет создан файл credit.xls).
9. Введите в ячейки В10:Е10 заголовки расчетной таблицы: Периоды выплат, Плата по процентам, Основной платеж, Остаток.
10. Отформатируйте заголовки в ячейках В10:Е10.
11. В диапазоне ячеек В11:В22 постройте числовой ряд со значениями от 1 до 12.
12. Введите в ячейку С11, используя Мастер функций, формулу для вычисления платы по процентам за 1-й месяц (ячейка В11):
=ПРПЛТ($Е$6; В11; $Е$5; $Е$4)
Здесь первый аргумент Норма — процентная ставка за период, второй аргумент Период - номер периода, третий аргумент Кпер - число периодов, четвертый аргумент Тс (текущая стоимость) - сумма кредита.
13. Выполните копирование ячейки С11 на диапазон ячеек С12:С22. Так как финансовая функция ПРПЛТ определяет убыток, то в диапазоне С11:С22 будут отображены отрицательные числа.
14. Введите в ячейку D11 формулу для вычисления значения величины погашения кредита за первый месяц
=ОСПЛТ($Е$6; В11; $Е$5; $Е$4)
Параметры функции ОСПЛТ имеют тот же смысл, что и для функции ПРПЛТ.
15. Выполните копирование ячейки D11 на диапазон D12:D22. Функция ОСПЛТ, так же как и ПРПЛТ, определяет убыток для клиента, поэтому в диапазоне D11:D22 тоже будут отображены отрицательные числа.
16. Введите в ячейку Е11 формулу для вычисления невыплаченной части кредита (остатка) для первого периода:
=$E$4+D11
Остаток вычисляется как разность между начальной суммой кредита в ячейке Е4 и величиной из ячейки D11, взятой со знаком минус.
Вычисление остатка для первого периода отличается от всех последующих тем, что в нем участвуют значение из исходных данных и значение, вычисляемое в таблице.
17. Введите в ячейку Е12 формулу вычисления остатка на конец второго периода, который складывается из значения в ячейке Е11 (остатка на конец первого периода) и значения в ячейке D12 (выплачено за второй период):
=Е11+D12
18. Скопируйте содержимое ячейки Е12 на диапазон ячеек Е13:Е22. Внимание! В ячейке Е22 должно получиться нулевое значение.
19. Выполните контрольное суммирование диапазона ячеек D11:D22:
· Выделите ячейку D24 и введите в нее формулу
=СУММ(D11:D22)
· Сравните значение в ячейке D24 со значением в ячейке Е4. Должно получиться значение, равное сумме займа, но с отрицательным знаком.
· Удалите содержимое ячейки D24.
20. Переименуйте лист с таблицей, назвав его Погашение кредита вместо Лист1.
21. Для иллюстрации процесса погашения кредита постройте диаграмму с областями (наложенную), используя данные блока B10:D22. При равномерном погашении займа периодический платеж состоит из платы по процентам по непогашенной части кредита и основного платежа (выплаты задолженности). Так как непогашенная часть займа (остаток) уменьшается со временем, то уменьшается и доля платежей по процентам, а доля выплаты задолженности растет.
Упражнение 1.Постройте диаграмму типа График для графического представления невыплаченной части кредита (остатка) по периодам.
Указание. Упражнения 2 и 3 выполните на новых листах.
Упражнение2. Представьте план погашения кредита, предполагая, что выплаты будут производиться а) один раз в квартал, б) один раз в полгода.
Упражнение3. Решите ЗАДАЧУ, исходя из предположения, что кредит предоставляется на два года и выплаты по нему будут производиться а) ежемесячно, б) один раз в квартал.
Указание. Для расширения расчетной таблицы вниз (при увеличении числа периодов выплат) достаточно выделить последнюю строку и скопировать ее, протащив маркер заполнения вниз до нужного числа периодов выплат.
Помните о контроле: остаток равен нулю в последнем периоде и сумма выплат по кредиту за все периоды равна сумме займа.