Construction Costs Yearly Cash Flow

[nikolacm]

Hello everyone, I am trying to prepare an annual cash flow for construction costs. These will take a straight line and not an s-curve. Regarding to assumptions, construction will start month 1 and will end in month 18. However, i am struggling in order to identify how to treat formula in order to calculate the cost for the remaining period and not the whole second year.

Valuation Date	30-06-21
Discount rate	10.50%
Growth Rate	2.00%
Exit Yield	8.50%
	Year		1	2	3	4	5	6	7	8	9	10	11
	Month Begin		Jul-2021	Jul-2022	Jul-2023	Jul-2024	Jul-2025	Jul-2026	Jul-2027	Jul-2028	Jul-2029	Jul-2030	Jul-2031
	Start Year		1	13	25	37	49	61	73	85	97	109	121
	End Year		12	24	36	48	60	72	84	96	108	120	132
			0.00	0.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00
	Capital Expenses	(1,161,600)	(580,800)	(580,800)	0	0	0	0	0	0	0	0	0
					Construction period
					From	To	Duration (month)	Duration (Year)
	Constr. Cost	880 €/m²	1,200.00 μ²	1,056,000.00 €	1	24	24	2
	Contigencies	5% of CC		52,800 €
	Prof. Fees	5% of CC		52,800 €
	FF&E	9,000 €/δωμάτιο		0 €
				1,161,600.00 €

 

[jorismoerings]

Hi,

If this question is still open, could you add the expected result (calculated manually) to your screenshot?

 

[jorismoerings]

addition to previous post:

do you mean something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Valuation Date	30-6-2021
3	Discount rate	10,5%
4	Growth Rate	2,0%
5	Exit Yield	8,5%
6
7
8
9		Year		1	2	3	4	5	6	7	8	9	10	11
10		Month Begin		30-7-2021	31-7-2022	31-7-2023	31-7-2024	31-7-2025	31-7-2026	31-7-2027	31-7-2028	31-7-2029	31-7-2030	31-7-2031
11		Start Year		1	12	24	36	48	60	72	84	96	108	120
12		End Year		12	24	36	48	60	72	84	96	108	120	132
13				0	0	0,5	1	1	1	1	1	1	1	1
14		Capital Expenses	-1161600	-290400	-580800	-290400	0	0	0	0	0	0	0	0
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29						Construction period
30						From	To	Duration (month)	Duration (Year)
31		Constr. Cost	880	1200	1056000	6	30	24	2
32		Contigencies	0,05		52800
33		Prof. Fees	0,05		52800
34		FF&E	9000		0
35					1161600
Sheet1

Cell Formulas
Range	Formula
B3	B3	=B4+B5
D10	D10	=EDATE(B2,1)
E10:N10	E10	=EOMONTH(D10,12)
E11:N11	E11	=D12
D13:N13	D13	=IF(D12<$G$31+1,0,IF(D11>$G$31,1,(D12-$G$31)/12))
C14	C14	=SUM(OFFSET(C14,0,1,1,100))
D14:N14	D14	=-$E$35/$H$31*IF(MIN($G$31,D12)-MAX($F$31,D11)>=0,MIN($G$31,D12)-MAX($F$31,D11),0)
C31	C31	=1100*0.8
E31	E31	=C31*D31
E32	E32	=C32*E31
E33	E33	=C33*E31
E34	E34	=C35*L32
E35	E35	=SUM(E31:E34)

 

[nikolacm]

Hi,

If this question is still open, could you add the expected result (calculated manually) to your screenshot?

Hi,

Apologies for my tardy reply. The expected result is 1.161.600.

 

[nikolacm]

addition to previous post:

do you mean something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Valuation Date	30-6-2021
3	Discount rate	10,5%
4	Growth Rate	2,0%
5	Exit Yield	8,5%
6
7
8
9		Year		1	2	3	4	5	6	7	8	9	10	11
10		Month Begin		30-7-2021	31-7-2022	31-7-2023	31-7-2024	31-7-2025	31-7-2026	31-7-2027	31-7-2028	31-7-2029	31-7-2030	31-7-2031
11		Start Year		1	12	24	36	48	60	72	84	96	108	120
12		End Year		12	24	36	48	60	72	84	96	108	120	132
13				0	0	0,5	1	1	1	1	1	1	1	1
14		Capital Expenses	-1161600	-290400	-580800	-290400	0	0	0	0	0	0	0	0
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29						Construction period
30						From	To	Duration (month)	Duration (Year)
31		Constr. Cost	880	1200	1056000	6	30	24	2
32		Contigencies	0,05		52800
33		Prof. Fees	0,05		52800
34		FF&E	9000		0
35					1161600
Sheet1

Cell Formulas
Range	Formula
B3	B3	=B4+B5
D10	D10	=EDATE(B2,1)
E10:N10	E10	=EOMONTH(D10,12)
E11:N11	E11	=D12
D13:N13	D13	=IF(D12<$G$31+1,0,IF(D11>$G$31,1,(D12-$G$31)/12))
C14	C14	=SUM(OFFSET(C14,0,1,1,100))
D14:N14	D14	=-$E$35/$H$31*IF(MIN($G$31,D12)-MAX($F$31,D11)>=0,MIN($G$31,D12)-MAX($F$31,D11),0)
C31	C31	=1100*0.8
E31	E31	=C31*D31
E32	E32	=C32*E31
E33	E33	=C33*E31
E34	E34	=C35*L32
E35	E35	=SUM(E31:E34)

This was exactly what I was looking for. Many thanks for your assistance!