Как в Excel использовать средство «Подбор параметра» для выполнения банковских расчетов

Приведенные в этой статье примеры и упражнения покажут, как применять средство Подбор параметра для решения задач, связанных с кредитом на покупку квартиры, автомобиля и банковскими депозитами.

Кредит на покупку квартиры

Прежде чем рассматривать задачи, связанные с кредитом на покупку квартиры, создайте рабочий лист, показанный на рис. 1.9. Если вы не в курсе, то Excel доступен и в онлайн-режиме, для этого вам потребуется Mozilla Firefox скачать бесплатно.

Рис. 1.9. Рабочий лист для решения задач о банковском кредите

На этом рабочем листе сумма кредита, срок погашения кредита (в месяцах) и годовая процентная ставка представлены в виде чисел, а ежемесячный платеж рассчитывается с помощью функции =ПЛТ(Ставка;Кпер;Пс). В этой функции аргумент Ставка задает ежемесячную процентную ставку по кредиту (поэтому в нашей формуле этот аргумент равен В5/12), аргумент Кпер — количество периодов погашения кредита (ячейка В4), аргумент Пс — сумма кредита (ячейка В3).

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

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

1. В ячейку В4 введите число 180 (15 лет, умноженных на 12 месяцев). В ячейку В5 введите 5,75%.
2. Выберите команду Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число — 11000.

[stextbox id=»info»]В поле Значение вводится отрицательное число, что указывает на то, что ежемесячные платежи заемщик отдает, а не получает (как при банковских вкладах).[/stextbox]

5. В поле ввода Изменяя значение ячейки введите В3 или щелкните на ячейке В3.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

Ответ: если кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб., то максимальная сумма кредита составит 1 324 647 руб.

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

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

1. В ячейку В3 введите число 2250000, в ячейку В5 введите 7%.
2. Выберите команду Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число — 14230.
5. В поле ввода Изменяя значение ячейки введите В4 или щелкните на ячейке В4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

Ответ: кредит в сумме 2 250 000 руб. с процентной ставкой 7% годовых и ежемесячными платежами в размере 14 230 руб. берется на 439 месяцев (примерно 36,6 года).

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

Задача: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?

1. В ячейку В3 введите число 8500000, в ячейку В4 введите 360.
2. Выберите команду Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра
3. В поле ввода Установить в ячейке введите В6 или щелкните на ячейке В6.
4. В поле ввода Значение введите число — 52250.
5. В поле ввода Изменяя значение ячейки введите В4 или щелкните на ячейке В4.
6. Щелкните на кнопке ОК.
7. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.

Ответ: если кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб., то можно согласиться на процентную ставку в размере 6,23%.