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

 















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


Задание № 1

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


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

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

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

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

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

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


Пример

ABДанныеОписание8%Годовая процентная ставка10Количество месяцев платежей10000Сумма кредитаФормулаОписание (результат)=ПЛТ(A2/12; A3; A4)Месячная сумма платежа по указанному кредиту (-1 037,03)=ПЛТ(A2/12; A3; A4; 0; 1)Месячная сумма платежа по указанному кредиту, исключая платежи, производимые в начале периода (-1 030,16)

Рассмотрим пример расчета 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

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

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

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


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

ВариантnPiA1927000051027200500612382206007114103007008225935080091767210700612782509001132893105001231910320600132710113604001423115150200510126160500612137180200719148190100121615923010092916102403001027171126090052818122705006181962807007102072903008192153303009122210300700822237180200719248190100121625923010092926102403001027

ЗАДАНИЕ №2

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


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

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

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

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

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


Пример 1

ABДанныеОписание10%Годовая ставка дисконтирования-10 000Начальные затраты на инвестиции за один год, считая от текущего момента3 000Доход за первый год4 200Доход за второй год6 800Доход за третий годФормулаОписание (результат)=ЧПС(A2; A3; A4; A5; A6)Чистая приведенная стоимость инвестиции (1 188,44)

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

Пример 2


ABДанныеОписание8%Годовая ставка дисконтирования. Она может представлять собой темп инфляции или процентную ставку по конкурирующим инвестициям.-40 000Начальные затраты на инвестиции8 000Доход за первый год9 200Доход за второй год10 000Доход за третий год12 000Доход за четвертый год14 500Доход за пятый годФормулаОписание (результат)=ЧПС(A2; A4:A8)+A3Чистая приведенная стоимость этой инвестиции (1 922,06)=ЧПС(A2; A4:A8; -9000)+A3Чистая приведенная стоимость этой инвестиции с потерей 9000 на шестом году (-3 749,47)

В этом примере начальные затраты в 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; ...). Для уточнения процентной ставки использовать метод подбора параметра.


ВариантНРР1Р2Р3Р4Р515370005000700080009000110002420700600060009000700033200005000800080004530700500010000180002000300054450001200090001000018000652190040005000800010600110007532500800090001000030702000843120090001000010000150009332000150001000010000103366001070015000210001141800040006000980055001251630050006000705080040013318500850047006000144195001020050007000300015523800900080006060500040001632430096001000070001742690070005000702080001832700015000800060001932805080001000011050205295009000700080507000230021433800800070001000090302242070060006000900070002353070050001000018000200030002452190040005000800010600110002541950010200500070003000263270001500080006000

ЗАДАНИЕ №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, обозначающее, когда должна производиться выплата.


Пример

ABДанныеОписание500Деньги, уплачиваемые по страховке в конце каждого месяца8%Процентная ставка, которую приносят выплачиваемые деньги20Число лет, по истечении которых деньги будут выплаченыФормулаОписание (результат)=ПС(A3/12; 12*A4; A2; ; 0)Приведенная стоимость аннуитета с указанными выше условиями (-59 777,15).

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

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

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

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

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



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

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

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

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

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

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


ВариантНРА1121706003001028200700310203922080033030410300900340405113505004105067210400320607825030037030893102004002091032044035010101136007041030115160080330201291800903101013719005029030148230050300201592400402801016102600302702017527500055020186285000480101972930004201020113340004303021934200039040221032044035010238230050300202451600803302025527500055020261217060030010

ЗАДАНИЕ № 4

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


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

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

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

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

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

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

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


Пример

ABДанныеОписание10%Годовая процентная ставка1Период, для которого требуется найти проценты3Срок займа (в годах)8000Стоимость займа на текущий моментФормулаОписание (результат)=ПРПЛТ (A2/12; A3*3; A4; A5)Выплаты по процентам за первый месяц на приведенных выше условиях (-22,41)=ПРПЛТ (A2; 3; A4; A5)Выплаты по процентам за последний год на приведенных выше условиях (начисления процентов производятся ежегодно) (-292,45)

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

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

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

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

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

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

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

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

Заметки

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


Пример

ABДанныеОписание (результат)10%Годовая процентная ставка2Срок займа в годах2000Сумма займаФормулаОписание (результат)=ОСПЛТ(A2/12; 1; A3*12; A4)Величина платежа в погашение основной суммы за первый месяц указанного займа (-75,62)Рассмотрим пример вычисления основных платежей, платы по процентам, общей ежегодной платы и остатка долга на примере ссуды 1000000 рублей на срок 5 лет при годовой ставке 2%.

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


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

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

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

=D6*$B$1

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

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

=D6-C7

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

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

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

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

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

либо

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

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


ВариантНРi1121705005210200600639220700741030080085113509009692101001071225020011893103001291032040013101136050014115150600712916070081371808005148190900615923010010161029020091711330300818624040041972605006201127060072193407001022113509009231225020011241032040013255150600726923010010

ЗАДАНИЕ № 5

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


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

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

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

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

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

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

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

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

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

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

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

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



ВариантiРА131704003001024200500310203522060033030463007003404057350800410506821090032060792501003707081031020040010943203003502010536040041030113150500250401241606002605013521070027030146230800355201572809003055016829010038010179330300390201810340400420301931805002804020419060029050215240700325102242005003102023522060033030244320300350202582901003801026318050028040

ЗАДАНИЕ №6

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


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

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

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

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

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

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


Пример

ABДанныеОписание12%Годовая процентная ставка12Количество платежей-1000Объем платежейФормулаОписание (результат)=БС(A2/12; A3; A4)Будущая стоимость вклада на приведенных выше условиях (12 682,50)

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

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

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

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

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

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

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

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


Вариант123456789101112А203019201780164014602430320042305210711010501080Н10111213148910111267I234223456742

Вариант13141516171819202122232425А2010204010801560103410302020205030201460320052107110Н8109118476101491112I2342334452467

ЗАДАНИЕ №7


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

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

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


ВариантАВNСтоимость товаровОбъемы реализации (тыс. шт.)12майсентябрь644,45,46,47,201,20224,25,26,27,36,3813августоктябрь3248,255,279121,120,12514сентябрьянварь441,49,40,4612,15,10,1715декабрьапрель639,38,40,41,49, 3625,27,28,22,23,2916январьиюнь421,38,20,2914,13,10,4117мартсентябрь379,74,7713,14,1618апрельавгуст541,92,43,40,4651,52,93,54,5919майноябрь314,10,1360,61,6920августдекабрь7201,205,305,205,11,14,2270,71,72,73,74,99,8521сентябрьдекабрь4123,120,130,12991,82,89,8522сентябрьдекабрь5420,430,401,400, 30087,89,80,85, 8123декабрьиюль310,18,7730,33,3424мартсентябрь3222,225,22618,19,2025августянварь4302,201,205,20844,705,33,10226февраль август519,14,17,15,1421,39,41,59,22


ФИНАНСОВЫЙ АНАЛИЗ В EXCEL Задание № 1 Описание функции ПЛТ или ППЛАТ (ставка ; кпер; пс; бс; т

Больше работ по теме:

КОНТАКТНЫЙ EMAIL: [email protected]

Скачать реферат © 2018 | Пользовательское соглашение

Скачать      Реферат

ПРОФЕССИОНАЛЬНАЯ ПОМОЩЬ СТУДЕНТАМ