Лабораторная работа №11. Использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами
Задание
Рассчитать ежемесячные выплаты по займу и платежи по процентам в зависимости от различных процентных ставок. Исходные данные: Сумма ссуды – 500000 руб., срок погашения 3 года, годовая процентная ставка 3%; Сумма займа на начало года 250000 руб., срок погашения 3 года, год выплаты - 1.
Решение
На рабочем листе создается удобный интерфейс с введенными в соответствующие ячейки исходными данными (рис. 32).
Р
ис.
32. Таблицы
исходных данных
В ячейке В5 находится формула =ПЛТ($B$4/12;$B$3*12;$B$2), (описание функции приведено на стр. 40), которая позволяет рассчитать ежемесячные выплаты по займу.
В ячейку D6 поместите формулу =ПРОЦПЛАТ($D$4;$D$5;$D$3;$D$2).
Функция ПРОЦПЛАТ( ) вычисляет проценты, выплачиваемые за определенный инвестиционный период. Аргументы функции:
ПРОЦПЛАТ(Ставка; Период; Кпер; Пс)
Ставка - процентная ставка для инвестиции; Период – период, для которого требуется найти прибыль; должен находиться в интервале от 1 до Кпер. Кпер – общее число периодов выплат для данной инвестиции. Пс – стоимость инвестиции на текущий момент. Для займа Пс – это сумма займа.
В нашем случае Ставка = 3% (ячейка $D$4, в которой для правильной работы таблицы набрана формула =$В$4), Период = 1 (год выплат, ячейка $D$5), Кпер = 3 (срок погашения, ячейка $D$3) и Пс = 250000 руб. (сумма займа, ячейка $D$2). Правильно указанные аргументы функций ПЛТ и ПРОЦПЛАТ должны приводить к расчетам значений в ячейках В5 и D6 как на рис. 32.
Все аргументы, означающие деньги, которые вы платите (например, депозитные вклады), представляются отрицательными числами; деньги, которые вы получаете (например, дивиденды), представляются положительными числами.
После подготовки исходных данных необходимо перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной (процентной ставки). В верхней строке будущей таблицы необходимо указать адрес каждой формулы, для которой нужно получить список результатов. Вместо адреса можно скопировать непосредственно формулу. Слева расположите изменяемые значения переменной, которые нужно протестировать (рис. 33).
Р
ис.
33. Подготовленная
таблица подстановки
В нашем случае в ячейке В9 введена формула =В5, а в ячейке С9 формула =D6. Значения процентной ставки заполнены методом автозаполнения, формат ячеек А10:А19 – процентный.
Для заполнения таблицы необходимо выделить блок ячеек А9:С19, который содержит верхнюю строку с формулами, а самый левый столбец – столбец варьируемых значений. Результат подстановки будет помещен в пустые ячейки.
Далее необходимо воспользоваться командой Данные/Таблица подстановки и в появившемся диалоговом окне Таблица подстановки указать, куда и какие значения необходимо подставлять (рис. 34).
Р
ис.
34. Окно
Таблица подстановки
В нашем случае подстановка исходных значений – столбец А10:А19 происходит в ячейку В4, так как в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемые формулы. В ячейках В10:С19 появятся результаты подстановки в исходные формулы (рис. 35).
Р
ис.
35. Вид
заполненной таблицы подстановки
Самостоятельная работа
Используя таблицу подстановки рассчитать ежемесячные выплаты по займу и платежи по процентам по исходным данным:
Таблица 10
Вариант |
Задание |
||||
Сумма ссуды (руб.) |
Сумма займа (руб.) |
Срок погашения |
Год выплаты |
Процентная ставка |
|
1 |
100000 |
50000 |
5 |
2 |
3-15 |
2 |
1000000 |
300000 |
4 |
1 |
1-10 |
3 |
600000 |
200000 |
3 |
3 |
4,5 – 6,5 |
4 |
400000 |
100000 |
5 |
2 |
5-20 |
5 |
1500000 |
500000 |
4 |
1 |
1-20 |
6 |
300000 |
300000 |
10 |
5 |
3-30 |
7 |
5000000 |
400000 |
5 |
2 |
5-25 |
8 |
1500000 |
500000 |
5 |
3 |
1-10 |
9 |
600000 |
200000 |
5 |
1 |
4,5 – 9,5 |
10 |
1000000 |
300000 |
3 |
3 |
1-20 |