Практическая работа 3 «Финансовые функции»

Некоторые функции в Excel:

ЕСЛИ

Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ. Например, формула =ЕСЛИ(A1>10,"Больше 10","10 или меньше") возвращает строку "Больше 10", если значение в ячейке A1 больше 10, и "10 или меньше", если оно меньше или равно 10.

ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь])

Функция ЕСЛИ имеет аргументы указанные ниже:

Лог_выражение - обязательный аргумент. Любое значение или выражение, дающее в результате значение ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, в противном случае — значение ЛОЖЬ. В этом аргументе может использоваться любой оператор сравнения.
Значение_если_истина — необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ИСТИНА. Например, если данный аргумент — строка "В пределах бюджета", а аргумент лог_выражение соответствует значению ИСТИНА, функция ЕСЛИ возвращает текст "В пределах бюджета". Если аргумент лог_выражение соответствует значению ИСТИНА, а аргумент значение_если_истина опущен, возвращается значение 0. Чтобы отобразить слово ИСТИНА, используйте логическое значение ИСТИНА в качестве этого аргумента.
Значение_если_ложь - необязательный аргумент. Значение, которое возвращается, если аргумент лог_выражение соответствует значению ЛОЖЬ, Например, если данный аргумент — строка "Превышение бюджета", а аргумент лог_выражение соответствует значению ЛОЖЬ, функция ЕСЛИ возвращает текст "Превышение бюджета". Если аргумент лог_выражение соответствует значению ЛОЖЬ, а аргумент значение_если_ложь опущен (т. е. после аргумента значение_если_истина нет запятой), возвращается логическое значение ЛОЖЬ. Если аргумент лог_выражение соответствует значению ЛОЖЬ, а значение аргумента значение_если_ложь опущено (т. е. в функции ЕСЛИ нет запятой после аргумента значение_если_истина), возвращается значение 0.

ОКРУГЛВВЕРХ

Функция ОКРУГЛВВЕРХ(число; число_разрядов) округляет число до ближайшего большего по модулю.

Аргументы функции:

Число - обязательный аргумент. Любое вещественное число, которое требуется округлить вверх.
Число_разрядов - обязательный аргумент. Количество цифр, до которого округляется число.

КПЕР

Функция КПЕР(ставка; плт; пс; [бс], [тип]) возвращает количество периодов платежей для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

Аргументы функции:

Ставка – обязательный аргумент. Годовая ставка в процентах, разделённая на количество периодов платежей за год =Ставка кредита/12;
плт – обязательный аргумент. Ссылка на ячейку «Размер ежемесячного платежа». Так как деньги списываются, необходимо изменить знак на отрицательный;
пс – обязательный аргумент. Ссылка на ячейку «Сумма кредита».

ДОЛЯГОДА

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

Аргументы функции:

нач_дата — обязательный аргумент. Начальная дата.
кон_дата Обязательный. Конечная дата.
Базис — необязательный аргумент. Используемый способ вычисления дня.

ПС

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

Аргументы функции:

Ставка - обязательный аргумент. Процентная ставка за период. Например, если получен кредит на автомобиль под 10 процентов годовых и выплаты производятся ежемесячно, процентная ставка за месяц составит 10%/12 (0,83%). В качестве значения аргумента "ставка" нужно ввести в формулу 10%/12, 0,83% или 0,0083.
Кпер - обязательный. Общее число периодов платежей для ежегодного платежа. Например, если получен кредит на 4 года на покупку автомобиля и платежи производятся ежемесячно, то кредит имеет 4*12 (или 48) периодов. В качестве значения аргумента "кпер" в формулу нужно ввести число 4*12 или 48.
Плт - обязательный. Выплата, производимая в каждый период и не меняющаяся на протяжении всего периода ежегодного платежа. Например, ежемесячная выплата по кредиту в размере 10 000 ₽ под 12 процентов годовых на 4 года составит 263,33 ₽. В качестве значения аргумента "плт" нужно ввести в формулу число -263,33 (сумма возможного вносимого ежемесячного платежа плательщика с обратным знаком, т.к. деньги будут списываться со счета).

БС

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

Аргументы функции:

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

СЦЕП

Функция СЦЕП(текст1; текст2; …) в Excel используется для объединения (склеивания) текстовых строк из нескольких ячеек в одну строку. Функция принимает от двух до более 250 аргументов (текстовых строк или ссылок на ячейки), которые нужно объединить. Результатом функции будет новая текстовая строка, содержащая все текстовые строки, указанные в качестве аргументов, в порядке их следования.

Ниже приведены примеры:

=СЦЕП("Популяция рек для ";A2;" ";A3;" составляет ";A4;" на километр.");
=СЦЕП(В2;" ";C2).

Аргументы функции:

текст1 - обязательный аргумент. Первый элемент для объединения. Это может быть текстовое значение, число или ссылка на ячейку;
текст2,… - необязательные аргумент. Дополнительные текстовые элементы для объединения. Можно указать до 255 элементов и до 8192 символов.

Задание 1.1 «Выгодные условия»

B книге «FinFunctions.xlsx» на листе «Банки» приведена информация о банках города Сайлент Хилл. Клиент желает найти банк с наиболее выгодными для себя условиями кредитования. На погашение кредита он планирует ежемесячно расходовать фиксированную часть от дохода. Дополните таблицу личной информацией клиента:

ежемесячный доход — 65000 руб.;
желаемая сумма кредита — 300000 руб.;
доля отчисления — 25% от ежемесячного дохода.

Рассчитайте сроки погашения кредита для разных банков и отсортируйте информацию по возрастанию количества месяцев погашения кредита.

Обратите внимание! В этом задании данные Ставка вклада и Периодичность начисления % по вкладу не используются.

Скачайте архив по ссылке ниже.

Практическая работа 3 «Финансовые функции». FinFunctions

Файл: zipДата загрузки: 18 дек. 2025г.

Откройте книгу «FinFunctions.xlsx». Перейдите на лист «Банки».
Добавьте выше имеющейся информации строки для внесения персональных данных клиента (Ежемесячный доход, Сумма кредита, Доля дохода на погашение кредита), а также строку «Размер ежемесячного платежа»(рис. 1).

Электронные таблицы. ПР 3. Рис 1 — Рис. 1

Вычислите Размер ежемесячного платежа в ячейке B5 (по данным Ежемесячный доход и Доля дохода на погашение кредита).
Сформируйте столбец Период выплаты (Рис 2). Для вычислений используйте функцию КПЕР.

Электронные таблицы. ПР 3. Рис 2 — Рис. 2

Поскольку количество периодов дробное число, его необходимо округлить до целого. Используйте функцию ОКРУГЛВВЕРХ(число; число разрядов).
Отсортируйте банки в порядке возрастания значений в столбце
Сравните свой результат с результатом, приведённым на рис. 3.

Электронные таблицы. ПР 3. Рис 3 — Рис. 3

Сохраните результат работы в личной папке в файле «FinFunctions.xlsx».

Задание 1.2 «SMS (Short Message Service - Служба коротких сообщений)»

На листе «Клиенты» книги «FinFunctions.xlsx» подготовьте текст SMS- предложения банка по кредитованию для каждого из клиентов.

Срок кредитования банк определяет по правилу: если клиент пользуется банком не менее десяти полных лет, то предлагается кредит на два года, если менее, то на один год.

При вычислении суммы предлагаемого кредита банк исходит из того, что клиент будет платить 25% от средней суммы поступлений на его счёт за месяц.

Округлите предлагаемую банком сумму кредита к ближайшему кратному 5000 числу, используя функцию ОКРВВЕРХ.

Примерный текст сообщения:

«Иван Иванович, Вам одобрен кредит на 155000 руб. сроком на 2 года».

Откройте книгу «FinFunctions.xlsx». Перейдите на лист «Клиенты».
Ориентируясь на любой из банков, в ячейку B1 занесите актуальную на данный момент ставку потребительского кредита.
Проверьте, что в ячейке D1 стоит текущая дата формирования сообщений.
Создайте формулу для заполнения Срока кредитного предложения для каждого клиента используя функцию ДОЛЯГОДА и правило кредитования банка.
Используя функцию ПС, рассчитайте размер предлагаемого кредита. Значения для аргументов функции:
- ставка – (актуальная на данный момент ставка потребительского кредита)/12;
- кпер – срок кредитного предложения*12;
- плт – сумма возможного вносимого ежемесячного платежа плательщика с обратным знаком, т.к. деньги будут списываться со счета.
Задайте вариант слова «год» или «года» в зависимости, от срока кредитного предложения используя функцию ЕСЛИ.
Сформируйте текстовое сообщение используя функцию СЦЕП.
Сохраните результат работы в книге «FinFunctions.xlsx».

Задание 1.3 «Ожидаемая прибыль от вклада»

По данным листа «Банки» книги «FinFunctions.xlsx» вычислите ожидаемую прибыль для клиента, желающего сделать вклад на два года на сумму 200000 руб., при этом клиент не планирует снимать или добавлять деньги в период вклада.
Для того чтобы клиенту было проще сделать выбор, отсортируйте банки по убыванию значений ожидаемой прибыли.

Откройте книгу «FinFunctions.xlsx». Перейдите на лист «Банки».
Подпишите ячейку С3 – «Сумма вклада». В ячейку D3 введите значение суммы вклада.
Сформируйте столбец «Сумма по окончании вклада». Для вычислений используйте функцию БС(ставка; кпер; плт; [пс], [тип]), которая возвращает будущую стоимость инвестиции при условии периодических равных платежей и постоянной процентной ставки. Значения для аргументов функции:
- ставка — годовая ставка в процентах, разделённая на количество периодов начисления процентов за год (сколько раз проценты начислили за год на счёт);
- кпер — количество периодов начисления процентов. При вычислении учтите данные периодичности начисления процентов и весь срок вклада;
- плт — фиксированная сумма, на которую изменяется вклад каждый период времени: если клиент планирует снимать некоторую сумму, то число положительное; 0, если деньги не снимаются и не добавляются; при планируемом добавлении сумм к вкладу число отрицательное;
- пс — начальная сумма вклада (инвестиция, которая должна являться отрицательным числом).
Отсортируйте банки по убыванию значений ожидаемой прибыли.
Проведя вычисления для разных значений величины плт, выясните, как эта величина влияет на возможную прибыль.
Сохраните результат работы в книге «FinFunctions.xlsx». Переименуйте книгу «FinFunctions.xlsx» в «Фамилия Имя FinFunctions.xlsx».

Задание 1.4

Отправьте документ на проверку. Для этого:
- перейдите по ссылке «Форма обратной связи»;
- заполните в открывшемся окне необходимые поля:
  - тема обращения — Практическая работа;
  - Фамилия, Имя, Адрес электронной почты;
  - сообщение — Работа выполнена или два знака «+»;
  - прикрепите файл — файл с выполненной практической работой.
Нажмите кнопку «Отправить».
Дождитесь уведомления в зелёной рамке «Спасибо за Ваше сообщение. Оно успешно отправлено»;
Сообщите учителю о завершении работы.