ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ MS EXCEL ДЛЯ ФИНАНСОВОГО АНАЛИЗА ПРЕДПРИЯТИЯ

Цель работы: изучить финансовые функции программы MS Excel 2003.

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

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

Пример. Рассмотрим расчет 30-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате с помощью функции ПЛТ. Для приведенного на рис. 1 ипотечного расчета в ячейки введены формулы, показанные на рис. 2.

Функция ПЛТ вычисляет величину постоянной периодической выплаты ренты при постоянной процентной ставке.

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

Более подробное описание аргументов функции ПЛТ см. в описании функции ПС.

Ставка — процентная ставка по ссуде.

Кпер — общее число выплат по ссуде.

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

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

Тип — число 0 (ноль) или 1, обозначающее, когда должна производиться выплата.

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

Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные

!j*J              QMfr.4              fru              ГАт-jjpra              Cjy*»*              ?*ий              ?nwe*e

A '%: ’ • ¦              4              *              й              *              u              ;

Crvr^^jc

.

*jr

S 51* #•

:* - Д. - ’

. г

X Л 4

Иіладные длины*

20'Ш

20% a%

!0t67O er^U'+C^’-i'OTP 0              ыетз j(igt;

9 Ciul*' ПгГ'.ЧЫ^ИЛ ССуДЫ              ;5gt;j              и=lt;Г*^?-

ЇО              Pjc'wi

U ПйрнОДіГ+еШ"? ЄДЛЛ-gt;П!              1              IfcS.fbp

12. обід;»» суй*** выплат              іgt;gt; № ' ¦ р
13. общая сунил *:дш1Сlt;«оимыgt;              2?0’і,ЙОр

14

м 4 * «\Л*ст1^‘Пие:г2/ГХТЗ/              |

4 qenj

6              fO-ИСК

6. I'tUOtf»» ПрЛЦбЛГтЭ;* i'T3|S;« 4
7. ріі.ш-гр ссуды

и ід;.

4 У) Я 1'2,

3€8 то:..

нг

Рис. 1. Расчет ипотечной ссуды

Mtoo*»ft 1«*1 - Кмкгаї

3*j фа«1 amp;ие*д Еид Встар+-д 'Scctyt Cjf»ic Дайн»* ?»жlt; ?лр**;* Стгґ#*9е              .              ?              X

ІЗ i# В г/ 41 «і А Г 1 Эь © - -'f * • -              *              * - Si Si » •¦’•              @

iWCvr.              .30. Ж К Ч ІЕ * ж fH ф •»;) V'              .              •              -*              •              Д.              •              *.

GI              -              А

‘                   ~              с ' о                                г-

в

ГЮ^ОДНЫ-? Р^І\П,)Т\4

эз

-ПГГГ|Е6,09.'В?|

-Р9’0П

=8Г(!-В5gt;

9 йтсвч :f шчт

1! *пігг©блгде..в7)

и ~ш*аи її =amp;і;-в/

14

15

н 4 > gt;4 \Лlt;т І х’ *V*r2 ^ fVer3 /

Гй+cw

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

выплаты по четырехгодичному займу из расчета 12% годовых, то используйте 12% : 12 для задания аргумента «ставка» и 4 х 12% для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента «ставка» и 4 для задания аргумента «кпер».

Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ПЯТ значение на величину «кпер».

Задача 1. Вас просят дать в долг 10 ООО руб. и обещают вернуть через год 2000 руб., через два года — 4000 руб., через три года — 7000 руб. При какой годовой процентной ставке эта сделка выгодна?

На рисунке 3 при расчете в ячейку В8 введена формула:

= ЧПС(В7;ВЗ:В5).

Кроме того, для автоматизации составления таблицы в ячейку С6 введена формула:

=ЕСЛИ(В6=1 ;”год”;ЕСЛИ(И(В6gt;=2;В6lt;=4);”года”;”лет”)). Первоначально в ячейку В7 вводится произвольный процент, например 3%. После этого выбираем команду «Сервис», «Подбор параметра», как показано на рис. 4.

Рис.

В поле «Установить в ячейке» делаем ссылку на ячейку В8, в которой вычисляется чистый текущий объем вклада по формуле:

= ЧПС(В7;ВЗ:В5).

Изменяя значение ячейки: | |Щт[              ’Vi

OK              I Отмена {

Рис. 4. Диалоговое окно «Подбор параметра»

В поле «Значение» укажем 10 ООО — размер ссуды. В поле «Изменяя значение ячейки» укажем ссылку на ячейку В7, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10 ООО руб. Результат вычисления выводится в ячейку В7. В нашем случае годовая учетная ставка равна 11,79%. Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.

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

Синтаксис:

ЧПС(ставка;значение1 ;значение2; ...)

Ставка — ставка дисконтирования за один период.

Значениеі, значение2, ... — от 1 до 29 аргументов, представляющих расходы и доходы.

Значение 1, значение2, ... должны быть равномерно распределены во времени, выплаты должны осуществляться в конце каждого периода.

ЧПС использует порядок аргументов значениеі, значение2,... для определения порядка поступлений и платежей. Убедитесь в том, что ваши платежи и поступления введены в правильном порядке. Аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел, учитываются; аргументы, которые являются значениями ошибки или текстами, которые не могут быть преобразованы в числа, игнорируются.

Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, текст или значения ошибок в массиве или ссылке игнорируются.

Считается, что инвестиция, значение которой вычисляет функция ЧПС, начинается за один период до даты денежного взноса значение 1 и заканчивается с последним денежным взносом в списке. Вычисления функции ЧПС базируются на будущих денежных взносах. Если первый денежный взнос приходится на начало первого периода, то первое значение следует добавить к результату функции ЧПС, но не включать в список аргументов. ЧПС аналогична функции ПС (текущее значение). Основное различие между функциями ПС и ЧПС заключается в том, что ПС допускает, чтобы денежные взносы происходили либо в конце, либо в начале периода. В отличие от денежных взносов переменной величины в функции ЧПС, денежные взносы в функции ПС должны быть постоянны на весь период инвестиции.

ЧПС также связана с функцией ВСД (внутренняя ставка доходности). ВСД — это ставка, для которой ЧПС равняется нулю: ЧПС(ВСД(...); ...) = 0.

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

Синтаксис:

ВСД(значения; предположение)

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

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

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

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

Microsoft Excel использует метод итераций для вычисления ВСД. Начиная со значения «предположение», функция ВСД выполняет циклические вычисления, пока не получит результат с точностью 0,0000%. Если функция ВСД не может получить результат после 20 попыток, то выдается значение ошибки #ЧИСЛО!.

В большинстве случаев нет необходимости задавать предположение для вычислений с помощью функции ВСД. Если предположение опущено, то оно полагается равным 0,1 (1%).

Если ВСД возвращает значение ошибки #ЧИСЛО! или если результат далек от ожидаемого, можно попытаться выполнить вычисления еще раз с другим значением аргумента «предположение».

ВСД тесно связана с функцией ЧПС. Ставка доходности, вычисляемая ВСД, связана с нулевой чистой текущей стоимостью. Взаимосвязь функций ЧПС и ВСД отражена в следующей формуле: ЧПС(ВСД(В1 :В6);В1 :В6) равняется 3.60Е-08 [Учитывая точность расчета для функции ВСД, значение 3.60Е-08 можно считать 0 (нулевым)].

Таким образом, данную задачу можно решить также с помощью функции ВСД. Для этого в ячейку В2 вместо 10000 руб. надо ввести -10000, а в ячейку В7 — функцию ВСД(В2:В5), которая и найдет минимальную годовую учетную ставку.

Задача 2. У вас просят в долг 10 000 руб. и обещают возвращать по 2000 руб. в течение шести лет. Будет ли выгодна эта сделка при годовой ставке 7%?

В приведенном на рис. 5 расчете в ячейку В5 введена формула: =ПС(В4;В2;-В3)

Кроме того, для автоматизации составления таблицы в ячейки С2 и В6 введены формулы:

=ЕСЛИ(В2=1 ;”год”;ЕСЛИ(И(В2gt;=2;В2lt;=4);”года”;”лет”)). =ЕСЛИ(В1 lt;В5;”Выгодно дать деньги в долг”;ЕСЛИ(В5=В1; ’’Варианты равносильны”;”Выгоднее деньги положить под проценты”)).

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

тшшмяшт

Wm Гулы: л              Эстара

Стог«Рлде              »              X

rv ы= а ,-; *1 «* 'Л f .Ї ih «- Чг «-г • 4. у. - S; if ф              ;

Чк)Су              -              10              . X Л lt;j "¦ « « Г--1 *5» %              -              1»              •              Д              •              Ї

Чг              -              Г.

І і

: ? tp«»

( 'і »,-gt;:йГ'..-дно --• ;• СС'-МД.П'Чио дии^ги ¦ -5 Фд:Ф -я «ргцздНи:и C 'jH' i

[ S -»vh'7'«;гі‘» #в» гд^и ОІИтеИ Р'/ЗД*

• и ЦН'-'Д

_у__              ^                Е

:*3® tlt; пат

ДШ ’%

%33

Ой г о дней думгги п*ч год проде-»*:ы

ІІ..

- 1 и

К

|М * > w              /ПиетЗ/

Гlt;»?піО

Рис. 5. Расчет эффективности капиталовложений

Синтаксис:

ПС(ставка;клер;ллт;бс;тип)

Ставка — процентная ставка за период. Например, если получена ссуда на автомобиль под 10% годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10% : 12 или 0,83%. В качестве значения аргумента «ставка» нужно ввести в формулу 10% : 12, или 0,83%, или 0,0083.

Клер — общее число периодов платежей по аннуитету. Например, если получена ссуда на четыре года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4x12 (или 48) периодов. В качестве значения аргумента «кпер» в формулу нужно ввести число 48. Плт — выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12% годовых составит 263,33 руб. В качестве значения аргумента «выплата» нужно ввести в формулу число -263,33.

Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50 000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.

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

Пример 2. Рассмотрим вычисление основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 100 ООО руб на срок пять лет при годовой ставке 2% (рис 6).

шшв

‘ІЗ              Of-'***              lt;y*ti

I : Л У с-і '* ф ' '¦ -

toamp;’Zy*              »              10              »              Ж              А              Ч              Й‘              -L#              ^              '

П? -              ґ,

А              ~~              ~~Я              '              V              С“

З Lpj*              6              С              lt;5!

З у+-ргодн«-». рл^-гл

р іімрр '6 ,'?U

! ГГУЇС

f ?*..

І 5

4 » И \ Лигг I / two /ГУКГ'і? / I * ;

Рис. 6. Вычисление основных платежей и платы по процентам

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

=ПЛТ(процент;срок;-размер_ссуды), где ячейки В1, В2, В4 имеют имена, процент, срок и размер ссуды, соответственно. Присвоение имени ячейке осуществляется с помощью команды «Вставка», «Имя», «Присвоить». За первый год плата по процентам в ячейке В7 вычисляется по формуле.

=D6xnpoueHT.

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

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

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

=D6-C7.

За оставшиеся годы эта плата определяется с помощью протаскивания маркера заполнения выделенного диапазона В7: D7 вниз по столбцам. Отметим, что основную плату по процентам можно было найти с помощью функций ОСПЛТ и ПРПЛТ соответственно.

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

Синтаксис:

ПРПЛТ(ставка;период;кпер;пс;бс;тип)

Функция ОСПЛТ возвращает величину платежа в погашение основной суммы по инвестиции заданный период на основе постоянства периодических платежей и постоянства процентной ставки.

Синтаксис:

ОСПЛТ(ставка;период;кпер;пс;бс;тип)

Аргументы данных функций:

Ставка — процентная ставка за период;

Период — это период, для которого требуется найти платежи по процентам; должен находиться в интервале от 1 до «кпер»;

Кпер — общее число периодов выплат годовой ренты;

Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей;

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

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

Эти функции взаимосвязаны между собой.

Функция БС вычисляет будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки. Функция БС подходит для расчета итогов накоплений при ежемесячных банковских взносах.

Синтаксис:

БС(ставка;кпер;плт;пс;тип)

Ставка — это процентная ставка за период;

Кпер — это общее число периодов платежей по аннуитету;

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

Пс — это приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей. Если аргумент бс опушен, то он полагается равным 0. В этом случае должно быть указано значение аргумента плт;

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

Пример 3. Рассмотрим порядок использования функции БС. Предположим, вы хотите зарезервировать деньги для специального проекта, который будет осуществлен через год. Для этого вы собираетесь вложить 1000 руб. при годовой ставке 6%, а затем вкладывать по 100 руб. в начале каждого месяца в течение года. Сколько денег будет на счете в конце 12 месяцев? С помощью формулы:

=БС(6%/12;12;-100;-1000;1) получаем ответ: 2301,40 руб.

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

Синтаксис:

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

Ставка — процентная ставка за период.

Пят — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно платеж состоит из основного платежа и платежа по процентам и не включает налогов и сборов.

Пс — приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей.

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

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

Например, если вы берете в долг 1000 руб. при годовой ставке 1% и собираетесь выплачивать по 100 руб. в год, то число выплат вычисляется следующим образом:

=КПЕР(1 %;-100;1000).

В результате получим ответ: 11.

Функция СТАВКА возвращает процентную ставку по аннуитету за один период. СТАВКА вычисляется путем итерации и может давать нулевое значение или несколько значений. Если последовательные результаты функции СТАВКА не сходятся с точностью 0,0000001 после 20 итераций, то СТАВКА возвращает сообщение об ошибке #ЧИСЛО!.

Синтаксис:

СТАВКА] кпер; пят; п с; бс;тип; предположение)

Кпер — общее число периодов платежей по аннуитету.

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

Пс — приведенная к текущему моменту стоимость или. общая сумма, которая на текущий момент равноценна ряду будущих платежей.

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

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

Пример 4. Рассмотрим использование функции СТАВКА. Чтобы определить процентную ставку для четырехлетнего займа размером в 8000 руб. с ежемесячной выплатой 200 руб., можно использовать формулу =СТАВКА(48;-200;8000) в результате получаем: месячная (так как период равен месяцу) процентная ставка равна 0,77%.

Задания для самостоятельной работы

1. Вычислить н-годичную ипотечную ссуду покупки квартиры за Р руб. с годовой ставкой г'% и начальным взносом А%. Сделать расчет для ежемесячных и ежегодных выплат.

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

Вопросы для самоконтроля

1. Какие финансовые функции MS Excel вам известны?
2. Какие финансовые функции используются для вычисления простых процентов?
3. Какие финансовые функции используются для вычисления сложных процентов?
4. Какие финансовые функции используются для вычисления постоянной ренты?
5. Какие финансовые функции используются для анализа общего потока платежей?
6. Какие финансовые функции используются для анализа инвестиционных проектов?
7. Как решаются задачи на сравнение инвестиционных проектов?