Генерировать ряды выплат процентов в соответствии с ежемесячной / годовой ставкой платежа?

Question

У меня есть таблица, которая состоит из фиксированной основной стоимости, даты начала и окончания, процентной ставки (в процентах), а также "Ежемесячно" или "Ежегодно", в зависимости от согласованной ставки платежа. Эта таблица показывает сумму процентов, подлежащих выплате ежемесячно / ежегодно за определенный период времени по процентной ставке, рассчитанной по основной стоимости.

Что мне нужно сделать, так это сгенерировать x строк, где x - количество произведенных процентных платежей, каждая из которых показывает сумму, уплаченную за этот платеж. Так, если, например, у меня есть 2% -ая процентная ставка на сумму 1000 за год, при использовании ежемесячных платежей получится 12 строк, содержащих по 2% от 1000 каждая.

Любой совет, как подойти к этому?

Answer 1

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

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

Столбец Future Value рассчитывается с использованием FV(rate, nper, pmt, pv, type). Вы указываете процентную ставку, основную сумму и количество периодов. Введите эту формулу в B5 и заполните.

=FV(D$2/12,A5,0,-A$2)

Здесь годовая процентная ставка 2% должна быть разделена на 12, так как мы рассчитываем начисленные проценты каждый месяц. A5:A16 содержит количество периодов оплаты. Там нет периодических платежей в инвестиции, поэтому следующий срок равен 0. И, наконец, принципал вводится как отрицательное число. (Для этих формул существует несколько странных соглашений. Отрицательный знак указывает на первоначальный основной был выплачен в банк или инвестиции).

Теперь легко рассчитать выплату процентов каждый месяц. Вычитая 1000 долларов США (первоначальную основную сумму) из FV первого месяца, мы видим, что она увеличилась на 1,67 доллара США, что является суммой первого процентного платежа. Вычитание FV предыдущего месяца из последующих месяцев приводит к выплате процентов в столбце C.

Но есть также способ рассчитать платежи за один шаг. Для этого используется функция выплаты процентов - IPMT(ставка, per, nper, pv, fv, type). Столбец C рассчитывался по следующей формуле:

=IPMT(D$2/12,A5,F$2,-A$2,FV(D$2/12,F$2,0,-A$2))

Вы можете придумать и по-разному рассчитать процентную ставку в зависимости от того, указан ли процент ежемесячно или ежегодно:

=IPMT(IF(E$2="Yearly",D$2/12,D$2),A5,F$2,-A$2,FV(IF(E$2="Yearly",D$2/12,D$2),F$2,0,-A$2))

И вы можете рассчитать количество периодов от даты начала и окончания, набрав это в F2:

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)+1

Я надеюсь, что это поможет, и я уверен, что вы хотели спросить об инвестициях, а не о кредите. Удачи!