Formula to repeat a value starting at x year and repeating y years

[Robert Davidson]

I am preparing a cashflow model which has a column with years starting at say 2023 going on for several decades. I wish to make a periodic income in the adjacent column that starts in say 2025 then repeats every say 3 years. Could someone help construct a formula for this? The start year, the income, the first income year and the frequency all need to be variables. An example would be. The aim is to produce a periodic income for a NPV calculation arising from felling trees so the frequency could be up to perhaps 120 years.

Year	Income
2023	0
2024	0
2025	1000
2026	0
2027	0
2028	1000
2029	0
2030	0
2031	1000

 

[Robert Davidson]

Full explanation in the screenshot:

 

[Peter_SSs]

I am not clear on the relationship between the last note (effectively "cut cashflow in 2100") and cell B3 (Model duration = 1000).
Does B3 mean the table goes for 1000 rows? Seems very long!!?
If so, does B7 mean that income stops in 2100 but costs continue for the whole 1000 rows?

 

[Sanjeev1976]

Robert, you can try the below formula :

=LET(s,SEQUENCE(IF((B2+B3)>B7,B7-B2+1,B3),,B2),HSTACK(s,BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),B5,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),-B8,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),ROUND(B5/(1+B11)^(y-B2),2),IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),ROUND(-B8/(1+B11)^(y-B2),2),0))))))

 

[Robert Davidson]

Robert, you can try the below formula :

=LET(s,SEQUENCE(IF((B2+B3)>B7,B7-B2+1,B3),,B2),HSTACK(s,BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),B5,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),-B8,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),ROUND(B5/(1+B11)^(y-B2),2),IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),ROUND(-B8/(1+B11)^(y-B2),2),0))))))

I am not clear on the relationship between the last note (effectively "cut cashflow in 2100") and cell B3 (Model duration = 1000).
Does B3 mean the table goes for 1000 rows? Seems very long!!?
If so, does B7 mean that income stops in 2100 but costs continue for the whole 1000 rows?

Peter, Sorry I wasn't that clear. Discounting and working out nett present value (the sum of the discounted column) requires long time frames to ensure the values in the discounted column eventually turn to zero. I should really re-label B7 to be last income and last costs. The reason I need to cut cost and income at some point in the future is that a block of trees might be being felled and replanted as normal then the landuse is changed for that block to be, for example, a wind farm (as happens here in the UK). Thus income and costs associated with forestry stop. Hopefully that makes sense.

 

[Robert Davidson]

Robert, you can try the below formula :

=LET(s,SEQUENCE(IF((B2+B3)>B7,B7-B2+1,B3),,B2),HSTACK(s,BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),B5,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),-B8,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),ROUND(B5/(1+B11)^(y-B2),2),IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),ROUND(-B8/(1+B11)^(y-B2),2),0))))))

Thank you for your help Sanjeev. Have you tried setting B9 (restocking delay) to zero? If I do that with your above formula the number in the discounted column appears to only discount income and does not discount cost. If I leave B7 blank then the above formula shows VALUE! If that is blank then I would want the default length to be per Model Duration. Also, I realise that if there is a restocking delay then that should be added to the rotation. For example a 2 year delay plus a 5 year rotation would effectively make the real length of the rotation 2 + 5 = 7.

 

[Peter_SSs]

@Robert Davidson
If you are still needing help with this, could you please address the issues raised in my previous post?

 

[Robert Davidson]

@Robert Davidson
If you are still needing help with this, could you please address the issues raised in my previous post?

Peter, Sorry I wasn't that clear plus I had replied earlier but the reply seems to have come up in the response to Sanjeev. Discounting and working out nett present value (the sum of the discounted column) requires long time frames to ensure the values in the discounted column eventually turn to zero. I should really re-label B7 to be last income and last costs. The reason I need to cut cost and income at some point in the future is that a block of trees might be being felled and replanted as normal then the landuse is changed for that block to be, for example, a wind farm (as happens here in the UK). Thus income and costs associated with forestry stop. Hopefully that makes sense.

 

[Sanjeev1976]

Robert, you can try the below. Hope it takes care of all your points.

=LET(s,SEQUENCE(IF((B2+B3)>=MIN(B7,B2+B3),IF(B7>B2,B7-B2+1,B3),B3),,B2),HSTACK(s,BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),B5,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),-B8,0))),BYROW(s,LAMBDA(y,IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6,B4)=0),ROUND(IF(AND(B9=0,B8>0),(B5-B8),B5)/(1+B11)^(y-B2),2),IF(AND(y>=B6,IF(ISBLANK(B7),B2+B3,y<=B7),MOD(y-B6-B9,B4)=0),ROUND(-B8/(1+B11)^(y-B2),2),0))))))

 

[Peter_SSs]

I had replied earlier but the reply seems to have come up in the response to Sanjeev.

Ah, I see. I had not expanded that quote so missed the reply. You have embedded quotes within quotes and put replies in original quotes so you need to try to sort that out going forward.

Does this do what you want?

Robert Davidson.xlsm
	A	B	C	D	E	F	G
1				Year	Income	Cost	Discounted
2	Start	2024		2024	0.00	0.00	0.00
3	Model Duration	1000		2025	0.00	0.00	0.00
4	Rotation	3		2026	24,000.00	0.00	22,622.30
5	Income	24000		2027	0.00	-3,500.00	-3,203.00
6	First felling	2026		2028	0.00	0.00	0.00
7	Last income & costs	2100		2029	24,000.00	0.00	20,702.61
8	Restocking cost	3500		2030	0.00	-3,500.00	-2,931.19
9	Restocking delay	1		2031	0.00	0.00	0.00
10				2032	24,000.00	0.00	18,945.82
11	Discount Rate	3%		2033	0.00	-3,500.00	-2,682.46
12				2034	0.00	0.00	0.00
13				2035	24,000.00	0.00	17,338.11
14				2036	0.00	-3,500.00	-2,454.83
15				2037	0.00	0.00	0.00
16				2038	24,000.00	0.00	15,866.83
17				2039	0.00	-3,500.00	-2,246.52
18				2040	0.00	0.00	0.00
19				2041	24,000.00	0.00	14,520.39
20				2042	0.00	-3,500.00	-2,055.88
21				2043	0.00	0.00	0.00
22				2044	24,000.00	0.00	13,288.22
23				2045	0.00	-3,500.00	-1,881.42
24				2046	0.00	0.00	0.00
25				2047	24,000.00	0.00	12,160.60
26				2048	0.00	-3,500.00	-1,721.77
27				2049	0.00	0.00	0.00
28				2050	24,000.00	0.00	11,128.67
29				2051	0.00	-3,500.00	-1,575.66
30				2052	0.00	0.00	0.00
31				2053	24,000.00	0.00	10,184.31
32				2054	0.00	-3,500.00	-1,441.95
33				2055	0.00	0.00	0.00
34				2056	24,000.00	0.00	9,320.09
35				2057	0.00	-3,500.00	-1,319.59
36				2058	0.00	0.00	0.00
37				2059	24,000.00	0.00	8,529.20
38				2060	0.00	-3,500.00	-1,207.61
39				2061	0.00	0.00	0.00
40				2062	24,000.00	0.00	7,805.43
41				2063	0.00	-3,500.00	-1,105.14
42				2064	0.00	0.00	0.00
43				2065	24,000.00	0.00	7,143.07
44				2066	0.00	-3,500.00	-1,011.36
45				2067	0.00	0.00	0.00
46				2068	24,000.00	0.00	6,536.92
47				2069	0.00	-3,500.00	-925.54
48				2070	0.00	0.00	0.00
49				2071	24,000.00	0.00	5,982.21
50				2072	0.00	-3,500.00	-847.00
51				2073	0.00	0.00	0.00
52				2074	24,000.00	0.00	5,474.57
53				2075	0.00	-3,500.00	-775.12
54				2076	0.00	0.00	0.00
55				2077	24,000.00	0.00	5,010.01
56				2078	0.00	-3,500.00	-709.35
57				2079	0.00	0.00	0.00
58				2080	24,000.00	0.00	4,584.87
59				2081	0.00	-3,500.00	-649.15
60				2082	0.00	0.00	0.00
61				2083	24,000.00	0.00	4,195.80
62				2084	0.00	-3,500.00	-594.07
63				2085	0.00	0.00	0.00
64				2086	24,000.00	0.00	3,839.75
65				2087	0.00	-3,500.00	-543.65
66				2088	0.00	0.00	0.00
67				2089	24,000.00	0.00	3,513.92
68				2090	0.00	-3,500.00	-497.52
69				2091	0.00	0.00	0.00
70				2092	24,000.00	0.00	3,215.73
71				2093	0.00	-3,500.00	-455.30
72				2094	0.00	0.00	0.00
73				2095	24,000.00	0.00	2,942.85
74				2096	0.00	-3,500.00	-416.67
75				2097	0.00	0.00	0.00
76				2098	24,000.00	0.00	2,693.13
77				2099	0.00	-3,500.00	-381.31
78				2100	0.00	0.00	0.00
79				2101	0.00	0.00	0.00
80				2102	0.00	0.00	0.00
81				2103	0.00	0.00	0.00
Cashflow (3)

Cell Formulas
Range	Formula
D2:G1001	D2	=LET(yr,SEQUENCE(B3,,B2),inc,BYROW(yr,LAMBDA(y,IF(AND(y>=B6,y<=B7,MOD(y-B6,B4)=0),B5,0))),dinc,BYROW(yr,LAMBDA(y,IF(AND(y>=B6,y<=B7,MOD(y-B6,B4)=0),ROUND(B5/(1+B11)^(y-B2),2),0))),cost,-BYROW(yr,LAMBDA(y,IF(AND(y>=B6,y<=B7,MOD(y-B6,B4)=B9),B8,0))),dcost,-BYROW(yr,LAMBDA(y,IF(AND(y>=B6,y<=B7,MOD(y-B6,B4)=B9),ROUND(B8/(1+B11)^(y-B2),2),0))),HSTACK(yr,inc,cost,dinc+dcost))
Dynamic array formulas.

If so, I don't see any point in cell B3 (Model Duration) since the duration is already limited by 'Last Income & Costs' in B7. So you could use this instead.

Robert Davidson.xlsm
	A	B	C	D	E	F	G
1				Year	Income	Cost	Discounted
2	Start	2024		2024	0.00	0.00	0.00
3	Rotation	3		2025	0.00	0.00	0.00
4	Income	24000		2026	24,000.00	0.00	22,622.30
5	First felling	2026		2027	0.00	-3,500.00	-3,203.00
6	Last income & costs	2100		2028	0.00	0.00	0.00
7	Restocking cost	3500		2029	24,000.00	0.00	20,702.61
8	Restocking delay	1		2030	0.00	-3,500.00	-2,931.19
9				2031	0.00	0.00	0.00
10	Discount Rate	3%		2032	24,000.00	0.00	18,945.82
11				2033	0.00	-3,500.00	-2,682.46
12				2034	0.00	0.00	0.00
13				2035	24,000.00	0.00	17,338.11
14				2036	0.00	-3,500.00	-2,454.83
15				2037	0.00	0.00	0.00
16				2038	24,000.00	0.00	15,866.83
17				2039	0.00	-3,500.00	-2,246.52
18				2040	0.00	0.00	0.00
19				2041	24,000.00	0.00	14,520.39
20				2042	0.00	-3,500.00	-2,055.88
21				2043	0.00	0.00	0.00
22				2044	24,000.00	0.00	13,288.22
23				2045	0.00	-3,500.00	-1,881.42
24				2046	0.00	0.00	0.00
25				2047	24,000.00	0.00	12,160.60
26				2048	0.00	-3,500.00	-1,721.77
27				2049	0.00	0.00	0.00
28				2050	24,000.00	0.00	11,128.67
29				2051	0.00	-3,500.00	-1,575.66
30				2052	0.00	0.00	0.00
31				2053	24,000.00	0.00	10,184.31
32				2054	0.00	-3,500.00	-1,441.95
33				2055	0.00	0.00	0.00
34				2056	24,000.00	0.00	9,320.09
35				2057	0.00	-3,500.00	-1,319.59
36				2058	0.00	0.00	0.00
37				2059	24,000.00	0.00	8,529.20
38				2060	0.00	-3,500.00	-1,207.61
39				2061	0.00	0.00	0.00
40				2062	24,000.00	0.00	7,805.43
41				2063	0.00	-3,500.00	-1,105.14
42				2064	0.00	0.00	0.00
43				2065	24,000.00	0.00	7,143.07
44				2066	0.00	-3,500.00	-1,011.36
45				2067	0.00	0.00	0.00
46				2068	24,000.00	0.00	6,536.92
47				2069	0.00	-3,500.00	-925.54
48				2070	0.00	0.00	0.00
49				2071	24,000.00	0.00	5,982.21
50				2072	0.00	-3,500.00	-847.00
51				2073	0.00	0.00	0.00
52				2074	24,000.00	0.00	5,474.57
53				2075	0.00	-3,500.00	-775.12
54				2076	0.00	0.00	0.00
55				2077	24,000.00	0.00	5,010.01
56				2078	0.00	-3,500.00	-709.35
57				2079	0.00	0.00	0.00
58				2080	24,000.00	0.00	4,584.87
59				2081	0.00	-3,500.00	-649.15
60				2082	0.00	0.00	0.00
61				2083	24,000.00	0.00	4,195.80
62				2084	0.00	-3,500.00	-594.07
63				2085	0.00	0.00	0.00
64				2086	24,000.00	0.00	3,839.75
65				2087	0.00	-3,500.00	-543.65
66				2088	0.00	0.00	0.00
67				2089	24,000.00	0.00	3,513.92
68				2090	0.00	-3,500.00	-497.52
69				2091	0.00	0.00	0.00
70				2092	24,000.00	0.00	3,215.73
71				2093	0.00	-3,500.00	-455.30
72				2094	0.00	0.00	0.00
73				2095	24,000.00	0.00	2,942.85
74				2096	0.00	-3,500.00	-416.67
75				2097	0.00	0.00	0.00
76				2098	24,000.00	0.00	2,693.13
77				2099	0.00	-3,500.00	-381.31
78				2100	0.00	0.00	0.00
79
Cashflow (4)

Cell Formulas
Range	Formula
D2:G78	D2	=LET(yr,SEQUENCE(B6-B2+1,,B2),inc,BYROW(yr,LAMBDA(y,IF(AND(y>=B5,MOD(y-B5,B3)=0),B4,0))),dinc,BYROW(yr,LAMBDA(y,IF(AND(y>=B5,MOD(y-B5,B3)=0),ROUND(B4/(1+B10)^(y-B2),2),0))),cost,-BYROW(yr,LAMBDA(y,IF(AND(y>=B5,MOD(y-B5,B3)=B8),B7,0))),dcost,-BYROW(yr,LAMBDA(y,IF(AND(y>=B5,MOD(y-B5,B3)=B8),ROUND(B7/(1+B10)^(y-B2),2),0))),HSTACK(yr,inc,cost,dinc+dcost))
Dynamic array formulas.

 

[Robert Davidson]

Dear Peter,
Thank you very much for all your help. Both are helpful but I will stick with the option to continue on the model period while truncating the cashflow early as I will use a series of these formulae for calculating the values of each part of a forest as each one can then be treated differently but the cash flow can be seen to terminate at a common year.
Robert.