Подбор параметра и организация обратного расчета в LibreOffice Calc

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

Подбор параметра и организация обратного расчета

Время выполнения — 2 часа

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

Средства обеспечения занятия:

• Оборудование: ПК;

• Программное обеспечение: OS Windows, LibreOffice Calc

Задание:

• Выполнить задания в соответствии с установленным порядком;

• Оформить отчет.

Ход работы:

Запустите LibreOffice Calc и создайте в своей папке файл ЛР9_Подбор параметра_1.ods.

Задача 1. Кредит на квартиру

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

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

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

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

1. Установите формат ячеек как на рисунке 8.1: B3 – денежный, В5 – процентный; B7 - денежный (дробная часть – 2, отрицательные числа красным, разделитель разрядов).

Рисунок 8.1 – Подсчет ежемесячного платежа

1. Создайте две копии для Листа 1, щелкнув ПКМ на Листе 1 (рисунок 8.2).

Рисунок 8.2 – Создание дубликатов листа

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

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

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

1. Введите новые данные на листе Кредит_1: в ячейку В4 – число 180 (15 лет, умноженных на 12 месяцев); в ячейку В5 – 5,75%.

2. Выполните команду Сервис/Подбор параметра, в котором:

• в поле Целевая ячейка введите В6 или щелкните на ячейке В6;

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

• в поле Изменяемая ячейка введите В3 или щелкните по ячейке В3 (рисунок 8.3).

1. Результат показан на рисунке 8.4 – максимальная сумма кредита 1324647 руб.

Рисунок 8.3 – Подбор параметра по первому варианту кредита

Рисунок 8.4 – Максимальная сумма кредита 1 324 647 р.

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

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

1. Перейдите на лист Кредит_2 и введите новые данные: в ячейку В3 – число 850000; в ячейке В4 – 360. Остальные значения оставьте неизменными.

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

• Целевая ячейка: В6;

• Целевое значение: – 5000;

• Изменяемая ячейка: В5 (рисунок 8.5).

Рисунок 8.5 – Подбор параметра по второму варианту кредита

1. Вставьте результат в изменяемую ячейку (рисунок 8.6).

Рисунок 8.6 – Процентная ставка

1. Результат показан на рисунке 8.4 – можно согласиться на процентную ставку 5,82 % (рисунок 8.7)

Рисунок 8.7 – Результат

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

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

1. Перейдите на лист Кредит_3 и введите новые данные: в ячейку В3 – число 2250000; в ячейке В5 – 7 %. Остальные значения оставьте неизменными.

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

• Целевая ячейка: В6;

• Целевое значение: – 14230;

• Изменяемая ячейка: В4 (рисунок 8.8).

Рисунок 8.8 – Подбор параметра по третьему варианту кредита

1. Вставьте результат в изменяемую ячейку (рисунок 8.9).

Рисунок 8.9 – Срок кредита

1. Результат показан на рисунке 8.10 – срок погашения кредита 439 месяцев (примерно 36,6 лет).

Рисунок 8.10 – Срок погашения кредита

Задача 2. Ссуда на покупку машины

1. Создайте новый лист и оформите на этом свободном листе таблицу как показано на рисунке 8.11, установив необходимые форматы ячеек и вычислив значение в ячейке B5 формулой =ПЛТ.

Рисунок 8.11 – Исходная таблица

1. Создайте две копии созданного листа и новые листы назовите соответственно Ссуда_1, Ссуда_2, Ссуда_3.

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

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

1. Введите новые данные на листе Ссуда_1: в ячейку В4 –  2,9 %; в ячейку В6 – 72 (6 лет умножить на 12 месяцев).

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

• в поле Целевая ячейка введите В6 или щелкните на ячейке В5;

• в поле Целевое значение введите число –1395;

• в поле Изменяемая ячейка введите В3 (рисунок 8.12).

Рисунок 8.12 – Подбор параметра для вычисления

максимального размера ссуды

1. Результат показан на рисунке 8.13 – Максимальный размер ссуды 92085,41 руб.

Рисунок 8.13 – Максимальный размер ссуды 92085 руб.

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

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

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

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

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

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

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

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

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

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

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

1. Для чего применяется подбор параметра в электронных таблицах?

2. Назначение функции =ПЛТ().

3. Приведите примеры финансовых функций.

6