Уокенбах Формулы в Excel

Мы можем классифицироватьрассматриваемые задачи по уровню сложности. В простых задачах мыимеем дело только с двумя изтрех переменных (текущее значение, выплата и будущее значение). В сложных задачах мы имеем дело со всеми тремя параметрами. Хотя мы классифицируем их какпростые и сложные задачи, формулы Excel требуют введения значений для всех трех переменных. Таким образом, вместо "отсутствующего" элемента в формуле вводится нуль.

Простые задачи накопления

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

ПРИМЕР 1

Скольконакопитсяна счету в $1 000 по истечении трех лет при 7% годовых?

На рис. 11.1 показана эта задача на рабочем листе.

Задача

Какая сумма нзксгится на ычъдь $1000 спустя три года при годовой ставке 7%?

Функция

БС(ставка.кпер ппт пс -и~)

Рис. 11.1. Вычислениебудущего значения

Используемая функция: БС (ставка; кпер; плт;пс; тип) Эта формула возвращает $1 225,04:

=БС(7%;3;0;-1000;0)

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

Обратите внимание, что эта задача рассматривается с точки зрения вкладчика. Таким образом, начальный вклад (пс) имеет отрицательное значение. Регулярные выплаты не производятся, так что аргумент плт равен 0. Без выплат тип аргумента несущественен.

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

ПРИМЕР2

Если вклад $1 000 увеличился до $2 000 за 8 лет, то какой была средняягодовая ставка?

Используемая функция: СТАВКА (кпер; плт; пс; бс; тип; предположение) Эта формула возвращает 9,050773%:

=СТАВКА(8;0;-1000;2 000;0)

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

ПРИМЕР3

У меня на депозитном счету $100 000, вложенные под 14% годовых. Скольковремени потребуетсядля того, чтобыя сталмиллионером ?

Используемая функция: КПЕР (ставка; плт; пс; бс; тип) Эта формула возвращает 17,573:

=КПЕР(14%;0;-100000;1000000;0)

Этот пример рассмотрен с точки зрения вкладчика. Таким образом, аргумент пс отрицателен и аргумент (поступление $1 миллиона) бс положителен. Поскольку ставка представляется в годовых процентах, то эффективный срок тоже выражается в годах.

ПРИМЕР4

У меня на депозитномсчету $10 573,45,положенныепод 1% ежемесячно.Счет открыт 12 месяцев назад. Каков начальный вклад?

Используемая функция: ПС(ставка; кпер; плт; бс; тип) Эта формула возвращает -$9 383,40:

=ПС(1%;12;0;10573,45;0)

Без регулярных выплат аргумент плт равен 0 и тип аргумента несущественен. Так как $10 573,45 уже есть на счету, то аргумент бс получает знак "плюс", а подсчитанное начальное значение отрицательное.

ПРИМЕР5

Если я вношу $300 ежемесячно (начиная с сегодня) на счет, положенный под 1% ежемесячно, сколькоу меня будет на счету через 2 года ?

Используемая функция: БС (ставка; кпер; плт; пс; тип) =БС(1%;24;-300;0;1)

Это формула возвращает $8 172,96:

В этом примере срок указывается в годах, но процент и выплаты ежемесячные. Это требует предварительного преобразования единиц времени. Наиболее эффективный способ — преобразовать годы в месяцы. Другой вариант— преобразовать процентную ставку в ежегодную эффективную ставку, а потом преобразовать $300 в эквивалентную сумму за год. Это должно привести к тому же результату, хотя расчеты в данном случае более громоздкие.

Обратите внимание, что начало выплат с "сегодня" характеризует авансовые платежи. Следовательно, тип аргумента равен 1. Никакой начальный взнос не указан, поэтому аргумент пс равен 0.

Во всех предыдущих примерах вопросы можно перефразировать таким образом, что отрицательное значение станет положительным, а положительное — отрицательным. Так, пример 1 может быть перефразирован следующим образом.

ПРИМЕР6

Если я занимаю $1 000 на три годапод 7% годовых, то сколько всего я должен выплатить?

Используемая функция: БС (ставка; кпер; плт; пс; тип) Это формула возвращает -$1 225,04:

=БС(7%;3;0;1000;0)

Здесь вопрос ставится с точки зрения заемщика, поэтому формула изменена так, что начальный вклад (аргумент пс) положительный. Регулярные выплаты не производятся, так что аргумент плт равен 0. Без выплат тип аргумента несущественен.

Примеры 2-5 можно также перефразировать, например, вкладчик становится заемщиком, а заемщик становится вкладчиком.

ПРИМЕР7

Если с $1 000 вклад увеличился до $3 000 по истечениивосьми лет, то какой была средняя годовая ставка ?

Используемая функция: СТАВКА (кпер; плт; пс; бс; тип; предположение) Это формула возвращает 14,720269%:

=СТАВКА(8;0;-1000;3 000;0)

Этот пример рассмотрен с точки зрения вкладчика. Таким образом, аргумент пс отрицателен, а аргумент бс положителен. Из-за того, что срок выражался в годах, то возвращаемое значение — эффективная годовая ставка. Без регулярных выплат аргумент плт равен 0, а тип аргумента несущественен.

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

Следующие действия демонстрируют метод проверки результата 14,720269%, полученного в последнем примере.

1.Вычислите, сколько с суммы $1 000 накапливается за 8 лет при подсчитанной ставке. Формула возвратит $3 000:

=БС(14/72 02б9%;8;0;-1000;0)

2.Вычислите начальное значение для вклада $3 000, открытого с указанной ставкой восемь лет назад. Следующая формула возвращает - 1 000: =ПС(14,720269%;8;0;3000;0)

3.Вычислите, сколько времени потребуется, чтобы вклад с $1 000 увеличился до $3 000 при неизменной ставке. Следующая-формула возвращает восемь:

=КПЕР(14,7202 69%;0;-1000;3000;0)

4.Вычислите будущую стоимость, используя следующую формулу, которая возвращает 14,720269%:

=(3000/--1000)л (1/8)-1

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

Сложные задачи накопления

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

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

•Задачи, которые имеют ненулевые значения всех трех параметров (начальное значе-

ние, платеж и будущее значение) и требуют выполнения функций СТАВКА или КПЕР.

ПРИМЕР8

С начальным балансом $5 500 и ежемесячнойвыплатой $500 (в конце каждого месяца), сколько я могу накопить за срок более трех лет при 0,75% в месяц?

На рис 11.2 показан этот пример, открытый на рабочем листе.

Examples 08 11 (Complex Accureulai

2 Задача

з ;

|При начальном балансе $5 500 и ежемесячных выплатах $500 (в конце месяца) сколько

4накопится на счете через три года при месячнойставке 0,75%?

5\

6Функция

8*]БС(ставка,кпер,плт,пс.тип)

9;

Рис. 11.2. Вычисление будущего значения

Используемая функция: БС (ставка; кпер; плт; пс; тип) Эта формула возвращает $27 773,91:

=БС(/ 75%;36;-500;-5500;0)

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

ПРИМЕР9

Мой остаток на счету пять лет назад составлял$25 000. Я добавлял $4 500 в конце каэюдого года. Сейчас баланс равен $70 000. Какой быламоя среднегодоваяставка?

Используемая функция: СТАВКА (кпер; плт;пс;бс; тип; предположение) Это формула возвращает 10,9382%:

=СТАВКА(5;-4500;-2 5000;70000;0;0)

СТАВКА — это особенно мощная функция, так как решение поставленной задачи получается только в результате итерации. Только иногда приходится добавлять предполагаемую ставку в качестве шестого аргумента. Если егоопустить, то Excel по умолчанию предложит 0.

ПРИМЕР 10

У меня на счету задолженность банку $12 000 и я вношу $1 000 в конце каждого месяца. Сколько времени потребуется на то, чтобы я стал миллионером, если я выплачиваю пеню 0,6% за месяц?

Используемая функция: КПЕР (ставка; плт; пс; бс; тип) Следующая формула возвращает 337,78 месяцев:

=КПЕР(0,6%;-1000;12 000;1000000;0)

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

Если задолженность на текущем счету рассматривается как заем, будущее значение должно быть положительным. В таком случае требуются два независимых вычисления. Сначала надо вычислить время до получения нулевого баланса, а потом необходимо рассчитать время для получения $1 миллиона на счету.

Используя курс 0,8% для кредита и 0,6% для вклада. Формула возвращает 337,96 месяцев: =КПЕР(0/8%;-1000;12 000;0;0)+КПЕР(0,6%;-1000;0;1000000;0)

ПРИМЕР 11

Я вношу $1 000 в месяц (в конце периода) и собираюсь это делать на протяжении последующих десяти лет. Если предстоит накопить $1 000 000, сколько я должен сейчас при месячной ставке 0,7%?

Используемая функция: ПС(ставка;кпер; плт;бс; тип) Эта формула возвращает -$351 972,24:

=ПС(0,7%;120;-1000;1000000;0)

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

Проработав первые 11 примеров, можно получить общий алгоритм вычислений:

1.Определите требуемую функцию.

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

3.Убедитесь, что единицы измерения времени в функциях СТАВКА, КПЕР и ПЛТ одни и те же (или преобразуйте значения так, чтобы сделать их равными).

4.Вставьте аргументы в правильном порядке (предпочтительно с помощью ссылок на ячейки).

5.Проанализируйте полученный результат.

6.Определите, какая функция или вычисления требуются для проведения перекрестной проверки.

7.Убедитесь в отсутствии ошибок.

Простые задачи дисконтирования

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

вается простымдисконтированием, а если все три, то — сложным дисконтированием.

ПРИМЕР 12

Какой начальныйвклад предполагаетполучение $25 000 после пяти лет при ставке 6,5% годовых ?

На рис. 11.3 показан этот пример, открытый на рабочем листе.

4 Сколько необходимо внести сейчас, чтобы получить на счете через пять лет $25 000 при годовой ста

6Функция

7•

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

9В текущей задаче периодических выплат нет, поэтому ПЛТ равно 0,а тип-- О

10"

"и"

Рис. 11.3. Вычисление начальногозначения

Используемая функция: ПС(ставка; кпер; плт;бс; тип) Эта формула возвращает —$18 247,02:

=ПС(6,5%;5;0;25000;0)

Обратите внимание на логику знаков. Если вы собираетесь получить на вкладе деньги, то БС — положительный аргумент. Чтобы получить в будущем положительное значение нам надо вначале положить в банк определенную сумму, т.е. использовать в формуле отрицательный аргумент. Без выплат тип аргумента несущественен.

Точность вычисления гарантируется перекрестной проверкой ответа другой функцией. В этом случае можно проверить, накопится ли $25 000 после пяти лет на вкладе $18 247,02 при ставке 6,5%, или нет. Следующая формула перекрестной проверкидействительно возвращает $25 000:

=БС(6,5%;5;0;-18247,02;0)

ПРИМЕР 13

Ежегоднаяарендная плата за недвижимость на следующие25 лет составляет $25 000. Если считать аренду займом с 8% годовых, то сколько надо заплатить сейчас, чтобы рассчитаться через 25лет?

Используемая функция: ПС (ставка; кпер; плт; бс; тип) Следующая формула возвращает -$266 869,40:

=ПС(8%;25;25000;0;0)

Этот результат можно проверить, используя функцию СТАВКА. Она возвращает значение 8,00%:

=СТАВКА(2 5;25000;-2 66869,40;0;0)

Как правило, выплаты за аренду недвижимого имущества производятся авансом. В таком случае пример 13 необходимо изменить, установив параметр тип равным 1.

ПРИМЕР 14

Предположите, что выплата в примере 13 суммы $25 000 проводитсявечно. При ставке 8%, сколько необходимозаплатить сначала?

Это пример задачи дисконтирования, которую Excel не может решить, используя стандартные функции. Проблема состоит в том, что нельзя использовать термин "вечность" в качестве аргумента кпер. Решение состоит в том, чтобы использовать очень долговременный период, например 1 000 лет. Получаемый результат наиболее точен для большинства задач.

Используемая функция: ПС (ставка; кпер; плт; бс; тип) Следующая формула возвращает -$312 500,00:

=ПС(8%;1000;25000;0;0)

Другой вариант — использовать формулу для вычисления текущего значения: ПС = ПЛТ/СТАВКА

В нашем примере, следующая формула возвращает $312 500,00: =25000/0,08

Обратите внимание, что знак разный, так как в последней формуле знаки вообще не учитывались.

Если сумма выплачена авансом, то расчеты адаптируются в результате присвоения 1 аргументу тип. Следующая формула возвращает -$337 500,00:

=ПС(8%;1000;25000;0;1)

Формула проверки полученного результата также изменяется:

ПС = ПЛТ*(1+СТАВКА)/СТАВКА

В нашем примере следующая формула возвращает $337 500,00: =25000*(1+0/ 08)/0,08

ПРИМЕР 15

Имущество с текущей стоимостью$2 000 000 продается на правах аренды с номинальной арендной платой сроком на пять лет. Покупатель оплатил $1 750 000. Не принимая во внимание рост стоимости, какой былаучетная ставка?

Номинальная арендная плата — плата, которая взимается с клиента в учет покупки жилья.

Используемая функция: СТАВКА (кпер; плт; пс; бс; тип; предположение) Эта формула возвращает 2,706609%:

=СТАВКА(5;0;-1750000;2000000;0)

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

Для проверки ответа используйте следующую формулу (которая возвращает $2 000 000,03): =БС(2,706609%;5;-1750000;0)

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

ПРИМЕР 16

Недвижимость, проданная на правах аренды, оценена в $230 000. Аренда длится четыре года и выплачивалась каждый месяц наперед в размере $6 000. Если предположить, что доход должен равняться 0,75%, то какая прибыль была бы получена от аренды в результате транзакции? Прибыль от аренды — это значение расчетной арендной платы минус выплаченная сумма.

Используемая функция: ПЛТ ( с т а в к а ; к п е р ; п с ; б с ; тип) Следующая формула возвращает $5 680,95:

=ПЛТ(0,75%;48;-230000;0;1)

Добавив выплаченную наперед арендную плату ($6 000) получим значение $11 680,95.

Сложные задачи дисконтирования

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

ПРИМЕР 17

Если использовать учетную ставку 0,75% в месяц, сколько необходимо выплатить вначале за имущество, которое по оценке будет стоить $5 000 000, при ежемесячной выплате $25 000 в течение пяти лет ?

Используемая функция: ПС ( с т а в к а ; к п е р ; п л т ; б с ; тип) Следующая формула возвращает -$4 406 865,34:

=ПС(0,75%;60;25000;5000000;1)

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

Можно проверить это вычисление с помощью функции СТАВКА. Следующая формула возвращает 0,75%:

=СТАВКА(60;25000;-4406865,34;5000000;1)

ПРИМЕР 18

Я оплатил $1 200 000 за имущество с помощью расчетной арендной платы $12 000 в месяц. Если я продам на протяжении пяти лет за $1 500 000, какую суммарную прибыль я получу?

Требуемая функция: СТАВКА ( к п е р ; п л т ; п с ; б с ; тип; п р о г н о з ) Следующая формула возвращает 1,29136%:

=СТАВКА(60;12000;-1200000;1500000;1)

Этот результат можно проверить с помощью функции ПС. Следующая формула возвращает -$1 200 000,00:

=ПС(1,2913 6%;60;12 000;1500000;1)

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

Обратите внимание, ежемесячная арендная плата не преобразуется в ежегодную арендную плату. Это происходит, потому что сумма аренды $12000 в месяц отличается от суммы $144 000 в год. Чтобы получить эквивалентную ежегодную сумму, надо знать ставку, являющуюся частью данных, которую мы пытаемся вычислить.

ПРИМЕР 19

Имуществоприобретеноза $1 600 000. Расчетная арендная плата составляла$10 000 и выплачиваласьавансом каждый месяц. Если я хочу получить доход равный 1% в месяц, какой должнабыть стоимость имущества через5 лет, еслия планирую его продать ?

Используемая функция: БС (ставка; кпер; плт; пс; тип) Эта формула возвращает $2 081 851,05:

=БС(1%;60;10000;-1600000;1)

Этот результат можно проверить, используя следующую формулу (которая возвращает - $1600 000):

=ПС(1%;60;10000;2081851,05;!)

Задачи амортизации

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

ПРИМЕР 20

Какие выплаты необходимо сделать по займу $200 000, взятому на 10 лет под 0,5% месячных?

Этот пример показан на рис. 11.4.

20 25 (Лпнмt.zalton) xt*

_4JОпределите выплаты по займу $200 000 сроком 10 лет и месячной ставкой 0,5%

Ё

7| Функция

8|

9'ПЛТ(ставка;кпер.пс.6с.тип)

т

Рис. 11.4. Вычислениевыплаты займа

Используемая функция: ПЛТ (ставка; кпер; пс; бс; тип)

Следующая формула возвращает $2 220,41: =ПЛТ(0,5%;120/2 00000;0;0)

Можно проверить результат с помощью функции ПС для начальной суммы займа. Следующая формула возвращает $200 000:

=ПС(0,5%;12 0;-222 0,41;0;0)

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

ПРИМЕР 21

Используемая функция: ПС ( с т а в к а ; к п е р ; п л т ; б с ; тип) Эта формула возвращает $366 433,74:

=ПС(0,45%;2 40; - 2 5 00;0;0)

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

Можно проверить ответ, рассчитав ставку для займа $366 433,74, предоставляемой на 240 месяцев. Следующая формула возвращает 0,45%:

=СТАВКА(240;-2 5 00;3 6643 3,74;0;0 )

ПРИМЕР 22

В настоящий момент я имею задолженность $150 000 по займу, и выплачиваю $1 900 в месяц. Текущая процентная ставка равна 0,45% в месяц. Сколько времени потребуется для выплаты всего долга?

Используемая функция: КПЕР ( с т а в к а ; п л т ; п с ; б с ; тип) Следующая формула возвращает 97,76:

=КПЕР(0,45%;-1900;150000;0;0)

Из-за того, что выплаты производятся ежемесячно и ставка определяется в расчете на один месяц, формула возвращает период амортизации в месяцах. Этот ответ, хотя математически и правилен, имеет редкое практичное применение. Выплаты действительно производятся точно раз в месяц. Это подразумевает, что заем будет выплачен к 98 месяцу. Фактически, перед вами стоит выбор: произвести дополнительную оплату в конце 97 месяца, или сделать уменьшенную оплату на 98 месяц. Каждый вариант подсчитан с помощью функцииБС.

Чтобы вычислить дополнительную оплату в конце 97 месяца, подсчитайте сумму с помощью следующей формулы (которая возвращает -$1 429,85):

=БС(0,45%;97; - 1900;150000;0)

Таким образом, заключительная оплата после 97 месяца равна -$3 329,85 (то есть, нормальный платеж -$1 900 плюс -$1 429,85).

Чтобы вычислить уменьшенную оплату в 98 месяце, используйте эту же формулу (которая возвращает $463,72):

=БС(0,45%;98;- 1900;150000;0)

Таким образом, конечная оплата через 98 месяцев равна -$1 436,28 (то есть, нормальный платеж -$1,900 плюс $463,72).