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]

Harvest Period (HP)	4
Current Period (CP)	0	1	2	3	4	5	6	...	10	11
CF if CP < HP	0	-26216	-26610	-27018	-27444	-27887	-28348	...	-30388	-30951
CF if CP = HP	0	-23934	-17326	-10568	-3662	3387	10573	...	26822	30747
CF if CP > HP	0	0	0	0	0	0	0	0	0	0
CF per period	0	-26216	-26610	-27018	-3662	0	0	0	-30388	-30951

<tbody>
</tbody>

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.