Как посчитать тарифные ставки в excel

Практическая работа

«Использование логических функций»

Задание № 1

Работа с функциями Год и Сегодня

Ячейки, в которых
выполнена заливка серым цветом, должны содержать формулы!

1.     
Создать и отформатировать таблицу
по образцу (Фамилии ввести из списка)

2.     
Заполнить столбец «Дата приема на
работу» значениями типа дата.

3.     
Вычислить стаж работы сотрудников
фирмы по формуле:

=ГОД(СЕГОДНЯ()-Дата приема
на работу)-1900

(Полученный результат может не совпадать со
значениями в задании. Почему?)

4.     
Переименовать Лист1 в Сведения
о стаже сотрудников

Задание № 2

Работа с функцией ЕСЛИ

Тарифные ставки сотрудников фирмы «Рога и
копыта»

1. Скопировать таблицу из
задания № 1 на Лист2 и переименовать его в Тарифные ставки

2. Изменить заголовок таблицы

3. Добавить столбец Тарифные
ставки и вычислить их таким образом:

1-
если стаж меньше 5 лет, 2- если стаж больше или равен 5 лет

Задание № 3

Работа с вложенными функциями ЕСЛИ

Заработная плата сотрудников фирмы «Рога и
копыта»

1.
Скопировать таблицу из задания № 2 на Лист3 и переименовать его в Налоги.

2.
Изменить заголовок таблицы.

3.
Добавить столбцы Ставка, Начислено, Налог, Заработная
плата

и
заполнить их таким образом:

Ставка
= произвольное число от 500 до … (на ваше
усмотрение!!)

Начислено
= Ставка * Тарифные ставки

Налог
= 0, если Начислено меньше 1000,
12%, если Начислено больше 1000, но меньше 3000, и 20%,
если Начислено больше или равно 3000

Заработная
плата = Начислено – Налог * Начислено/100

Итог
выполнения работы

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

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

Примеры финансовых расчетов по функции СТАВКА в Excel

Пример 1. В МФО был взят кредит сроком на 16 дней, сумма которого составляет 1000 долларов. Сумма возврата составляет 1250 долларов. Определить годовую процентную ставку по указанным условиям займа.

Таблица данных:

Для расчета в ячейку B7 введем следующую формулу:

=СТАВКА(B4;0;B5;B6;0;0,1)*B3/B2

Описание аргументов:

• B4 – число периодов выплат (в данном случае – 1);
• 0 – размер фиксированной выплаты (поскольку в данном примере только один период выплат, указано значение 0);
• B5 – тело кредита;
• B6 – сумма на момент погашения долга;
• 0 – характеризует тип выплат, при котором выплата производится в конце периода;
• 0,1 – предполагаемое значение процентной ставки (любое число из диапазона от 0 до 1);
• B3/2 – коэффициент для пересчета полученного значения ставки к годовой процентной ставке.

Результат вычислений:

Пи данных условиях микрокредитования сроком займа на 16 дней процентная ставка составляет 570,31% годовых! Несмотря на это, услуги по микрокредитованию сегодня продолжают набирать популярность.



Анализ пенсионных отчислений с использованием функции СТАВКА в Excel

Пример 2. Определить темпы роста пенсионных отчислений (процентную ставку), если баланс средств на конец года составляет 12000 долларов, а в начале года – 2400 долларов. Еженедельные платежи на протяжении года составляли 150 долларов (то есть, количество периодов – 52).

Исходные данные:

Формула для расчета:

=СТАВКА(B2;B3;B4;B5;0;0,1)*52

Описание аргументов:

• B2 – количество периодов выплат;
• B3 – сумма платежа (расходная операция, поэтому отрицательное значение);
• B4 – сумма средств до наступления первого периода выплат;
• B5 – сумма по окончанию последнего периода выплат;
• 0 – выплаты в конце периода;
• 0,1 – произвольное значение из интервала от 0 до 1;
• 52 – количество периода выплат для пересчета размера ставки в годовых.

Результат вычислений:

То есть, пенсионные отчисления выполняются под 7% годовых.

Определение реальной процентной ставки по кредиту

Пример 3. Ноутбук одной и той же модели можно приобрести за 1200 долларов в рассрочку (беспроцентную, судя по рекламе в первом магазине) или за 1050 долларов в другом магазине. Рассрочка выдается на 1 год с 12 периодами выплат. Определить реальный процент «беспроцентной» рассрочки платежей по кредиту.

Исходные данные:

Формула для расчета:

=СТАВКА(B2;-B3/B2;B4;0;0;0,01)*B2

Описание аргументов:

• B2 – число периодов выплат;
• -B3/B2 – выражение для расчета размера ежемесячного платежа;
• B4 – реальная стоимость ноутбука (используется как начальная стоимость финансового инструмента, цена которого повысится до 1200 к окончанию последнего периода выплат);
• 0 – остаток по окончанию последнего периода выплат;
• 0 — выплаты в конце периода;
• 0,01 — произвольное значение предполагаемой ставки.

Результат расчетов:

То есть, фактически в первом магазине клиенту предложили кредит на ноутбук под 25,4% годовых.

Функция СТАВКА в Excel и особенности ее использования

Функция СТАВКА имеет следующий синтаксис:

= СТАВКА(кпер; плт; пс; [бс]; [тип]; [прогноз])

Описание аргументов:

• кпер – обязательный аргумент, характеризующий число периодов выплат по аннуитетной схеме.
• плт – обязательный аргумент, характеризующий фиксированное значение выплаты, производимой в каждый из периодов выплат. Сумма выплаты за каждый период включает две составляющие: тело и проценты без учета прочих комиссий и сборов. Если данный аргумент опущен, следующий аргумент должен быть указан явно.
• пс – обязательный аргумент, характеризующий текущую стоимость задолженности (либо вознаграждения), эквивалентную общей сумме последующих платежей на данный момент. Если значение неизвестно, необходимо явно указать значение 0 (нуль).
• [бс] – необязательный аргумент, характеризующий размер желаемого остатка средств после выполнения последней выплаты согласно графика платежей. Если явно не указан, по умолчанию используется значение 0 (нуль), а аргумент пс становится обязательным для заполнения.
• [прогноз] – необязательный аргумент, характеризующий предполагаемый размер процентной ставки. Если аргумент явно не указан, по умолчанию принимается значение 10%. Если полученное в результате вычислений значение не сходится с указанной прогнозной величиной, величину данного аргумента следует изменить. Рекомендуется выбирать значение для аргумента [прогноз] из диапазона от 0 до 1.
• [тип] – необязательный аргумент, принимающий значения 0 или 1:

1. Если введен 0, считается, что выплата производится в конце периода;
2. Если введен 1, считается, что выплата производится в начале периода.

Примечания:

1. Единицы измерения величин, указанных в качестве аргументов кпер и [прогноз], должны соответствовать друг другу. Например, при расчете ставки по займу, выданному на два года под 16% с ежемесячными выплатами необходимо, в качестве аргумента [прогноз] необходимо использовать значение 16%/12, а кпер – 2*12.
2. Если хотя бы в качестве одного из аргументов функции было передано текстовое значение, результатом выполнения функции будет являться код ошибки #ЗНАЧ!.
3. Аннуитетная схема выплаты вознаграждения либо погашения задолженности предполагает выплаты фиксированной суммы, включающей вознаграждение или тело кредита и дополнительных процентов (в зависимости от установленной процентной ставки) на протяжении установленного количества периодов выплат. В отличие от классической схемы, при которой проценты начисляются на остаток вознаграждения или задолженности, в аннуитетной схеме соотношение тело кредита/проценты является изменяющейся величиной.
4. При выполнении расчетов функция СТАВКА использует итерационный метод. Если после 20 итераций последующие результаты вычислений отличаются друг от друга более, чем на 10-7, результатом вычислений будет являться код ошибки #ЗНАЧ!.

Лист содержит перечень разрядов и
соответствующих им тарифных коэффициентов
и ставок. Ставка 1 разряда может
периодически изменяться.

Создайте следующую таблицу на листе,
которому дайте имя Тарифные ставки.

Ставка отдельного разряда рассчитывается
по формуле Коэффициент* МРОТ.

Для удобства дальнейшей работы следует
создать именованные блоки ячеек Разряд,
Коэффициент, Ставка. (См. ниже).

Создание именованных блоков ячеек.

Блок — прямоугольная область ячеек,
расположенных на одном и том же листе
или различных листах рабочей книги.

Блок ячеек одного листа может включать
не обязательно смежные ячейки. Для
выделения нескольких областей ячеек
листа одного блока используется
клавиша<Ctrl>.

1 Способ создания именованного блока ячеек одного листа:

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

• Выполнить команду меню Вставка/Имя/Присвоить;

• Ввести имя создаваемого блока ячеек в
  строке Имя (автоматически вводится
  имя, соответствующее содержимому
  ячейки, расположенной над выделенным
  блоком —Разряд), в строкеФормулавыводится ссылка на диапазон выделенных
  ячеек. Нажать кнопкуОК.

2 Способ создания именованного блока ячеек одного листа:

• Закрасить диапазон ячеек, содержащий,
  например, номера рабочих разрядов, а
  также название столбца;

• Выполнить команду меню Вставка/Имя/Создать;

• Указать, что имя блока находится в
  строке выше;

• Нажать кнопку ОК.

Для удаления неправильно определенного
блока следует:

• Выполнить команду Вставка/Имя/Присвоить;

• Выбрать из списка имя удаляемого блока;

• Нажать кнопку Удалить.

Лист Тарифные ставкиследует защитить
от изменений.

При обеспечения защиты данных следует:

1. Обеспечить возможность внесения новой
   величины МРОТ в условиях защищенного
   рабочего листа. Для этого установить
   курсор на ячейку, содержащую МРОТ,
   выполнить команду Формат/Ячейки
   вкладка Защита и снять
   защиту с ячейки, которая установлена
   по умолчанию.

2. Скрыть от просмотра на защищенном листе
   формулы. Для этого выделить блок Ставка
   (например, с помощью командыПравка/Перейти)
   и вызвав командуФормат/Ячейки/Защита,
   установить флажкиЗащищаемая
   ячейка иСкрыть формулы.

3. Защитить лист от изменений путем
   выполнения команды Сервис/Защита/Защитить
   лист (можно ввести пароль).

Лист Должностные оклады

Данный лист содержит сведения о
должностных окладах работающих:

При заполнении таблицы предусмотреть:

1. Выбор должностного разряда из
   ограниченного списка значений. Для
   этого следует выделить весь диапазон
   ячеек в колонке Должн.разряд,выполнить командуДанные/Проверка
   и указать на вкладкеПараметрытип данных —Список, Источник —
   именованный блокРазряд (список
   именованных блоков вызывается нажатием
   клавишиF3).

2. Величина должностного оклададля
   первой должности выбирается из
   справочникаТарифные ставкис
   помощью формулы:

=ПРОСМОТР(Х№; Разряд; Ставка)

где Х№ — адрес ячейки, содержащей
тарифный разряд работающего.

Данная формула копируется в остальные
ячейки столбца.

Соседние файлы в папке Лекции. Все темы!

• #
• #
• #
• #
• #
• #
• #

• Мне нравится