ОБЩДОХОД для расчета общей суммы погашение инвестиций в Excel

Функция ОБЩДОХОД в Excel предназначена для расчета общей суммы платежей, идущих на погашение основной стоимости инвестиции, за указанное число периодов платежей и возвращает соответствующее числовое значение.

Получая кредит в банке, клиент соглашается выплачивать как тело кредита (фактическую сумму, которую он получил), так и проценты за использование. При использовании аннуитетной схемы платежей, каждый такой платеж состоит из двух частей: средства на оплату тела кредита и на оплату начисленных процентов. Функция ОБЩДОХОД позволяет рассчитать часть средств, потраченных на оплату тела кредита за определенный промежуток времени, исчисляемый количеством прошедших периодов платежей.

Расчет общей суммы платежей для погашения инвестиции в Excel

Рассматриваемая функция имеет взаимосвязь с функцией ОСПЛТ. Фактически, ОБЩДОХОД возвращает сумму значений массива возвращаемых ОСПЛТ чисел, рассчитанных для каждого периода выплат. Например, в банке был выдан кредит на сумму 1000 рублей под 10% на 12 месяцев. Для определения части средств четвертого платежа, идущих на оплату тела кредита, можно использовать функцию =ОСПЛТ(0,1;4;12;-1000). Если нужно вычислить всю сумму средств за 4 периода выплат, потраченных на оплату тела кредита, можно использовать формулу массива =СУММ(ОСПЛТ(0,1;{1;2;3;4};12;-1000)). С помощью рассматриваемой функции расчеты упрощаются: =ОБЩДОХОД(0,1;12;1000;1;4;0).

Пример 1. Банк выдал кредит под 17% годовых на 12 месяцев (сумма – 12000 рублей). Определить, на сколько больше денег заплатит клиент в период с 6-го по 10-й месяц, чем в период со 2-го по 6-й месяц.

Вид таблицы данных:

Пример 1.

Для решения запишем формулу:

Вычисляем разницу результатов, возвращаемых ОБЩДОХОД для различных периодов выплат по кредиту и с помощью функции ABS получаем значение по модулю:

ОБЩДОХОД.

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



Расчет досрочного погашения кредита в Excel

Пример 2. Клиенту выдан кредит на сумму 25000 рублей под 15% годовых на 10 месяцев. Какую часть от общей суммы он выплатит за первые 6 месяцев?

Вид таблицы данных:

Пример 2.

Общая сумма состоит из процентов и тела кредита. Для определения процентной части можно использовать функцию ОБЩПЛАТ. Для решения запишем следующую формулу:

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

Расчет досрочного погашения кредита.

То есть, клиент выплатит около 57% от всей суммы задолженности.

Правила использования функции ОБЩДОХОД в Excel

Функция имеет следующую синтаксическую запись:

=ОБЩДОХОД(ставка;кол_пер;нз;нач_период;кон_период;тип)

Описание аргументов (каждый обязателен для заполнения):

  • ставка – принимает числовое значений процентной ставки, приведенной к количеству периодов выплат. Например, если к финансовому продукту применимо значение годовой ставки, а выплаты проводятся ежемесячно, начальную ставку необходимо разделить на число месяцев в году;
  • кол_пер – принимает числовое значение, характеризующее число периодов выплат. Если ожидаются ежемесячные платежи, а срок действия договора указан в годах, необходимо выполнить пересчет - N*12, где N – число лет;
  • нз – принимает числовое значение, характеризующее текущую стоимость инвестиции, например, реальную сумму, которую получил заемщик у кредитора;
  • нач_период – принимает числовое значение, характеризующее номер начального периода, относительно которого выполняются вычисления;
  • кон_период – принимает числовое значение конечного периода для расчетов;
  • тип – принимает два возможных значения: числа 0 (платежи выполняются в конце отчетного периода) и 1 (в начале периода).

Примечания:

  1. Функция возвращает отрицательное значение, поскольку характеризует суммы, идущие на погашение задолженности. Если при указании аргумента пс функции ОСПЛТ в виде отрицательного числа, чтобы получить модуль платежа в счет оплаты тела кредита, ошибки не возникает, то при указании аргумента нз (имеет аналогичный смысл) в виде отрицательного числа функция ОБЩДОХОД возвращает код ошибки #ЧИСЛО!. Чтобы получить положительное значение, следует использовать запись вида =ABS(ОБЩДОХОД(аргументы)).
  2. Если любой из аргументов функций принимает данные, которые не могут быть преобразованы к числовым значениям, функция ОБЩДОХОД вернет код ошибки #ЗНАЧ!.
  3. Рассматриваемая функция возвращает код ошибки #ЧИСЛО! в следующих случаях:
  • a. аргумент ставка взят из диапазона отрицательных чисел или равен 0;
  • b. аргумент кол_пер указан в виде отрицательного числа или нулем;
  • c. аргумент нз – число, которое меньше или равно нулю;
  • d. нач_период > кон_период или оба эти аргумента принимают значения <1.

en ru