Microsoft Excel

Microsoft Excel

Тема: Подбор параметра. Организация обратного расчета  в Excel

Цель занятия: Изучение технологии подбора параметра при обратных расчетах.

Ход работы:

Запустить Microsoft Excel и создайте в своей папке файл  Подбор параметра_1.xlsx

ЗАДАЧА 1. КРЕДИТ НА КВАРТИРУ

1. Оформите на Листе1 таблицу как показано на рисунке:

·                     Для расчета ежемесячного платежа используется функция  =ПЛТ(Ставка;Кпер;Пс),

где

Ставка – ежемесячная процентная ставка по кредиту (в нашей формуле это В5/12)

Кпер – количество периодов (месяцев) погашения (В4)

Пс – сумма кредита (В3)

2. Создайте две копии для Листа 1

3. Эти три листа назовите соответственно: Кредит_1, Кредит_2, Кредит_3

Задание 1. (подбор параметра для вычисления суммы кредита)

Кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11000 руб. Какова максимальная сумма кредита?

1.                   Введите новые данные на листе Кредит_1:

·                     в ячейку В4 – число 180 (15 лет, умноженных на 12 месяцев)

·                     в ячейку В5 – 5,75%

·                     перейдите на страницу Ленты – Данные

·                     В разделе Работа с данными разверните кнопку Анализ «что-если» и выберите команду Подбор параметра

·                     откроется диалоговое окно Подбор параметров в котором:

·                     в поле Установить в ячейке введите   В6 или щелкните на ячейке В6;

·                     в поле Значение введите число –11000 (число должно быть отрицательно, что указывает на то, что ежемесячный платеж заемщик отдает, а не получает)

·                     в поле Изменения значения ячеек введите В3 или щелкните по ячейке В3

Ответ: Максимальная сумма кредита 1 324 647 руб.

Задание 2. (подбор параметра для вычисления процентной ставки)

Кредит в размере 850 000 руб. берется на 30 лет с максимальными ежемесячными платежами 5000 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1. Перейдите на лист Кредит_2 и введите новые данные:

·                     в ячейку В3 – число 850000

·                     в ячейке В4 – 360

2. Выберите команду Подбор параметров (см. задание 1):

·                     в поле Установить в ячейке введите – В6 или щелкните на ячейке В6;

·                     в поле Значение введите число –5000

·                     в поле Изменения значения ячеек введите В5

Ответ: Можно согласиться на процентную ставку 5,82 %.

Задание 3. (подбор параметра для вычисления срока погашения кредита)

Каков срок погашения кредита, если сумма кредита равна
2 250 000 руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?

1. Перейдите на лист Кредит_3 и введите новые данные:

·                     в ячейку В3  число 2250000

·                     в ячейке В5  –  7 %

·                     выберите команду Подбор параметров (см. предыдущее задание)

·                     в поле Установить в ячейке введите – В6 или щелкните на ячейке В6;

·                     в поле Значение введите число –14230

2. В поле Изменения значения ячеек введите В4

Ответ: Срок погашения кредита 439 месяцев (примерно 36,6 лет)

ЗАДАЧА 2. ССУДА НА ПОКУПКУ МАШИНЫ

1.                                                                                           Оформите на свободном листе таблицу как показано на рисунке

2.                                                                                           Создайте две копии созданного листа

3.                                                                                           Новые листы назовите соответственно Ссуда_1, Ссуда_2, Ссуда_3

Задание 1. (подбор параметра для вычисления размера ссуды)

Ссуда берется на 6 лет с процентной ставкой 2,9 % при условии, что сумма ежемесячных платежей не должна превышать 1395 руб. Каков максимальный размер ссуды?

1.                   Введите новые данные на листе Ссуда_1:

·                     в ячейку –  2,9 %

·                     в ячейку В7 – 72 (6лет умножить на 12 месяцев)

2.                   выберите команду Подбор параметров

·                     в поле Установить в ячейке введите   В6 или щелкните на ячейке В6;

·                     в поле Значение введите число –1395

·                     в поле Изменения значения ячеек введите В3

Ответ: Максимальный размер ссуды 92085,41 руб.

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ

Задание 2. (подбор параметра для вычисления срока погашения ссуды)

Каков срок погашения ссуды, если сумма ссуды равна 180000 руб., процентная ставка 1,7% годовых, а ежемесячные платежи равны 3250 руб.?

1.                   Введите новые данные на листе Ссуда_2 и выполните подбор параметров самостоятельно:

Ответ (проверьте себя): ссуда берется на 58 месяцев.

Задание 3. (подбор параметра для вычисления процентной ставки)

Ссуда в размере 130000 руб. берется на 5 лет с максимальными ежемесячными платежами 2390 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1.                   Введите новые данные на листе Ссуда_3 и выполните подбор параметров самостоятельно:

Ответ (проверьте себя): возможная процентная ставка  3,93%

2.                   Сохраните файл Подбор параметра_1.xlsx

3.                   Откройте файл Зарплата.xlsx

4.                   Сохраните файл под именем Подбор параметра_2.xlsx

5.                   На рабочем листе Зарплата октябрь используя режим подбора параметра, определить, при каком значении %Премии общая сумма заработной платы будет равна 250 000 р.

Анализ результатов работы и формулировка выводов

В  отчете необходимо предоставить: в своей папке файлы: Подбор параметра_1.xlsx, Подбор параметра_2.xlsx