Рассчитайте PMT для нескольких участников в Excel

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

Реально разобрался с PMT. Я рассчитал 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, главным образом потому, что они «равны» каждый месяц, я не могу понять, как разделить основную сумму и проценты.

Может ли кто-нибудь предложить что-то точное или приблизительное значение суммы основного долга и процентов каждый месяц?


excel excel-formula cumulative-sum amortization
person Shahid Thaika    schedule 23.06.2015    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Ознакомьтесь с функциями 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. Предполагается, что первая ссуда возникнет в период Period = 0. Предполагается, что Curr_Period равен 1, 2, 3, ... Часть формулы ROW (...) - ROW (...) используется для генерации серии возрастов для каждой ссуды, поэтому, если Curr_Period = 5, эта часть формулы генерирует {5; 4; 3; 2; 1}. Эти возрасты используются в качестве аргумента per в IPMT.

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

person xidgel    schedule 24.06.2015
comment
Эти функции работают только с одной основной суммой. У меня их несколько - по одному в месяц, и мне нужна кумулятивная формула. - person Shahid Thaika; 25.06.2015