2.1 Решение финансовых задач в Excel

Задача 1

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

Решение задачи

Для решения задачи используется функция ПЛТ.

Функция вычисляет величину выплаты за один период годовой ренты (например, регулярных платежей по займу) при постоянной процентной ставке. ПЛТ(ставка;кпер;пс;бс;тип), Ставка — процентная ставка по ссуде, Кпер — общее число выплат по ссуде.

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

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

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

Исходные данные для решения задачи представлены на рис.1.

Рис.1 Исходные данные.

Формулы для решения задачи представлены на рис.2.

Рис.2 Формулы для решения задачи.

Результаты решения задачи представлены на рис.3.

Рис.3 Результаты решения задачи.

Вывод При расчёте 10-летней ипотечной ссуды со ставкой 8% годовых при начальном взносе 20% суммы и ежемесячной (ежегодной) выплате. Общая сумма выплат составит 357 670,77р.

Задача 2

Рассмотреть возможность инвестиций 30 000 руб. Возврат 5 000 руб. через год, 10 000 руб. через два года и т. д. 18 000 руб. — через 3 лет.

Решение задачи

ЧПС – Возвращает величину чистой приведенной стоимости инвестиции, используя ставку дисконтирования, а также стоимости будущих выплат (отрицательные значения) и поступлений (положительные значения).

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

Исходные данные для решения задачи представлены на рис.4.

Рис.4 Исходные данные.

Формулы для решения задачи представлены на рис.5.

Рис.5 Формулы для решения задачи.

Результаты решения задачи представлены на рис.6.

Рис.6 Результаты решения.

Вывод

При расчёте инвестиций на 3 лет, с возвратом по

30000, 5000, 10000, 18000 рублей с процентной ставкой более 1% не имеет смысл.

Задача 3

Рассмотреть возможность инвестиций 300000 руб. Возврат по 34000 руб. в течение 10 лет. При какой годовой процентной ставке это имеет смысл?

Решение задачи

ПС( ставка; кпер; выплата; бз; тип)

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

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

выплата — величина постоянных периодических платежей;

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

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

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

Исходные данные для решения задачи представлены на рис.7.

Рис.7 Исходные данные.

Формулы для решения задачи представлены на рис.8.

Рис.8 Формулы для решения задачи.

Результаты решения задачи представлены на рис.9.

Рис.9 Результаты решения.

Вывод

При рассмотрении возможности инвестиций 30 0000 руб на 10 лет. С ежегодным возвращением по 34 000 руб . С процентной ставкой более 1% , не выгодна.

Задача 4

Вычислить основные платежи, плату по процентам, общую ежегодную выплату и остаток долга на примере ссуды 300000 руб. под годовую ставку8% на срок 10 лет.

Решение задачи

Присвойте ячейкам следующие имена:

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

Ячейка Имя

B1 Процент

B2 Срок

B3 Ежегодная_плата

B4 Размер_ссуды

Кроме того, в ячейки введите формулы:

Ячейка Формула Описание

ВЗ =ПЛТ(Процент;Срок;–Размер_ссуды) Ежегодная плата

D6 =Размер_ссуды Начальный остаток долга

А7 =A6+1 Номер года

В7 =D6*Процент Плата по процентам

С7 =Ежегодная_плата-В7 Основная плата за первый год

D7 =ЕСЛИ(D6-С7<>0;D6-С7) Остаток долга за первый год

Исходные данные для решения задачи представлены на рис.10.

Рис.10 Исходные данные.

Формулы для решения задачи представлены на рис.11.

Рис.11 Формулы для решения задачи.

Результаты решения задачи представлены на рис.12.

Рис.12 Результаты решения.

Вывод

Были вычислены основные платежи, плата по процентам , общая ежегодная выплата.

Задача 5

Рассмотреть возможность инвестиций 300000 руб. под годовую ставку 6%. Возможна выплата по 34000 руб. в год. Сколько лет займут эти выплаты?

Решение задачи

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

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

выплата — величина постоянных периодических платежей;

нз — текущее значение, т. е. общая сумма, которую составят будущие платежи;

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

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

Исходные данные для решения задачи представлены на рис.13.

Рис.13 Исходные данные.

Формулы для решения задачи представлены на рис.14.

Рис.14 Формулы для решения задачи.

Результаты решения задачи представлены на рис.15.

Рис.15 Результаты решения.

Вывод

При расмотрении инвестиций на сумма 33000 руб. под годовую ставу 6% и возможных выплат по 34000 руб. Выплаты зайдут 13 лет.

Задача 6

Рассмотреть возможность инвестиций 30000 руб. С выплатами в определенные дниИмеет ли смысл это при годовой ставке 1 % (Сумма выплат из задания 2).

Решение задачи

ЧПС (ставка; 1-е значение; 2-е значение; ...)

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

1-е значение, 2-е значение, ... — от 1 до 29 аргументов, представляющих расходы и доходы. 1-е значение, 2-е значение, ... должны быть равно­мерно распределены по времени и осуществляться в конце каждого пе­риода. НПЗ использует порядок аргументов 1-е значение, 2-е значение,…для определения порядка поступлений и платежей.

Исходные данные для решения задачи представлены на рис.16.

Рис.16 Исходные данные.

Формулы для решения задачи представлены на рис.17.

Рис.17 Формулы для решения задачи.

Результаты решения задачи представлены на рис.18.

Рис.18 Результаты решения.

Вывод

Рассмотрев возможность инвестиций 30000 руб. С выплатами в определенные дни Не имеет смысл это при годовой ставке 15 %.

Задача 7

Составить отчетную ведомость реализации товаров 6 магазинами с

август по январь.

Решение задачи

В этой отчетной ведомости надо определить:

суммарную и среднюю выручку каждого из магазинов за отчетный период;

суммарную выручку всех магазинов за каждый месяц отчетного периода;

место, которое занимает каждый из магазинов в суммарном объеме вы­ручки;

долю каждого из магазинов в суммарном объеме выручки;

количество магазинов, имеющих суммарную выручку до 1000 млн руб., от 1000 млн руб. до 1500 млн руб., от 1500 млн руб. до 2000 млн руб. и свы­ше 2000 млн руб.

Исходные данные для решения задачи представлены на рис.19.

Рис.19 Исходные данные.

Формулы для решения задачи представлены на рис.20,1/20,2.

Рис.20,1 Формулы для решения задачи.

Рис.20,2 Формулы для решения задачи.

Результаты решения задачи представлены на рис.21.

Рис.21 Результаты решения.

Вывод

Составлена отчетная ведомость реализации товаров 6 магазинами с

август по январь.

Задача 8

Рассмотреть возможность инвестиций 5900 руб. под годовую ставку 2% и собираетесь отдавать по 164 руб. в год. Сколько лет займут выплаты?

Решение задачи

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

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

выплата — величина постоянных периодических платежей;

нз — текущее значение, т. е. общая сумма, которую составят будущие платежи;

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

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

Исходные данные для решения задачи представлены на рис.22.

Рис.22 Исходные данные

Формулы для решения задачи представлены на рис.23.

Рис.23 Формулы для решения задачи.

Результаты решения задачи представлены на рис.24.

Рис.24 Результат решения.

Вывод

Рассмотрели возможность инвестиций 59000 руб. под годовую ставку 2% с выплатами по 164 руб. выплаты займут 64,4 лет.

Задача 9

Рассмотреть возможность вкладывать по 164 руб. в течение 13 лет при годовой ставке 2%. Сколько денег будет на счету через 13 лет?

Решение задачи

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

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

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

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

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

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

Исходные данные для решения задачи представлены на рис.25.

Рис.25 Исходные данные.

Формулы для решения задачи представлены на рис.26.

Рис.26 Формулы для решения задачи.

Результаты решения задачи представлены на рис.27.

Рис.27 Результат решения.

Вывод

При рассмотрении возможности вкладывать по 164 руб. в течение 13 лет при годовой ставке 2%. Денег будет через 13 лет 1 861,13руб.

Задача 10

Определить процентную ставку для 64,2 летнего займа в 5900 руб. с ежегодной выплатой в 164 руб.

Решение задачи

СТАВКА(кпер;плт;пс;бс;тип;предположение)

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

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

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

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

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

Исходные данные для решения задачи представлены на рис.28.

Рис.28 Исходные данные.

Результаты решения задачи представлены на рис.29.

Рис.29 Решения задачи.

Вывод

Определена процентная ставку для 64,2 летнего займа в 5900 руб. с ежегодной выплатой в 164руб.

Задача 11

Составить таблицу начисления премии по итогам работы сети n магази­нов с августа по февраль по следующему правилу:

если продукции продано не меньше чем на 86000 руб., то комиссионные составляют 4%;

за первое место дополнительно начисляется 6%, за второе место – 3% ,за третье место – 1,2%, за четвёртое место – 0,75%.

Решение задачи

Cрзнач находит среднее арифметическое значение из указанного диапазона ячеек.

Синтаксис:

СРЗНАЧ (число1; число2; …)

Функция ранг возвращает ранг числа в списке чисел. Ранг числа - это его величина относительно других значений в списке. (Если список от­сортировать, то ранг числа будет его позицией.)

Синтаксис:

РАНГ(число; ссылка; порядок)

число — число, для которого определяется ранг;

ссылка — массив или ссылка на список чисел. Нечисловые значения в ссылке игнорируются;

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

Функция ранг присваивает одинаковым числам одинаковый ранг.

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

Синтаксис:

ЧАСТОТА (массив_данных; двоичный__массив)

массив_данных — массив или ссылка на множество данных, для которых вычисляются частоты; если массив_данных не содержит значений, то функция частота возвращает массив нулей;

двоичный_массив — массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных. ЕСЛИ двоичный_массив не содержит значений, то функция частота возвращает количество эле­ментов в аргументе двоичный_массив.

Исходные данные для решения задачи представлены на рис.30.

Рис.30 Исходные данные.

Формулы для решения задачи представлены на рис.31,1/31,2.

Рис.31,1 Формулы для решения задачи.

Рис.31,2 Формулы для решения задачи.

Результаты решения задачи представлены на рис.32.

Рис.32 Решения задачи.

Задача 12

Предприятие владеет основными средствами 86000 руб. Рассчитать показатели амортизации за 6лет.

Решение задачи

АПЛ Возвращает величину амортизации актива за один период, рассчитанную линейным методом.

АПЛ(нач_стоимость;ост_стоимость;время_эксплуатации)

Нач_стоимость     — затраты на приобретение актива.

Ост_стоимость     — стоимость в конце периода амортизации (иногда называется остаточной стоимостью имущества).

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

Исходные данные для решения задачи представлены на рис.33.

Рис.33 Исходные данные.

Формулы для решения задачи представлены на рис.34.

Рис.34 Формулы для решения задачи.

Результаты решения задачи представлены на рис.35.

Рис.35 Результаты решения.

Вывод

Предприятие владеет основными средствами 86000 руб. Рассчитали показатели амортизации за 6 лет. Результат амортизации 13 177 руб.

Задача 13

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

Решение задачи

ДДОБ (стоимость; остаточная_стоимость; время__эксплуатации; период;коэффициент)

стоимость — начальная стоимость имущества;

остаточная_стоимость — остаточная стоимость в конце периода;

время__эксплуатации — количество периодов, за которые собственность амортизируется (иногда называется временем полной амортизации);

время_эксплуатации — количество периодов, за которые амортизируется имущество (иногда называется временем полной амортизации);

период — номер периода для вычисления амортизации (должен быть из­мерен в тех же единицах, что и время полной амортизации);

коэффициент — норма снижения балансовой стоимости (амортизации).Если коэффициент опущен, то предполагается, что он равен 2 ( метод двукратного учета амортизации).

Исходные данные для решения задачи представлены на рис.36.

Рис.36 Исходные данные.

Формулы для решения задачи представлены на рис.37.

Рис.37 Формулы для решения задачи.

Результаты решения задачи представлены на рис.38.

Рис.38 Результаты решения.

Вывод

Предприятие владеет основными средствами 86 000руб. Рассчитать показатели амортизации за период 6 лет . при дополнительном условии: вычислении амортизации за первый год равной 5 302, 65руб.