Исходные данные для упражнения 4

2. Сохранить книгу в своей папке под именем «Упражнение 4».

Рисунок 10 - Вспомогательная таблица

3. В начале работы подготовить вспомогательную таблицу (рисунок 10), в которой будут отображаться годовые процентные ставки и рассчитываться квартальные процентные ставки. Создать эту таблицу в левом верхнем углу книги. Для рисования определения границ воспользуйтесь пунктом ленты «Главная», группа «Шрифт», «Все границы».

4. Под готовой (но пока ещё незаполненной) вспомогательной таблицей необходимо создать ещё одну – основную таблицу, в которой будут находиться результаты наших расчётов. Сразу же можно занести в таблицу некоторые начальные данные об остатках долга по кредитам. У всех вариантов они одинаковые, а значит, в результате внесения этих данных должна получиться следующая картина (рисунок 11).

Рисунок 11 - Основная таблица

1. Теперь следует внести в первую (вспомогательную) таблицу (рисунок 10) исходные данные, индивидуальные для вашего варианта – данные о годовых процентных ставках по кредитам. После внесения данных должен получиться примерно следующий результат (рисунок 12).

Рисунок 12 - Таблица с индивидуальными данными

Обратите внимание, что у вас будут другие исходные данные
в соответствии с вашим вариантом

2. Следующая задача – вычислить квартальную процентную ставку. В данном случае рассчитайте квартальную ставку как 1/4 от годовой процентной ставки. Для этого:

а) установите курсор в ячейку C2;

б) нажмите на клавиатуре клавишу «=». Символ «=» отобразится как в самой ячейке, так и в строке формулы (рисунок 13);

Рисунок 13 - Вычисление квартальной процентной ставки

в) далее в строке формулы введите формулу =B2/4 для вычисления квартальной ставки;

г) аналогичным образом рассчитайте процентные ставки для второго и третьего кредитов.

Проверьте себя! В ячейках С3 и С4 должны быть формулы = В3/4 и =В4/4
соответственно.

1. Теперь, когда вспомогательная таблица полностью заполнена, приведите её в удобочитаемый вид. Для этого выделите диапазон ячеек B2:C4, и щелкните по выделенной области правой кнопкой мыши. В появившемся меню выберите «Формат ячеек», выберите тип «Процентный» и установите число десятичных знаков, равное двум. Нажмите кнопку «ОК»и посмотрите на результат.

2. Начните заполнять основную таблицу. Первое, что нужно выполнить – определить ежеквартальные платежи по основному долгу. Они зависят от суммы остатка и срока, в течение которого кредит должен быть погашен. Предположим, кредит №1 нужно выплатить в течение двух лет. Это означает, что оплата будет происходить два года или 8 кварталов. Становится понятно, что размер ежемесячного платежа по основному долгу составит:

12 000 000 / 8 = 1 500 000 р. в квартал.

Вы могли бы сразу записать в первые восемь ячеек соответствующей строки это значение, однако перед вами стоит задача создания плана, который автоматически пересчитывался бы при изменении значений. То есть все значения должны вычисляться при помощи формул. Для этого сделайте следующее:

а) установите курсор в ячейку B12;

б) введите формулу =B8/8 и нажмите «Enter». Обратите внимание, что по условиям вашего варианта для первого кредита может быть другой период выплат. Поэтому если кредит выплачивается 1 год, то вы введёте =B8/4, а если 3 года, то =B8/12. В результате вы получите выплату основного долга за первый квартал;

в) установите курсор в следующую ячейку – C12. Её значение должно быть таким же, как и в предыдущей. Вы могли бы установить в этой ячейке формулу, как в предыдущем пункте, но лучше сделать следующее: нажмите клавишу «=», щёлкните левой кнопкой мыши по предыдущей ячейке и нажмите «Enter». Таким образом вы установили в этой ячейке такое же значение, как и в предыдущей;

г) чтобы повторить результат для каждой следующей ячейки щелкните по уже заполненной ячейке С12 и «протяните» её на столько позиций вправо, насколько вам нужно (это зависит от того, сколько кварталов вы будете выплачивать первый кредит). В нашем случае, поскольку платежей восемь, результат будет следующим (рисунок 14);

Рисунок 14 - Маркер автозаполнения

д) аналогичным образом заполните строки гашения основного долга для кредитов № 2 и № 3. Помните, что у этих кредитов разные сроки погашения. Поэтому и заполненных ячеек в разных строках будет разное количество.

3. Поскольку проценты начисляются на остаток долга на начало месяца, то теперь следует заполнить строки с остатками долга по кредитам. На начало первого квартала остатки уже даны и вычисления не требуют, поэтому начнём со второго квартала. Очевидно, что остаток каждого следующего квартала будет равен остатку предыдущего квартала за вычетом выплаты основного долга предыдущего квартала.

Пошагово заполним ячейки на примере первого кредита (рисунок 15):

а) установите курсор в ячейку C8;

б) введите формулу =B8-B12 и нажмите «Enter»;

в) с помощью маркера автозаполнения «протяните» формулу ячейки C8 так, чтобы заполнились ячейки, соответствующие всем кварталам, в которые происходят выплаты. Обратите внимание, что если «протянуть» формулу ещё правее, то значения станут нулевыми и даже отрицательными. При необходимости, очистите ненужные ячейки;

г) аналогичным образом заполните остатки для других кредитов.

Рисунок 15 - Остатки долга по кредитам

1. Следующим шагом необходимо определить сумму уплачиваемых в каждом квартале процентов. Для этого:

а) установите курсор в ячейку B16;

б) введите формулу =B8*$C$2 и нажмите «Enter». Обратите внимание на необходимость ввода символа $ при вводе ссылки на ячейку C2. Это нужно для установления абсолютной ссылки на ячейку. Даже при использовании автозаполнения во всех автоматически заполненных ячейках будет сохраняться ссылка именно на ячейку C2;

в) с помощью маркера автозаполнения «протяните» формулу ячейки В16 так, чтобы заполнились ячейки, соответствующие всем кварталам, в которые происходят выплаты процентов;

г) аналогичным образом заполните ячейки для других кредитов.

В результате выполнения пунктов 8–10 основная таблица почти полностью заполнена и выглядит примерно следующим образом (рисунок 16).

Следует помнить, что результат зависит от выбранного варианта

Рисунок 16 - Заполненная основная таблица

1. На следующем шаге вам предстоит посчитать суммарные выплаты по всем кредитам. Для этого заполним сначала строки 7, 11 и 15, которые будет информировать о суммарном остатке долга, суммарном гашении основного долга и суммарной выплате процентов соответственно. Для этого:

а) установите курсор в ячейку B7;

б) нажмите кнопку «f(x)», которая находится слева от строки формулы. После этого в появившемся списке функций выберите функцию «СУММ» и нажмите «ОК»;

в) в строке «Число1» открыв­шегося окна введите B8:B10 или, установив в эту строку курсор, выделите мышкой три ячейки B8:B10. После этого нажмите «ОК». В результате в ячейке B7 появится сумма трёх ячеек снизу (рисунок 17);

г) теперь вы можете «протянуть» формулу вправо;

д) проделайте те же действия для строк 11 и 15 самостоятельно.

Рисунок 17 - Диалоговое окно ввода функции

В результате вы получите почти полностью заполненную таблицу (см. рисунок 3.23);

е) чтобы строки с суммами выделялись на фоне остальных значений, установите для них полужирное начертание. Для этого выделите каждую строку мышкой и нажмите сочетание клавиш «Ctrl + B», либо используйте кнопку в меню «Главная» и группе «Шрифт»;

ж) добавьте в таблице итоговую строку, в которой будет отражаться суммарный платёж по всем кредитам с учётом погашения основного долга и процентов по кредиту. Назовите её «ИТОГО ПЛАТЁЖ» (рисунок 18);

з) в ячейке B19 установите формулу =B7+B11+B15, нажмите «Enter», а затем «протяните» формулу, чтобы заполнить всю строку.

Рисунок 18 - Основная таблица с итоговой строкой

1. Как видно (см. рисунок 18), получившийся отчёт не слишком удобочитаем. В некоторых ячейках после запятой отображаются три знака, в других вообще нет знаков после запятой. Кроме того, трудно сразу определить порядок числа, так как не разделены группы разрядов. Для того, чтобы улучшить восприятие таблицы, выделите всю её основную область, содержащую числовые значения (без названия строк и шапки). Затем щёлкните правой кнопкой по выделенной области и выберите пункт меню «Формат ячеек» (рисунок 19). В появившемся окне установите число десятичных знаков равное нулю, а также установите галочку «Разделитель групп разрядов». Нажмите «ОК». Посмотрите на результат (рисунок 20).

Рисунок 19 - Меню «Формат ячеек»

Рисунок 20 - Основная таблица с округлёнными данными

1. Для определения суммы уплаченных за весь период процентов за кредит создайте новую таблицу под основной таблицей, начиная с ячейки А21 (см. рисунок 20). Заполните таблицу самостоятельно на основе указаний, полученных ранее. Сделайте это с использованием функции «СУММ».

2. В завершение проиллюстрировать полученные данные графически:

 во-первых, постройте график суммарного остатка по кредитам. Для этого:

а) выделите диапазон А6:М7. В него войдут номера кварталов и данные по остаткам на начало каждого квартала;

б) в меню «Вставка» в группе «Диаграммы» выберите тип диаграммы «График». Расположите появившийся график под таблицами. В меню «Конструктор» настройте параметры графика устраивающим вас образом. Кроме того, расположите легенду под графиком, а в формате оси Х установите положение оси «по делениям»;

 во-вторых, постройте график суммарных платежей. Для этого:

в) поскольку названия кварталов находятся в шапке таблицы, а суммарные платежи – в самом низу, то нужно выделить диапазон A6:M6 и диапазон А19:М19. Чтобы сделать это нужно при нажатой клавише «Ctrl» сначала выделить первый диапазон, а затем второй;

г) так же, как и в пункте «б», в меню «Вставка» в группе «Диаграммы» выберите тип диаграммы «График», а затем настройте его аналогичным образом, как и предыдущий;

 во-третьих, построить круговую диаграмму, характеризующую структуру выплат процентов. Для этого:

д) выделите область А22:В24, а в меню «Вставка» в группе «Диаграммы» выберите тип диаграммы «Круговая». Настройте её так, чтобы в секторах диаграммы отображались суммы по процентам. Дайте ей название. Дополнительные настройки примените по своему вкусу.