Ексель. Формулы и функции для финансовых расчетов в этой главе
 Скачать 1.72 Mb.
|
|
Глава Формулы и функции для финансовых расчетов В ЭТОЙ ГЛАВЕ... Стоимость денег во времени Кредитный калькулятор Расчет параметров инвестиций Расчет амортизации Финансовое прогнозирование Н е секрет, что чаще всего Excel используется для всевозможных финансовых расчетов. Каждый день люди принимают тысячи решений, основываясь на результатах вычислений в электронных таблицах. Принимаемые решения могут быть как очень простыми (могу ли я купить этот автомобиль, таки достаточно сложными (окупятся ли инвестиции в компанию АВС за 18 месяцев. В настоящей главе описаны основные приемы, применяемые в Excel для финансовых расчетов. Стоимость денег во времени Номинальная стоимость денег не всегда может соответствовать фактической. Ключевое понятие здесь — стоимость денег во времени (СДВ). Вычисления, выполняемые над денежными суммами, могут производиться в прошлом, настоящем или в будущем времени. Выбор типа вычислений основывается на том, что сумма денег увеличивается, поскольку постоянно поступают начисления по процентам. Другими словами, вложив один рубль сегодня, завтра вы получите больше. Например, предположим, что богатенький заокеанский дядюшка решил подарить вам деньги, причем вы должны выбрать один из перечисленных ниже вариантов 2016 Biblia.indb 357 31.01.2017 13:56:44 Часть II. Формулы и функции Получить 800 000 e сегодня Получить 950 000 e через год Получить 1 200 000 e через пять лет Получать 15 000 р. каждый месяц на протяжении пяти лет. Чтобы получить максимальную выгоду, нужно учитывать не только собственно номинальную сумму, но и ту стоимость, которую будут представлять деньги на момент, когда вы их получите на руки. Стоимость денег во времени зависит от ваших возможностей и перспектив. Другими словами, вы можете быть либо кредитором, либо заемщиком. Когда выберете кредит на покупку автомобиля, вы выступаете в роли заемщика, а учреждение, которое дает вам деньги, — в роли кредитора. Когда же вы вкладываете деньги в банк на депозит (сберегательный счет, вы — кредитор, поскольку занимаете свои деньги банку, а банк является вашим заемщиком. Со стоимостью денег во времени связано еще несколько понятий Приведенная (текущая) стоимость Это основная сумма, или капитал, на который начисляются проценты. Например, если на депозит в банке вкладывается 500 000 e, то эта величина представляет собой капитал или приведенную (текущую) стоимость вложенных денег. Если берется ссуда размером 1 500 000 e. на приобретение автомобиля, то данная сумма будет основной или приведенной стоимостью ссуды. Приведенная стоимость может быть как положительной, таки отрицательной Будущая стоимость Представляет собой сумму приведенной (текущей) стоимости и начисленных по ней процентов. Например, если на депозитный счет в банке вкладывается 50 000 e на пять лет под 6% годовых, тов конце срока можно будет получить 66 911,28 e. Последняя сумма будет будущей стоимостью инвестиции. Если же берется ссуда натри года на покупку автомобиля в размере 150 000 e. под 7% годовых, тов конце срока нужно будет выплатить 240 867,22 e. Иными словами, нужно будет вернуть основную сумму плюс проценты. Будущая стоимость, в зависимости от перспективы кредитор или заемщик, может быть положительной или отрицательной Взнос Взносом (платежом) может быть либо капитал, либо капитал и начисленные на него проценты. Если каждый месяц вкладывается по 1000 e на депозитный счет, то 1000 e — это взнос. Если для погашения ссуды ежемесячный взнос составляет 8 250 e, то он состоит из тела кредита (основной суммы) и начисленных процентов Процентная ставка Часть основной суммы (в процентах, начисляемая за определенный период (как правило, за год. Например, деньги могут быть вложены в банк на депозит с процентной ставкой 5,5% годовых. Или процентная ставка по кредиту может составлять 7,75% в год Период Промежуток времени, по истечении которого выплачиваются проценты (например, банк может выплачивать проценты по депозитам ежеквартально, тогда как кредит закупленный автомобиль чаще всего приходится гасить ежемесячно Срок Промежуток времени, на который вкладываются или берутся в кредит деньги. Например, деньги вкладываются в банк на депозит на срок один год или ссуда берется на срок 30 лет 2016 Biblia.indb 358 31.01.2017 13:56:44 Глава 15. Формулы и функции для финансовых расчетов 359 Кредитный калькулятор Теперь рассмотрим, как расчитываются выплаты по кредиту. Следует помнить, что кредит состоит из перечисленных ниже компонентов Тело кредита (собственно размер ссуды, которую вы взяли в банке Процентная ставка Сроки оплаты Размер регулярного платежа Если известны любые три параметра, то можно создать формулу для вычисления четвертого параметра. Все вычисления в этом разделе выполняются для ссуды с фиксированными процентной ставкой и сроком. Функции для расчета выплат по кредиту В настоящем разделе описываются шесть функций ПЛТ, ОСПЛТ, ПРПЛТ, СТАВКА, КПЕР и ПС. В табл. 15.1 описаны аргументы этих функций. Таблица 15.1. Аргументы функций для финансовых расчетов Аргумент Описание Ставка Процентная ставка за один период. Если она выражена в процентах за год, то эту величину нужно разделить на количество периодов (месяцев) Кпер Общее количество выплат (периодов) Период Заданный период, который должен быть меньше или равен значению параметра Кпер Пс Приведенная стоимость, или основная сумма Плт Сумма, которая вносится за каждый период (постоянная величина) Бс Необязательный аргумент, равный будущей стоимости после последней выплаты. Если аргумент опущен, то он полагается равным нулю. (Например, будущая стоимость ссуды после ее погашения, очевидно, равна нулю.) Тип Указывает, когда должны осуществляться платежи. Равен нулю, если платежи выполняются в конце периода, и единице — если вначале. Это необязательный аргумент, который по умолчанию равен нулю Оценка Используется в функции СТАВКА и означает начальную оценку ожидаемого результата. Функция СТАВКА выполняет пошаговое вычисление. Поэтому, если полученный результат не будет сходиться, следует изменить значение этого аргумента Функция ПЛТ Функция ПЛТ позволяет рассчитать размер выплат по кредиту (основная сумма плюс проценты) за один период, полагая постоянными размер выплати процентную ставку. Эта функция имеет такой синтаксис: ПЛТ(Ставка;Кпер;Пс;Бс;Тип) Следующая формула возвращает размер ежемесячных выплат по кредиту, размер которого 50 000 e, а процентная ставка составляет 6% годовых кредит взят на срок четыре года (48 месяцев): =ПЛТ(6%/12;48;-50000) На заметку 2016 Biblia.indb 359 31.01.2017 13:56:44 Часть II. Формулы и функции 360 Вычислив формулу, получим, что размер ежемесячных выплат по ссуде равен. В качестве первого аргумента Ставка указан размер годовой ставки по кредиту, деленный на 12 месяцев (количество месяцев в году. Обратите также внимание на то, что третий аргумент (Пс, приведенная стоимость) — отрицательный. Это указывает на то, что деньги были взяты в долг. Функция ОСПЛТ Функция ОСПЛТ возвращает основную часть платежа по кредиту за определенный период, подразумевая постоянным размер выплати фиксированную процентную ставку. Функция имеет следующий синтаксис: ОСПЛТ(Ставка;Период;Кпер;Пс;Бс;Тип) Следующая формула возвращает основную часть выплаты за первый месяц по ссуде 50 000 e, взятой под 6% годовых ссуда взята на срок четыре года (48 месяцев): =ОСПЛТ(6%/12;1;48;-50000) Вычислив формулу, получим, что основная часть первой выплаты равна 924,25 e, или приблизительно 78,7% от общей суммы выплаты. Если в качестве второго аргумента ввести число 48 (для вычисления основной части последней выплаты, то формула вернет 1168,41 e, те. приблизительно 99,5% от общей суммы выплаты. Чтобы вычислить размер основного кумулятивного платежа, который нужно будет сделать между любыми двумя определенными периодами, можно воспользоваться функцией ОБЩДОХОД. Функция содержит два дополнительных аргумента нач_период и кон_ период. В версиях, предшествовавших Excel 2007, функция ОБЩДОХОД становилась доступной только после установки надстройки Пакет анализа. Функция ПРПЛТ Функция ПРПЛТ вычисляет ту часть общей суммы выплат по кредиту, которая идет на погашение процентов, полагая постоянными размер выплати процентную ставку. Функция имеет следующий синтаксис: ПРПЛТ(Ставка;Период;Кпер;Пс;Бс;Тип) Формула, приведенная ниже, вычисляет размер первой выплаты по процентам для ссуды 50 000 e, взятой под 6% годовых ссуда взята на 4 года (48 месяцев): =ПРПЛТ(6%/12;1;48;-50000) После вычисления формулы получим, что сумма выплат по процентам за первый месяц составляет 250,00 e, но размер выплат по процентам за последний месяц будет всего 5,84 Чтобы вычислить размер кумулятивного платежа по процентам, который нужно будет сделать между любыми двумя определенными периодами, следует воспользоваться функцией ОБЩПЛАТ. Функция содержит два дополнительных аргумента нач_период и кон_ период. В версиях, предшествовавших Excel 2007, функция ОБЩПЛАТ становилась доступной только после установки надстройки Пакет анализа. На заметку На заметку 2016 Biblia.indb 360 31.01.2017 13:56:44 Глава 15. Формулы и функции для финансовых расчетов 361 Функция СТАВКА Функция СТАВКА позволяет вычислить размер процентной ставки по кредиту за один период на основании указанного общего количества периодов выплат, размера регулярного платежа и размера ссуды. Функция СТАВКА имеет следующий синтаксис: СТАВКА(Кпер;Плт;Пс;Бс;Тип;Оценка) Следующая формула вычисляет годовую процентную ставку для ссуды размером, взятой на 48 месяцев размер регулярного платежа составляет 1174,25 e: =СТАВКА(48;1174,25;-50000)*12 Формула вернет 6,00%. Заметьте, что результат вычисления функции умножается на 12, поскольку функция возвращает процентную ставку за один период (месяц. Поэтому, чтобы получить годовую процентную ставку, нужно умножить полученное значение на количество периодов выплат в году (в нашем примерена Функция КПЕР Функция КПЕР возвращает общее количество периодов выплат по ссуде поза- данным размеру ссуды, процентной ставке и размеру регулярного платежа. Функция имеет следующий синтаксис: КПЕР(Ставка;Плт;Пс;Бс;Тип) Формула, приведенная ниже, вычисляет количество выплат по ссуде размером и размеру ежемесячной выплаты, равному 1174,25 e; ссуда взята под 6% годовых: =КПЕР(6%/12;1174,25;-50000) С небольшой погрешностью (меньше 0,0001) функция вернет число 48. Это значит, что всего нужно будет сделать 48 (за 48 месяцев) выплат по кредиту. Полученный результат оказался неточным из-за того, что размер выплат указан с точностью до одной копейки, те. округлен. Функция ПС Функция ПС вычисляет приведенную стоимость ссуды (те. первоначальный размер кредита) по заданным процентной ставке, количеству периодов и размеру регулярного платежа. Функция имеет такой синтаксис: ПС(Ставка;Кпер;Плт;Бс;Тип) Следующая формула возвращает размер первоначальной ссуды, взятой на 48 месяцев под 6% годовых размер регулярного платежа равен 1174,25 e в месяц: =ПС(6%/12;48;-1174,25) Формула вернет 49 999,94 e. Поскольку размер регулярного платежа указан с точностью до одной копейки, результат имеет погрешность в 6 копеек 2016 Biblia.indb 361 31.01.2017 13:56:44 Часть II. Формулы и функции 362 Пример кредитного калькулятора На рис. 15.1 показан рабочий лист, используя который, можно вычислить размер регулярного платежа по кредиту. В ячейке В находится сумма кредита, а в ячейке В — годовая процентная ставка. В ячейке В содержится длительность одного периода, выраженная в месяцах. Например, если в ячейке В находится число 1, значит, выплаты производятся ежемесячно. Общее количество периодов хранится в ячейке В. В примере, показанном на рис. 15.1, вычисляется размер ежемесячной выплаты по кредиту размером 100 000 e, взятому под 6,25% годовых на 36 месяцев. В ячейке В содержится следующая формула: =ПЛТ(B2*(B3/12);B4;-B1) Рис. 15.1. Вычисление размера регулярного платежа по кредиту с помощью функции ПЛТ Обратите внимание на то, что первым аргументом является выражение, которое вычисляет периодическую процентную ставку на основе значений годовой процентной ставки и периодичности выплат. Поэтому, если выплаты производятся ежеквартально на протяжении трех лет, периодичность выплат будет равна 3, а количество периодов — 12. Для того чтобы вычислить периодическую процентную ставку, нужно умножить годовую ставку на Рабочую книгу с примерами для данного раздела можно найти в файле loan payment. xlsx на сайте книги (www.wiley.com/go/excel2016bible). Для русскоязычного издания книги файл называется В ячейках В и В вычисляются размеры платежей, погашающих тело кредита и проценты за период, указанный в ячейке В. Не забывайте, что значение в ячейке В должно быть меньше или равно значению ячейки В. Иными словами, заданный период не должен превышать общее количество периодов. Формула в ячейке В представлена ниже она позволяет вычислить размер тела кредита для заданного в ячейке В периода: =ОСПЛТ(B2*(B3/12);B9;B4;-B1) В Сети Excel 2016 Biblia.indb 362 31.01.2017 13:56:45 Глава 15. Формулы и функции для финансовых расчетов 363 Приведенная ниже формула (ячейка В) вычисляет размер выплат по процентам для заданного в ячейке В периода: =ПРПЛТ(B2*(B3/12);B9;B4;-B1) Обратите внимание на то, что сумма ячеек В и В всегда равна размеру регулярного платежа по кредиту, который вычисляется в ячейке В. Тем не менее соотношение между платежами по кредиту и выплатам по процентам зависит от текущего периода. (Доля платежей по кредиту с каждой последующей выплатой увеличивается, а выплат по процентам — уменьшается) На рис. 15.2 графически показаны размеры выплачиваемых сумм для всего срока погашения кредита. Рис. 15.2. Соотношение между платежами по кредиту и выплатам по процентам в зависимости от периода выплат Расчеты по кредитной карточке Предположим, у вас есть кредитная карточка с определенной начальной суммой ивы хотите узнать, за сколько месяцев вы сможете погасить этот кредит при условии внесения минимально возможного ежемесячного платежа. На рис. 15.3 показан рабочий лист, на котором выполняются вычисления такого типа. В диапазоне В1:В5 записаны исходные данные по кредитной карточке. В нашем примере начальная сумма на кредитной карточке составляет 10 000 e, годовая процентная ставка равна 21,25%. Здесь принято, что минимальная сумма платежа составляет 2% от суммы, хранимой на кредитной карте, те. в данном случае минимальный платеж будет составлять 200 e в месяц. В ячейку В вы можете ввести любую другую сумму, но она должна быть достаточной для погашения всей суммы кредита. Например, вы можете вносить ежемесячно по 500 e, чтобы погасить кредит быстрее. С другой стороны, очевидно, что 100 e в месяц будет недостаточно, поэтому формула возвратит ошибку. Рабочую книгу с примерами для данного раздела можно найти в файле credit card payments.xlsx на сайте книги (www.wiley.com/go/excel2016bible). Для русскоязычного издания книги файл называется Pic15_03.xlsx. В Сети Excel 2016 Biblia.indb 363 31.01.2017 13:56:45 Часть II. Формулы и функции 364 Рис. 15.3. На этом рабочем листе подсчитывается количество платежей для погашения баланса по кредитной карте при условии внесения минимального ежемесячного платежа В диапазоне В7:В9 выполняются вычисления. Приведенная ниже формула, записанная в ячейке В, вычисляет, за сколько месяцев вы сможете погасить баланс по кредитной карте: =КПЕР(В2/12;В5;-В1;0) Здесь предполагается, что в конце вычисленного периода баланс по карте должен быть погашен, поэтому последний аргумент в функции КПЕР равен нулю. Простая формула в ячейке В вычисляет общую сумму, которую вы должны будете заплатить за все время с учетом начисленных процентов: =В7*В5 Формула в ячейке В вычисляет сумму, начисленную по процентам: =В8–В1 В этом примере наглядно показано, что кредит в 10 000 e выбудете гасить 123,4 месяца (те. более 10 лет) при условии внесения ежемесячного минимального платежа в 200 e. За это же время на взятые в кредит по карте 10 000 e вам будет начислено 8932,93 e процентов. Конечно, эти вычисления предполагают, что за все время пользования карточкой вы не будете снимать с нее никаких дополнительных средств. Из этого примера вы уже должны были понять, почему банки так упорно рассылают кредитные карты по обычной почте буквально направо и налево, а также всячески поощряют вас пользоваться такими картами. На рис. 15.4 показаны дополнительные вычисления, связанные с нашим примером с кредитной карточкой. Например, если вы захотите погасить баланс по карточке за 12 месяцев, то каждый месяц вы должны будете вносить по 932,34 e. При этом общая сумма выплат составит 11 188,06 e, а начисления по процентам — 1188,06 e. В ячейке В записана такая формула: =ПЛТ($B$2/12;A13;-$B$1) Создание графика погашения ссуды График погашения ссуды представляет собой таблицу, содержащую различную информацию по каждому периоду выплат. На рис. 15.5 показан рабочий лист, который содержит формулы, используемые для создания графика погашения ссуды 2016 Biblia.indb 364 31.01.2017 13:56:45 Глава 15. Формулы и функции для финансовых расчетов 365 Рис. 15.4. В столбце B приведены размеры платежей, которые нужно сделать, чтобы погасить баланс по кредитной карте за указанный период Рис. 15.5. График погашения ссуды 2016 Biblia.indb 365 31.01.2017 13:56:45 Часть II. Формулы и функции 366 Рабочую книгу с примерами для данного раздела можно найти в файле loan amortiza- tion schedule.xlsx на сайте книги (www.wiley.com/go/excel2016bible). Для русскоязычного издания книги файл называется Данные по взятой в банке ссуде (кредиту) введены в диапазон B1:B4. Они используются в формулах, содержащихся в ячейках, начиная с девятой строки. В табл. 15.2 представлены формулы из строки 9 графика погашения ссуды. Формулы были скопированы вовсе строки графика до 488 строки. Поэтому с помощью этого рабочего листа можно рассчитать график погашения ссуды продолжительностью до 480 периодов (40 лет). Формулы в тех строках, которые не относятся к периодам выплат, будут возвращать ошибку. Поэтому для сокрытия данных в этих строках используются средства условного формати- рования. Подробнее об условном форматировании можно узнать в главе 21, Визуализация данных средствами условного форматирования”. Таблица 15.2. Формулы для создания графика погашения ссуды Ячейка Формула Описание A9 =A8+1 Возвращает номер платежа B9 =ПЛТ($C$2*($C$3/12);$C$4;-$C$1) Вычисляет размер периодического платежа C9 =C8+B9 Возвращает общую (кумулятивную) сумму выплат D9 =ПРПЛТ($C$2*($C$3/12);A9; Вычисляет процентную часть выплаты E9 =E8+D9 Вычисляет кумулятивную сумму выплат по процентам F9 =ОСПЛТ($C$2*($C$3/12);A9; Вычисляет размер тела кредита за одну выплату G9 =G8+F9 Вычисляет кумулятивную сумму тела кредита H9 =H8-F9 Вычисляет остаток суммы кредита в конце каждого периода Анализ параметров ссуды с помощью таблиц данных Таблицы данных являются, по-видимому, одним из самых мощных средств Excel. Следует принять во внимание, что таблицы данных — это не совсем тоже, что таблица (созданная с помощью команды ВставкаðТаблицыðТаблица). Таблица данных — это удобное средство для подведения итогов вычислений, зависящих от одной или двух изменяющихся ячеек. В этом примере, изменяя значения одного или двух параметров ссуды, можно проследить их влияние на размер платежа. В следующих разделах описано, как создавать таблицы данных с одним или двумя входами. В предыдущих русскоязычных версиях Excel таблицы данных назывались таблицами подстановки. — Примеч. ред. В Сети На заметку Дополнительная информация На заметку 2016 Biblia.indb 366 31.01.2017 13:56:45 Глава 15. Формулы и функции для финансовых расчетов 367 Таблицы данных более подробно описаны в главе 35, Анализ данных с помощью сценариев что если””. Рабочую книгу с примерами использования таблиц данных с одними двумя входами можно найти в файле loan data tables.xlsx на веб-сайте книги (www.wiley.com/go/ excel2016bible). Для русскоязычного издания книги файл называется Создание таблицы данных с одним входом Таблица данных с одним входом позволяет выполнить произвольное количество расчетов, изменяя значения водной ячейке. На рис. 15.6 показана таблица (диапазон В, в которой вычисляются три параметра ссуды (размер периодического платежа, общая сумма выплат по кредиту и общая сумма выплат по процентам) при различных значениях процентной ставки, которая изменяется от 7,00 до 8,50% (всего семь значений. В данном примере значения подставляются в ячейку В2. Рис. 15.6. Пример таблицы данных с одним входом для вычисления параметров ссуды для различных значений процентной ставки Чтобы создать таблицу данных с одним входом, выполните следующие действия. Введите формулы для вычисления параметров ссуды, которые будут использоваться в таблице данных. В этом примере формулы находятся в диапазоне В6:В8. 2. Введите значения изменяемой ячейки в соответствующие ячейки. Здесь изменяемым значением является процентная ставка, значения которой находятся в ячейках СВ столбце, который находится слева от изменяемых значений, введите ссылки на ячейки с формулами. В данном примере ссылки следует ввести в диапазон В11:В13. Например, в ячейке В содержится формула В. Выделите диапазон, который содержит все данные, созданные в предыдущих пунктах. В данном примере это диапазон В10:I13.) Дополнительная информация В Сети Excel 2016 Biblia.indb 367 31.01.2017 13:56:46 Часть II. Формулы и функции. Выберите команду ДанныеðПрогнозðАнализ что если”ðТаблица данных. На экране появится диалоговое окно, показанное на рис. Рис. 15.7. Диалоговое окно Таблица данных. В поле Подставлять значения по столбцам в укажите ссылку на изменяемую ячейку В данном случае изменяемое значение хранится в ячейке В. Второе поле оставьте пустым. Оно используется для создания таблиц данных с двумя входами. Щелкните на кнопке ОК. Excel создаст формулу массива, в которой используется функция ТАБЛИЦА с одним аргументом. Пожеланию отформатируйте полученную таблицу. Например, можете добавить фон для заголовков таблицы. Обратите внимание на то, что формула массива была введена не вовсе ячейки выделенного диапазона. Первые строка и столбец диапазона, выделенного в п. 4, остались без изменений. При создании таблицы данных в левом столбце (в п. 3 в нем мы указали ссылки на ячейки с формулами) содержатся результаты вычислений для заданного в ячейке B2 значения изменяемого параметра. В нашем примере эти значения повторяются в столбце D. Поэтому, чтобы избежать недоразумений, лучше всего скрыть значения в левом столбце. Для этого можно, например, сделать одинаковыми цвет шрифта и цвет фона для левого столбца. Создание таблицы данных с двумя входами Таблица данных с двумя входами содержит результаты вычислений по одной формуле при изменении двух входных параметров. На рис. 15.8 показана таблица данных с двумя входами (диапазон B10:I16), которая используется для вычисления размера платежа для семи значений процентной ставки и шести значений суммы кредита. Чтобы создать таблицу данных с двумя входами, выполните такие действия. Создайте формулу, которая будет вычислять данные, используемые в таблице подстановки. В этом примере такая формула находится в ячейке В. Формулы в ячейках В и Вне используются. Введите в строку значения первого изменяемого параметра. В данном примере первым параметром является значение процентной ставки. Подставляемые значения хранятся в диапазоне C10:I10. 3. Введите в столбец значения второго изменяемого параметра. В этом примере размер ссуды выступает в качестве второго параметра, значения которого содержатся в диапазоне В11:В16. 4. Создайте ссылку на ячейку, содержащую формулу для вычисления значений в таблице. В нашем примере в ячейке В содержится ссылка =В6. Совет Excel 2016 Biblia.indb 368 31.01.2017 13:56:46 Глава 15. Формулы и функции для финансовых расчетов 369 Рис. 15.8. Применение таблицы данных с двумя входами для вычисления размера платежа при разных параметрах ссуды. Выделите диапазон, содержащий все ячейки, в которые были введены данные на предыдущих шагах, в данном случае — диапазон В. Выберите команду ДанныеðПрогнозðАнализ “что–если”ðТаблица данных. Excel отобразит одноименное диалоговое окно, показанное на рис. 15.7. 7. В поле Подставлять значения по столбцам в укажите ссылку на ячейку, которая содержит текущее значение первого параметра В данном примере следует ввести ссылку на ячейку В. В поле Подставлять значения по строкам в введите ссылку на ячейку с текущим значением второго параметра таблицы подстановки Вашем примере создайте ссылку на ячейку В. Щелкните на кнопке ОК. Excel вставит формулу массива с функцией ТАБЛИЦА с двумя аргументами. После создания таблицы данных можно изменить формулу, по которой выполняются расчеты. Для этого следует изменить ссылку в верхней левой ячейке таблицы подстановки. Например, если в этом примере заменить существующую ссылку в ячейке В ссылкой В, тов таблице будет рассчитываться общая сумма выплат по процентам, а если в ячейку В ввести ссылку В, тов таблице будет рассчитываться общая сумма выплат с учетом процентов. Если таблица данных будет иметь очень большой размер, то скорость вычислений может существенно снизиться. Поэтому в Excel предусмотрен специальный режим вычислений для подобных случаев. Выберите команду ФормулыðВычислениеðПараметры вычисленийðАвтоматически, кроме таблиц данных. После выбора этой команды пересчет таблиц данных будет выполняться не в автоматическом режиме, а после нажатия клавиши Совет 2016 Biblia.indb 369 31.01.2017 13:56:46 Часть II. Формулы и функции 370 Расчет параметров погашения ссуды нерегулярными платежами До сих пор во всех примерах из этой главы рассчитывались параметры погашения ссуды (кредита) регулярными платежами. Нов некоторых случаях ссуда погашается нерегулярно. Например, вы заняли денег друзьям без официального соглашения о том, как будут производиться выплаты. И если при этом вас будет интересовать, какую сумму вы получите в качестве процентов, тов таком случае нужно выполнить вычисления на основании дат реальных платежей. На рис. 15.9 представлен рабочий лист, с помощью которого можно отслеживать состояние таких займов. Значение годовой процентной ставки ссуды вводится в ячейку В, которой присвоено имя ПС. Размер и дата первоначальной ссуды находятся в ячейках В и С соответственно. Формулы, расположенные в й строке и ниже, позволяют отследить состояние ссуды, а также производят дополнительные расчеты. Рис. 15.9. Рабочий лист, с помощью которого можно отследить параметры погашения ссуды нерегулярными платежами В столбце В содержатся размеры платежей, сделанных вдень, указанный в столбце С. Заметьте, что выплаты осуществлялись нерегулярно. Также следует отметить, что в двух случаях (строки 11 и 24) размер выплаты отрица- Excel 2016 Biblia.indb 370 31.01.2017 13:56:46 Глава 15. Формулы и функции для финансовых расчетов 371 тельный. Это значит, что производились дополнительные займы, которые добавлялись к остатку ссуды. Формулы в ячейках D и Е вычисляют сумму выплат по процентами телу кредита. Значения в столбцах F и G позволяют отследить общую сумму основных выплати сумму выплат по процентам. Формулы в столбце H вычисляют остаток по ссуде после каждой выплаты. В табл. 15.3 приведены формулы, которые содержатся в строке 6, а также их краткое описание. Обратите внимание на то, что в каждой формуле используется функция ЕСЛИ, предназначенная для определения отсутствия даты платежа в столбце C. Если дата отсутствует, формула возвращает пустую строку ив ячейке, таким образом, данных не будет. Таблица 15.3. Формулы для вычисления параметров погашения ссуды нерегулярными платежами Ячейка Формула Описание D6 =ЕСЛИ(C6<>""; ПС "По этой формуле вычисляется размер выплаты по процентам на основании даты платежа E6 =ЕСЛИ(C6<>""; B6-D6; "Чтобы получить размер основной выплаты, из суммы платежа вычитается сумма выплат по процентам F6 =ЕСЛИ(C6<>""; F5+B6; "Эта формула прибавляет сумму данного платежа к общей сумме выплат G6 =ЕСЛИ(C6<>""; G5+D6; "Эта формула прибавляет сумму выплат по процентам к общей сумме H6 =ЕСЛИ(C6<>""; H5-E6; "Формула вычисляет остаток в конце каждого периода Рабочую книгу с примерами использования таблиц данных с примером вычисления параметров погашения ссуды нерегулярными платежами можно найти в файле irregular payments.xlsx на веб-сайте книги (www.wiley.com/go/excel2016bible). Для русскоязычного издания книги файл называется Расчет параметров инвестиций При расчете параметров инвестиций основной интерес представляет вычисление прибыли от инвестиций с фиксированной процентной ставкой, таких как банковские сберегательные счета, депозиты или аннуитеты. Такого рода вычисления можно проводить для инвестиций, состоящих из одного или более вкладов. Рабочую книгу со всеми расчетами, приведенными в этом разделе, можно найти в файле investment calculations.xlsx на веб-сайте книги (www.wiley.com/go/excel- 2016bible). Для русскоязычного издания книги файл называется Будущая стоимость одного вклада Большинство инвестиций представляет собой одноразовый вклад на определенный срок, проценты по которому выплачиваются по окончании этого срока. В настоящем разделе описаны способы вычисления простых и сложных процентов. В Сети В Сети Excel 2016 Biblia.indb 371 31.01.2017 13:56:46 Часть II. Формулы и функции 372 Расчет выплат по простому проценту В случае простого процента сумма выплаченных процентов не прибавляется к основной сумме вклада. Чтобы вычислить доход на основании простого процента, используется приведенная ниже формула: Доход = Основная_Сумма * Процентная_Ставка * Срок Например, предположим, что в банк на депозит вложено 10 000 e на один год, при условии, что простая годовая процентная ставка составляет 5%. Через год банк вернет сумму вклада, а также выплатит проценты в размере 500 e. Итого сумма выплаты составит 10 500 e. В этом случае, чтобы вычислить полученные проценты, следует умножить основную сумму (10 000 e) на процентную ставку (0,05) и на срок 1 год. Если срок инвестиции меньше одного года, то простая процентная ставка будет соответствующим образом преобразована. Например, если в предыдущем примере срок вклада будет шесть месяцев, то по процентам банк выплатит только 250 e, поскольку в этом случае годовая процентная ставка умножается на На рис. 15.10 показан рабочий лист, который содержит формулы, выполняющие расчет выплат по простому проценту. Формула в ячейке Вей присвоено имя Доход) вычисляет размер выплат по процентам в конце срока: =Основная_Сумма*Процентная_Ставка*Срок По сути, она перемножает значения в ячейках B3 ( Основная_Сумма), B4 (Процентная Ставка) и B5 (Срок). Рис. 15.10. Расчет выплат по простому проценту Формула в ячейке В суммирует размер выплат по процентами размер ин- вестиции. Расчет выплат по сложному проценту В большинстве случаев выплаты по процентам вычисляются на основе одного из типов сложного процента. Под сложным процентом понимается многократное начисление процентов, которые добавляются к общей сумме вклада, причем в следующий раз проценты начисляются как по основной сумме вклада, таки по начисленным ранее процентам 2016 Biblia.indb 372 31.01.2017 13:56:46 Глава 15. Формулы и функции для финансовых расчетов 373 Например, предположим, что сумма депозита в банке составляет 10 000 e. Годовая процентная ставка составляет 5%, проценты начисляются каждый месяц. После первого месяца проценты начисляются по основной сумме вклада, а затем полученная сумма выплат по процентам добавляется к основной. В следующем месяце проценты будут начисляться уже по большей сумме (основная плюс выплаты по процентам за первый месяц. Полученная сумма снова добавляется к текущей сумме вклада. Чтобы вычислить объем инвестиции в конце срока, можно создать последовательность формул (рис. Рис. 15.11. Чтобы выполнить расчет выплат по сложному проценту, можно создать последовательность формул В столбце В содержатся формулы, вычисляющие выплаты по процентам для каждого месяца. Например, в ячейке В содержится следующая формула: =C9*($B$5*(1/12)) или =C9*(Процентная_ставка*(1/12)) Формулы в столбце С суммируют начисления по процентам за текущий месяц с суммой инвестиции за предыдущий месяц. Например, в ячейке С содержится такая формула: =C9+B10 В конце срока (12 месяцев) сумма инвестиции составляет 10 511,62 e. Таким образом, ежемесячное начисление сложного процента принесло (по сравнению с простым процентом) дополнительную прибыль в размере 11,62 e. Excel 2016 Biblia.indb 373 31.01.2017 13:56:46 Часть II. Формулы и функции 374 Вместо того чтобы создавать последовательность формул, можно воспользоваться функцией БС. На рис. 15.12 показан рабочий лист, который содержит формулы для расчета выплат по сложному проценту. В ячейке В хранится число выплат за один год. Если выплаты производятся ежемесячно, тов ячейке В должно находиться число 12. Если выплаты будут производиться ежеквартально, то число выплат равно 4. Если же выплаты производятся ежедневно, то количество выплат будет равно 365. Срок инвестиции, указанный в виде количества лет, содержится в ячейке В7. Рис. 15.12. Расчет выплат по сложному проценту с помощью одной формулы В ячейке В содержится приведенная ниже формула, которая вычисляет процентную ставку за один период. Полученное значение процентной ставки используется для вычисления суммы выплаты за каждый период: =B5*(1/B6) или =Процентная_ставка*(1/Выплат_в_году) В формуле, которая хранится в ячейке В, для расчета размера инвестиции в конце срока вклада используется функция БС: =БС(B9;B6*B7;;-B4) или =БС(Проценты_за_период;Выплат_в_году*Срок;;-Основная_сумма) Первым аргументом функции БС является значение процентной ставки за один период, которое вычисляется в ячейке В. Второй аргумент представляет собой общее число периодов. Третий аргумент пропущена четвертый аргумент это размер инвестиции, который записан как отрицательное число. Общая сумма выплат по процентам вычисляется с помощью простой формулы, записанной в ячейке В 2016 Biblia.indb 374 31.01.2017 13:56:46 Глава 15. Формулы и функции для финансовых расчетов 375 или =Сумма_выплат-Основная_сумма Формула в ячейке В позволяет вычислить годовую доходность (сумму начислений за год, выраженную в процентах относительно объема инвестиции): =(B11/B4)/B7 или =(Проценты/Основная_сумма)/Срок Например, пусть депозит в банке равен 50 000 e, процентная ставка составляет годовых, начисляемых ежеквартально. В этом случае количество периодов выплат за год равно 4 (ячейка В. Срок вклада составляет три года, поэтому в ячейку В вводится число 3. Формула в ячейке Ввернет значение 59 340,67 Предположим, что другой банк предлагает депозиты на тех же условиях, но начисления по процентам происходят ежедневно. На рис. 15.13 показаны параметры депозита размером 50 000 e с ежедневными выплатами. Сравните полученные значения со значениями на рис. 15.12. Как видно, разница очень мала (общая сумма выплат по процентам равна 9340,67 и 9412,78 e соответственно. Затри года по второму вкладу будет выплачено на 72,11 e больше, чем по первому. Если же сравнивать годовую доходность, то при ежеквартальных начислениях она будет составлять 6,23%, а при ежедневных — Рис. 15.13. Расчет выплат по сложному проценту с ежедневным начислением процентов Расчет выплат по сложному проценту с непрерывным начислением процентов Термин непрерывное начисление процентов означает, что проценты начисляются и добавляются к сумме вклада постоянно. Иными словами, число периодов выплат по инвестиции за год — бесконечно. Следующая формула позволя- Excel 2016 Biblia.indb 375 31.01.2017 13:56:47 Часть II. Формулы и функции 376 ет определить будущую стоимость инвестиции объемом 50 000 e, вложенную под 5,75% годовых, которые начисляются непрерывно на протяжении трех лет: =50000*EXP(0,0575*3) Эта формула вернет значение 59 413,59 e, что на 0,08% больше по сравнению с аналогичной инвестицией, нос ежедневными выплатами (рис. Рис. 15.14. Расчет выплат по сложному проценту с непрерывным начислением процентов Сложный процент можно вычислить без использования функции БС. Общая формула выглядит следующим образом: сумма * (1 + ставка) ^ кпер Здесь сумма — основная сумма инвестиции ставка — процентная ставка за один период кпер — общее количество периодов выплат. Например, пусть инвестиция размером 50 000 o вкладывается на пять лет под 5% годовых, которые начисляются ежемесячно. В этом случае будущую стоимость инвестиции можно вычислить по формуле =50000*(1+5%/12)^(12*5) Правило числа Что делать, если нужно быстро принять решение о том, вкладывать деньги на определенных условиях или нет, а компьютера поблизости нет Простое правило числа 72” позволяет определить количество лет, по истечении которых стоимость инвестиции, вложенной под определенные проценты с ежегодными начислениями, удвоится. Просто разделите число 72 назначение процентной ставки. Например, пусть 100 000 o вкладываются под 4% годовых. Через сколько лет эта сумма возрастет доте. удвоится Разделив 72 на 4, получим, что стоимость данной инвестиции удвоится через 18 лет. А что будет, если процентная ставка будет равняться 5% годовых В последнем случае сумма удвоится чуть больше чем через 14 лет. Теперь рассмотрим точность этого правила. В таблице, приведенной ниже, во втором столбце представлены значения, полученные с помощью правила числа 72”, а в правом столбце — реальные значения. Как видно, для малых значений процентной ставки это простое правило обеспечивает удивительную точность. Тем не менее помните, что, если процентная ставка будет равна 30% или больше, точность будет значительно меньше. На заметку 2016 Biblia.indb 376 31.01.2017 13:56:47 Глава 15. Формулы и функции для финансовых расчетов 377 Процентная ставка Правило числа Фактическое значение 69,66 2% 36,00 35,00 3% 24,00 23,45 4% 18,00 17,67 5% 14,40 14,21 6% 12,00 11,90 7% 10,29 10,24 8% 9,00 9,01 9% 8,00 8,04 10% 7,20 7,27 15% 4,80 4,96 20% 3,60 3,80 25% 2,88 3,11 30% 2,40 Правило числа 72 работает ив обратном направлении. Например, чтобы удвоить стоимость инвестиции за шесть лет, следует разделить 72 на 6. В результате получим, что деньги нужно вкладывать под 12% годовых. Остается пожелать успехов! Будущая стоимость серии вкладов Рассмотрим еще один тип инвестиций, в котором на протяжении всего срока инвестиции следует периодически делать дополнительные вклады. Этот тип инвестиции называется аннуитетом. Некоторые банковские учреждения предлагают такую услугу. Функции, описанные выше, в разделе Кредитный калькулятор, можно применить и для расчета аннуитетов, но для этого нужно изменить перспективу кредитор вместо заемщика. Самый простой пример такого вида инвестиций — это целевые сберегательные вклады, предназначенные для проведения отпуска и предлагаемые целым рядом банковских организаций. Смысл состоит в том, что из каждой выплаты вам банк удерживает определенную фиксированную сумму, которая зачисляется в виде депозита на специальный сберегательный счет. Затем, через заранее оговоренный промежуток времени, например через один год, вы можете снять всю сумму (включая проценты) и использовать ее для проведения отпуска. Предположим, вначале каждого месяца (на протяжении 12 месяцев) насчет вносится 2000 e под 4,25% годовых, начисляемых ежемесячно. Следующая формула позволяет вычислить будущую стоимость депозита: =БС(4,25%/12;12;-2000;;1) Формула вернет значение 24 559,74 e. Это число представляет собой сумму общего объема взносов (24 000 e) и начислений по процентам (559,74 e). Последний аргумент функции БС равен 1. Это значит, что взносы производятся вначале каждого периода. На рис. 15.15 показан рабочий лист, который содержит формулы для расчета параметров аннуитета. В табл. 15.4 представлены формулы этого листа с краткими описаниями 2016 Biblia.indb 377 31.01.2017 13:56:47 Часть II. Формулы и функции 378 Рис. 15.15. Расчет параметров аннуитета Эту рабочую книгу можно найти в файле annuity calculator.xlsx на веб-сайте книги ( www.wiley.com/go/excel2016bible). Для русскоязычного издания книги файл называется Таблица 15.4. Формулы для вычисления параметров аннуитета Ячейка Формула Описание B4 Значение вводится Размер первого взноса (может быть равным нулю) B5 Значение вводится Размер периодического взноса B6 Значение вводится Число взносов за год B7 Значение вводится ИСТИНА, если взносы вносятся вначале периода ЛОЖЬ — в противном случае B10 Значение вводится Срок инвестиции (лет. Значение может быть дробным B13 Значение вводится Значение годовой процентной ставки B16 =B4 Отображает размер первого взноса B17 =B5*B6*B10 Вычисляет общую сумму сделанных взносов B18 =B16+B17 Вычисляет сумму первого и всех сделанных взносов B19 =B13*(1/B6) Вычисляет процентную ставку за период B20 =БС(B19; B6*B10; -B5; -B4; ЕСЛИ(B7;1;0)) Вычисляет будущую стоимость инвестиции B21 =B20-B18 Вычисляет прибыль от инвестиции В Сети Excel 2016 Biblia.indb 378 31.01.2017 13:56:47 Глава 15. Формулы и функции для финансовых расчетов 379 Расчет амортизации Программа Excel содержит пять функций, которые позволяют рассчитать амортизацию активов за время их эксплуатации. Значение амортизации в данный момент времени (уменьшение стоимости в заданный момент времени по сравнению с начальной стоимостью) можно описать с помощью функции, аргументами которой являются начальная стоимость и время эксплуатации. Выбор функции зависит от используемого метода амортизации. В табл. 15.5 кратко описаны функции Excel, позволяющие рассчитать амортизацию, а также их аргументы. Дополнительную информацию можно найти в справочной системе Таблица 15.5. Функции для расчета амортизации Функция Метод амортизации Аргументы* АПЛ Линейный метод. Стоимость актива каждый год уменьшается на одинаковую сумму на протяжении всего срока эксплуатации нач_стоимость; ост_стоимость; время_эксплуатации ФУО Метод фиксированного уменьшения остатка. Значение амортизации за период вычисляется с помощью фиксированной процентной ставки нач_стоимость; ост_стоимость; время_эксплуатации; период [месяцы] ДДОБ Метод двойного уменьшения остатка. Амортизация вычисляется с помощью увеличенного коэффициента. Значение амортизации максимально в первый периода затем постепенно уменьшается нач_стоимость; ост_стоимость; время_эксплуатации;период; [коэффициент] АСЧ Метод суммы годовых чисел. Устанавливает большее значение амортизации впервые периоды эксплуатации нач_стоимость; ост_стоимость; время_эксплуатации;период ПУО Метод снижающегося остатка. Вычисляет амортизацию для любого периода (включая частичные периоды) с использованием метода двойного уменьшения остатка или любого другого указанного метода нач_стоимость; ост_стоимость; время_эксплуатации;нач_период; кон_период; коэффициент [без_ переключения] * Аргументы в квадратных скобках задавать необязательно. Для того чтобы лучше понять принцип работы функций для расчета амортизации, рассмотрим их аргументы нач_стоимость — начальная стоимость активов ост_стоимость — стоимость активов в конце срока эксплуатации время_эксплуатации — количество периодов, на протяжении которых эксплуатируются активы период — период, по истечении которого производится расчет амортизации месяцы — количество месяцев в первом году если аргумент опущен, он полагается равным 12. • нач_период — начальный период для вычисления амортизации кон_период — конечный период для вычисления амортизации 2016 Biblia.indb 379 31.01.2017 13:56:47 Часть II. Формулы и функции коэффициент — процентная ставка снижающегося остатка если аргумент опущен, то он полагается равным 2 (те. используется метод двойного уменьшения остатка без_переключения — логическое значение, которое указывает, следует ли использовать линейную амортизацию, если значение амортизации превышает величину, рассчитанную методом снижающегося остатка. На рис. 15.16 приведен пример расчета амортизации с помощью функций АПЛ, ФУО, ДДОБ и ФСЧ. Начальная стоимость актива составляет 100 000 e, срок эксплуатации лет, остаточная стоимость — 10 000 e. В разделе, озаглавленном Значение амортизации, представлены ежегодные объемы амортизации актива, рассчитанные разными методами. Во втором разделе Стоимость актива представлены значения уцененной стоимости актива на протяжении всего срока его эксплуатации. Рис. 15.16. Значения амортизации, полученные с использованием четырех функций Эту рабочую книгу можно найти в файле depreciation calculations.xlsx на веб- сайте книги ( www.wiley.com/go/excel2016bible). Для русскоязычного издания книги файл называется Pic15_16.xlsx. В Сети Excel 2016 Biblia.indb 380 31.01.2017 13:56:47 Глава 15. Формулы и функции для финансовых расчетов 381 На рис. 15.17 представлен график остаточной стоимости активов, вычисленный с использованием различных методов амортизации. Как видно, с использованием функции АПЛ стоимость актива снижается по прямой линии. При использовании других функций стоимость снижается по кривой, поскольку максимальное снижение происходит впервые периоды срока эксплуатации. Рис. 15.17. Расчет остаточной стоимости активов с помощью разных методов амортизации Функция ПУО используется в тех случаях, когда нужно вычислить амортизацию за несколько периодов (например, за второй и третий годы эксплуатации. На рис. 15.18 показан рабочий лист, который позволяет выполнить расчет амортизации с использованием функции ПУО. В ячейке В содержится формула =ПУО(B2;B4;B3;B6;B7;B8;B9) Она рассчитывает амортизацию актива за первые три года эксплуатации начиная с периода 0 и заканчивая периодом Финансовое прогнозирование Под прогнозированием мы подразумеваем предсказание значений, основанное на исторических значениях (те. назначениях, которые отмечались в прошлом. Эти значения могут быть финансовыми (например, объемы продаж или величины дохода) или какими-либо другими данными, связанными со временем (например, количество работников). В Excel 2016 включено пять новых функций рабочего листа для прогнозирования. Как видите может вставлять эти функции автоматически и может даже строить диаграмму. На заметку 2016 Biblia.indb 381 31.01.2017 13:56:47 Часть II. Формулы и функции 382 Рис. 15.18. Расчет амортизации за несколько периодов с помощью функции ПУО Эту рабочую книгу можно найти в файле forecasting example.xlsx на веб-сайте книги. Для русскоязычного издания книги файл называется Чтобы создать прогноз, начните с исторических, связанных со временем данных, например ежемесячных объемов продаж. На рис. 15.19 представлен простой пример. В столбце B содержатся данные ежемесячных объемов продаж с 2012 по 2015 год включительно. Кроме того, я построил график, из которого следует циклический характер изменения объемов продаж, причем продажи снижаются в летние месяцы. Цель заключается в том, чтобы предсказать ежемесячные объемы продаж на последующие два года. Рис. 15.19. Ежемесячные объемы продаж за четыре года В Сети Excel 2016 Biblia.indb 382 31.01.2017 13:56:47 Глава 15. Формулы и функции для финансовых расчетов 383 Начните с выделения соответствующих данных. Для этого примера сначала выделите диапазон A1:B49. Затем выберите команду ДанныеðПрогнозðЛист прогноза, и Excel отобразит диалоговое окно Создание листа прогноза, показанное на рис. 15.20. (Я щелкнул на кнопке Параметры, чтобы отобразить на экране дополнительные параметры) В этом диалоговом окне показан график, на котором отображаются исторические данные, прогнозируемые данные, а также доверительные интервалы для этого прогноза. Доверительный интервал (на графике он показан более тонкими линиями) определяет “плюс/минус” значения для этого прогноза и указывает степень доверия к этому прогнозу. Повышая значение доверительного интервала, вы расширяете диапазон предсказания. Обратите внимание график, представленный в этом диалоговом окне, изменяется при изменении вами параметров. Рис. 15.20. Диалоговое окно Создание листа прогноза Щелкните на кнопке Создать, и Excel вставит новый рабочий лист, который содержит таблицу и диаграмму. На рис. 15.21 представлена часть этой таблицы. В ней отображаются прогнозируемые значения, а также нижний и верхний дове- Excel 2016 Biblia.indb 383 31.01.2017 13:56:47 Часть II. Формулы и функции 384 рительные интервалы. Эти значения генерируются с помощью функций ПРЕДСКАЗ. ETS и ПРЕДСКАЗ.ETS.ДОВИНТЕРВАЛ. Это достаточно сложные функции (именно поэтому берет на себя выполнение всей соответствующей работы). Рис. 15.21. На рабочем листе прогноза содержатся таблица и график 2016 Biblia.indb 384 31.01.2017 13:56:48 |