Решение задач на определение платежей по процентам при помощи функции плпроц

Для решения задач на определение платежей по процентам в табличном процессоре существует функция ПЛПРОЦ из раздела "финансовые".

ПЛПРОЦ - Возвращает платежи по процентам за данный период на основе периодических постоянных выплат и постоянной процентной ставке.

Синтаксис:

ПЛПРОЦ(ставка;период;кпер;нз;бз;тип), где

Ставка - это процентная ставка за период.

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

Кпер - это общее число периодов выплат годовой ренты.

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

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

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

Итоговая отчетность:

Проиллюстрировать созданную книгу. Каждая задача расположена на отдельном листе. Задачи для каждого студента индивидуальны.

Контрольные вопросы:

1. Синтаксис функции БЗ. Привести пример задачи, в которой может быть использована эта функция.

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

3. Синтаксис функции ППЛАТ. Привести пример задачи, в которой может быть использована эта функция.

Синтаксис функции ПЛПРОЦ. Привести пример задачи, в которой может быть использована эта функция.
Практическое занятие №18-19.

Информационная технология таблиц подстановки.

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

 

План занятия:

1. Таблица подстановок для одного параметра.

2. Таблица подстановок для двух параметров.

 

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

Таблица данных для одной переменной

Предположим, что вы рассматриваете возможность покупки дома, для чего вам придется взять ссуду под закладную в $200 000 на 30 лет, и вы хотите вычислить месячные выплаты по этой ссуде для нескольких процентных ставок. Эту информацию может предоставить таблица данных для одной переменной.

Чтобы создать такую таблицу, выполните следующие действия:

• В новом рабочем листе введите в ячейку В2 процентную ставку – 6%, в ячейку В3 срок ссуды – 30 и в ячейку В4 размер ссуды – 200000.
• В ячейки В7:В12 введите интересующие вас процентные ставки 6%, 6,5%, 7%, 7,5%, 8% и 8,5%. Этот диапазон называется входным диапазоном, так как он содержит входные значения, которые необходимо проверить.
• Затем введите формулу, в которой используется входная переменная. В данном случае введите в ячейку С6 формулу

=ППЛАТ(В2/12;В3*12;В4)

где В2/12 — месячная процентная ставка, В3*12 — срок ссуды в месяцах и В4 — размер ссуды. Результат показан на рисунке

• Выделите диапазон таблицы данных — минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона. В данном случае выделите диапазон В6:С12.
• 
  В меню Данные выберите команду Таблица подстановки. В окне диалога Таблица подстановки, показанном на рисунке,

 

задайте местонахождение входной ячейки в поле Подставлять значения по столбцам в или в поле Подставлять значения по строкам в. Входная ячейка — это ячейка-метка, на которую ссылается формула таблицы данных, в данном случае В2. Чтобы таблица данных заполнялась правильно, вы должны ввести ссылку на входную ячейку в нужное поле. Если входные значения расположены в строке, введите ссылку на входную ячейку в поле Подставлять значения по столбцам в. Если значения во входном диапазоне расположены в столбце, используйте поле Подставлять значения по строкам в. В данном примере входные значения расположены в столбце, поэтому введите $В$2 в поле Подставлять значения по строкам в или щелкните на этом поле и затем — на ячейке А2.

• Нажмите кнопку ОК. Excel поместит результаты вычисления формулы для каждого входного значения в свободных ячейках диапазона таблицы данных. В нашем примере Excel поместит шесть результатов в диапазоне С7:С12, как показано на рисунке

При создании этой таблицы данных Excel ввел формулу массива

{=ТАБЛИЦА(;В2)}

в каждую ячейку диапазона С7:С12, который мы будем называть диапазоном результатов. В нашей таблице эта формула вычисляет значения функции ППЛАТ для каждой процентной ставки из столбца В. Например, формула в ячейке С9 вычисляет размер выплаты при ставке, равной 7 процентам. Функция ТАБЛИЦА, используемая в формуле, имеет следующий синтаксис:

=ТАБЛИЦА(входная ячейка для строки; входная ячейка для столбца)

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

После построения таблицы можно изменить формулу таблицы данных или любые значения во входном диапазоне для создания другого множества результатов. Например, предположим, что для покупки дома вы решили занять только $185 000. Если вы измените данные в ячейке В4 на 185000 значения в выходном диапазоне изменятся, как показано на рисунке.

Таблица данных для двух переменных

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

Предположим, что вы хотите создать таблицу данных, которая вычисляет месячные выплаты по ссуде под закладную в $200 000, но на этот раз нужно изменять не только процентную ставку, а также и срок ссуды. Требуется узнать месячные выплаты для процентной ставки 6, 6,5, 7, 7,5, 8 и 8,5% при сроках 15, 20, 25 и 30 лет.

Чтобы создать такую таблицу, выполните следующие действия:

• В новом рабочем листе введите в ячейку В2 процентную ставку – 6%, в ячейку В3 срок ссуды – 30 и в ячейку В4 размер ссуды – 200000.
• Введите первое множество входных значений в столбец. Как и раньше, введите шесть процентных ставок в ячейки В7:В12.
• Введите второе множество входных значений в строке, начинающейся выше и правее на одну ячейку от начала первого диапазона. В данном случае введите в ячейки С6:F6 сроки ссуды: 15, 20, 25 и 30.
• Теперь можно создать формулу для таблицы. Поскольку это таблица с двумя переменными, формула должна быть введена в ячейку на пересечении строки и столбца, содержащих два множества входных значений, то есть в ячейку В6. Хотя в таблицу данных для одной переменной можно включить любое число формул, в таблице с двумя переменными допускается использование только одной формулы. Формула для таблицы в нашем примере будет иметь следующий вид:

=ППЛАТ(В2/12;В3*12;В4)

В ячейку С5 введите надпись Кол-во месяцев :, а в ячейку А7 — Ставка :.

На рисунке представлен результат, полученный на данный момент.

 

• Выделите диапазон таблицы данных — минимальный прямоугольный блок, включающий в себя все входные значения и формулу. В данном случае выделите диапазон В6:F12.
• Выберите в меню Данные команду Таблица подстановки и задайте входные ячейки. Поскольку это таблица с двумя переменными, вы должны задать две входные ячейки: одну для первого множества входных значений, другую — для второго. В этом примере введите ссылку для первой входной ячейки $В$3 в поле Подставлять значения по столбцам в и затем введите ссылку для второй входной ячейки $В$2 в поле Подставлять значения по строкам в.
• 
  Нажмите Enter или кнопку ОК для вычисления таблицы. Результат представлен на рисунке.

 

Как и в предыдущих примерах, Ехсеl вводит формулы массива ТАБЛИЦА в диапазоне результатов С7:F12. Поскольку эта таблица имеет две переменных, формула ТАБЛИЦА содержит две ссылки:

{=ТАБЛИЦА(В3;В2)}

Значения в диапазоне результатов — это месячные выплаты по ссуде для каждой комбинации процентных ставок и сроков. Например, значение в ячейке D10 (-1611,19) — это выплата, необходимая для погашения ссуды в $200 000 за 20 лет при процентной ставке 7,5%.

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

Самостоятельная работа

Аналогично решите задачу с помощью функции НПЗ и таблицы подстановки для двух переменных.

Задача. В конце года капиталовложения по проекту составят около 150 тыс.гр. Ожидается, что за последующие 4 года проект принесет доходы: 62,69,75,79 тыс.гр. Рассчитайте чистую текущую стоимость проекта для различных норм дисконтирования (от 4% до 16% с шагом 4%) и объемов капиталовложений (от 200 тыс.гр. до 400 тыс гр. с шагом 50 тыс.гр.).

Контрольные вопросы:

1. Использование таблиц данных для одной переменной.

2. Использование таблиц данных для двух переменных.

Вывод: данная практическая работа позволяет использовать Таблицы подстановок при решении задач обработки экономической информации.

Практическое занятие №20.