Финансовый анализ в Excel

ФИНАНСОВЫЙ АНАЛИЗ В EXCEL

Задание № 1

Описание функции ПЛТ или ППЛАТ (ставка ; кпер; пс; бс; тип)

Возвращает сумму периодического платежа на основе постоянства сумм платежей и постоянства процентной ставки.

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

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

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

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

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

Пример

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

(ПЛТ).

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

Отметим, что очень важно быть последовательным в выборе единиц измерения для задания аргументов СТАВКА и КПЕР. Например, если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12% годовых, то для задания аргумента СТАВКА используйте 12%/12, а для задания аргумента КПЕР – 4*12. Если вы делаете ежегодные платежи по тому же займу, то для задания аргумента СТАВКА используйте 12%, а для задания аргумента КПЕР – 4.

Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, умножьте возвращаемое функцией ППЛАТ значение на величину КПЕР. Интервал выплат – это последовательность постоянных денежных платежей, осуществляемых за непрерывный период.

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

Например, депозит в банк на сумму 1000 рублей представляются аргументом -1000, если вы вкладчик, и аргументом 1000, если вы представитель банка.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вычислить n-годичную (общее число периодов выплат) ипотечную ссуду покупки квартиры за Р руб. с годовой ставкой i % и начальным взносом A%, . Cделать расчет для ежемесячных и ежегодных выплат. Найти суммы периодических ежемесячных и ежегодных выплат, общие суммы ежемесячных и ежегодных выплат, общие суммы ежемесячных и ежегодных комиссионных.

Для выполнения задания заполните таблицу своими исходными данными:

Стоимость квартиры – Р

Годовая ставка i%

Срок погашения ссуды n

Начальный взнос A%

Начальный взнос в денежном выражении рассчитывается по формуле:

стоимость квартиры*А%

Ежегодные выплаты рассчитываются по функции

(ПЛТ(ставка ; кпер; пс; бс; тип) либо ППЛАТ(ставка; срок; -ссуда);

ежемесячные выплаты

ППЛАТ(ставка/12; срок*12; -ссуда)), либо ПЛТ(ставка/12; срок*12; -ссуда)

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

Общие ежемесячные = ежемесячные*срок*12

Общие ежегодные = ежегодные*срок

Ежемесячные комиссионные = общие ежемесячные – ссуда

Ежегодные комиссионные = общие ежегодные – ссуда

ВАРИАНТЫ ЗАДАНИЙ

ЗАДАНИЕ №2

ЧПС (ставка; значение1; значение2; …) или НПЗ (ставка; значение1; значение2; …)

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

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

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

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

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

Пример 1

В примере начальные затраты в 10 000 руб. были включены как одно из значений, поскольку выплата производилась в конце первого периода.

Пример 2

В этом примере начальные затраты в 40 000 руб. не были включены как одно из значений, поскольку выплата производилась в начале первого периода.

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

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

=НПЗ (В6; В2:В4)

Первоначально в ячейку В6 вводится произвольный процент, например 3%. После этого выбираем команду Сервиз, Подбор параметра и заполняем открывшееся диалоговое окно Подбор параметра.

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

=НПЗ (В6; В2:В4)

В поле Значение указываем 10000 – размер ссуды. В поле Изменяя значение ячейки даем ссылку на ячейку В6, в которой вычисляется годовая процентная ставка. После нажатия кнопки ОК средство подбора параметров определит, при какой годовой процентной ставке чистый текущий объем вклада равен 10000 рублей. Результат вычисления выводится в ячейку В6.

В нашем случае годовая учетная ставка равна 11,79%.

Вывод: если банки предлагают большую годовую процентную ставку, то предлагаемая сделка не выгодна.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ: Вас просят дать в долг Р рублей и обещают вернуть Р1 руб. через год, Р2 руб. – через два года и т.д. и, наконец, РН руб. через Н лет. При какой годовой процентной ставке эта сделка имеет смысл? (ЧПС(ставка ; значение1; значение2; …). Для уточнения процентной ставки использовать метод подбора параметра.

ЗАДАНИЕ №3

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

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

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

кпер — общее число периодов платежей по аннуитету. Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48

плт — выплата, производимая в каждый период и не меняющаяся за все время выплаты ренты. Обычно выплаты включают основные платежи и платежи по процентам, но не включают других сборов или налогов. Например, ежемесячная выплата по четырехгодичному займу в 10 000 руб. под 12 процентов годовых составит 263,33 руб. В качестве значения аргумента выплата нужно ввести в формулу число -263,33.

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

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

Пример

Результат получается отрицательный, поскольку он представляет деньги, которые необходимо выплатить, исходящий денежный поток. Если бы за аннуитет требовалось заплатить 60 000, эта инвестиция была бы не выгодной, так как приведенная стоимость (59 777,15) аннуитета меньше данной суммы.

· Примечание. Чтобы получить месячную процентную ставку, разделите годовую ставку на 12. Чтобы узнать количество выплат, умножьте количество лет кредита на 12.

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

В функциях, связанных с аннуитетами, выплачиваемые денежные средства, такие как депозит на сбережения, представляются отрицательным числом; полученные денежные средства, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000 — для вкладчика и аргументом 1000 — для банка.

Рассмотрим следующую задачу. Допустим, что у вас просят в долг 10000 рублей и обещают возвращать по 2000 рублей в течение 6 лет. Будет ли выгодна эта сделка при годовой ставке 7%?

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

=ПЗ(В4; В2; -В3)

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

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вас просят дать в долг Р руб. и обещают возвращать по А руб. ежегодно в течении Н лет. При какой процентной ставке эта сделка имеет смысл.

Для решения задачи использовать функцию

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

ЗАДАНИЕ № 4

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

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

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

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

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

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

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

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

Пример

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

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

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

период — задает период, значение должно быть в интервале от 1 до «кпер».

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

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

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

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

Заметки

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

Пример

Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 1000000 рублей на срок 5 лет при годовой ставке 2%.

еxcel таблица формула

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

=ППЛАТ(процент; срок; – размер ссуды)

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

=D6*$B$1

Основная плата $B$3-B7

Остаток долга в ячейке D7 вычисляется по формуле

=D6-C7

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

Отметим, что основную плату и плату по процентам можно было непосредственно найти с помощью фукций ОСНПЛАТ и ПЛПРОУ соответственно.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вычислить ежегодные основные платежи, плату по процентам, общую годовую выплату и остаток долга на примере ссуды Р руб. под годовую ставку i% на срок Н лет.

Использовать функции

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

либо

ППЛАТ(ставка; срок; -ссуда), ПЛПРОЦ(ставка; период; срок; – ссуда), ОСНПЛАТ(ставка; период; срок; -ссуда).

Остаток долга = долг – ОСНПЛАТ

ЗАДАНИЕ № 5

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

Возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

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

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

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

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

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

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

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

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вы берете в долг Р руб. под годовую ставку i% и собираетесь выплачивать по А руб. в год. Сколько лет займут эти выплаты? Найти 2 способами

1 – й способ – использовать функции

ПС(ставка ; кпер; плт; бс; тип) либо ПЗ(ставка; срок; – ежегод.вклад)

2 – й способ – использовать функцию КПЕР(ставка ; -ежег.вклад; ссуда)

ЗАДАНИЕ №6

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

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

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

кпер — это общее число периодов платежей.

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

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

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

Пример

Примечание. Годовая процентная ставка делится на 12, т. к. начисление сложных процентов производится ежемесячно.

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

С помощью формулы

=БЗ(6%/12; 12; -100; -1000; 1)

получаем ответ 2 301,4 руб.

ИНДИВИДУАЛЬНОЕ ЗАДАНИЕ. Вы собираетесь вкладывать по А у. е. в течении Н лет при годовой ставке I%. Сколько денег будет на счете через n лет?

Использовать функцию

БС(ставка ; кпер; плт; пс; тип)) либо БЗ(ставка; срок; – выплата)

ЗАДАНИЕ №7

Составить отчетную ведомость реализации товаров N магазинами с месяца А по месяц В. Найти место магазина по суммарной выручке (функция РАНГ()), среднюю выручку магазина в месяц (СРЗНАЧ(массив выручки по месяцам)), процент прибыли магазина в общей выручке(суммарную выручку магазина/суммарную выручку всех магазинов). Построить 2 диаграммы (1 – процент прибыли к общей выручке, 2 – объемы реализации продукции).

Стоимость товара для каждого магазина разная.

Объемы выручки для первого магазина берутся с первой цифры, для второго магазина – со второй цифры (первая цифра перешла в конец списка), для третьего магазина – с третьей цифры ( первая и вторая цифры – в конце списка) и т.д.