2

I am preparing a cashflow statement where I take a loan every month - all at the same interest rate and duration. Thus, I need to forecast EMI, interest, and principal for each month from all the loans taken so far.

In reality, I figured out PMT. I calculated PMT for $1 and multiplied that by the sum of all principals that are active (using OFFSET formula to determine whether a period is within loan duration). Something like...

=SUM(OFFSET(AMOUNT_HEADER, MAX(1, Curr_Period-(DURATION*12)+1), 0, MIN(Curr_Period, DURATION*12), 1))*-PMT(Interest/12,DURATION*12,1)

Now, while I was able to crack EMI, mainly because it is "equal" every month, I am unable to figure out how to split the principal and interest.

Can anyone suggest something that is accurate or an approximation of Principal and Interest each month?

Shahid Thaika
  • 2,133
  • 5
  • 23
  • 59

1 Answers1

0

Check out the Excel functions PPMT and IPMT which calculate the principle and interest, respectively, for any period over the duration of the loan. To get the sum of interest payments for any period you can use the ARRAY formula (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))

This formula assumes that their is a column of loan amounts with the column header AMOUNT_HEADER. The first loan is assumed to occur at Period=0. Curr_Period is assumed to be 1, 2, 3, ... The ROW(...)-ROW(...) portion of the formula is used to generate a series of ages for each loan, so if Curr_Period = 5, this part of the formula generates {5;4;3;2;1}. These ages are used as the per argument in IPMT.

Note: This formula doesn't guard against exceeding Curr_Period exceding the duration of any of the loans, but could be modified to do so. It could also be modified to sum up principle payments instead of interest

xidgel
  • 3,085
  • 2
  • 13
  • 22
  • Those functions work only against one principal amount. I have multiple - one per month, and need a cumulative sort of formula. – Shahid Thaika Jun 25 '15 at 17:13