Анализ инвестиций в MS Excel

Лабораторная работа №1

 

Тема:  «Анализ инвестиций в MS Excel»

Цель: изучить инвестиционные функции MS Excel и их использование.

Ход работы:

Вариант №1

1.Вычислить 7-годичную ипотечную ссуду покупки машины за 170000 руб. с годовой ставкой 5 % и начальным взносом 10 %. Сделать расчёт для ежемесячных и ежегодных выплат.

Решение: Расчеты производились в Excel. На рис.1 показаны исходные и вычисленные данные, а на рис.2 формульный вариант вычисления данных.

Расчет ипотечной  ссуды
Исходные  данные
Цена	170000
Первый взнос	0,1
Годовая процентная ставка	0,05
Размер ссуды	153000
	Ежемесячные выплаты		Ежегодные выплаты
Срок погашения ссуды	84	месяца	7	лет
Расчет  результата
Периодические выплаты	2 162,49р.		26 441,43р.
Общая сумма выплат	181 649,00р.		185 090,03р.
Общая сумма комиссионных	28 649,00р.		32 090,03р.

Рис.1.Исходные и вычисленные данные

 

Функция ППЛАТ (ПЛТ) вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке.

Синтаксис:

ППЛАТ (ПЛТ) (ставка; кпер; нз (пс); бз(бс); тип)

Аргументы:

ставка - Процентная ставка за период

кпер - Общее число периодов выплат

нз(пс) - Текущее значение, т. е. общая сумма, которую составят будущие платежи

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

тип - Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 - то в начале периода.

 

Рис.2. Формульный вариант вычисления данных

 

2.Вас просят дать в долг 17000 руб. и обещают вернуть 5000 руб. через год, 7000 руб. – через два года, 8000 руб. – через три года. При какой годовой процентной ставке эта сделка имеет смысл?

Решение: На рис.1 приведен пример формульного варианта вычисления.

Рис.1. Формульный вариант вычислений

 

Первоначально в ячейку В6 ввела произвольный процент - 3%.

На рис.2 приведено решение задачи с помощью Подбора Параметра и показан результат вычислений.

 

 

Рис.2. Решение  задачи

 

Здесь годовая учетная ставка равна 8%. При ставке 8% и выше выгоднее дать деньги в долг, чем положить в банк.

Функция НПЗ(ЧПС) или в теории NPV возвращает чистый текущий объем вклада, вычисляемый на основе ряда последовательных поступлений наличных и нормы амортизации. Чистый текущий объем вклада — это сегодняшний объем будущих платежей (отрицательные значения) и поступлений (положительные значения).

Эту задачу можно попробовать решить также с помощью функции ВНДОХ(ВСД). Для этого в ячейку В1 вместо 17000 р. надо ввести – 17000 р., а в ячейку В6 — функцию ВНДОХ(ВСД) (В1:В4) (см.рис.3), которая и найдет минимальную годовую учетную ставку. Для вычисления ВНДОХ(ВСД) Excel использует метод итераций. Начиная со значения прогноз, функция ВНДОХ(ВСД) выполняет циклические вычисления, пока не получит результат с точностью 0,00001. Если функция ВНДОХ(ВСД) не может получить результат после 20 попыток, возвращается значение ошибки #ЧИСЛО!

 

Рис.3.Решение  задачи с помощью функции ВНДОХ(ВСД)

 

3.Вас просят дать в долг 170000 руб. и обещают возвращать по 30000 руб. в течение 7 лет. При какой процентной ставке эта сделка имеет смысл?

Решение: Для решения использую функцию ПС и подбор параметра. ПС в теории PV – возвращает текущий объем вклада на основе постоянных периодических платежей. Функция ПС аналогична функции ЧПС. Основное различие между ними заключается в том, что функция ПС допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. Кроме того, в отличие от функции ЧПС, денежные взносы в функции ПС должны быть постоянными на весь период инвестиции. Чистый текущий объем вклада =ПС(B3;B5;-B2). Далее через подбор параметра определю минимальную годовую процентную ставку, при которой выгодно давать деньги в долг. Она = 1,66%. Если ставка будет ниже, лучше положить деньги в банк. Потом надо узнать годовую процентную ставку, при которой выгодно давать деньги в долг по формуле в ячейке B3: = СТАВКА(B5;-B2;B1). Получу результат 6%. Значит, эта сделка имеет смысл при процентной ставке больше 6%. Если ставка будет ниже, выгодно положить деньги в банк.

Размер ссуды	170000
Ежегодно возвращаемые деньги	30000
Годовая процентная ставка	6%
Чистый текущий объем вклада	170 000,00р.
Срок	7

 

Размер ссуды	170000
Ежегодно возвращаемые деньги	30000
Годовая процентная ставка	=СТАВКА(B5;-B2;B1)
Чистый текущий объем  вклада	=ПС(B3;B5;-B2)
Срок	7

Рис.4.Нахождение процентной ставки

 

Удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис, Сценарии предоставляет такую возможность с одновременным автоматизированным составлением отчета. Рассмотрю способ применения этой команды для следующих трех комбинаций срока и суммы ежегодно возвращаемых денег: 7, 30000; 11, 20000 и 10, 21000. Выберу команду Сервис, Сценарии. В открывшемся диалоговом окне Диспетчер сценариев для создания первого сценария нажму кнопку Добавить. В диалоговом окне Добавление сценария в поле Название сценария введу, например ПС1, а в поле Изменяемые ячейки - ссылку на ячейки B2 и В5, в которые вводятся значения параметров задачи (срок и сумма ежегодно возвращаемых денег). После нажатия кнопки ОК появится диалоговое окно Значения ячеек сценария, в поля которого введу значения параметров для первого сценария. С помощью кнопки Добавить последовательно создам нужное число сценариев. С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию. Нажатие кнопки Отчет открывает диалоговое окно Отчет по сценарию. В этом окне в группе Тип отчета необходимо установить переключатель в положение Структура или Сводная таблица, а в поле Ячейки результата дать ссылку на ячейки, где вычисляются значения результирующих функций. После нажатия кнопки ОК создается отчет.

4.Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды 170000 руб. под годовую ставку 5 % на срок 7 лет.

Решение: Ежегодная плата вычисляется в ячейке ВЗ по формуле =ППЛАТ(процент; срок; -размер_ссуды). За первый год плата по процентам в ячейке В7 вычисляется по формуле

=D6*процент

Основная плата в  ячейке С7 вычисляется по формуле

=ежегодная_плата-В7

где ежегодная_плата — имя ячейки ВЗ. Остаток долга в ячейке D7 вычисляется по формуле

=D6-C7

В оставшиеся годы эти  платы определяются с помощью  протаскивания маркера заполнения выделенного диапазона B7:D7 вниз по столбцам.

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

Решение задачи представлено на рис.5.

 

Рис.5.Решение

 

5. Вы берёте в долг 170000 руб. под годовую ставку 3 % и собираетесь выплачивать по 31000 руб. в год. Сколько лет займут эти выплаты?

Решение: Функция КПЕР или в теории NPER вычисляет общее количество периодов выплаты для данного вклада на основе периодических постоянных выплат и постоянной процентной ставки.

Синтаксис:

КПЕР (ставка; выплата(плт); нз(пс); бз(бс); тип)

Аргументы:

ставка - Процентная ставка за период

выплата(плт) - Величина постоянных периодических платежей

нз(пс) – Текущее значение, т.е. общая сумма, которую составят будущие платежи

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

тип - Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — то в начале периода.

=КПЕР(3%;-31000;170000)

В результате получаю ответ: 6.

 

6.Вы собираетесь вкладывать по 200 руб. В течение 10 лет при годовой ставке 2%. Сколько денег будет на счёте через 10 лет?

Решение: Функция БЗ(ПС) или в теории FV вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БЗ(ПС) подходит для расчёта итогов накоплений при ежемесячных банковских взносах.

Синтаксис:

БЗ(ПС) (ставка; кпер; выплата(плт); нз(пс); тип)

Аргументы:

ставка - Процентная ставка за период

кпер - Общее число периодов выплат

выплата(плт) - Величина постоянных периодических платежей

нз(пс) - Текущее значение, т. е. общая сумма, которую составят будущие платежи

тип - Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 — в начале периода.

С помощью формулы: =БС(2%;10;-200), получаю ответ: 2 189,94р.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Ответы на вопросы:

1.Для чего используется функция ПЛТ?

Ответ: Функция ППЛАТ (ПЛТ) вычисляет величину постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке.

Синтаксис:

ППЛАТ (ПЛТ) (ставка; кпер; нз (пс); бз(бс); тип)

Аргументы:

ставка - Процентная ставка за период

кпер - Общее число периодов выплат

нз(пс) - Текущее значение, т. е. общая сумма, которую составят будущие платежи

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

тип - Число 0 или 1, обозначающее, когда должна производиться выплата. Если тип равен 0 или опущен, то оплата производится в конце периода, если 1 - то в начале периода.

 

2.Как рассчитать эффективность неравномерных капиталовложений с помощью функций MS Excel?

Ответ: Для расчета эффективности неравномерных капиталовложений используются функции НПЗ(ЧПС), ВНДОХ(ВСД) и Подбор параметра.

 

3.Для чего используется  сервисная функция Подбор параметра?

Ответ: Подбор параметра является частью блока задач, который иногда называют инструментами анализа "что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.

 

4.Как построить несколько сценариев  решения задачи?

Ответ: Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. Команда Сервис, Сценарии (Tools, Scenarios) предоставляет такую возможность с одновременным автоматизированным составлением отчета. Надо выбрать команду Сервис, Сценарии (Tools, Scenarios). В открывшемся диалоговом окне Диспетчер сценариев (Scenarios Manager) для создания первого сценария нажать кнопку Добавить (Add).

 

5.В чём отличие функций ПС  и БС?

Ответ: Функция ПС - возвращает приведенную (к текущему моменту) стоимость инвестиции. Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат. Функция БС - возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки.