Calculation of monthly accumulated interests

[Danny77]

Hello EXCEL-lences!

I submit to your attention the following issue for which I need your invaluable help: for my license thesis I have to do a cash-flow for a new bank, n order to estimate its break-even point.
Each month the bank grants new loans and I need to add interests for loans granted in previous months, but also to take in consideration the repayments amounts. I have done this in a dummy way: I have created payment plans for each month, and then I aggregate the sum related.

I want to ask you if there is a way (means a combination of formula), based on 2 variables (number of new fresh credits and amount per loan - please see Formula sheet), in order to simplify my cash-flow ?

Thank you in advance for any advice/help!

Kind regards

[Pepe Le Mokko]

Thanks for changing the title. much more descriptive

[Danny77]

thanks for warning, hope that the change made it is according with Forum Rule#1

[Danny77]

need help!!! up !

[JeteMc]

After working on the Formula sheet it occurs to me that you could use the work you have already done on the Interest Income Monthly sheet more efficiently.
1. Move the green row for each month to row 12
2. Populate the "Interest Incomes" column using: =SUMIFS(F13:BW13,F$12:BW$12,"Interest")
3. Populate the "Credits Balance" column using: =SUMIFS(F13:BW13,F$12:BW$12,"balance")
Of course you are also welcome to use the formulas on the Formula sheet.
1. For "Cumulated Interest Incomes" use: =IFERROR(-IPMT($B$6/12,B$2-(ROWS(A$1:A1)-1),$B$7,INDEX($B$4:$M$4,ROWS(A$1:A1))*INDEX($B$5:$M$5,ROWS(A$1:A1))),"")
2. For "Cumulated Principal" use: =IFERROR(PPMT($B$6/12,B$2-(ROWS(A$1:A1)-1),$B$7,INDEX($B$4:$M$4,ROWS(A$1:A1))*INDEX($B$5:$M$5,ROWS(A$1:A1))),"")
3. For "Partial Credits Balance" use: =IF(B20="","",INDEX($B$4:$M$4,ROWS(A$1:A1))*INDEX($B$5:$M$5,ROWS(A$1:A1))+SUM($B20:B20))
4. For "Total Credits Balance" use: =SUM(B32:B43)
Let us know if you have any questions.

[Danny77]

JeteMc,
I am speechless. Tks a lot!!!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.