Complex Array Loops Question...

K

kethee

New Member
Joined
Jun 25, 2010
Messages
2
I'm really hoping someone can help me...there doesn't seem to be an answer posted already on the internet and I wanted to consult the experts before giving up.

I have created a formula that acheives my desired result, however, because it is so complex, it causes my data tables to take 30 minutes to run (vs 1 minute with out the formula).

Im hoping someone can optimize my formula (so its not an array) or help me with a UDF that can run faster.

THE FORMULA:
=-SUMPRODUCT($B$3:B3,SUBTOTAL(9,OFFSET($C$3:C3,LARGE(ROW($C$3:C3)-ROW($C$3),ROW(INDIRECT("1:"&ROWS($C$3:C3)))),0,1)))

BACKGROUND :
Im trying to figure out what my net cashflow is each month where my outflows would be new loans I make each month (column C) and inflows are what I am getting back (column D) each month. However, what Im getting paid back is based on a recovery curve (column B), so each period I will get back X% of what I loaned out in prior months, and it will take 3 months for me to fully recover what I loaned out in period 0.

So for example below, I make a loan in month 1 for $100, in month 2, I will get 20% of it back, in month 3, I will get 50%, etc.

D5 is comprised of C4*B4 + C5*B3
D6 is comprised of C4*B5 + C5*B4 + C6*B3
etc

the formula above works as an array pasted in D3 (and pasted down)


<TABLE style="WIDTH: 354pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=472 border=0><COLGROUP><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3328" width=104><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 54pt" span=2 width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 54pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=72 height=20>
​
</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>A
​
</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 78pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=104>B
​
</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>C
​
</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>D
​
</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>E
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1
​
</TD><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">sum >>>>
​
</TD><TD class=xl67 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">100%
​
</TD><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">-800
​
</TD><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">800
​
</TD><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Month
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Recovery Curve
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Loan Out
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Payback
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Net CF
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>3
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">1
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">20%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(100)
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(100)
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>5
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">2
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">50%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(200)
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">20
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(180)
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>6
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">3
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">30%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(300)
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">90
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(210)
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>7
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">4
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(200)
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">190
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">(10)
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>8
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">5
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">250
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">250
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>9
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">190
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">190
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>10
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">7
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">60
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">60
​
</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>11
​
</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">8
​
</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0%
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0
​
</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">0

</TD></TR></TBODY></TABLE>
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
hi kethee

can you explain your calculation a bit more for me please...

I agree your formula is very complex for what are trying to achieve so i have tried to recreate it based on the comments you added.

You have said that in Month +1 from the loan you get 20%, Month +2 you get 30%, and Month 3 you receive final payment of 50%....please bear with me!!

If i think about the first and second loan and the repayment i see the transactions as follows

P1 - Loan 1 = (100)
P1 - Total Repayment = 0

P2 - Loan 2 = (200)
P2 - Repayment 1 - Loan 1 (20%) = 20
P3 - Total Repayment = 20

P3 - Loan 3 = (300)
P3 - Repayment 2 - Loan 1 (30%) = 30
P3 - Repayment 1 - Loan 2 (20%) = 40
P3 - Total Repayment = 70

P4 - Loan 4 = (200)
P4 - Repayment 3 - Loan 1 (50%) = 50 (Loan 1 is now paid back)
P4 - Repayment 2 - Loan 2 (30%) = 60
P4 - Repayment 1 - L0an 3 (20%) = 60
P4 - Total Repayment = 170

And so on until period 8 where there are no further cashflows in or out....i wont type them all out!!

The obvious problem is that you said that you were happy with your results, but although we match at total my numbers do not match yours by period, and i am not sure what logic i have missed in your process? :confused:

My formula in C4 (i have moved the repayment curve into the formula) and all cells below) for my steps above was

=-IF(A4<2,0,IF(A4=2,(B3*0.2),IF(A4=3,(B3*0.2)+(B2*0.3),(B3*0.2)+(B2*0.3)+(B1*0.5))))


columns F/G/H just show the 20%/30%/50% splits for each period, and are not involved with the formula

if you can help me with the reason why they're different hopefully i'll be able to amend this formula!

:)


<table style="border-collapse: collapse; width: 424pt;" border="0" cellpadding="0" cellspacing="0" width="565"><col style="width: 16pt;" width="21"> <col style="width: 51pt;" span="8" width="68"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl73" style="height: 15pt; width: 16pt;" height="20" width="21">
</td> <td class="xl72" style="width: 51pt;" width="68">A</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">B</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">C</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">D</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">E</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">F</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">G</td> <td class="xl72" style="border-left: medium none; width: 51pt;" width="68">H</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">1</td> <td>
</td> <td align="right">-800</td> <td align="right">800</td> <td align="right">0</td> <td>
</td> <td class="xl65" align="right">20%</td> <td class="xl65" align="right">30%</td> <td class="xl65" align="right">50%</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">2</td> <td>Period</td> <td>Loan</td> <td>Payback</td> <td>Net CF</td> <td>
</td> <td>P1</td> <td>P2</td> <td>P3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">3</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">4</td> <td align="right">1</td> <td align="right">-100</td> <td align="right">0</td> <td align="right">-100</td> <td>
</td> <td class="xl66" align="right">-20</td> <td class="xl67" align="right">-30</td> <td class="xl68" align="right">-50</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">5</td> <td align="right">2</td> <td align="right">-200</td> <td class="xl66" align="right">20</td> <td align="right">-180</td> <td>
</td> <td class="xl67" align="right">-40</td> <td class="xl68" align="right">-60</td> <td class="xl69" align="right">-100</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">6</td> <td align="right">3</td> <td align="right">-300</td> <td class="xl67" align="right">70</td> <td align="right">-230</td> <td>
</td> <td class="xl68" align="right">-60</td> <td class="xl69" align="right">-90</td> <td class="xl70" align="right">-150</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">7</td> <td align="right">4</td> <td align="right">-200</td> <td class="xl68" align="right">170</td> <td align="right">-30</td> <td>
</td> <td class="xl69" align="right">-40</td> <td class="xl70" align="right">-60</td> <td class="xl71" align="right">-100</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">8</td> <td align="right">5</td> <td align="right">0</td> <td class="xl69" align="right">230</td> <td align="right">230</td> <td>
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">9</td> <td align="right">6</td> <td align="right">0</td> <td class="xl70" align="right">210</td> <td align="right">210</td> <td>
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">10</td> <td align="right">7</td> <td align="right">0</td> <td class="xl71" align="right">100</td> <td align="right">100</td> <td>
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt; border-top: medium none;" align="right" height="20">11</td> <td align="right">8</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td>
</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> </tbody></table>
<table style="border-collapse: collapse; width: 408pt;" border="0" cellpadding="0" cellspacing="0" width="544"><tbody><tr style="height: 15pt;" height="20"><td style="height: 15pt; width: 51pt;" height="20" width="68">
</td><td style="width: 51pt;" align="right" width="68">
</td><td style="width: 51pt;" align="right" width="68">
</td><td style="width: 51pt;" align="right" width="68">
</td><td style="width: 51pt;" width="68">
</td><td class="xl63" style="width: 51pt;" align="right" width="68">
</td><td class="xl63" style="width: 51pt;" align="right" width="68">
</td><td class="xl63" style="width: 51pt;" align="right" width="68">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" height="20">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td class="xl64" align="right">
</td><td class="xl65" align="right">
</td><td class="xl66" align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td class="xl64" align="right">
</td><td align="right">
</td><td>
</td><td class="xl65" align="right">
</td><td class="xl66" align="right">
</td><td class="xl67" align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td class="xl65" align="right">
</td><td align="right">
</td><td>
</td><td class="xl66" align="right">
</td><td class="xl67" align="right">
</td><td class="xl68" align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td class="xl66" align="right">
</td><td align="right">
</td><td>
</td><td class="xl67" align="right">
</td><td class="xl68" align="right">
</td><td class="xl69" align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td class="xl67" align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td class="xl68" align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td class="xl69" align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td></tr><tr style="height: 15pt;" height="20"><td style="height: 15pt;" align="right" height="20">
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td><td>
</td><td align="right">
</td><td align="right">
</td><td align="right">
</td> </tr> </tbody></table>
 
Upvote 0
Hi keewhan,

Thank you for taking the time to help me. Your transactions per period below are correct (where you break out each transaction), but it looks like youre using a different logic when you bring it into the equation.

I think the disconnect in the formula is that it assumes all the loans are made at once, and each loan follows the curve at the same time. There are different loans being made in various periods, and its repayment will follow that curve in the periods subsequent to when its made.

Just as an fyi, these numbers are a very simplified version. The actual project I am working on is loand being made over hundreds of period and the recovery period of each loan is really over hundreds of periods as well, so the limitation of nested if's in excel will inhibit the approach you are contemplating.

Thank you again for youre help :)

-Kathie
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
817
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
583
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
345
esben
E

Forum statistics

Threads
1,214,644
Messages
6,120,709
Members
448,983
Latest member
Joaquim_Baptista

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top