Array Function: Using an array function to calculate loan outstanding

**akshaythakker** · 08-30-2016, 01:57 PM

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, 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 of course 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.

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

Thanks

Akshay