Заказ № 1634. Лабораторная работа № 1 «Финансы в Excel»

Лабораторная работа № 1 «Финансы в Excel»

Цель: научиться использовать функции для денежных расчетов; научиться использовать функции для расчета прогнозов; использовать функции для обработки текстовых строк.

1.     Расчет величины экономии.

2.     Как изменить результат формулы.

3.     Создание графика амортизационных выплат.

4.     Расчет значений на конец месяца.

         Ваш босс подошел к вашему рабочему месту с кипой папок и попросил помочь. Первая папка, принесенная вашим боссом, поступила из финансового отдела. Его сотрудники работают над проектом приобретения новой машины. Стоимость ее составляет 140000 р. У них возникли разногласия относительно величины погашения долга по кредиту. Вам необходимо представить финансовому аналитику корректные формулы.

1. Расчет величины экономии

         Создайте лист "Заем" в другой рабочей книге. Он содержит все необходимые данные для расчета выплат по кредиту, за исключением нужной формулы для осуществления вычислений.

Рис. 8.1. Лист "Заем"

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

1.   Выделите ячейку В6 и щелкните на кнопке Изменить формулу (рядом с панелью формулы знак =). Появится палитра формул. Поле Имя будет заменено полем из списка наиболее общеупотребительных функций. Одна из них поможет вам рассчитать платежи по кредиту. Это функция ППЛАТ (Периодические ПЛАТежи). Если там этой функции нет, то ищите среди Другие функции – Финансовые. Вы задаете функции ППЛАТ информацию об учетной ставке, периоде выплат и общей сумме, а она возвращает вам величину ежемесячных взносов, которые вам необходимо выплачивать.

2.   Щелкните на стрелке вниз рядом со списком функций, затем щелкните на ППЛАТ. Появится расширенная палитра формул.

3.   Введите 8,5%/12 (B4/12) в поле Норма и нажмите клавишу Tab, чтобы перейти к полю Кпер(общее число периодических выплат).

4.   Введите Срок*12 (B5*12) в поле Кпер и нажмите клавишу Tab, чтобы перейти к полю Нз (общая сумма всех будущих платежей с текущего момента).

5.   Введите Кредит (B2) в поле Нз и нажмите клавишу Tab, чтобы перейти к полю Бс (последний платеж при расчете за кредит).

6.   Введите Последний платеж (B3) в поле Бс и щелкните на кнопке Ок.

Если Вы сделали все правильно, то в ячейке В6 появилось число       -2 737,98р. выделенное красным цветом.

2. Как изменить результат формулы

Вы показали результаты вычислений финансистам, но те посчитали полученные значения неприемлемыми. В соответствии с их планами величина взноса не должна превышать 2500 р. На этот случай в Excel имеется инструмент Подбор параметра, с помощью которого осуществляется подбор значения, пока результат формулы не достигнет заданного вами значения.

1.   Выделите ячейку В6.

2.   В меню Сервис щелкните на команде Подбор параметра. Появится диалоговое окно Подбор параметра, в котором ячейка В6 уже будет фигурировать в поле Установить в ячейке. В поле значение введите целевое значение для ячейки В6: -2500. Значение последнего платежа находится в ячейке В3, поэтому именно эту ячейку вы должны указать в поле Изменяя значение ячейки.

3.   В поле Изменяя значение ячейки введите В3 и щелкните на кнопке Ок.

В ячейке В3 появилось число –27716 р.

3. Создание графика амортизационных выплат

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

         В столбцах с Е1 по J1 введите заголовки соответствующих значений, по месяцам за пять лет: Месяц, Начало, Доход, Осн_сумма, Конец, Дата платежа.

1.   В ячейке В1 введите заголовок Заем.

2.   В ячейке E2 введите апр.98. С использованием автопротяжки заполните столбец до марта 2003 года.

3.   В ячейке F2 в качестве начального значения для первого месяца введите =Заем Кредит (щелкнуть по B2) и нажмите клавишу Enter.

4.   В ячейке G2 введите =Начало*(Заем Процент/12) (подумайте сами куда щелкать) и нажмите клавишу Enter.

5.   В ячейке Н2 введите = - Заем Платеж - Доход и нажмите клавишу Enter.

6.   В ячейке I2 введите =Начало - Осн_сумма и нажмите клавишу Enter.

7.   В ячейке F3 для второго значения в начальном столбце введите =Конец и нажмите клавишу Enter.

Теперь у вас есть все необходимые формулы. Вам нужно лишь скопировать формулы вниз в соответствующие строки.

8.   Выделите ячейку F3 и дважды щелкните на манипуляторе автозаполнения.

9.   Выделите область G2:I2 и дважды щелкните на манипуляторе автозаполнения.

10.Выделите ячейку Е2 и дважды щелкните на нижней границе ячейки.

Конечное балансовое значение составит 27716 р., что в точности равно значению платежа по кредиту в соответствии с ранее произведенными вычислениями.

         График амортизации не только полезен для определения налоговых льгот, но и дает возможность подтвердить вычисления, выполненные с помощью функции ППЛАТ.

4. Расчет значений на конец месяца

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

1.   В рабочем листе "Заем" в ячейке J1 введите заголовок Дата_плат.

2.   Выделите ячейку J2 и щелкните на кнопке Изменить формулу. Щелкните на стрелке рядом со списком функций и выберите Другие функции.

3.   В диалоговом окне Мастер функций выделите функцию Дата и время в списке Категория, выделите Дата в списке Функция и щелкните на кнопке Ок. В Палитре формул теперь будут отображены аргументы для функции ДАТА. Эта функция имеет три аргумента: год, месяц и день.

4.   В качестве аргумента Год введите 1900. В качестве аргумента Месяц введите 12. В качестве аргумента День введите 31. Не нажимайте клавишу Enter.

5.   Дважды щелкните на числе 1900 в поле Год. Затем щелкните на стрелке рядом со списком функций и выделите Другие функции. В списке Функция выделите ГОД и щелкните на кнопке Ок. 

Excel отобразит новую Палитру формул для новой функции.

6.   В поле Дата_как_число введите слово "Месяц", но не щелкайте на кнопке Ок. Вместо этого щелкните на свободном месте в строке формулы правее самой формулы.

Вы снова вернетесь в поле основной формулы Палитры формул.

7.   Дважды щелкните на числе 12 в поле Месяц, затем щелкните на стрелке рядом со списком функций и выделите Другие функции. В списке Функция выделите МЕСЯЦ и щелкните на кнопке Ок.

8.   В поле Дата_как_число введите опять слово "Месяц", и снова щелкните на пустом пространстве в поле формулы.

9.   Замените число 31 в поле День на 0 и щелкните на кнопке Ок.

Задав число 0 в качестве аргумента для дней, вы получите последний день предыдущего месяца.

В ячейке J2 будет отображена дата 31.03.98.

10.Дважды щелкните на манипуляторе автозаполнения внизу ячейки J2, затем посмотрите, как формула рассчитала значения дат, соответствующие разным месяцам, после чего сохраните рабочую папку под своим именем. У вас получилась следующая таблица.

Месяц

Начало

Доход

Осн_сумма

Конец

Дата платежа

апр.98

140 000р.

992р.

1 508р.

138 492р.

31.03.98

май.98

138 491,67р.

981р.

1 519р.

136 973р.

30.04.98

июн.98

136 972,65р.

970р.

1 530р.

135 443р.

31.05.98

июл.98

135 442,87р.

959р.

1 541р.

133 902р.

30.06.98

…

…

…

…

…

…

дек.02

36 769,81р.

260р.

2 240р.

34 530р.

30.11.02

янв.03

34 530,26р.

245р.

2 255р.

32 275р.

31.12.02

фев.03

32 274,85р.

229р.

2 271р.

30 003р.

31.01.03

мар.03

30 003,47р.

213р.

2 287р.

27 716р.

28.02.03

Рис.8.2. Итоговая таблица

11.Измените данные в первой (маленькой) таблице. Возьмите кредит 500000 р. Пронаблюдайте, как меняются данные второй таблицы. Измените процент (10%). Пронаблюдайте, как меняются данные второй таблицы. Измените срок платежа (4 года). Пронаблюдайте, как меняются данные второй таблицы. 

Все задание в прикрепленном демо-файле, так как на сайте некорректно отображаются таблицы и рисунки. 

Работа выполнена и оформлена на высоком теоретическом и практическом уровне.