Методическая разработка по теме "Встроенные функции Excel"

Методческая разработка по теме:

«Встроенные функции Excel»

1 задание. Найти величину амортизации актива за один период для различных фирм (линейным методом):

Название фирмы	Заря	Тренд	МММ	Канцтовары	Луч
затраты на приобретение актива.	30000	20000	100000	50000	50000
стоимость в конце периода амортизации	7500	5000	90000	41000	35000
период амортизации	10	5	2	5	5
Амортизационные отчисления для каждого года	?	?	?	?	?

Теоретические сведения:

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

Синтаксис функции

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

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

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

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

Технология выполнения работы:

1. На листе 1 заполните таблицу фирм (см. выше) данными
2. Переименуйте лист 1 → АПЛ
3. Внесите формулу в строку Амортизационные отчисления для каждого года для фирмы Заря

           Выделите ячейку

           Наберите с клавиатуры =

           В строке формул выберите функцию АПЛ

           Мышкой укажите требуемые значения в окно Аргументы функции

           Нажмите ОК

4. Скопируйте формулу на остальные фирмы

Примерный результат работы:

2 задание. Найти величину амортизации актива за данный период для различных фирм
(методом суммы годовых чисел):

Название фирмы	Заря	Тренд	МММ	Канцтовары	Луч
затраты на приобретение актива.	30000	20000	100000	50000	50000
стоимость в конце периода амортизации	7500	5000	90000	41000	35000
период амортизации	10	5	2	5	5
Годовая амортизация за первый год:
Годовая амортизация за период:

Теоретические сведения:

Встроенная функция АСЧ возвращает величину амортизации актива за данный период, рассчитанную методом «суммы (годовых) чисел».

Синтаксис функции

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

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

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

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

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

Заметки АСЧ вычисляется следующим способом:

Технология выполнения работы:

1. На листе 2 заполните таблицу фирм (см. выше) данными
2. Переименуйте лист 2 → АСЧ
3. Внесите формулу в строку Годовая амортизация за первый год: для фирмы Заря

            Выделите ячейку

            Наберите с клавиатуры =

            В строке формул выберите функцию АСЧ

            Мышкой укажите требуемые значения в окно Аргументы функции (внимание! В опции период укажите значение 1)

            Нажмите ОК

4. Скопируйте формулу на остальные фирмы
5. Внесите формулу в строку Годовая амортизация за период: для фирмы Заря

            Выделите ячейку

            Наберите с клавиатуры =

            В строке формул выберите функцию АСЧ

            Мышкой укажите требуемые значения в окно Аргументы функции  (внимание! В опции период укажите значение период амортизации)

            Нажмите ОК

6. Скопируйте формулу на остальные фирмы

Примерный результат работы:

3 задание. Найти будущую стоимость вклада на различных условиях:

Годовая процентная ставка	14,25%	6,00%	12%	16%	11%
Количество платежей	240м	20л	3г	60м	35м
Объем платежей	-11000	-132000	-1600	-4500	-2000
Стоимость на текущий момент
Платежи осуществляются в начале (конце) месяца	в конце периода	в начале периода	в конце периода	в начале периода	в начале периода
Будущая стоимость инвестиции на приведенных условиях

Теоретические сведения:

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

Синтаксис функции

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

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

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

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

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

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

0 – в конце периода              1 – в начале периода

Заметки

·                    Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента ставка и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента «ставка» и 4 для задания аргумента «кпер».

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

Технология выполнения работы:

1. На листе 3 заполните таблицу (см. выше) данными
2. Переименуйте лист 3 → БС
3. Внесите формулу в строку Будущая стоимость инвестиции на приведенных условиях для первых условий

            Выделите ячейку

            Наберите с клавиатуры =

            В строке формул выберите функцию БС

            Мышкой укажите требуемые значения в окно Аргументы функции (будьте внимательнее в опциях ставка и количество платежей)

            Нажмите ОК

4. Скопируйте формулу на остальные условия

Примерный результат работы:

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

Деньги, уплачиваемые по страховке в конце каждого месяца	500	650	100
Процентная ставка, которую приносят выплачиваемые деньги	8%	11%	10%
Число лет, по истечении которых деньги будут выплачены	20	10	5
Приведенная стоимость аннуитета с указанными выше условиями

Теоретические сведения:

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

Синтаксис функции

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

Ставка — процентная ставка за период. Например, если получена ссуда на автомобиль под 10 процентов годовых и делаются ежемесячные выплаты, то процентная ставка за месяц составит 10%/12 или 0,83%. В качестве значения аргумента ставка нужно ввести в формулу 10%/12 или 0,83% или 0,0083.

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

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

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

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

0 или опущен – в конце периода                   1 – в начале периода

Замечания

·                    Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов ставка и кпер. Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента ставка и 4*12 для задания аргумента кпер. Если Вы делаете ежегодные платежи по тому же займу, то используйте 12% для задания аргумента ставка и 4 для задания аргумента кпер.

·                    Следующие функции используются при расчете аннуитетов:

ОБЩПЛАТ	ПРПЛТ	ОСПЛТ
ОБЩДОХОД	ПЛТ	ПС
БС	ЧИСТНЗ	СТАВКА
БЗРАСПИС	ЧИСТВНДОХ

·                   
Аннуитет — это ряд постоянных денежных выплат, делаемых в течение длительного периода. Например, заем под автомобиль или заклад являются аннуитетами.

·                    В функциях, связанных с аннуитетами, выплачиваемые денежные средства, такие как депозит на сбережения, представляются отрицательным числом; полученные денежные средства, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом -1000 — для вкладчика и аргументом 1000 — для банка.

Технология выполнения работы:

1. На листе 4 заполните таблицу (см. выше) данными
2. Переименуйте лист 4 → ПС
3. Внесите формулу в строку Приведенная стоимость аннуитета с указанными выше условиями для первой суммы страховки

            Выделите ячейку

            Наберите с клавиатуры =

            В строке формул выберите функцию ПС

            Мышкой укажите требуемые значения в окно Аргументы функции (будьте внимательнее в опциях ставка и кпер)

            Нажмите ОК

4. Скопируйте формулу на остальные страховки

Примерный результат работы:

Результат получается отрицательный, поскольку он представляет деньги, которые необходимо выплатить, исходящий денежный поток. Если бы за аннуитет требовалось заплатить 60 000, эта инвестиция была бы не выгодной, так как приведенная стоимость (59 777,15) аннуитета меньше данной суммы.

5 задание.

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

б) определите необходимые суммы месячных вкладов для получения требуемой суммы в различных банках

кредит
	ИКСО	Сбербанк	ВТБ	WDB
Годовая процентная ставка	14,25%	12%	15%	14,50%
количество месяцев платежей	240	240	240	240
сумма кредита	900000	900000	900000	900000
Месячная сумма платежа по указанному кредиту
общая сумма, выплачиваемая на протяжении интервала выплат
вклады
	ИКСО	Сбербанк	ВТБ	WDB
Годовая процентная ставка	6%	13%	16%	15%
Предполагаемое число лет хранения сбережений	18	10	5	7
Требуемое количество сбережений через период	50000	50000	50000	50000
Необходимая сумма месячного платежа для получения 50 000 в конце периода

Теоретические сведения:

Встроенная функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки.

Синтаксис функции

ПЛТ(ставка;кпер;пс;бс;тип)

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

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

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

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

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

Заметки

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

·                    Убедитесь, что вы последовательны в выборе единиц измерения для задания аргументов «ставка» и «кпер». Если вы делаете ежемесячные выплаты по четырехгодичному займу из расчета 12 процентов годовых, то используйте 12%/12 для задания аргумента «ставка» и 4*12 для задания аргумента «кпер». Если вы делаете ежегодные платежи по тому же займу, то используйте 12 процентов для задания аргумента «ставка» и 4 для задания аргумента «кпер».

Технология выполнения работы:

1. На листе 5 заполните таблицу по образцу (см. выше) данными сначала для кредита, затем для вкладов
2. Переименуйте лист 5 → ПЛТ
3. Внесите формулу в строку Месячная сумма платежа по указанному кредиту для первого банка

            Выделите ячейку

            Наберите с клавиатуры =

            В строке формул выберите функцию ПЛТ

            Мышкой укажите требуемые значения в окно Аргументы функции (будьте внимательнее в опциях ставка и кпер)

            Нажмите ОК

4. Скопируйте формулу на остальные банки
5. Внесите формулу в строку общая сумма, выплачиваемая на протяжении интервала выплат для первого банка

5.1.  Выделите ячейку

5.2.  Наберите с клавиатуры =

5.3.  Мышкой перемножьте ячейки количество месяцев платежей и месячная сумма платежа по кредиту)

5.4.  Нажмите ОК

6.       Скопируйте формулу на остальные банки

7.       Внесите формулу в строку Необходимая сумма месячного платежа для получения 50 000 в конце периода для первого банка

7.1.  Выделите ячейку

7.2.  Наберите с клавиатуры =

7.3.  В строке формул выберите функцию ПЛТ

7.4.  Мышкой укажите требуемые значения в окно Аргументы функции (будьте внимательнее в опциях ставка и кпер)

7.5.  Пропустите значений опций Пс, заполнять следует Бс!

7.6.  Нажмите ОК

8.       Скопируйте формулу на остальные банки

Примерный результат работы: