Как использовать таблицу данных с одним входом

 

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

Пример 6‑3

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

Для расчета используется функции ППЛАТ [48]  из категории функции “ Финасовые”

Синтаксис функции: ППЛАТ (норма, кпер,нз, бс,тип)

 где

норма - годовая процентная ставка (норма дисконтирования0

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

нз – начальная величина займа (или вклада)

бс, тип - не обязательные параметры

 

РЕШЕНИЕ

На листе Excel постройте таблицу, подобную показанной на рисунке.

 ячейках А2:В4 разместите условия задачи

В ячейке В7 разместите формулу: «=ППЛАТ($B$4/12;$B$3*12;$B$2)»

 

Рис. 6‑8 Фрагмент таблицы Excel для расчета платежей по займу

 

 

Обратите внимание на аргументы функции

$B$4/12 – величина месячной процентной ставки (норма)

$B$3*12 – количество периодов выплат для погашения ссуды (кпер)

$B$2   - величина займа (нз)

 

Выделите диапазон ячеек, содержащий исходные значения процентных ставок и формулу для расчета – А7:В17

Выполните команду ДАННЫЕ­ ÞТАБЛИЦА ПОДСТАНОВОК… На экране появится диалоговое окно «Таблица подстановок»

 

Рис. 6‑9. Диалоговое окно «Таблица подстановок»

 

Открывшееся диалоговое окно используется для задания рабочей ячейки на которую ссылается формула расчета. В нашем примере, это ячейка В4, которую и необходимо указать в поле «Подставлять значения по строкам в:» диалогового окна в абсолютных координатах (абсолютная ссылка).

Если исходные данные расположены в строке, то ссылку на рабочую ячейку необходимо ввести в поле «Подставлять значения по столбцам в»

При нажатии на кнопку «ОК» Excel заполнит столбец, как показано на рисунке.

Если в таблицу необходимо включить большее количество формул, использующих исходные значения (в нашем примере «Процентные ставки»), то дополнительные формулы вставляются справа от существующей в той же строке. Затем необходимо вновь выделить всю таблицу, включая полученные ранее значения, и заполнить диалоговое окно команды ДАННЫЕ ÞТАБЛИЦА ПОДСТАНОВОК.

 

 

Приложение 2. Порядковые номера дней в не високосном году

 

День	Месяц
	Январь	Февраль	Март	Апрель	Май	Июнь	Июль	Август	Сентябрь	Октябрь	Ноябрь	Декабрь
1	1	32	60	91	121	152	182	213	244	274	305	335
2	2	33	61	92	122	153	183	214	245	275	306	336
3	3	34	62	93	123	154	184	215	246	276	307	337
4	4	35	63	94	124	155	185	216	247	277	308	338
5	5	36	64	95	125	156	186	217	248	278	309	339
6	6	37	65	96	126	157	187	217	249	279	310	340
7	7	38	66	97	127	158	188	219	250	280	311	341
8	8	39	67	98	128	159	189	220	251	281	312	342
9	9	40	68	99	129	160	190	221	252	282	313	343
10	10	41	69	100	130	161	191	222	253	283	314	344
11	11	42	70	101	131	162	192	223	254	284	315	345
12	12	43	71	102	132	163	193	224	255	285	316	346
13	13	44	72	103	133	164	194	213	256	286	317	347
14	14	45	73	104	134	165	195	226	257	287	318	348
15	15	46	74	105	135	166	196	227	258	288	319	349
16	16	47	75	106	136	167	197	228	259	289	320	350
17	17	48	76	107	137	168	198	229	260	290	321	351
18	18	49	77	108	138	169	199	230	261	291	322	352
19	19	50	78	109	139	170	200	231	262	292	323	353
20	20	51	79	110	140	171	201	232	263	293	324	354
21	21	52	80	111	141	172	202	233	264	294	325	355
22	22	53	81	112	142	173	203	234	265	295	326	356
23	23	54	82	113	143	174	204	235	266	296	327	357
24	24	55	83	114	144	175	205	236	267	297	328	358
25	25	56	84	115	145	176	206	237	268	298	329	359
26	26	57	85	116	146	177	207	238	269	299	330	360
27	27	58	86	117	147	178	208	239	270	300	331	361
28	28	59	87	118	148	179	209	240	271	301	332	362
29	29		88	119	149	180	210	241	272	302	333	363
30	30		89	120	150	181	211	242	273	303	334	364
31	31		90		151		212	243		304		365

 

 

Приложение 3. Множители наращения по сложным процентам

Число периодов	Ставка процентов за период
	5,00%	10,00%	15,00%	20,00%	25,00%	30,00%	40,00%
1	1,05	1,1	1,15	1,2	1,25	1,3	1,4
2	1,1025	1,21	1,3225	1,44	1,5625	1,69	1,96
3	1,157625	1,331	1,520875	1,728	1,953125	2,197	2,744
4	1,215506	1,4641	1,749006	2,0736	2,441406	2,8561	3,8416
5	1,276282	1,61051	2,011357	2,48832	3,051758	3,71293	5,37824
6	1,340096	1,771561	2,313061	2,985984	3,814697	4,826809	7,529536
7	1,4071	1,948717	2,66002	3,583181	4,768372	6,274852	10,54135
8	1,477455	2,143589	3,059023	4,299817	5,960464	8,157307	14,75789
9	1,551328	2,357948	3,517876	5,15978	7,450581	10,6045	20,66105
10	1,628895	2,593742	4,045558	6,191736	9,313226	13,78585	28,92547
11	1,710339	2,853117	4,652391	7,430084	11,64153	17,9216	40,49565
12	1,795856	3,138428	5,35025	8,9161	14,55192	23,29809	56,69391
13	1,885649	3,452271	6,152788	10,69932	18,18989	30,28751	79,37148
14	1,979932	3,797498	7,075706	12,83918	22,73737	39,37376	111,1201
15	2,078928	4,177248	8,137062	15,40702	28,42171	51,18589	155,5681
16	2,182875	4,594973	9,357621	18,48843	35,52714	66,54166	217,7953
17	2,292018	5,05447	10,76126	22,18611	44,40892	86,50416	304,9135
18	2,406619	5,559917	12,37545	26,62333	55,51115	112,4554	426,8789
19	2,52695	6,115909	14,23177	31,948	69,38894	146,192	597,6304
20	2,653298	6,7275	16,36654	38,3376	86,73617	190,0496	836,6826

 

[1] Данное пособие, безусловно, не может полностью охватить предмета «Финансовая математика» и задумано как настольная книга пользователя ПК, помогающая ему, во-первых, самостоятельно изучить основные формулы процентных расчетов и, во-вторых, научиться работать с ними в электронных таблицах.

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

 

[2] В дореволюционной литературе очень часто можно встретить слово «Интерес», которое предприниматели использовали для характеристики выгодности какой – либо сделки.

[3] В литературе достаточно часто этот показатель называется коэффициентом или множителем наращения, показывающим во сколько раз наращенная сумма больше первоначальной суммы долга и. по существу может приниматься как базисный темп роста.

 

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

 

[5] Проценты, начисленные по ставке процентов, называются антисипативным и, а по учетной ставке – декурсивными.

 

[6] Напомним, что «процентные деньги» – это разность между первоначальной денежной суммой (PV) и наращенной денежной сумой (FV) – I = FV – PV.   

[7] Этот способ обычно используется в Германии, Дании, Швеции

[8] Этот способ имеет распространение во Франции, Бельгии, Испании, Швейцарии

[9] Этот способ применяется в Португалии, Англии, США.

 

[10] Как и в случае простых процентов, множитель наращения показывает будущую стоимость 1 денежной единицы, вложенной на n периодов. Для обозначения этого финансового коэффициента часто используется стандартная аббревиатура FVIF (от англ. Future Value Interest Factor – процентный множитель будущей стоимости). Будущая стоимость определяется умножением размера первоначально инвестированной суммы на этот коэффициент: FV = PV * FVIF ( n, r )

r

[11] Напомним, что величина k_н = (1 + r )* n – множитель наращения, экономический смысл которого состоит в том, что он показывает, чему будет равна одна денежная единица (один рубль, один доллар и т.п.) через n периодов при заданной процентной ставке i.

[12] В литературе, посвященной финансовому анализу, такой расчет часто называют приведением стоимостного показателя к заданному моменту времени, а величину PV называют приведенной (современной или текущей) величиной FV

[13] Вексель – обязательство вернуть указанную в векселе сумму (номинал векселя, обозначим его S), в указанный срок. Если держатель векселя (его собственник в данный момент) желает обменять вексель на деньги, он обращается в банк с предложением учесть имеющийся у него вексель, т.е. купить его за сумму Р, меньшую, чем номинал S. Такая сделка называется дисконтированием, а сумма скидки с номинала – дисконтом

[14] Дисконтирование может быть также связано и с проведением кредитной опе­рации при начислении процентов в начале интервала начис­ления и заемщик получает сумму PV за вычетом процентных денег D из наращиваемой суммы кредита FV, подлежащей к возврату

[15] Дисконтирование по простой учетной ставке чаще всего производится по французской практике начисления процентов, т.е. когда временная база принимается за 360 дней, а число дней в периоде берется точным.

 

[16] Обратите внимание, что величины n и d могут оказаться такими, что nd > 1 и, соответственно, величина PV будет меньше нуля. Это, конечно же, невозможно: никто не согласится отдать вексель, да еще уплатить за это сумму равную FV * (n * d -1). Поэтому дисконтирование применяют так, чтобы было 1 > n*d > 0.

[17] Если финансовая функция вызывается для продолжения ввода другой функции (вложенная функция) пр

[18] Возможен также вариант непосредственного ввода формулы, содержащей имена и параметры встроенных финансовых функций.

Формула должна начинаться со знака «=». Далее следует имя функции. В круглых скобках указываются ее аргументы, в последовательности соответствующей синтаксису функции. В качестве разделителя аргументов обычно используется точка с запятой. Так например, формула определения будущей стоимости инвестиции при непосредственном ее вводе в ячейку таблицы может иметь вид: БС(ставка;кпер;Плт;ПС;тип) = БС(5%;5;;-10000)

 

[19] Диалоговое окно «Диспетчер функций» может быть также вызвано командой ВСТАВКА­­ ðФУНКЦИЯ…

[20] В некоторых случаях при неполной инсталляции MS Office в этом списке могут содержаться только основные команды. Для того, чтобы в дальнейшем выводился полный список имен функций, выполните команду СЕРВИС ðНАДСТРОЙКИ … и в открывшемся окне установите «флажок» «Пакет Анализа»

[21] В младших версиях MS Excel это диалоговое окно может содержать кнопку «Далее», при щелчке на которой вызывается диалоговое окно самой функции.

[22] Microsoft Excel хранит даты как целые числа и может выполнять над ними вычисления. По умолчанию 1 января 1900 года имеет порядковый номер – 1, и, соответственно, 1 сентября 2006г будет иметь порядковый номер – 38961, так как интервал в днях между этими датами равен 38961,

[23] Использование функции «Дата» как вложенной функции будет рассмотрена более подробно в разделе «ДЕНЕЖНЫЕ ПОТОКИ»

[24] В младших версиях MS Excel диалоговое окно функции вызывается двойным щелчком на ячейке, содержащей редактируемую формулу.

[25] Напомним, что в германской практике расчета продолжительность года условно принимается за 360 дней, а целого месяца – за 30 дней

[26] Напомним, что в соответствии с французской системой расчета, продолжительность года условно принимается за 360 дней, а продолжительность ссуды рассчитывается точно по календарю.

 

[27] Напомним, что при выпуске ценных бумаг, заключении финансовых контрактов, займов в долговом соглашении обычно указывается годовая номинальная процентная ставка и период начисления (год, полугодие, квартал и т.д.)

Эффективная процентная ставка – это годовая сложных процентов, обеспечивающая тот же доход, что и m - разовое начисление процентов по ставке r / m.

[28] Заметим, что платежи могут быть неодинаковы не только по знаку и величине самого платежа, но и по времени их поступления.

[29] Заметим, что рассмотренное в предыдущей главе наращение и дисконтирование вложенной суммы может также рассматриваться как денежный поток с однократным поступлением денег и единичным периодом накопления.

[30] Это частный случай потока платежей, все члены которого - положительные величины. Примерами аннуитета могут быть регулярные взносы в пенсионный или другие фонды, выплаты процентов по ценным бумагам, например, по акциям и т.д.

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

[32] Это важнейшая характеристика финансового анализа, т.к. является основой для измерения эффективности различных финансово-кредитных операций, сравнения условий контрактов и т.п. Данная характеристика показывает, какую сумму следовало бы иметь первоначально, чтобы, разбив ее на равные взносы, на которые начислялись бы установленные проценты в течение всего срока, можно было бы получить необходимую наращенную сумму.

 

[33] Легко видеть, что выражения в квадратных скобках в (4-3) представляет собой множитель, равный современной стоимости аннуитета в 1 денежную единицу. Разделив современную стоимость PV денежного потока любого вида на этот множитель, можно получить величину периодического платежа CF эквивалентного ему аннуитета. Эта математическая зависимость часто используется в финансовом анализе для приведения потоков с неравномерными поступлениями к виду обыкновенного аннуитета

 

[34] Напомним, что по условиям задачи платежи и начисления процентов производятся в конце года. Таким образом, наращение первой вложенной суммы будет проходить в течение 5-1 =4 лет; наращение второй вложенной суммы – 5-2= 3 года и т.д

[35] Напомним, одновременное нажатие клавиш Ctrl+ Shift+ Enter позволяет ввести формулу как формулу для массива данных.

[36] Обратите внимание, что формула заключена в фигурные скобки, что характеризует ее как формулу массива.

[37] Аргумент Нз функции эквивалентен, аргументу Пс, использовавшемуся в других финансовых функциях.

[38] Часть этих функций была нами рассмотрена в главе «ИЗМЕНЕНИЕ СТОИМОСТИ ВЛОЖЕНИЙ ЗА СЧЕТ ПРИСОЕДИНЕНИЯ ПРОЦЕНТОВ»

[39] В младших версиях Excel эта функция обозначена как ППЛАТ()

[40] Необходимо отметить, что показатель NPV отражает прогнозную оценку изменения экономического потенциала фирмы в случае принятия данного проекта.

 

[41] По сути дела этот показатель определяет срок, в течение которого инвестиции будут "заморожены", так как реальный доход от инвестиционного проекта начнет поступать только по истечении периода окупаемости

[42] Формула реализует условие: если величина накопленного дисконтированного дохода больше нуля, то вычисляется период окупаемости как разность величины накопленного дисконтированного дохода в этот период и разности суммы накопленного дисконтированного дохода в предыдущий период и дисконтированного денежного потока, отнесенной к величине дисконтированного денежного потока.

[43] В том случае, если платежи имеют регулярный характер и даты их поступления не определены, для вычисления величины IRR может быть использована функция ВНДОХ() (либо функция ВСД в версии Excel 2002,2003, XP

[44]В литературе эта величина иногда носит название – средневзвешенная доходность

[45] В том случае, если платежи имеют регулярный характер и даты их поступления не определены, для вычисления величины IRR может быть использована функция ВНДОХ() (либо функция ВСД в версии Excel 2002,2003, XP

[46] В ряде случаев используется альтернативный стиль ссылок на ячейки рабочего листа, когда столбцы также нумеруются, а номер строки указывается в первую очередь, Перейти к альтернативному стилю адресации ячеек можно выполнив команду СЕРВИС ð ПАРАМЕТРЫ, выбрав в ее диалоговом окне на вкладке «Общие» в группе «Стиль ссылок» позицию переключателя R 1 C1. Тогда первая клетка (ячейка) рабочего листа так и будет именоваться R1C1, от английского Row1Column1 (ряд первый, колонка первая).

 

[47] Ниже будет подробно рассмотрен ситуационный анализ «Что – если…»

[48] Напомним, что, начиная с версии Excel 2000, эта формула обозначена как ПЛТ()