Рассчитать PMT из нескольких принципалов в Excel
Я готовлю отчет о движении денежных средств, в котором я беру кредит каждый месяц - с одинаковой процентной ставкой и продолжительностью. Таким образом, мне нужно прогнозировать EMI, проценты и основную сумму на каждый месяц по всем взятым на данный момент кредитам.
На самом деле я разобрался с ФЭУ. Я рассчитал PMT для 1 доллара США и умножил его на сумму всех действующих субъектов (используя формулу OFFSET, чтобы определить, находится ли период в пределах срока кредита). Что-то вроде...
=SUM(OFFSET(AMOUNT_HEADER, MAX(1, Curr_Period-(DURATION*12)+1), 0, MIN(Curr_Period, DURATION*12), 1))*-PMT(Interest/12,DURATION*12,1)
Теперь, когда мне удалось взломать EMI, главным образом потому, что он "равен" каждый месяц, я не могу понять, как разделить основную сумму и проценты.
Может ли кто-нибудь предложить что-то, что является точным или приближением принципала и интереса каждый месяц?
1 ответ
Проверьте функции Excel PPMT и IPMT, которые рассчитывают принцип и проценты, соответственно, для любого периода в течение срока кредита. Чтобы получить сумму процентных платежей за любой период, вы можете использовать формулу ARRAY (CTRL-ENTER):
=SUM(IPMT(Interest/12,ROW(OFFSET(AMOUNT_HEADER,Curr_Period+1,0,1,1))-ROW(OFFSET(AMOUNT_HEADER,1,0,Curr_Period,1)),DURATION*12,-OFFSET(AMOUNT_HEADER,1,0,Curr_Period,1),0,0))
В этой формуле предполагается, что это столбец суммы кредита с заголовком столбца AMOUNT_HEADER. Предполагается, что первый кредит будет получен в период с периодом 0. Curr_Period принимается равным 1, 2, 3, ... Часть формулы ROW(...)-ROW(...) используется для генерации серии возрастов для каждого кредита, поэтому, если Curr_Period = 5, эта часть формулы генерирует {5;4;3;2;1}. Эти возрасты используются в качестве аргумента в IPMT.
Примечание. Эта формула не защищает от превышения Curr_Period, превышающего продолжительность любого из ссуд, но может быть изменено для этого. Он также может быть изменен, чтобы суммировать основные выплаты вместо процентов