Array Function: Using an array function to calculate loan outstanding

[akshaythakker]

Hi Guys:

I am working on a math for a firm (Firm A) that issues loans with different payment terms every month - they basically earn the spread between the borrowing and lending cost. The cost of the funds (Investors) also keeps varying each month. They need to keep track of the total loan outstanding and the interest payable towards their own borrowing cost (cost of fund). I was able to calculate the emi that is due to the firm A and the principal that needs to be paid to the investor based on that emi. However, I need to calculate the cost of fund that Firm A incurs and for that I need to be able to calculate the total principal outstanding so that i can use an array function to calculate the principal outstanding per cost of fund type and find the total cost of fund. I have calculated a month on month principal paid so there is a way to calculate the principal outstanding through that, however for me to take it further and calculate the cost of fund per loan type, I need the principal outstanding to be an array function.

Please see the attached excel sheet (goo.gl/7IX1Ic), where I try to calculate the principal outstanding but for some reason it just returns a zero. I have tried every different permutation and combination but nothing seems to work with the array function. I ofcourse have the option to do a classic 60 (5 year) row X 60 column table, but there are many products and it just becomes cumbersome.

In the attached excel sheet C2toC8 are details of the loan issued by firm A and the cost of fund for the money it borrowed, D2toD8 are details of the loan issued and money borrowed in second month. What I am ultimately trying to do is figure out the outstanding amount due to the investors who have lent to firm A (please dont confuse this with the money that firm A has lent to its borrowers). Basically the value in cell E23 needs to match the value in cell E21. the ultimate goal is ofcourse to figure out an array formula to calculate the values in row 34 without having to do the math for each loan as shown in rows 25 to 32 because it would get really long when the period is expanded beyond two months.

Can someone please look at the array function and help me sort out the math for this.

Thanks

Akshay

 

[Rijnsent]

Hi Akshay,

I've done some digging: the remaining principle of a certain period can also be written as IPMT()/Rate instead of the principle minus all the payment (PPMT). So if you take your formula from row 15 and copy it one row down, change the PPMT for IPMT and divide by the interest percentage and multiply with the funding perctage. For D16 the formula would be (array, you might have to change the ; for , as that's my decimal symbol):

=SUMPRODUCT(IFERROR(IPMT($C$7:D7;D10-$C$2:D2+1;$C$4:D4;-1*$C$3:D3)/$C$7:D7;0);$C$6:D6;--($C$4:D4>=D10-$C$2:D2+1);$C$8:D8)

Cheers,

Koen

 

[akshaythakker]

hi Koen:

This is brilliant! Thanks for providing this elegant solution! Brilliant!

Best regards,
Akshay