Лабораторная работа №3. Математические вычисления в Excel.

Задание №1. Работа с массивами и матрицами

Для матриц и подсчитать количество отрицательных элементов (функция СЧЕТЕСЛИ ), вычислить определитель (функция МОПРЕД ), обратную (функция МОБР ) и транспонированную (функция ТРАНСП ) матрицы, найти сумму, разность и произведение матриц (фукция МУМНОЖ ).

*Примечание для получения численных результатов следует выделить необходимый диапазон, установить курсор в строку формул и нажать < Ctrl> +< Shift> +< Enter>.

Задание №2. Решение систем линейных уравнений.

При помощи функций для работы с массивами и матрицами решите систему линейных уравнений

 

Задание № 3. Нахождение корней уравнения с помощью подбора параметра.

Найти все корни уравнения

1. Будем считать, что первый корень уравнения равен 0. Введите 0 в ячейку В4. В ячейке В3 найдите значение уравнения при х=0.

2. Для вычисления первого корня выделите ячейку В3 и вызовите Подбор параметра (На вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Подбор параметра).

 

 

3. Аналогично вычислите оставшиеся корни.

Задание №4. Построение графиков в MS Excel.

Построить график функции .

1. Задайте область определения Х вводом начальных данных 0; 0, 1 а затем маркером автозаполнения подготовьте весь диапазон А4: А24

2. В ячейку В4 введите формулу =COS(ПИ()*A4) и с помощью автозаполнения скопируйте ее на диапазон В4: В27

3. Постройте график функции для этого:

· Выделите диапазон значений вашей функции (диапазон В4: В27)

· На вкладке Вставка в группе Диаграмма выберите График; выберите один из предложенных вариантов графиков;

· В группе Данные щелкните по кнопке Выбрать данные

· В Подписи горизонтальной оси щелкните Изменить и выделите диапазон А4: А24.

· Для того, чтобы изменить легенду необходимо в Элементах легенды (рядах) выделить необходимый ряд, щелкнуть кнопку Изменить и ввести имя ряда.

Лабораторная работа № 4. Некоторые финансовые функции Excel

Работа с финансовыми функциями в MS Excel предполагает использование следующей методики:

1. Подготовить на рабочем листе значения основных аргументов функции

2. Перейти к ячейке в которую вводиться формула, использующая встроенную финансовую функцию

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

4. Если аргумент финансовой функции является результатом расчета другой вложенной функции, используйте повторный вызов мастера функций для данного аргумента

5. После ввода всех аргументов нажмите кнопку ОК и MS Excel произведет расчет по формуле.

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

− Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления, (например, дивиденды) – положительными;

− Все даты, как аргументы функции имеют числовой формат

− Для логических аргументов используются константы ИСТИНА или ЛОЖЬ, либо функции категории Логические

− Каждый аргумент должен находиться на своем месте. Если аргументы пропускаются, то следует поставить соответствующее число разделительных знаков.

Задание №1.

Определить величину основного платежа за четвертый год, если выдана ссуда размером 1 000 000 000 сроком на 5 лет под 12% годовых.

Для основных платежей по займу, который погашается равными платежами в конце или в начале каждого расчетного периода используется функция ОСПЛТ, которая возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянства периодических платежей и постоянства процентной ставки.

ОСПЛТ(Ставка, Период, Кпер, Пс Бс)

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

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

Кпер — общее число периодов платежей по аннуитету. Например, если получена ссуда на 4 года под автомобиль и делаются ежемесячные платежи, то ссуда имеет 4*12 (или 48) периодов. В качестве значения аргумента кпер в формулу нужно ввести число 48.

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

Бс — требуемое значение будущей стоимости или остатка средств после последней выплаты. Если аргумент опущен, он полагается равным 0 (будущая стоимость займа, например, равна 0). Например, если предполагается накопить 50000 руб. для оплаты специального проекта в течение 18 лет, то 50 000 руб. это и есть будущая стоимость. Можно сделать предположение о сохранении заданной процентной ставки и определить, сколько нужно откладывать каждый месяц.

Ввод данных и расчеты производятся в соответствии с рисунком

 

 

Задание №2.

Рассчитать 20-летнюю ипотечную ссуду со ставкой 10% годовых при начальном взносе 25% и ежемесячной (ежегодной) выплате.

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

ПЛТ(Ставка; Кпер; Бс; Пс; Тип).

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

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

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

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

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

Тип	Когда нужно платить
0 или опущен	В конце периода
	В начале периода

В нашем случае функция ПЛТ имеет вид:

ПЛТ(10%/12; 20*12; -(350000*(1-25%))) – ежемесячные выплаты;

ПЛТ(10%; 20; -(350000*(1-25%))) – ежегодные выплаты.

Решение задачи приведено на рисунках.

 

Задание №3.

Определить, какая сумма окажется на счете, если 52000 руб. положены на 20 лет под 11% годовых. Проценты начисляются ежемесячно.

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

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

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

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

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

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

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

Тип	Когда нужно платить
	В конце периода
	В начале периода

Для нашей задачи функция БС примет вид:

БС(11%/12; 20*12;; -52000)

Решение задачи приведено на рисунке, а формула для расчета ячейки В30:

Задание №4.

Ожидается, что ежегодные доходы от реализации проекта составят 54000000 руб. Рассчитать срок окупаемости проекта, если инвестиции к началу поступления доходов составят 140000000 руб., а норма дисконтирования 7, 67.

Для определения срока платежа используется функция КПЕР, которая возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

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

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

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

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

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

Тип — число 0 или 1, обозначающее срок выплаты.

В нашей задаче функция выглядит следующим образом:

КПЕР(7, 67%; 54000000; -140000000)=3

Задание №5.

Облигация номиналом 200000 руб. выпущена на 7 лет. Предусматривается следующий порядок начисления процентов: в первый год начисляется 11%, последующие три года – по 16%, в оставшиеся три года по 20%. Рассчитать будущую (наращенную) стоимость облигации по сложной процентной ставке.

Для расчета наращенной стоимости облигации по сложной процентной ставке используется функция БЗРАСПИС, которая возвращает будущую стоимость первоначальной основной суммы после применения ряда (плана) ставок сложных процентов. Функция БЗРАСПИС используется для вычисления будущей стоимости инвестиции с переменной процентной ставкой.

БЗРАСПИС(первичное; план)

Первичное — стоимость инвестиции на текущий момент.

План — массив применяемых процентных ставок.

Решение приведено на рисунке

Формула для расчета примет следующий вид:

БЗРАСПИС(200000; {11%; 16%; 16%; 16%; 20%20%20%}).

Задание №6.

Затраты по проекту составят 600 млн. руб. Ожидаемые доходы в течение последующих 5 лет составят, соответственно, 50, 100, 300, 200, 300 млн. руб. Оценить экономическую целесообразность проекта по скорости оборота инвестиций, если рыночная норма дохода 15%.

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

ВСД(значения; предположение)

Значения — массив или ссылка на ячейки, содержащие числа, для которых требуется подсчитать внутреннюю ставку доходности.

Значения должны содержать по крайней мере одно положительное и одно отрицательное значение.

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

Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, такие значения игнорируются.

Предположение — величина, предположительно близкая к результату ВСД.

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

Для принятия решения о экономической целесообразности проекта используйте логическую функцию ЕСЛИ:

Решение приведено на рисунке:

Задание №7.

Предполагается, что доходы по проекту в течение 5 лет составят 120000000 руб., 200000000., 300000000 руб., 250000000 руб., 320000000 руб. Определить первоначальные затраты на проект, чтобы обеспечить скорость оборота 12%.

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

Первоначально для расчета величина затрат на проект выбирается произвольно и производятся вычисления. В ячейку В11 вводится функция ВСД.

Далее, на вкладке Данные в группе Средства обработки данных выберите команду Анализ условия, а затем выберите в списке пункт Подбор параметра. Находим величину первоначальных затрат на проект, обеспечивающих скорость обороты инвестиций в 12%:

⇐ Предыдущая123456Следующая ⇒

Поделиться: