Финансовые расчеты средствами Excel

Базовые понятия

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

Процент – абсолютная величина дохода от предоставления денег в долг.

Процентная ставка – относительная величина дохода, начисляемая за определенный период, измеряемая в процентах или долях.

Период начисления – интервал времени, за который начисляются проценты по депозитам или по ссуде (например, квартал или месяц).

Срок – промежуток времени, на который вкладываются или берутся в кредит деньги.

Будущая стоимость – сумма приведенной стоимости и начисленных по ней процентов.

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

Функции для финансовых расчетов

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

Ставка ‑ процентная ставка за один период. Если она выражена в процентах за год, то эту величину надо разделить на количество периодов в году.

Кпер ‑ общее количество периодов (выплат). Аргументы Ставка и Кпер должны быть согласованы.

Период – номер периода, для которого производятся расчеты. Этот аргумент должен быть меньше, либо равен значению Кпер.

Плт – платеж (взнос).

Пс ‑ приведенная стоимость.

Бс ‑ будущая стоимость или остаток средств после последней выплаты. Если аргумент опущен, он полагается равным.

Тип – аргумент, указывающий, когдадолжна производиться выплата: равен 0, если выплата производится в конце периода, и 1 ‑ если в начале. По умолчанию равен 0.

Расчет параметров ссуды

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

ПЛТ(Ставка; Кпер; Пс; Бс; Тип) ‑ рассчитывает сумму периодического платежа по заданной ставке, количеству периодов и размеру ссуды.

Пример: ежемесячные платежи по ссуде размером 50000 р. сроком на 4 года при ставке 6% годовых составляют

ПЛТ(0,06/12; 4*12; -50000)» 1174,25 р.

Здесь Пс<0, т.к. деньги были взяты в долг, Бс=0, т.к. будущая стоимость ссуды после ее погашения равна 0.

ПС(Ставка; Кпер; Плт; Бс; Тип) ‑ вычисляет приведенную стоимость ссуды по заданной ставке, количеству периодов и размеру платежа.

Пример: сумма ссуды, взятой на 4 года под 6% годовых и ежемесячном платеже в размере 1174,25 р. Составляет

ПС(0,06/12; 4*12; -1174,25)» 49999,94 р.

Небольшая погрешность (6 к.) получается из-за округления размера платежа с точностью до 1 к.

СТАВКА(Кпер; Плт; Пс; Бс; Тип) ‑ вычисляет процентную ставку за один период по заданному количеству периодов (платежей), размеру платежа и размеру ссуды.

Пример: месячная процентная ставка для ссуды размером 50000 р., взятой на 4 года, при ежемесячном платеже 1174,25 р. Составляет

СТАВКА(4*12; 1174,25; -50000) = 0,5%.

Годовая ставка равна 0,5%×12=6%.

КПЕР(Ставка; Плт; Пс; Бс; Тип) ‑ вычисляет общее количество периодов (платежей) для данной ссуды, при заданном размере платежа и заданной процентной ставке.

Пример: количество ежемесячных платежей по ссуде размером 50000 р. при ставке 6% годовых и платежу в размере 1174,25 р. равно

КПЕР(0,06/12; 1174,25; -50000)» 48 мес. = 4 года.

ОСПЛТ(Ставка; Период; Кпер; Пс; Бс; Тип) ‑ вычисляет величину платежа в погашение основной суммы за заданный период по ссуде заданного размера и заданной ставке.

Пример: основная часть платежа за 1-й месяц по ссуде 50000 р, взятой под 6% годовых на 4 года, составляет

ОСПЛАТ(0,06/12; 1; 4*12; -50000)» 924,25 р.

Основная часть платежа при тех же условиях за последний период равна

ОСПЛАТ(0,06/12; 48; 4*12; -50000)» 1168,41 р.

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

Пример: часть платежа по процентам за 1-й месяц по ссуде размером 50000 р., взятой на 4 года под 6% годовых составляет

ПРПЛТ(0,0612; 1; 4*12; -50000) = 250,00 р.

Размер выплат по процентам за последний месяц составляют

ПРПЛТ(0,0612; 48; 4*12; -50000)» 5,84 р.

График погашения ссуды

График погашения ссуды представляет собой таблицу, содержащую следующую информацию по каждому периоду выплаты: период, сумма выплаты (платеж), накопленная сумма выплат, выплаты по процентам, накопленная сумма выплат по процентам, основная сумма, накопленная основная сумма, остаток.

В таблице используются следующие формулы:

- Период – столбец заполняется целыми числами от 1 до значения Кпер;

- Сумма выплаты – вычисляется с помощью функции ПЛТ (постоянная величина для всех периодов);

- Накопленная сумма выплат – сумма платежей на текущий период: рассчитывается как накопленная сумма выплат на предыдущий период плюс сумма выплаты (платеж) за текущий период (накопленная сумма в 1-й период равна платежу) или как произведение платежа на период;

- Выплаты по процентам – рассчитываются с помощью функции ПРПЛТ для каждого периода;

- Накопленная сумма выплат по процентам ‑ рассчитывается как накопленная сумма выплат по процентам на предыдущий период плюс сумма выплат по процентам за текущий период (накопленная сумма выплат по процентам в 1-й период равна выплате по процентам за 1-й период);

- Основная сумма – вычисляется с помощью функции ОСПЛТ для каждого периода;

- Накопленная основная сумма – рассчитывается как накопленная основная сумма на предыдущий период плюс основная сумма за текущий период (накопленная основная сумма на 1-й период равна основной сумме за 1-й период);

- Остаток – разность между размером ссуды и накопленной основной суммой на текущий период.

Расчет параметров инвестиций

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

Вычисление простого процента

Простые проценты начисляются один раз в конце срока вклада:

Пример: при вкладе размером 10000 р. на 1 год под 5% годовых процент составит

10000 × 0,05 × 1 = 500 (р.).

При вкладе той же суммы при той же ставке на 9 месяцев процент будет равен

10000 × 0,05/12 × 9 = 375 (р.).

Будущая стоимость равна сумме вклада и процента

Вычисление сложного процента

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

Пример: при вкладе размером 10000 р. сроком на 1 год под 5% годовых и ежемесячном начислении процентов начисленный в конце 1-го периода процент составит

10000 × 0,05/12 = 41,67 (р.).

Сумма вклада после 1-го периода равна

10000 + 41,67 = 10041,67 (р.).

Тогда процент, начисленный в конце второго периода, составит

10041,67 × 0,05/12 = 41,84 (р.).

Сумма вклада после 2-го периода равна

10041,67 + 41,84 = 10083,51 (р.), и т.д.

По окончании срока вклада будущая стоимость составит 10511,62 р.

Общая формула будущей стоимости имеет вид

.

Для приведенного примера

.

Будущая стоимость зависит от частоты начисления процентов, т.е. от количества периодов. Проценты могут начисляться ежеквартально (тогда за 1 год Кпер = 4), ежемесячно (Кпер = 12), ежедневно (Кпер = 365). А также проценты могут начисляться постоянно, в этом случае число периодов бесконечно, и формула для определения будущей стоимости принимает вид:

.

При постоянном начислении процентов в приведенном выше примере .

Будущая стоимость последовательности вкладов

В данном случае под последовательностью вкладов понимается вклад с периодическим постоянным пополнением. Такой тип инвестиций называется аннуитетом.

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

Пример: будущая стоимость вклада размером 10000 р., сроком на 1 год под 5% годовых, ежемесячно пополняемого на 1000 р., составит

БС(0,05/12; 12; -1000; -10000) = 22790,47 (р.).

Эту же функцию можно использовать и для одиночных (непополняемых) вкладов, полагая аргумент Плт=0.

Пример: при вкладе размером 10000 р. сроком на 1 год под 5% годовых будущая стоимость составит

БС(0,05/12; 12; 0; -10000) = 10511,62 (р.).

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

Расчет параметров ссуды

1. Рассчитать ежемесячный платеж для погашения ссуды размером 100000 р., взятой на 3 года под 9,5% годовых. Составить график погашения ссуды.

2. Банк выдает ссуды под 15% годовых. На какой срок следует взять ссуду размером 100000 р., чтобы ежемесячный платеж не превышал 5000 р.? Составить график погашения ссуды.

3. Определить годовую процентную ставку, если известно, что ежемесячный платеж для погашения ссуды размером 50000 р., взятой на 2 года, составляет 2544,79 р.

Расчет параметров инвестиций

4. Определить значение будущей стоимости вклада размером 120000 р. сроком на 3 года под 12% годовых, начисляемых ежемесячно. Расчет выполнить двумя способами: 1) с помощью формулы; 2) с помощью встроенной функции.

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

Определить будущую стоимость вклада при постоянном начислении процентов.

5. Начальная сумма вклада сроком на 6 месяцев под 4,6% годовых, начисляемых ежемесячно, составила 40000 р. Вклад ежемесячно пополняется на 2000 р. Рассчитать будущую стоимость вклада в конце срока и составить таблицу, отражающую будущую стоимость вклада на конец каждого периода.

6. В начале каждого месяца на протяжении года на счет вносится 5000 р. Под 4,25% годовых, начисляемых ежемесячно. Определить будущую стоимость депозита. Составить таблицу, отражающую будущую стоимость депозита на начало каждого месяца.

7. Какую сумму необходимо положить на депозит, чтобы через 5 лет получить 200000 р. При ежемесячном пополнении вклада на 1000 р., если годовая ставка составляет 5%, начисляемых ежемесячно?

СПИСОК ЛИТЕРАТУРЫ

1. Уокенбах Джон. Microsoft Office Excel 2007. Библия пользователя.: Пер. с англ. – М.:ООО «И.Д. Вильямс», 2008.

2. Музычкин П. А. Автоматизированные информационные технологии в экономике, РЭА им. Г. В. Плеханова, 2001.

 

практические работы по табличному процессору

excel 2007

 

Составители: АНТОНЕНКОВА Альбина Викторовна,

МУЗЫЧКИН Павел Арсенович.

 

Редактор М. Ю. Ляхович

 

Подписано в печать. Формат 60х84 1/16.

Печать офсетная. Усл. печ. л..

Уч.-изд. л.. Тираж 200 экз. Заказ.

 

ГОУ ВПО «РЭА им. Г. В. Плеханова».

117997, Москва, Стремянный пер., 36.

Напечатано в ГОУ ВПО «РЭА им. Г. В. Плеханова».

117997, Москва, ул. Зацепа, 41/4.