nested IF statements in a cash flow model

[dmcelrath]

I am building a cash flow model to determine my IRR depending on when I sell the investment.

The logic I am trying to follow for the equation is:

• If current period is equal to harvest period, then populate the cell with the sell price minus the cash outflow for that period.
• If current period is greater than harvest period, then populate the cell with 0.
• If current period is less than harvest period, then populate the cell with the cash outflow for that period.

My Harvest Period is cell B11
My Current Period row ranges from 0:17 which equates to cells E2:V2
My cash flow if current period = harvest period ranges from E11:V11
My cash flow if current period > harvest period ranges from E12:V12
My cash flow if current period < harvest period ranges from E10:V10

(I only created these individual rows calculating my cash flow for each scenario for each period bc my equations were getting lengthy and I was trying to simplify the equation for troubleshooting)

My equation for Current Period 0 is: =IF($B$11=E2,E11,IF($B$11<E2,E12,E10))

I then copy and paste this into my subsequent periods.

What happens is I have my Harvest Period set at 4, and the equation works for Periods 0:4, then pulls 0 for Periods 5:9 like it should, but when I get to Periods 10:17 it pulls the value from row 10 like the current period < harvest period.

I've tried all sorts of things trying to figure this out and don't know what to do!

Can someone please help me?

I am happy to share the spreadsheet if that would make it easier, I just need some guidance on how to do that.

 

[dmcelrath]

My equation got cut off. Here it is:

=IF($B$11=E2,E11,IF($B$11<E2,E12,E10))

 

[dmcelrath]

Copy and paste didn't work for some reason

=IF($B11=E2,E11,IF($B$11<E2,E12,E10))

 

[jtakw]

Hi,

Place a space before and after all your < and > symbols in the formula for it to show properly.

 

[dmcelrath]

Thank you. Fourth time's a charm:

=IF($B$11 = E2 ,E11, IF($B$11 < E2, E12, E10))

 

[jtakw]

Sorry, not following your description to the formula.
Can you post a few lines of sample data and expected results with description.

 

[dmcelrath]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Harvest Period (HP)[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Current Period (CP)[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]...[/TD]
[TD]10[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]CF if CP < HP[/TD]
[TD]0
[/TD]
[TD]-26216[/TD]
[TD]-26610[/TD]
[TD]-27018[/TD]
[TD]-27444[/TD]
[TD]-27887[/TD]
[TD]-28348[/TD]
[TD]...[/TD]
[TD]-30388[/TD]
[TD]-30951[/TD]
[/TR]
[TR]
[TD]CF if CP = HP[/TD]
[TD]0[/TD]
[TD]-23934[/TD]
[TD]-17326[/TD]
[TD]-10568[/TD]
[TD]-3662[/TD]
[TD]3387[/TD]
[TD]10573[/TD]
[TD]...[/TD]
[TD]26822[/TD]
[TD]30747[/TD]
[/TR]
[TR]
[TD]CF if CP > HP[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]CF per period[/TD]
[TD]0[/TD]
[TD]-26216[/TD]
[TD]-26610[/TD]
[TD]-27018[/TD]
[TD]-3662[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-30388[/TD]
[TD]-30951[/TD]
[/TR]
</tbody>[/TABLE]


The equation I posted is being used in the "CF per period" row. The expected result is correct for periods 0-9, the problem is from period 10 and on. The "CF per period" for these periods should be 0, not the value in that period's "CF if CP < HP" row.

 

[jtakw]

I'm not getting the error you're getting...
How is B11 and E2:V2 populated, manually or by formula, I suspect leading and/or trailing spaces...

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2				0	1	2	3	4	5	6	7	8	9	10	11
9
10	Harvest Period		Current	0	26216	26610	27018	27444	27887	28348				30388	30951
11	4		Current=Harvest	0	23934	17326	10568	-3662	3387	10573				26822	30747
12			Current>Harvest	0	0	0	0	0	0	0				0	0
13
14	CF per Period			0	26216	26610	27018	-3662	0	0	0	0	0	0	0
Sheet10

Cell Formulas
Range	Formula
E14	=IF($B$11=E2,E11,IF($B$11))

 

[dmcelrath]

Seems to be working now. E2:V2 had error flags bc they were "numbers stored as text" and when I made the suggested correction from Excel everything was fixed.

Thanks for the help!

 

[jtakw]

OK, thanks for letting us know.