Help Needed to Resolve Sum issue with Positive and Negative Values

[djbush3]

I sure hope there is someone out there that can help me. I have the following simplified scenario in a monthly/quarterly cash flow model:

Z = Investors put in $ monthly which is reflected as negative until project generates positive cashflow (Z41). Rows 39-41 represent 3 months or 1 quarter. Once project generates positive cashflow quarterly, the positive $ goes to repay principal (before any interest, etc.)
AA= tracks investment $ on a monthly basis
AB= Summarizes $ invested by quarter
AC= Summarizes total positive cashflow to payback investor principal
AD = Cashflow back to investor = AB+AC. Sum of all negative/positive values above/below are used to calc IRR.
AB42,AC42,AD42,AB45,AC45,AD45 all represent quarterly totals for the prior 3 months.

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD=AB+AC[/TD]
[/TR]
[TR]
[TD]Row[/TD]
[TD]Cashflow[/TD]
[TD]Investor $[/TD]
[TD]Qtr $ Invested[/TD]
[TD]Principal Payback[/TD]
[TD]Investor Cashflow[/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD]-43000[/TD]
[TD]-43000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]-14000[/TD]
[TD]-14000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]41[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD]-57000[/TD]
[TD]5000[/TD]
[TD]-52000[/TD]
[/TR]
[TR]
[TD]43[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]44[/TD]
[TD]14000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]16000[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]40000[/TD]
[TD]40000[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ttl Distributions[/TD]
[TD]40000[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Ttl Investment[/TD]
[TD]-52000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My proforma is working correctly except for the bottom of AD where I am trying to sum the total amount of distributions to the investor and the total amount of their investment. Total distributions should read = $45,000 (AC42+AC45). Total investment should read = -57,000 (AB42). My problem is on row 42. AD42 is the total of AB42 and AC42. This is correct for the cashflow calculation AD42, but not for my sums in the total distribution (AD47) and total investment(AD48) cells. Can anyone offer any guidance on how to resolve this issue? I need to keep these totals in column AD.

Thank you!

 

[BlueHornet]

This may be more simplistic than you want, but …

	Z	AA	AB	AC	AD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"] Cashflow [/TD]
[TD="bgcolor: #FAFAFA"] Investor $ [/TD]
[TD="bgcolor: #FAFAFA"] Qtr $ Invested [/TD]
[TD="bgcolor: #FAFAFA"] Principal Payback [/TD]
[TD="bgcolor: #FAFAFA"] Investor Cashflow [/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (14,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (14,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (14,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (57,000)[/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 5,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 5,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 5,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (52,000)[/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (52,000)[/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 10,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 10,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 10,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (42,000)[/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 14,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 14,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 14,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (28,000)[/TD]

[TD="align: center"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 16,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ - [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 16,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 16,000 [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (12,000)[/TD]

[TD="align: center"]46[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"] Ttl Distributions [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 45,000 [/TD]

[TD="align: center"]48[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"] Ttl Investment [/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (57,000)[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AA39[/TH]
[TD="align: left"]=IF( Z39 < 0, Z39, 0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AB39[/TH]
[TD="align: left"]=IF( Z39 > 0, Z39, 0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AC39[/TH]
[TD="align: left"]=AA39 + AB39[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD39[/TH]
[TD="align: left"]=AC39[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AA40[/TH]
[TD="align: left"]=IF( Z40 < 0, Z40, 0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AB40[/TH]
[TD="align: left"]=IF( Z40 > 0, Z40, 0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AC40[/TH]
[TD="align: left"]=AA40 + AB40[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD40[/TH]
[TD="align: left"]=AD39 +AC40[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD47[/TH]
[TD="align: left"]=SUM( AB39:AB45)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]AD48[/TH]
[TD="align: left"]=SUM( AA9:AA450)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[BlueHornet]

What this does is essentially show running totals of the (investment) / return per month, then separately add "total investment" and "total return" at the end of the quarter.

 

[ChandraAmgain]

If i understood what you are looking for, Try this:

[TABLE="width: 562"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Cashflow[/TD]
[TD]Investor $[/TD]
[TD]Qtr $ Invested[/TD]
[TD]Principal Payback[/TD]
[TD]Investor Cashflow[/TD]
[/TR]
[TR]
[TD="align: right"]-43000[/TD]
[TD="align: right"]-43000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]-14000[/TD]
[TD="align: right"]-14000[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5000[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-57000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]-52000[/TD]
[/TR]
[TR]
[TD="align: right"]10000[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]14000[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]16000[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]0[/TD]
[TD] 40,000.00[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Ttl Distributions[/TD]
[TD="align: right"]45000[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Ttl Investment[/TD]
[TD="align: right"]-57000



[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1124"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Cashflow[/TD]
[TD]Investor $[/TD]
[TD]Qtr $ Invested[/TD]
[TD]Principal Payback[/TD]
[TD]Investor Cashflow[/TD]
[/TR]
[TR]
[TD]-43000[/TD]
[TD]=IF(Z39<0,Z39,0)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]=AB39+AC39[/TD]
[/TR]
[TR]
[TD]-14000[/TD]
[TD]=IF(Z40<0,Z40,0)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]=AB40+AC40[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]=IF(Z41<0,Z41,0)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]=AB41+AC41[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]=IF(Z42<0,Z42,0)[/TD]
[TD]=SUMIF(Z39:Z42,"<0")[/TD]
[TD]=SUMIF(Z39:Z42,">0")[/TD]
[TD]=AB42+AC42[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]=IF(Z43<0,Z43,0)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]=AB43+AC43[/TD]
[/TR]
[TR]
[TD]14000[/TD]
[TD]=IF(Z44<0,Z44,0)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]=AB44+AC44[/TD]
[/TR]
[TR]
[TD]16000[/TD]
[TD]=IF(Z45<0,Z45,0)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]=AB45+AC45[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]=SUMIF(Z43:Z46,"<0")[/TD]
[TD]=SUMIF(Z43:Z46,">0")[/TD]
[TD]=AB46+AC46[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Ttl Distributions[/TD]
[TD]=SUMIF(Z39:Z47,">0")[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Ttl Investment[/TD]
[TD]=SUMIF(Z39:Z47,"<0")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[djbush3]

Thank you Blue Hornet and Chandra Amgain for your assistance. I was looking to solve both AD47 and AD48 by just using the results in column AD, but I thought that was a long shot. Referencing the sumifs in columns AB and AC will still get me to the end result I need at bottom of AD. Thanks again.


QUOTE=BlueHornet;4772536]This may be more simplistic than you want, but …

	Z	AA	AB	AC	AD

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"] Cashflow[/TD]
[TD="bgcolor: #FAFAFA"] Investor $[/TD]
[TD="bgcolor: #FAFAFA"] Qtr $ Invested[/TD]
[TD="bgcolor: #FAFAFA"] Principal Payback[/TD]
[TD="bgcolor: #FAFAFA"] Investor Cashflow[/TD]

[TD="align: center"]39[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (43,000)[/TD]

[TD="align: center"]40[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (14,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (14,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (14,000)[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (57,000)[/TD]

[TD="align: center"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 5,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 5,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 5,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (52,000)[/TD]

[TD="align: center"]42[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (52,000)[/TD]

[TD="align: center"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 10,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 10,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 10,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (42,000)[/TD]

[TD="align: center"]44[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 14,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 14,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 14,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (28,000)[/TD]

[TD="align: center"]45[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 16,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ -[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 16,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 16,000[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (12,000)[/TD]

[TD="align: center"]46[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]47[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"] Ttl Distributions[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ 45,000[/TD]

[TD="align: center"]48[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"] Ttl Investment[/TD]
[TD="bgcolor: #FAFAFA, align: right"] $ (57,000)[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AA39[/TH]
[TD="align: left"]=IF( Z39 < 0, Z39, 0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AB39[/TH]
[TD="align: left"]=IF( Z39 > 0, Z39, 0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AC39[/TH]
[TD="align: left"]=AA39 + AB39[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AD39[/TH]
[TD="align: left"]=AC39[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AA40[/TH]
[TD="align: left"]=IF( Z40 < 0, Z40, 0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AB40[/TH]
[TD="align: left"]=IF( Z40 > 0, Z40, 0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AC40[/TH]
[TD="align: left"]=AA40 + AB40[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AD40[/TH]
[TD="align: left"]=AD39 +AC40[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AD47[/TH]
[TD="align: left"]=SUM( AB39:AB45)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]AD48[/TH]
[TD="align: left"]=SUM( AA9:AA450)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/QUOTE]