Microsoft Excel - это электронная таблица Microsoft Office, которая используется для вычислений с помощью формул или функций, через ячейки, состоящие из столбцов и строк.
Его основная функция - выполнять математические операции - так же, как работает самый мощный калькулятор, - но также вычислять сложные взаимосвязи и упорядочивать и представлять полученные результаты в виде графика. Excel позволяет размещать, сортировать и искать данные, а также вставлять блоки текста и изображений. Основные элементы работы:
Ряд: набор из нескольких ячеек, расположенных горизонтально.
Заголовок строки: он всегда слева и называет строки номерами, которые в случае Excel Xp находятся в диапазоне от 1 до 65 536.
Столбец: набор из нескольких ячеек, расположенных вертикально.
Заголовок столбца: он всегда находится вверху и обозначает столбцы буквами от A до IV. После столбца Z идет AA, AB, AC и т. Д.; после AZ идет BA, BB, BC и т. д.; и так далее.
Ячейка: это пересечение строки и столбца, и в него мы вводим графику, будь то текст, числа, дату или другие данные. Ячейка названа в честь столбца, за которым следует имя строки. Например, ячейка, которая является пересечением строки 29 со столбцом F, - это F29.
Диапазон: диапазоны - это ссылка на набор ячеек в электронной таблице. Они обозначаются буквами и цифрами. Именуется ячейкой в одном углу диапазона (обычно в верхнем левом углу), затем двоеточием и противоположным углом. Например, диапазон, который включает ячейки C4, C5, C6, C7, D4, D5, D6, D7, E4, E5, E6 и E7, - это C4: E7.
Характеристики
Функции - это предопределенные формулы, которые выполняют вычисления с использованием определенных значений, называемых аргументами, в определенном порядке или структуре. Функции могут использоваться для выполнения простых или сложных операций.
Если функция недоступна и возвращает ошибку # ИМЯ ?, установите и загрузите программу расширения инструментов анализа.
Как?:
В меню «Инструменты» выберите «Плагины».
В списке «Доступные надстройки» выберите поле «Инструменты для анализа» и нажмите кнопку «ОК».
При необходимости следуйте инструкциям в программе установки.
Создать формулу
Формулы - это уравнения, которые выполняют вычисления для значений на листе. Формула начинается со знака равенства (=). Например, следующая формула умножает 2 на 3, а затем добавляет 5 к результату. = 2 * 3 + 5
Найти цель
Это часть серии команд, иногда называемых инструментами анализа «что, если». Если вы знаете желаемый результат простой формулы, но не знаете переменную, определяющую результат, вы можете использовать функцию «Поиск цели», щелкнув «Поиск цели» в меню «Инструменты». При выполнении целевого поиска Microsoft Excel изменяет значение конкретной ячейки до тех пор, пока формула, зависящая от этой ячейки, не вернет желаемый результат.
Настройте значение одной ячейки, чтобы получить конкретный результат для другой.
- В меню «Инструменты» щелкните «Найти цель». В поле «Определить ячейку» введите ссылку на ячейку, содержащую формулу (формула: последовательность значений, ссылки на ячейки, имена, функции или операторы ячейки, которые вместе создают новое значение. Формула всегда начинается со знака (=).), которое требуется решить. В поле Со значением введите желаемый результат. В поле Чтобы изменить ячейку, введите ссылку на ячейку, содержащую значение. вы хотите приспособиться. На эту ячейку должна ссылаться формула в указанной ячейке в поле «Определить ячейку». Нажмите «ОК».
Упражнение 1 (Применение функции цели поиска в Excel)
Если ссуда 5000 UM под 3,8% в месяц для выплаты в 6 месяцев, она утроится каждые два месяца, рассчитайте рассрочку к выплате.
Решение:
ВА = 5000; i = 0,038; n = 6; C1… 6 =?
Первый взнос может иметь любую стоимость; Важно то, что остальные взносы (начиная со второго) зависят от первого; так что, когда меняется первый, меняются и другие шансы, и остальная часть таблицы. Стоимость первого взноса необходимо будет изменить до тех пор, пока окончательный баланс не станет нулевым. Это можно сделать вручную, но компьютер делает это быстрее с помощью вышеупомянутой опции «Поиск цели». Мы определяем ячейку, в которой окончательный баланс последнего периода имеет нулевое значение, и просим изменить ячейку, в которой находится первый взнос.
Работа с поиском цели Excel.
1-й. Мы составляем таблицу амортизации, как показано в выписке из таблицы Excel.
В столбце E3 (Платеж) мы вводим 10 произвольное значение, как показано ниже:
Ячейка E3 10
Ячейка E4 = E3
Ячейка E5 = E4 * 2 (согласно условию проблемы).
Ячейка E6 = E5
Ячейка E7 = E6 * 2
Ячейка E8 = E9
ПРОЦЕНТ = НАЧАЛЬНЫЙ БАЛАНС x 0,038
ОПЛАТА = ПОИСК ЦЕЛИ
АМОРТИЗАЦИЯ = ОПЛАТА - ПРОЦЕНТ
(= E3 - C3)… (= E8 - C8)
Когда в таблице много периодов (строк) и нет двойного условия или MU X плюс каждые 2, 3 и т. Д. сборы; Самый быстрый способ действовать, это ввести в первую ячейку (ОПЛАТА) любое число, затем мы вводим вторую ячейку (ОПЛАТА) со знаком (=) и щелкаем мышью по первой ячейке ОПЛАТА. Наконец, мы помещаем указатель во вторую ячейку PAYMENT и из нижнего угла перетаскиваем указатель в форме креста в последнюю ячейку PAYMENT таблицы.
Параметр «Цель поиска» применим, когда мы рассчитываем стоимость взносов любого кредита или инвестиции с равномерными потоками.
Начало
- Финансовые функции
Даже со скоростью работы с электронными таблицами Excel решение сложных проблем требует времени и усилий. Тема финансовых функций в этой книге разделена на две большие группы: 9.5.1. Функции конвертации процентных ставок и 9.5.2. Функции для управления однородными сериями.
2.1. Функции конвертации процентной ставки
В этой группе мы классифицируем две функции, которые служат для преобразования эффективных процентных ставок в номинальные и наоборот.
Аргументы, используемые финансовыми функциями для конвертации курсов, следующие:
Num_per: количество периодов сложных процентов в год. (Когда мы работаем с НОМИНАЛЬНОЙ СТАВКОЙ).
Num_per_year: это количество периодов сложных процентов в год. (Когда мы работаем с CASH INT.).
Int_nominal: это номинальная годовая процентная ставка, выраженная в десятичном выражении.
Эффективная_ процентная ставка: это годовая эффективная процентная ставка, то есть эффективная доходность, полученная при реинвестировании процентов на тех же условиях в течение оставшейся части года.
Период сложного процента: время, которое проходит между двумя датами выплаты процентов; В случае этих функций мы предполагаем, что выплаченные проценты не изымаются и не расходуются, если не реинвестируются до конца года.
2.1.1. ВНУТРЕННИЕ НАЛИЧНЫЕ
Возвращает эффективную годовую процентную ставку, если мы знаем номинальную годовую процентную ставку и количество периодов сложных процентов в году. Применимо при точных сроках оплаты.
Синтаксис
INT.EFFECTIVE (int_nominal; число_за_год)
Если какой-либо из аргументов меньше или равен нулю или если аргумент number_per_year меньше единицы, функция возвращает значение ошибки #NUM !.
Полученный ответ выражается в десятичном формате и должен быть выражен в процентном формате. Никогда не делите и не умножайте результат этих функций на сто. Эта функция обеспечивает эффективную процентную ставку причитающегося процентного платежа. Для ожидаемых процентов эффективная ставка должна быть рассчитана с применением формулы.
Аргумент num_per_year усекается до целого числа, когда периоды нерегулярны, мы должны быть особенно осторожны с этой функцией, она дает надежные результаты только тогда, когда количество периодов выплат в году (num_per_year) имеет точные значения; например: ежемесячно (12), ежеквартально (4), раз в полгода (2) или ежегодно (1).
Результат, предоставляемый этой функцией, также получается по следующей формуле:
УПРАЖНЕНИЕ 2 (Применение функции CASH INT)
(A) Если сроки оплаты точны и результат надежен:
ДАТА НАЧАЛА: 15.03.2004
ДАТА КОНЕЦ: 15.06.2004
НОМИНАЛЬНАЯ СТАВКА: 68% годовых, ежеквартально.
Решение:
n = (15.03.2004 - 15.06.2004) = 90/30 = 3, m = (12/3) = 4
Применяя оба метода:
(B) Когда сроки оплаты неточны и, следовательно, результат нереален.
ДАТА НАЧАЛА: 15.03.2004
ДАТА КОНЕЦ: 15.06.2004
НОМИНАЛЬНАЯ СТАВКА: 68% годовых, каждые 2,20 месяца.
Решение:
n = (15.03.2004 - 21.05.2004) = 66/30 = 2,2, m = (12 / 2,2) = 5,2174
Применяя оба метода:
Начало
Наблюдая за обоими результатами, мы обнаруживаем, что они разные. В этих случаях рекомендуется использовать формулы, их результаты более реальны.
2.1.2. Финансовая функция НОМИНАЛЬНАЯ СТАВКА
Возвращает годовую номинальную процентную ставку, если мы знаем эффективную ставку и количество периодов сложных процентов в год.
Синтаксис
НОМИНАЛЬНАЯ СТАВКА (эффективная_ ставка, число_пер)
Аргумент num_per округляется до целого числа, мы должны быть особенно осторожны с этой функцией, она дает надежные результаты только тогда, когда количество периодов платежей в году (num_per) имеет точные значения; например: ежемесячно (12), ежеквартально (4), раз в полгода (2) или ежегодно (1).
Если какой-либо из аргументов меньше или равен нулю или если аргумент num_per меньше единицы, функция возвращает значение ошибки #NUM!
Полученный ответ выражается в десятичном формате и должен быть выражен в процентном формате. Никогда не делите и не умножайте результат этих функций на сто.
Эта функция обеспечивает номинальную ставку подлежащих выплате процентов. Для ожидаемых процентов номинальная ставка должна быть рассчитана по формуле:
пример
i = 0,3449; n = 12; j =?
2.2. Функции для управления однородными сериями
В нем представлены функции, которые служат для решения проблем, в которых между начальной и конечной стоимостью бизнеса есть выплаты комиссионных или полученные значения.
Во всех функциях однородных рядов мы предполагаем, что ценности, полученные или уплаченные в течение времени ведения бизнеса, реинвестируются на оставшееся время общего срока при тех же существующих условиях для первоначальных инвестиций. Задача равномерного ряда - это когда она полностью удовлетворяет следующим условиям:
- а) сумма платежей, произведенных во время инвестирования, постоянна; б) периодичность платежей, производимых во время инвестирования, постоянна; в) процентная ставка (выплаты по платежам, произведенным во время инвестирования) это постоянно.
Аргументы, используемые финансовыми функциями единого ряда, следующие:
Va, P в финансовом отношении. Это текущая стоимость серии равных будущих платежей. Если этот аргумент не указан, он будет считаться 0.
Оплата, C в финансовом отношении. Это платеж, производимый в каждый период, и не изменяется в течение срока действия аннуитета. Платеж включает основную сумму и проценты, но не включает никаких других сборов или налогов. Этот аргумент должен иметь знак, противоположный знаку Va, чтобы сохранить условия движения денежных средств: доход имеет положительный знак, а расходы - отрицательный.
Nper (n в финансовом выражении). Это общее количество периодов в аннуитете, то есть общий срок бизнеса.
Ставка (i в финансовом отношении). Это процентная ставка за период. Учтите, что это не годовая ставка, а номинальная ставка платежного периода, выраженная в десятичной дроби. Последовательность в использовании единиц важна при указании ставки и кпер.
Vf (F в финансовом выражении). Это будущая стоимость или остаток денежных средств, которого вы хотите достичь после совершения последнего платежа. Если аргумент Vf опущен, мы предполагаем, что значение равно 0.
Тип Это число 0 или 1, указывающее форму платежа между подлежащим выплате и авансом.
Определить тип
0 или пропущено в конце периода
1 В начале периода
Период. Задает порядковый номер исследуемой квоты, который должен находиться в диапазоне от 1 до Nper.
Per_inicial и Per_final Они определяют порядковый номер первого и последнего взноса периода для соответствующего анализа выплаченных взносов.
Оценка Это расчетная процентная ставка для Excel, с которой начинается итерационное вычисление процентной ставки однородного ряда. Если аргумент Estimate опущен, мы предполагаем, что это 10%.
2.2.1. Финансовая функция VF
Это позволяет рассчитать VF из C или VA. Он также используется для расчета стоимости FV с указанием того, является ли это авансовым платежом (тип = 1) или просроченным (тип = 0). Если мы хотим вычислить VF из VA, мы опускаем значение C; если квота просрочена, мы опускаем значение типа.
Возвращает будущую стоимость инвестиций, равную регулярным периодическим платежам с постоянной процентной ставкой.
Синтаксис: VF (ставка; кпер; платеж; идет; тип)
Результат, предоставляемый этой функцией, также получается по следующей формуле:
УПРАЖНЕНИЕ 3 (Применение функции VF)
Если мы откладываем 350 UM в месяц в течение 3 лет в банке, который платит 18% от номинала в год, и хотим знать, сколько денег мы сэкономим по истечении 3 лет:
Решение:
С = 350; п = (3 * 12) = 36; i = 0,015 (0,18 / 12); VF =?
Применяя оба метода, мы получаем:
Мы вводим данные в аргументы функции в порядке, указанном в поле синтаксиса:
При решении примеров и упражнений в этой книге мы будем использовать упрощенный формат, указанный в поле «Синтаксис», когда мы работаем с инструментом «Финансовые функции Excel». Эта методология ввода данных применима ко всем функциям Excel, используемым в работе, конечно, каждая со своим собственным экраном аргументов функции.
В этом примере необходимо рассмотреть три аспекта:
- a) Проценты, включенные в аргумент Ставка, должны быть в той же единице времени, что и аргумент Кпер. В этом случае, поскольку это ежемесячные платежи, процентная ставка должна быть ежемесячной, необходимо разделить номинальную годовую ставку на двенадцать. B) VA можно не указывать, как мы видим в мастере для функций, а в строке формул он автоматически оставляет пространство в функция, считая его равным 0. c) Если мы хотим, чтобы цифры в электронной таблице были положительными, мы вводим аргумент Payment со знаком минус, как мы можем видеть в мастере для функций (-350, в C2).
2.2.2. Финансовая функция VA
Позволяет рассчитать ВА от C или VF. Он также используется для расчета стоимости FV с указанием того, является ли это авансовым платежом (тип = 1) или просроченным (тип = 0). Чтобы рассчитать VA по VF, опустите значение C; а когда мы работаем с просроченными платежами, не указываем стандартную стоимость. Возвращает текущую стоимость инвестиции. Приведенная стоимость - это сумма серии будущих платежей. Например, когда мы занимаем деньги, сумма займа является для кредитора приведенной стоимостью.
Версия Excel для XP рекомендует использовать функцию вставки fx строки формул. При нажатии fx появляется меню функций и мы выбираем желаемую функцию. Эта функция сохраняет те же наблюдения, что и для VF.
Синтаксис: VA (ставка; кпер; платеж; vf; тип)
Результат, предоставляемый этой функцией, также получается по следующей формуле:
Например:
Если мы откладываем 350 UM в месяц в течение 3 лет в банке, который платит 18% от номинала в год, и мы хотим знать, сколько составляют эти ежемесячные платежи на сегодняшний день.
Решение:
С = 350; п = (3 * 12) = 36; i = 0,015 (0,18 / 12); VA =?
Применяя оба метода, мы получаем:
2.2.3. ОПЛАТА
Рассчитайте выплату по кредиту исходя из постоянных выплат и постоянной процентной ставки.
Синтаксис
ОПЛАТА (ставка; кпер; va; vf; тип)
Совет: чтобы найти общую сумму, выплаченную за период ссуды, умножьте значение, возвращаемое функцией PAYMENT, на аргумент nper.
Результат, предоставляемый этой функцией, также получается по следующей формуле:
УПРАЖНЕНИЕ 4 (Применение функции ОПЛАТА)
Мы получаем кредит в размере 10000 UM для оплаты 24 равными ежеквартальными платежами по номинальной годовой ставке 36% за квартал просроченной задолженности:
Решение:
ВА = 10 000; n = 24; i = (0,36 / 12) = 0,03; C =?
Применяя оба метода, мы получаем:
В некоторых случаях может потребоваться как VA, так и VF; как и в случае с лизингом, при котором, помимо первоначальной стоимости команды, у нас есть равные ежемесячные взносы, а в конце платежа пользователю предоставляется возможность приобрести актив.
Например:
При аренде в размере 50 000 UM на 24 месяца с процентной ставкой 2,87% в месяц и возможностью покупки 12% функция оплаты для расчета ежемесячного платежа, подлежащего выплате, будет работать следующим образом:
Решение:
ВА = 50 000; i = 0,0287; n = 24; VF = 12%; C =?
2.2.4. СТАВКА финансовой функции
Возвращает процентную ставку за период аннуитета. СТАВКА рассчитывается путем итераций и может иметь ноль или более решений. Если последовательные результаты RATE не сходятся в пределах 0,0000001 после 20 итераций, RATE возвращает значение ошибки #NUM !.
С помощью этой функции можно рассчитать процентную ставку, комбинируя не только VA и FV, но также VA и C, C и FV и VA, C и FV.
Поскольку это ставка за период, она может быть как номинальной, так и эффективной. Чтобы преобразовать эту ставку в годовую, необходимо соблюдать осторожность с используемой формулой, в зависимости от того, какую ставку мы хотим рассчитать: номинальную ставку или годовую эффективную ставку (TEA).
Синтаксис
СТАВКА (кпер; платеж; ва; ср; ставка; оценка)
Например:
ВА = 5000; n = 5; С = 1250; я =?
Функция, используемая для расчета периодической ставки аннуитета. Формулы для расчета ставки аннуитета отсутствуют.
2.2.5. Финансовая функция КПЕР
Возвращает количество периодов, в течение которых инвестиции должны быть равны серии равных периодических выплат.
Синтаксис
КПЕР (ставка; выплата; va; vf; тип)
Единица времени, введенная в функции Nper, должна быть такой же, как и в процентной ставке.
Результат, обеспечиваемый этой функцией, также получается с помощью следующих формул, в зависимости от случая:
Например:
i = 0,06; С = 14000; VA = 93 345,50; п =?
2.2.6. Финансовая функция NPV или NPV
Рассчитывает чистую приведенную стоимость инвестиций на основе ставки дисконтирования и будущих выплат (отрицательные значения) и дохода (положительные значения).
Синтаксис
ЧПС (ставка; значение1; значение2;…)
Значения, включенные в денежный поток, не обязательно должны быть постоянными. Это основное отличие от функции VA, она сохраняет условие, что и процентная ставка, и периодичность постоянны; то есть все скидки на денежные потоки по одной и той же ставке и значения, включенные в нее, происходят с равными интервалами.
Из диапазона денежных потоков мы исключаем текущую стоимость, находящуюся в нулевом периоде (0), указанная стоимость выражена в сегодняшних MU. Первоначальная инверсия ячейки с периодом 0 не вводит значения в аргумент, позже мы вычитаем из результата, который возвращает функция.
Чистая приведенная стоимость является показателем экономической целесообразности инвестиций, она предполагает субъективность инвестора, который должен выбрать процентную ставку для дисконтирования денежного потока. При расчетах с двумя разными скоростями мы получаем два результата, для оценки этих случаев необходимо учитывать, что ответ выражается в MU нулевого периода и его значение можно интерпретировать следующим образом:
- NPV> 0, положительный результат указывает на то, что исследуемый бизнес дает более высокую прибыльность, чем требуется инвестору, после вычета инвестиций тогда удобно вести бизнес VNA = 0, в случае представления результат, равный нулю, указывает что бизнес приносит доход, равный требуемому инвестору, выполнение проекта необязательно. VNA <0, отрицательная чистая приведенная стоимость не означает, что исследуемый бизнес приводит к убыткам, только доходность ниже, чем требуется инвестору, и он, в частности, не подходит для бизнеса.
Из вышеизложенного мы заключаем, что когда мы объявляем NPV проекта, следует уточнить, какая ставка дисконтирования использовалась для ее расчета, то есть какое значение было введено в аргументе Rate.
2.2.7. Финансовая функция IRR
Возвращает внутреннюю норму доходности (норму доходности) денежных потоков, представленных числами в аргументе значений. Эти денежные потоки не постоянны, как в аннуитетах. Однако денежные потоки должны происходить через регулярные промежутки времени, например, месяцы или годы. Внутренняя норма прибыли эквивалентна процентной ставке, производимой инвестиционным проектом с платежами (отрицательные значения) и доходом (положительные значения), которые происходят в регулярные периоды.
Синтаксис
IRR (значения; оценка)
Для расчета функции IRR мы включаем весь денежный поток в диапазон значений, при этом необходимо, чтобы были положительные и отрицательные значения. Аргумент Estimate необязательный. Если он не указан, Excel предполагает начальную ставку 10%.
IRR учитывает только конкретные условия проекта и не зависит от субъективности инвестора. Однако математические трудности приводят к недоверию к полученным результатам.
Завершим эту часть следующим наглядным примером.
Сезар откладывает 350 UM в месяц в течение 3 лет в банке, который платит 18% годовых и хочет знать, сколько денег он сэкономит по истечении 3 лет:
Решение:
С = 350; п = 36 (3 * 12); i = 0,015 (0,18 / 12); VF =?
В этом примере необходимо рассмотреть три аспекта:
- a) Проценты, включенные в аргумент ставки, должны быть в той же единице времени, что и в аргументе Nper, в этом случае, поскольку они являются ежемесячными платежами, процентная ставка должна быть ежемесячной, поэтому ее необходимо разделить на двенадцать - номинальная годовая ставка. b) VA можно не указывать, как мы видим в мастере для функций, а в строке формул он автоматически оставляет пространство в функции, принимая его за ноль. c) Чтобы числа в электронной таблице были положительными, аргумент Payment имеет отрицательный знак, как мы видим в мастере функций (-350).
При решении примеров и упражнений в этой книге мы используем упрощенный формат (из примера приложения), когда работаем с инструментом Excel Financial Functions.
Скачать оригинальный файл
