Вычисление суммы кредита
Задача: кредит берется на 15 лет с процентной ставкой 5,75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 руб. Какова максимальная сумма кредита?
1) В ячейку В3 введите число 180 (15 лет, умноженных на 12 месяцев). В ячейку В4 введите 5,75%.
2) В диалоговом окне Подбор параметра в поле ввода Установить в ячейке введите В5.
3) В поле ввода Значение введите число -11 000. В поле Значение вводится отрицательное число, что указывает на то, что ежемесячные платежи заемщик отдает, а не получает (как при банковских вкладах)
4) В поле ввода Изменяя значение ячейки введите В2 или щелкните на ячейке В2.
5) Щелкните на кнопке ОК, в открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: максимальная сумма кредита составит 1 324 647 руб.
2.3. Подбор параметра для вычисления срока погашения кредита
Задача: каков срок погашения кредита, если сумма кредита равна 2 250 ООО руб., процентная ставка составляет 7% годовых, а ежемесячные платежи равны 14 230 руб.?
1. В ячейку В2 введите число 2 250 000, в ячейку В4 введите 7%.
2. Вызовите окно Подбора параметра.
3. В поле ввода Установить в ячейке введите В5 или щелкните на ячейке В5.
4. В поле ввода Значение введите число -14 230.
5. В поле ввода Изменяя значение ячейки введите В3 или щелкните на ячейке В3.
6. Щелкните на кнопке ОК. В открывшемся окне Результат подбора параметра щелкните на кнопке ОК.
Ответ: кредит берется на 439 месяцев (примерно 36,6 года).
2.4. Подбор параметра для вычисления процентной ставки
Задача: кредит в размере 8 500 000 руб. берется на 30 лет с максимальными ежемесячными платежами 52 250 руб. На какую максимальную процентную ставку можно согласиться при таких условиях?
1. В ячейку В2 введите число 8 500 000, а в ячейку В3 число 360.
2. В поле ввода Установить в ячейке щёлкните по ячейке В5.
3. В поле ввода Значение введите число -52250.
4. В поле ввода Изменяя значение ячейки щёлкните по ячейке В3.
Ответ: можно согласиться на процентную ставку в размере 6,23%.
3. Расчеты по депозитам
Создайте таблицу, содержащую следующие данные: начальная сумма депозита – 250 000р.; срок депозита (месяцы) – 48; процентная ставка – 9,15%. Конечную сумму вычисляет функция БС(Ставка;Кпер;;-Пс), где аргумент Ставка задает ежемесячную процентную ставку депозита (поэтому в нашей формуле этот аргумент равен процентная ставка/12), аргумент Кпер — срок хранения депозита (48), аргумент Пс — начальная сумма депозита (250 000р.). Эта функция подобна функции ПЛТ из предыдущего примера, но вычисляет не ежемесячные платежи, а конечную накопленную сумму.
Подбор параметров производится аналогично предыдущему упражнению.
3.1. Подбор параметра для вычисления начальной суммы депозита
Задача: депозит открывается на 3 года с процентной ставкой 12,75% годовых. Какова должна быть начальная сумма депозита, если конечная сумма должна быть порядка 150 ООО руб.?
Ответ: если депозит открывается на 3 года с процентной ставкой 12,75%, то для получения конечной суммы 150 000 руб. начальная сумма должна составлять 102 530 руб.
3.2. Подбор параметра для вычисления срока депозита
Задача: на какой срок необходимо открыть депозит, чтобы при процентной ставке 10,7% годовых из начальной суммы 125 000 руб. получить конечную сумму 200 000 руб.?
Ответ: 53 месяца (примерно 4,4 года).
3.3. Подбор параметра для вычисления процентной ставки
Задача: депозит в размере 250 000 руб. открывается на 2 года с целью получить конечную сумму не менее 300 000 руб. Какова при таких условиях может быть минимальная процентная ставка?
Ответ: не менее 9,15% годовых.
4. Подбор параметра для оптимизации продажи театральных билетов
В этом подразделе рассматриваются задачи определения количества билетов одной из трех категорий таким образом, чтобы стоимость «кассы» составляла определенную сумму. Создайте рабочий лист, показанный на рисунке 5.
| А | В | С | |
| Цена билета | Кол-во билетов | ||
| детские билеты | 60р. | ||
| билеты для взрослых | 100р. | ||
| льготные билеты | 90р. | ||
| стоимость всех билетов | =В2*С2+В3*С3+В4*С4 |
Рис. 5. Таблица данных для расчёта стоимости билетов
Этот рабочий лист прост для понимания. Он содержит данные о ценах билетов трех категорий (детские, для взрослых и льготные) и их количестве. Общая выручка, которую должна получить театральная касса, подсчитывается в ячейке В6 как сумма стоимостей билетов всех категорий.
4.1. Вычисление количества билетов для детей
Задача: сколько необходимо продать детских билетов по цене 55 руб. для того, чтобы в кассе было 22 000 руб., если продано 150 «взрослых» билетов по цене 95 руб. и 100 льготных билетов по цене 75 руб.?
Ответ: при выполнении условий задачи необходимо продать 5 (4,54) детских билетов.
4.2. Вычисление количества билетов для взрослых
Задача: сколько необходимо продать билетов для взрослых по цене 90 руб. для того, чтобы в кассе было 21 500 руб., если продано 125 детских билетов по цене 60 руб. и 100 льготных билетов по цене 75 руб.?
Ответ: при выполнении условий задачи необходимо продать 72 билета для взрослых.
4.3. Вычисление цены билетов одной из трех категорий таким образом, чтобы стоимость «кассы» составляла определенную сумму.
Задача: какова должна быть цена льготных билетов для того, чтобы в кассе было 20 700 руб., если продано 95 детских билетов по цене 50 руб. и 125 билетов для взрослых по цене 105 руб. и планируется продать 40 льготных билетов?
Ответ: при выполнении условий задачи необходимо установить цену льготных билетов 71 руб.
Ошибки при работе со средством «Подбор параметра»
После того как вы щелкнете на кнопке ОК в диалоговом окне Подбор параметра, чтобы найти нужное значение, вместо диалогового окна Результат подбора параметра может появиться одно из следующих сообщений об ошибке.
Ячейка должна содержать формулу. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Установить в ячейке диалогового окна Подбор параметра, не содержит формулы. Чаще всего в этом поле введён адрес ячейки, который должен быть указан в поле ввода Изменяя значение ячейки. Закройте сообщение об ошибке, а затем введите в поле Установить в ячейке адрес ячейки, содержащей формулу.
Введено недопустимое значение. Это сообщение появляется тогда, когда в поле ввода Значение кроме числа введены еще какие-то символы, которые Excel не может распознать как числа. Закройте сообщение об ошибке, а затем введите в поле Значение правильное число (целое или десятичное). К числу можно добавить знак денежной единицы или процента
Ячейка должна содержать значение. Это сообщение появляется тогда, когда ячейка, адрес которой указан в поле ввода Изменяя значение ячейки, не содержит числового значения (а содержит, например, текст или формулу). Чтобы исправить эту ошибку, введите в поле Изменяя значение ячейки адрес ячейки, содержащей числовое значение.
Введенный текст не является правильной ссылкой или именем. Это сообщение появляется тогда, когда в поле ввода Установить в ячейке или в поле ввода Изменяя значение ячейки введено нечто, что Excel не может распознать как ссылку на ячейку. Чаще всего такая ошибка возникает тогда, когда адрес ячейки вводится вручную, а не указывается путем щелчка мыши на нужной ячейке. Чтобы исправить эту ошибку, введите в поле ввода правильный адрес ячейки.
Решение не найдено. Это сообщение появляется в диалоговом окне Результат подбора параметра (а не как сообщение об ошибке) тогда, когда Excel не может подобрать такое значение для изменяемого параметра, чтобы указанная формула возвратила заданное значение. Такое же сообщение появится, если в поле Значение введено экстремально маленькое или экстремально большое число. Чтобы исправить эту ошибку, сначала в диалоговом окне Результат подбора параметра щелкните на кнопке Отмена, данное окно закроется. Затем снова вызовите окно Подбор параметра и в поле Значение введите другое число.