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

Для удобства работы с MS Excel рекомендуется присваивать имена ячейкам и диапазонам ячеек, так как это повышает наглядность и облегчает понимание используемых формул. На листе Имена приведены все используемые в данной рабочей книге имена.

2. Создание Отчета о прибылях и убытках

Откройте лист Отчет о приб. На рис. 6.1 приведен результирующий вид Отчета о прибылях и убытках.

Рис. 6.1. Результирующий вид Отчета о прибылях и убытках

В ячейку С2 введите формулу

=ГОД(ДатаНачала).

В ячейку D2 введите формулу =C2+1 и размножьте ее далее по строке.

Для ввода имени ДатаНачала можно щелкнуть по соответствующей ячейке ('Общие данные'!С2) или воспользоваться клавишей F3.

Рассмотрим 1-й способ расчета валового объема продаж - в ячейку С3 введите формулу =Сбыт!B6*Цена/(1+НДС) и размножьте формулу по строке.

2-й способ. Выделите диапазон C4:G4 и введите формулу

=ОбъемСбыта*Цена/(1+НДС)

Щелкните левой кнопкой мыши в строке формул и одновременно нажмите клавиши Ctrl, Shift и Enter, чтобы ввести формулу массива. Данный стиль ввода формул считается более предпочтительным.

В ячейку С5 введите формулу =C4*Потери и размножьте ее по строке.

В ячейку С6 введите формулу =C3-C5 и размножьте ее по строке.

В диапазон C7:G7 введите формулу массива

{=ОбъемСбыта*(Материалы+Электроэнергия)/(1+НДС)}

В диапазон C8:G8 самостоятельно введите формулу массива.

В диапазон C9:G9 введите формулу массива

{=ОбъемСбыта*СдельнаяЗарплата}

В ячейку С10 введите формулу =C9*СУММ('Общие данные'!$D$8:$D$10) и размножьте ее по строке.

Чтобы ввести в ссылке на ячейку знаки $ удобно пользоваться клавишей F4. Вспомните, для чего служат знаки $ в ссылке на ячейку.

В ячейку С11 введите формулу =СУММ(C7:C10), в ячейку С12 введите формулу =C6-C11, а в ячейку С13 введите формулу

=СУММПРОИЗВ(СтоимостьОПФ;НормыНаРемонт)

Размножьте эти формулы по соответствующим строкам.

В диапазон C14:G14 введите формулу массива

{=Топливо/(1+НДС)}

В ячейку С15 введите формулу =C13+C14 и размножьте ее по строке.

В диапазон C16:G16 введите формулу массива

{=Менеджмент*(1+СУММ('Общие данные'!D8:D10))+ПрочиеИздержки}

В ячейку С17 введите формулу =C15+C16 и размножьте ее по строке.

В дальнейшем нам потребуется использовать некоторые финансовые функции Excel. Перед началом работы с финансовыми функциями рекомендуется установить надстройку Пакет анализа. Для этого выполните команду меню СервисНадстройки… В диалоговом окне Надстройки установите флажок напротив строки Пакет анализа и нажмите кнопку OK/ В результате вам будут доступны все финансовые функции Excel, которые ориентированы на решение задач, связанных с расчетами различных аннуитетов, амортизации, цены, доходности и других параметров ценных бумаг (облигаций, акций и т.п.), а также задач оценки эффективности инвестиционных проектов.

В диапазоне C18:G18 для расчета величины амортизационных отчислений за год воспользуемся финансовой функцией АПЛ().

Синтаксис функции АПЛ(Стоимость;Остаток;Период).

Стоимость - это начальная стоимость имущества.

Остаток - это стоимость имущества в конце периода амортизации.

Период - это количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации).

В нашем случае Остаток равен 0. Зная годовую норму амортизации, можно рассчитать аргумент функции Период=1/Годовая норма амортизации. Тогда в ячейку D18 нужно ввести формулу

=АПЛ(ЗданияСооружения;0;1/'Осн. фонды'!$D$3)+АПЛ(Оборудование;0;1/'Осн. фонды'!$D$4)

. Для ввода функции АПЛ() выделите ячейку D18, нажмите кнопку Вставка функции , выберите категорию Финансовые, функция АПЛ.

Так как в 2006 году основные фонды будут эксплуатироваться только три месяца, то в ячейку С18 введите формулу

=3/12*D18

В диапазон C19:G19 введите значение 0.

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

В ячейку С20 введите формулу =СУММ(C18:C19), в ячейку С21 введите =C12-C17-C20, в С22 введите =ЕСЛИ(C21<0;0;C21*НалогНаПрибыль), в С23 введите =C21-C22. Размножьте эти формулы по соответствующим строкам.

3. Создание Отчета о движении денежных средств

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



Рис. 6.2. Результирующий вид Отчета о движении денежных средств

Таблица 6.1

№	Наименование статьи	=ГОД(ДатаНачала)	=C2+1
1	Чистая прибыль	{=ЧистПрибыль}	{=ЧистПрибыль}
2	Амортизация	{=Амортизация}	{=Амортизация}
3	КЭШ-ФЛО ОТ ОПЕРАЦИОННОЙ ДЕЯТЕЛЬНОСТИ	=C3+C4	=D3+D4
4	Затраты на приобретение активов	=СУММ('Инв. план'!F4:F6)	0
5	Другие издержки подготовительного периода	='Инв. план'!F3	0
6	КЭШ-ФЛО ОТ ИНВЕСТИЦИОННОЙ ДЕЯТЕЛЬНОСТИ	=-(C6+C7)	=-(D6+D7)
7	Кредиты	0	0
8	Выплаты в погашение кредитов	0	0
9	КЭШ-ФЛО ОТ ФИНАНСОВОЙ ДЕЯТЕЛЬНОСТИ	0	0
10	САЛЬДО НАЛИЧНОСТИ НА НАЧАЛО ПЕРИОДА	=СтартКапитал+C11	=C13+D11
11	САЛЬДО НАЛИЧНОСТИ НА КОНЕЦ ПЕРИОДА	=C12+C5+C8	=D12+D5+D8

4. Расчет показателей эффективности проекта

Откройте лист Эффект. На рис 6.3 представлен результирующий вид этого листа.



Рис. 6.3. Результирующий вид рабочего листа Эффект

В ячейку В4 введите формулу ='Отчет о движ.'!C5+'Отчет о движ.'!C8, в В5 =СУММ($B4:B4), а в В6 =ЕСЛИ(B5<0;1;0). «Растяните» формулы по строкам.

Чтобы рассчитать срок окупаемости (Payback Period, PP), нужно рассчитать момент времени, когда кумулятивный чистый поток денежных средств изменит знак с минуса на плюс. В нашем случае это произойдет между вторым и третьим годом. В ячейке А6 получена грубая оценка срока окупаемости с помощью формулы =СУММ(B6:F6). Для уточнения срока окупаемости в ячейку В7 введите

=A6-0,5-ИНДЕКС(КумЧистПотокДенСредств;;A6)/

(ИНДЕКС(КумЧистПотокДенСредств;;A6+1)-ИНДЕКС(КумЧистПотокДенСредств;;A6))

В данной работе для простоты предполагается, что все доходы и расходы распределены равномерно в течение года (на практике это зачастую не так). В этом случае рекомендуется считать, что все элементы потока денежных средств относятся к середине соответствующего года. Поэтому, когда кумулятивный поток изменил знак с «-» (-959921) на «+» (+719562), мы считаем, что срок окупаемости лежит между серединой второго года и серединой третьего года. Последняя формула выведена с учетом этого предположения.

Чтобы ввести формулу в диапазон B10:F10, выделите этот диапазон и введите формулу =ЧистПотокДенСредств/(1+СтавкаДисконта)^(Год-0,5), а затем одновременно нажмите клавиши Ctrl и Enter.

Нажав одновременно клавиши Ctrl и Enter, мы определяем, что каждое значение в диапазоне ЧистПотокДенСредств будет разделено на соответствующее ему значение массива (1+СтавкаДисконта)^(Год-0,5). Здесь используется показатель степени Год-0,5, так как мы условились относить все доходы и расходы к середине года.

В остальные ячейки диапазона А11:F13 необходимые формулы введите самостоятельно.

Чтобы ввести формулу в диапазон B15:F15, выделите этот диапазон и введите формулу

=ЧистПотокДенСредств/((1+Инфляция)*(1+СтавкаДисконта))^(Год-0,5)

Затем нажмите клавиши Ctrl и Enter. В диапазоне А16:F18 остальные формулы введите самостоятельно.

Чтобы убедиться, что все сроки окупаемости рассчитаны правильно, построим графики всех трех кумулятивных потоков денежных средств. Выделите диапазоны А5:F5, А11:F11 и А16:F16. Нажмите кнопку Мастер диаграмм и выберите тип диаграммы График. Нажмите кнопку Готово.

Чтобы выделить несмежные диапазоны ячеек, нужно выделить первый диапазон ячеек обычным способом, а затем, нажав и удерживая клавишу Ctrl, выделить следующие диапазоны ячеек.

Для расчета чистого приведенного дохода NPV существует две финансовые функции - ЧПС() и ЧИСТНЗ() (см. Приложение). Функция ЧПС() используется, если платежи (поступления) происходят через равные промежутки времени. С учетом того, что все платежи (поступления) мы относили к середине года, в ячейку В20 введите формулу

=ЧПС(СтавкаДисконта;ЧистПотокДенСредств)*(1+СтавкаДисконта)^0,5

Обратите внимание, что полученное значение 2068348 совпадает со значением в ячейке F11.

Для расчета чистого приведенного дохода NPV с учетом инфляции нужной функции не существует, поэтому в ячейку В21 введите формулу =F16.

В ячейку В22 введите формулу

=-ЧПС(СтавкаДисконта;'Отчет о движ.'!C8:G8) *(1+СтавкаДисконта)^0,5

Эта формула предполагает, что все инвестиции осуществлялись в середине 2006 года. Однако данные инвестиционного плана (лист Инв. план) позволяют уточнить эту оценку. Перейдите на лист Инв. план и добавьте в таблицу этап №0 со стоимостью - р., а также добавьте столбец Момент инвестирования согласно рис. 6.4. Попытайтесь самостоятельно ввести формулы в диапазон Н3:Н7.

Теперь на рабочем листе Эффект в ячейку В23 введите формулу

=ЧИСТНЗ(СтавкаДисконта;'Инв. план'!F3:F7;'Инв. план'!H3:H7)

Функция ЧИСТНЗ() используется, если платежи (поступления) происходят через неравные промежутки времени (как в нашем случае). Очевидно, что уточненная оценка не существенно отличается от упрощенной оценки.

В ячейку В24 введите формулу =1+B20/B23, а в В25 формулу =1+B21/B23.

Рис. 6.4. Результирующий вид рабочего листа Инв. План

Для расчета внутренней нормы рентабельности (доходности) проекта IRR (Internal Rate of Return) служат финансовые функции ВСД() и ЧИСТВНДОХ(). Функция ВСД() используется, если платежи (поступления) происходят через равные промежутки времени. В ячейку В26 введите формулу

=ВСД(ЧистПотокДенСредств)

Функции для расчета IRR с учетом инфляции не существует. Поэтому в ячейку В27 необходимо ввести формулу

=(ВСД(ЧистПотокДенСредств)-Инфляция)/(1+Инфляция)

Рассчитанные показатели эффективности инвестиционного проекта свидетельствуют о его эффективности при заданной инвестором ставке дисконтирования e=15% , так как выполняются условия NPV>0, IRR>e и PI>1.

5. Анализ чувствительности инвестиционного проекта

Для исследования чувствительности проекта к изменениям различных условий можно использовать такое средство MS Excel как сценарии. Будем исследовать влияние изменения цены, объема сбыта и уровня инфляции на эффективность проекта (в частности, на NPV, IRR и срок окупаемости с учетом инфляции).

К сожалению, на листе Сбыт не представлены значения прогноза инфляции, NPV, IRR и срока окупаемости, а все изменяемые ячейки сценария должны находиться на активном листе (т.е. на листе Сбыт). Чтобы значение прогноза инфляции присутствовало на листе, перейдите на лист Общие данные, вырежьте диапазон А11:С11 (кнопка Вырезать ) и вставьте в любом месте на листе Сбыт (теперь ячейка с именем Инфляция располагается на листе Сбыт). Чтобы не перемещать ячейки результатов NPV, IRR и срок окупаемости РР с листа Эффект на лист Сбыт, создадим копии этих ячеек на листе Сбыт. В ячейку В10 введите формулу =Эффект!B21, в В11 введите =Эффект!B27, в В12 введите =Эффект!B18. Ячейке В10 присвойте имя NPV, В11 - IRR, В12 - PP.

Перейдите на лист Сбыт и выполните команду СервисСценарии… В окне Диспетчер сценариев нажмите кнопку Добавить… В окне Добавление сценария задайте Название сценария Базовый сценарий, Изменяемые ячейки C3;B6:F6;Инфляция и нажмите кнопку ОК. В окне Значения ячеек сценария указаны текущие значения изменяемых ячеек. Нажмите кнопку ОК.

Добавьте сценарий Рост цен на 10% (Изменяемые ячейки С3, Цена =130*1,1 или 143). Самостоятельно создайте сценарий Падение цен на 10%.

Добавьте сценарий Рост продаж на 10% (Изменяемые ячейки B6:F6, $B$6 =50000*1,1 или 55000, $С$6 =300000*1,1 или 330000 и т.д.). Самостоятельно создайте сценарии Падение продаж на 10%, Инфляция 15% и Инфляция 20%.

Для создания отчета по сценариям нажмите кнопку Отчет… в окне Диспетчер сценариев. В окне Отчет по сценарию укажите Тип отчета структура, Ячейки результата =$B$10:$B$12. Нажмите кнопку ОК. Проанализируйте созданный рабочий лист Структура сценария. Убедитесь, что наибольшее влияние на эффективность проекта имеет цена.

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

Чтобы получить удовлетворительный отчет с использованием сводной таблицы, необходимо предварительно удалить Базовый сценарий. После создания листа Сводная таблица по сценарию отредактируйте сводную таблицу. Для полей IRR и РР измените параметры поля - щелкните правой кнопкой мыши в нужном поле, выберите команду Параметры поля… и в окне Вычисление поля сводной таблицы задайте Операция Сумма, после чего нажмите кнопку ОК. Отформатируйте сводную таблицу так, чтобы она приняла следующий вид.

Рис. 6.5. Результирующий вид листа Сводная таблица по сценарию

Обратите внимание, что в сводной таблице результаты рассчитаны при совокупном действии сценариев в отличие от листа Структура сценария.

Лабораторная работа №7.Финансовые функции Microsoft Excel

Цель. Изучить некоторые финансовые функции Microsoft Excel и научиться использовать их для расчета различных экономических показателей, связанных с амортизацией основных фондов, анализом аннуитетов и т.д.

Задание

1. Активизировать все финансовые функции Excel.

2. Рассчитать величины амортизационных отчислений и остаточной стоимости основных фондов (задачи 1-4).

3. Рассчитать параметры аннуитетов (задачи 5-8).

4. Рассчитать схемы погашения кредитов (задачи 9-12).

Основные сведения

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

Технология работы

Задача 1. Первоначальная стоимость объекта 60000 руб. Срок полезного использования - 2 года. Объект вводится в эксплуатацию 1 мая 2004 года. Рассчитать норму амортизации, суммы амортизационных отчислений линейным методом, накопленный износ и остаточную стоимость по месяцам.

Запустите на выполнение программу Microsoft Excel, создайте рабочую книгу с именем Финансовые функции.xls. Переименуйте лист Лист1 с помощью команды меню ФорматЛистПереименовать лист. Задайте новое имя Задача 1

Введите данные на лист Задача 1 согласно рис. 7.1 (в ячейки Е3:Е4, С7:Е18 и G7:I18 данные пока вводить не надо).

Чтобы ввести названия месяцев, в ячейку В7 введите Январь, а затем, нажав левую кнопку мыши, «протащите» курсор по ячейкам В7:В18.

Рис. 7.1

При форматировании ячеек В6 и F6 воспользуйтесь командой меню ФорматЯчейки…Граница. Выравнивание текста в этих ячейках можно произвести с помощью пробелов.

В ячейку Е3 самостоятельно введите формулу для расчета нормы амортизации за один месяц. Норма амортизации рассчитывается по формуле , где n - срок полезного использования в месяцах.

В ячейке Е4 для расчета величины амортизационных отчислений за месяц используйте функцию АПЛ() (см. Приложение). Задайте аргументы Стоимость $Е$1, Остаток 0, Период $Е$2.

В ячейку С12 введите формулу =$E$4, а в ячейку С13 введите формулу =C12+$E$4. Скопируйте формулу из ячейки С13 в ячейки С14:С18.

В ячейку D7 введите формулу =C18+$E$4, а в ячейку D8 введите =D7+$E$4. Скопируйте формулу из ячейки D8 в ячейки D9:D18.

В ячейки Е7:Е11 скопируйте формулы из ячеек D7:D11.

В ячейки С7:С11 и Е12:Е18 введите 0.

Выделите диапазон ячеек С7:Е18 и задайте денежный формат данных (кнопка Денежный формат ).

В ячейку G11 введите формулу =$E$1-C11, а затем скопируйте эту формулу в соответствующие ячейки.

Задача 2. Решить задачу 1 при условии, что используется нелинейный метод начисления амортизации.

Создайте копию листа Задача 1 и переименуйте его в лист Задача 2.

В ячейку Е3 введите формулу для расчета нормы амортизации. Норма амортизации при нелинейном методе рассчитывается по формуле .

Строку 4 можно удалить.

Для расчета сумм амортизации при нелинейном методе используйте функцию ПУО(). Функция ПУО возвращает величину амортизации за один или несколько периодов, используя метод двойного процента (или иного явно указанного процента) со снижающегося остатка.

Синтаксис функции

ПУО(Стоимость;Остаток;Период;Нач_период;Кон_период;Коэф;Без_перекл)

Аргументы Стоимость, Остаток и Период имеют тот же смысл, что и для функции АПЛ.

Нач_период - это начальный период, для которого вычисляется амортизация.

Кон_период - это конечный период, для которого вычисляется амортизация. Нач_период и Кон_период должны быть заданы в тех же единицах, что и период.

Коэф - это коэффициент, используемый при вычислении нормы амортизации. Если Коэф опущен, то он полагается равным 2.

Без_перекл - это логическое значение. Если аргумент Без_перекл имеет значение ЛОЖЬ или опущен, то Microsoft Excel переключается на линейный метод начисления амортизации, если амортизация, исчисленная по линейному методу, больше амортизации, исчисленной по нелинейному методу. Если аргумент Без_перекл имеет значение ИСТИНА, то Microsoft Excel никогда не переключается на линейный метод начисления амортизации.

Введите в ячейку С11 формулу =ПУО($E$1;0;$E$2;0;A11-5) и скопируйте ее в ячейки С12:С17.

Обратите внимание, что пятый аргумент A11-5 в формуле =ПУО($E$1;0;$E$2;0;A11-5) позволяет задать порядковый номер месяца, для которого рассчитывается накопленный износ.

В ячейку D6 введите формулу =ПУО($E$1;0;$E$2;0;A6+7) и скопируйте ее в ячейки D7:D17.

Самостоятельно задайте формулу для ячейки Е6 и скопируйте ее в ячейки Е7:Е10.

На рис. 7.2 представлена полученная таблица.

Рис. 7.2

В результате можно убедиться, что, начиная с июня 2005 года, амортизация начисляется по линейному методу и составляет 1760 руб. ежемесячно. Однако, согласно Налоговому Кодексу линейный метод применяется, если остаточная стоимость достигнет 20% от первоначальной стоимости основных фондов, т.е. в нашем случае линейный метод можно применять, только начиная с декабря 2006 года.

Создайте копию листа Задача 2. На новом листе необходимо запретить переключаться на линейный метод амортизации в период с июня 2005 по ноябрь 2005. Для этого необходимо исправить соответствующие формулы в ячейках D6:D17, добавив два аргумента: Коэф равный 2 и Без_перекл равный ИСТИНА.

Вместо значения ИСТИНА можно использовать значение 1.

В результате в ячейке Н17 должно получиться значение 11 486 р., представляющее собой остаточную стоимость на 1 декабря 2005 года. Начиная с этого момента нужно применять линейный метод.

В ячейку Е6 введите формулу =D17+$H$17/5, а ячейку Е7 формулу =E6+$H$17/5. Скопируйте последнюю формулу в ячейки Е8:Е10. Полученный результат представлен на рис. 7.3.

Рис. 7.3

Задача 3. Решить задачу 1 при условии, что используется метод учета целых периодов службы основных фондов.

По данному методу суммируется число периодов службы основных фонд. В нашем случае 1+2+…++24=24*(24+1)/2=300. Тогда в первом периоде амортизация равна 60000*24/300=4800 руб., во втором - 60000*23/300=4600 руб. и т.д. Для вычисления амортизации за один период служит функция АСЧ().

Синтаксис функции

АСЧ(Стоимость;Остаток;Период;Текущий_период).

Аргументы Стоимость, Остаток и Период имеют тот же смысл, что и для функций АПЛ и ПУО.

Текущий_период - это период, для которого рассчитывается амортизация.

Создайте лист Задача 3. В итоге он должен иметь вид, представленный на рис. 7.4.

Рис. 7.4

В ячейку С10 введите формулу =АСЧ($E$1;0;$E$2;A10-5), а в ячейку С11 введите формулу =C10+АСЧ($E$1;0;$E$2;A11-5). Скопируйте формулу из ячейки С11 в ячейки С12:С16.

В ячейку D5 введите формулу =C16+АСЧ($E$1;0;$E$2;A5+7), а в ячейку D6 введите формулу =D5+АСЧ($E$1;0;$E$2;A6+7). Скопируйте формулу из ячейки D6 в ячейки D7:D16.

В ячейки Е5:Е9 формулы введите самостоятельно.

Задача 4. Построить графики, отображающие изменение остаточной стоимости для всех выше рассмотренных случаях.

Нажмите кнопку Мастер диаграмм и выберите тип диаграммы График. Нажмите кнопку Далее. В следующем окне щелкните по вкладке Ряд. Щелкните по кнопке Добавить и введите Имя Линейный метод. В поле Значения укажите диапазон данных

='Задача 1'!$G$11:$G$18;'Задача 1'!$H$7:$H$18;'Задача 1'!$I$7:$I$10;'Задача 1'!$I$11

Чтобы добавить на диаграмму следующий ряд, соответствующий нелинейному методу, используйте кнопку Добавить. Задайте Имя Нелинейный метод. В поле Значения укажите диапазон данных

='Задача 2'!$G$10:$G$17;'Задача 2'!$H$6:$H$17;'Задача 2'!$I$6:$I$10

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

Нажмите кнопку Далее. Задайте Название диаграммы Остаточная стоимость по периодам. Завершите создание диаграммы. В результате должна получиться диаграмма, представленная на рис. 7.5.

Рис.7.5

Задача 5. Рассчитать современную и будущую стоимости аннуитета за 10 лет, если величина каждого отдельного платежа 5000 руб., годовая процентная ставка 15%, платежи осуществляются в конце каждого года.

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

Различают будущую и современную стоимость аннуитета.

Будущая стоимость аннуитета , где n - общее число платежей (периодов); P_t - платеж, произведенный в начале или конце t-ого периода (зачастую рассматривают одинаковые размеры платежей, т.е. Р_t=Р); i_c - доходность платежей (ставка дисконта); _t - коэффициент наращивания.

Современная стоимость аннуитета , где _t - коэффициент дисконтирования.

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

Способ 1. Введите данные согласно рис. 7.6.

Рис.7.6

Чтобы ввести значения от 0 до 10 в ячейки В2:L2, введите 0 в ячейку В2, подведите курсор к черному квадратику в правом нижнем углу ячейки, чтобы курсор превратился в черный крестик. Нажмите и удерживайте клавишу Ctrl и, нажав левую кнопку мыши, «протащите» курсор по ячейкам C2:L2.

В ячейку B4 введите формулу =1/(1+$B$1)^B2, в ячейку L5 введите формулу =(1+$B$1)^(10-L2). Размножьте формулы по строке.

В ячейку В7 введите формулу =СУММПРОИЗВ(B3:L3;B4:L4). В ячейку В8 введите формулу =СУММПРОИЗВ(B3:L3;B5:L5).

Недостаток данного способа - необходимо вводить все платежи.

Достоинство - можно задавать различные величины платежей и ставки дисконта для каждого платежа.

Способ 2. Воспользуемся формулами для стоимости аннуитета.

Современная стоимость аннуитета постнумерандо , где Р - размер платежа. В ячейку С7 введите формулу =C3*(1-1/(1+B1)^10)/B1.

Будущая стоимость аннуитета постнумерандо . Самостоятельно введите соответствующую формулу в ячейку С8.

Способ 3. Воспользуемся встроенными функциями Excel ПС() и БС().

В ячейку D7 вставьте финансовую функцию ПС(). В открывшемся диалоговом окне задайте аргументы: Норма В1 Кпер 10 Выплата С3 Остальные аргументы можно не задавать. Нажмите клавишу ОК. В результате в ячейке D7 окажется формула =ПС(B1;10;C3)

Синтаксис функции ПС(Норма;Кпер;Выплата;Бз;Тип)

Норма - это процентная ставка дисконта (норма прибыли) за период. В случае, если, например, задана годовая ставка дисконта 18% и в течение года производятся ежемесячные платежи, то в качестве значения аргумента Норма нужно ввести 18%/12 или 1,5% или 0,015.

Кпер - это общее число периодов выплат аннуитета. В случае, если, например, аннуитет выплачивается в течение 4 лет, платежи делаются ежемесячно, то в качестве значения аргумента Кпер нужно ввести 4*12 или 48.

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

Бз - величина дополнительного платежа, производимого в последнем периоде. Если аргумент опущен, то он полагается равным 0.

Тип - это число 0 или 1. Если аргумент Тип равен 0 или опущен, то платежи осуществляются постнумерандо (в конце периода). Если аргумент Тип равен 1, то платежи осуществляются постнумерандо (в начале периода).

В финансовых функциях выплачиваемые деньги, такие как взносы в банк на накопление, представляются отрицательным числом, а полученные деньги, такие как дивиденды, представляются положительным числом. Например, взнос в банк на сумму 1 000 руб. представляется аргументом _1 000 руб. для вкладчика и представляется аргументом +1 000 руб. для банка.

Самостоятельно задайте в ячейке D8 формулу для вычисления будущей стоимости аннуитета, воспользовавшись функцией БС(). Аргументы этой функции аналогичны аргументам функции ПС(), за исключением четвертого аргумента Бз, который для функции БС() обозначается Нз и равен величине дополнительного платежа, производимого в самом первом периоде. Если аргумент опущен, то он полагается равным 0.

Задача 6. Инвестор предполагает накопить в течение 2 лет на счете в банке 150 тыс. руб. Платежи осуществляются в начале каждого месяца при годовой процентной ставке 10%. Рассчитать величину каждого платежа, если первоначальный взнос 30 тыс. руб.

Способ 1. Финансовые функции БС(), ПС(), КПЕР(), СТАВКА(), ПЛТ() взаимосвязаны. Excel выражает каждый финансовый аргумент через другие, используя формулу

,

где PV - стоимость вклада в самом первом периоде; FV - стоимость вклада в самом последнем периоде.

В данном случае FV=150000, PV= -30000; Тип=1, i_c=10%/12; n=2*12=24. Необходимо рассчитать Р.

Введите данные согласно рис. 7.7.

В ячейки С5 и С7 самостоятельно введите соответствующие формулы.

В ячейку Е10 введите формулу

= -(C2+C8*(1+C7)^C5)*C7/((1+C7)^C5-1)/(1+C7*C9)

Рис. 7.7

Способ 2. В ячейку F10 самостоятельно введите формулу, воспользовавшись функцией ПЛТ().

Задача 7. В аренду сдается здание, стоимостью 600 тыс. руб. Арендная плата выплачивается в течение 5 лет. Платежи осуществляются в конце каждого квартала. Требуемая норма прибыли 16%. Рассчитать величину каждого платежа, если арендодатель желает, чтобы к концу срока аренды современная стоимость аннуитета была равна стоимости здания.

Чтобы воспользоваться рабочим листом, полученным при решении задачи 6, скопируйте его и получившийся лист назовите Задача 7.

Внесите необходимые изменения в рабочий лист Задача 7 (для этого достаточно изменить исходные данные в ячейках С2:С9). В результате каждый платеж должен быть равен -20149,05 руб.

Задача 8. Инвестор предполагает накопить на счете в банке 900 тыс. руб. Планируется, что платежи в размере не более 100 тыс. руб. осуществляются в начале каждого года при годовой процентной ставке 15%. Рассчитать общее число платежей и величину каждого платежа.

Создайте лист Задача 8, скопировав лист Задача 6 или Задача 7.

Внесите необходимые изменения в рабочий лист согласно рис. 7.8 (для этого достаточно изменить исходные данные в ячейках С2:С9).

Рис. 7.8

Способ 1. Формула для определения числа платежей имеет вид . Формула получена при условии, что PV=0. Введите в ячейку Е3 соответствующую формулу. Предварительный размер платежа Р возьмите из ячейки С10.

Способ 2. Воспользуемся функцией КПЕР(). В ячейку F3 введите формулу

=КПЕР(C7;C10;C8;-C2;C9)

Получившееся дробное значение числа платежей округлите в большую сторону в ячейке С5 с помощью функции =ОКРУГЛВВЕРХ(F3;0). Обратите внимание, что рассчитанные в ячейках Е10 и F10 размеры платежей удовлетворяют ограничению в 100000 руб.

Задача 9. Предприятие предполагает взять кредит в размере 1500 тыс. руб. Банк предложил следующую схему платежей: ежеквартальные платежи в размере 250 тыс. руб.; платежи в конце квартала; кредит предоставляется на 2 года. Определить какую годовую норму прибыли использовал банк при расчете платежей.

Способ 1. Введите данные согласно рис. 7.9.

Рис.7.9

В строку 4 формулы введите самостоятельно (см. задачу 5, рис. 7.6).

В ячейку В7 формулу введите самостоятельно.

Для нахождения нормы прибыли воспользуемся средством Подбор параметра (команда меню СервисПодбор параметра). В диалоговом окне Подбор параметра задайте данные согласно рис. 7.10.



Рис. 7.10

В результате в ячейке В1 будет получена норма прибыли за квартал. Чтобы получить в ячейке В6 годовую номинальную норму прибыли нужно норму прибыли за квартал (ячейка В1) умножить на 4.

Способ 2. Введите в ячейку С6 формулу =4*ВСД(B3:J3)

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

Синтаксис функции ВСД(Платежи;Прогноз)

Платежи - диапазон ячеек, содержащих последовательность платежей.

Прогноз - это величина, о которой предполагается, что она близка к результату ВСД.

Задача 10. Покупатель взял в кредит 20 тыс. руб. на 12 месяцев под 25% годовых. Кредит погашается в течение года равными уплатами в конце каждого месяца. Рассчитать величину каждой уплаты, а также суммы, идущие на погашение основного долга и платежи за пользование кредитом.

Уплата в погашение кредита =Платежи в погашение основного долга + Проценты на оставшуюся сумму долга.

Введите данные согласно рис. 7.11.

В ячейку С4 введите формулу самостоятельно.

В ячейку В7 введите формулу =-ОСПЛТ($C$4;A7;$C$3;$C$1)

В ячейку С7 введите формулу =-ПРПЛТ($C$4;A7;$C$3;$C$1)

В ячейку D7 введите формулу =-ПЛТ($C$4;$C$3;$C$1)

В ячейку Е7 введите формулу =-ОБЩДОХОД($C$4;$C$3;$C$1;$A$7;A7;0)

В ячейку F7 введите формулу =-ОБЩПЛАТ($C$4;$C$3;$C$1;$A$7;A7;0)

В ячейку G7 введите формулу самостоятельно.

Скопируйте эти формулы в соответствующие ячейки.

Рис. 7.11

Задача 11. Покупатель взял в кредит 20 тыс. руб. на 12 месяцев под 25% годовых. Кредит погашается в течение года уплатами в конце каждого месяца. Рассчитать величину каждой уплаты, а также суммы, идущие на погашение основного долга и платежи за пользование кредитом, если основной долг погашается равными платежами.

Введите данные согласно рис. 7.12. Все необходимые формулы введите самостоятельно.

Рис. 7.12

Для расчета выплат процентов в данной схеме погашения кредита (столбец С) может использоваться функция ПРОЦПЛАТ(). Например, в ячейку С7 можно задать функцию

=ПРОЦПЛАТ($C$4;A7-1;$C$3;$C$1)

Задача 12. Решить задачу 11 при условии, что кредит был взят 10 декабря 2003 года. Платежи в банк должны поступать с периодичностью один месяц. Если дата платежа приходится на праздничный или выходной день, то дата платежа переносится на первый рабочий день, следующий за нерабочим днем. Долг погашается равными платежами.

Ожидаемый результат работы представлен на рис. 7.13.



Рис. 7.13

Чтобы найти дату предстоящего платежа воспользуемся функцией ДАТАМЕС(). Эта функция возвращает в числовом формате дату, отстоящую на заданное количество месяцев вперед или назад от заданной даты (Нач_дата). Функция ДАТАМЕС() используется для вычисления даты платежа, приходящейся на тот же день месяца, что и дата получения кредита.

Синтаксис функции ДАТАМЕС(Нач_дата;Число_месяцев)

Нач_дата - это начальная дата.

Число_месяцев - это количество месяцев до или после даты Нач_дата.

Например, если в ячейку В7 ввести формулу =ДАТАМЕС($C$4;A7), то в результате получим дату 10.01.04. Однако этот день приходится на нерабочий день - субботу.

Чтобы определить ближайший рабочий день, используют функцию РАБДЕНЬ().

Подробности об использовании функции РАБДЕНЬ() приведены в описании лабораторной работы №5.

Таким образом, чтобы найти ближайший рабочий день для даты 10.01.04 можно было бы ввести в ячейку В7 формулу

=РАБДЕНЬ(ДАТАМЕС($C$4;A7);0;$G$7:$G$14)

Однако в итоге все равно получаем ту же дату 10.01.04. Это связано с тем, что значение параметра Количество_дней не должно быть равным нулю. Чтобы «обмануть» функцию РАБДЕНЬ() необходимо немного изменить формулу в ячейке В7, отняв от Нач_дата один день и задав параметр Количество_дней равным 1

=РАБДЕНЬ(ДАТАМЕС($C$4;A7)-1;1;$G$7:$G$14)

Скопируйте полученную формулу в соответствующие ячейки.

Для вычисления суммы выплачиваемых процентов обычно используют формулу

Тогда в ячейку D7 необходимо ввести формулу =C1*$C$2/365*(B7-C4), а в ячейку D8 введите формулу =($C$1-СУММ(C$7:C7))*$C$2/365*(B8-B7)

Последнюю формулу необходимо скопировать в ячейки D9:D18.

Лабораторная работа №8. Финансовые функции Microsoft Excel-2

Цель. Изучить некоторые финансовые функции Microsoft Excel, связанные с анализом рынка ценных бумаг.

Задание

1. Рассчитать величины амортизационных отчислений и остаточной стоимости основных фондов (задачи 1-4).

2. Рассчитать параметры аннуитетов (задачи 5-8).

3. Рассчитать схемы погашения кредитов (задачи 9-12).

Основные сведения

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

Технология работы

Задача 1. Банк предлагает заемщику потребительский кредит на условиях 20% годовых, платежи ежемесячные. Рассчитать эффективную процентную ставку по кредиту.

Последнюю формулу необходимо скопировать в ячейки D9:D18.

Лабораторная работа №9. Решение оптимизационных задач средствами Microsoft Excel

Цель. Изучить средства Microsoft Excel, связанные с решением оптимизационных задач.

Задание

1. Найти точку безубыточности для данного вида продукции (задачи 1-2. Рассчитать показатели эффективности инвестиционных проектов и выбрать наиболее эффективный из них (задача 3).

2. Решить задачу оптимизации производственной программы предприятия (задача 4).

3. Решить задачу о назначении (задача 5).

4. Решить транспортную задачу (задача 6).

5. Найти оптимальный портфель ценных бумаг (задачи 7 и 8).

Основные сведения

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

Технология работы

Задача 1. Банк предлагает заемщику потребительский кредит на условиях 20% годовых, платежи ежемесячные. Рассчитать эффективную процентную ставку по кредиту.

Лабораторная работа №10. Прогнозирование средствами Microsoft Excel

Цель. Изучить средства Microsoft Excel, позволяющие строить прогнозы различных экономических показателей.

Задание

1. Построить прогноз методом скользящего среднего (задача 1).

2. Построить прогноз методом экспоненциального сглаживания (задача 2).

3. Построить прогноз методом аналитического выравнивания (задача 3).

4. Рассчитать показатели эффективности инвестиционных проектов и выбрать наиболее эффективный из них (задача 3).

5. Решить задачу оптимизации производственной программы предприятия (задача 4).

6. Решить задачу о назначении (задача 5).

7. Решить транспортную задачу (задача 6).

8. Найти оптимальный портфель ценных бумаг (задачи 7 и 8).

Основные сведения

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

Технология работы

Задача 1. Банк предлагает заемщику потребительский кредит на условиях 20% годовых, платежи ежемесячные. Рассчитать эффективную процентную ставку по кредиту.

Список литературы

1. Бажин И.И. Информационные системы менеджмента. - М.: ГУ-ВШЭ, 2000.

2. Богданов В.В. Управление проектами в Microsoft Project 2003. - СПб.: Питер, 2004.

3. Ильина О.И. Информационные технологии бухгалтерского учета. - СПб.: Питер, 2001.

4. Информатика для юристов и экономистов. Учебник/Под ред. В.А. Симоновича. - СПб.: Питер, 2000.

5. Карлберг К. Бизнес-анализ с использованием Excel. - К.:Диалектика, 1997.

6. Маклаков С.В. BPwin и ERwin. CASE-средства разработки информационных систем.-М.:Диалог-МИФИ, 2000.

7. Экономическая информатика. Учебник/Под ред. В.В. Евдокимова. - СПб.: Питер, 1997.

8. Экономическая информатика. Учебник/Под ред. П.В. Конюховского, Д.Н. Колесова. - СПб.: Питер, 2000.

Приложение

В новых версиях программы Microsoft Excel (Excel 2003, Excel XP и Excel 2007) произошли изменения имен некоторых финансовых функций. В таблице приведены старые (Excel 97 и Excel 2000) и новые имена (Excel 2003, Excel XP и Excel 2007) некоторых функций.

Имена других функций, по-видимому, изменений не претерпели.

Excel 97 и Excel 2000	Excel 2003, Excel XP и Excel 2007
АМР	АПЛ
НПЗ	ЧПС
ВНДОХ	ВСД
ПДОБ	ПУО
АМГД	АСЧ
БЗ	БС
ПЗ	ПС
НОРМА	СТАВКА
ППЛАТ	ПЛТ
ОСНПЛАТ	ОСПЛТ
ПЛПРОЦ	ПРПЛТ

Размещено на Allbest.ru

...