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

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this the sort of thing you mean?

23 08 09.xlsm
	A	B	C	D	E
1	Start	2023		Year	Income
2	Years	40		2023	0
3	Repeat Yrs	3		2024	0
4	Amount	1000		2025	1000
5	First Income	2025		2026	0
6				2027	0
7				2028	1000
8				2029	0
9				2030	0
10				2031	1000
11				2032	0
12				2033	0
13				2034	1000
14				2035	0
15				2036	0
16				2037	1000
17				2038	0
18				2039	0
19				2040	1000
20				2041	0
21				2042	0
22				2043	1000
23				2044	0
24				2045	0
25				2046	1000
26				2047	0
27				2048	0
28				2049	1000
29				2050	0
30				2051	0
31				2052	1000
32				2053	0
33				2054	0
34				2055	1000
35				2056	0
36				2057	0
37				2058	1000
38				2059	0
39				2060	0
40				2061	1000
41				2062	0
42
Cashflow

Cell Formulas
Range	Formula
D2:E41	D2	=LET(s,SEQUENCE(B2,,B1),HSTACK(s,BYROW(s,LAMBDA(y,IF(AND(y>=B5,MOD(y-B5,B3)=0),B4,0)))))
Dynamic array formulas.

 

[Robert Davidson]

That is exactly what I wanted so thank you very much. If I wanted to add in another column to discount the income could that be included in this formula. The calculation would be “income/(1+r)^(year of income-start year)”. The variable r could be, for example, 0.05 i.e. 5%.

 

[Peter_SSs]

Could you show us some sample data with the expected results manually filled in & explain the calculations in relation to that sample data?

 

[Robert Davidson]

Here's an example.
Model start year 2023
First income year 2025
Repeat yrs 3
Income 1000
Discount rate (r) 5%

In the Discounted column the formula would be: =Income/(1+r)^(Income year-Model Start Year)
So for the income in 2028 the numbers would be 1000/(1+.05)^(2028-2023) = 783.53. It's probably best to round to 2 decimal places.
I would then sum the entire discounted column separately to derive the Present Value of the cash flow.

Year	Income	Discounted
2023	0	0
2024	0	0
2025	1000	907.03
2026	0	0
2027	0	0
2028	1000	783.53
2029	0	0
2030	0	0
2031	1000	676.84

 

[Peter_SSs]

Here's an example.

Thanks. Is this what you mean?

23 08 09.xlsm
	A	B	C	D	E
1	Start	2023		Year	Income
2	Years	40		2023	0
3	Repeat Yrs	3		2024	0
4	Amount	1000		2025	907.03
5	First Income	2025		2026	0
6	Discount	5%		2027	0
7				2028	783.53
8				2029	0
9				2030	0
10				2031	676.84
11				2032	0
12				2033	0
13				2034	584.68
14				2035	0
15				2036	0
16				2037	505.07
17				2038	0
18				2039	0
19				2040	436.3
20				2041	0
21				2042	0
22				2043	376.89
23				2044	0
24				2045	0
25				2046	325.57
26				2047	0
27				2048	0
28				2049	281.24
29				2050	0
30				2051	0
31				2052	242.95
32				2053	0
33				2054	0
34				2055	209.87
35				2056	0
36				2057	0
37				2058	181.29
38				2059	0
39				2060	0
40				2061	156.61
41				2062	0
Cashflow (2)

Cell Formulas
Range	Formula
D2:E41	D2	=LET(s,SEQUENCE(B2,,B1),HSTACK(s,BYROW(s,LAMBDA(y,IF(AND(y>=B5,MOD(y-B5,B3)=0),ROUND(B4/(1+B6)^(y-B1),2),0)))))
Dynamic array formulas.

 

[Robert Davidson]

That is perfect. Many thanks for all your help. I have learnt a load more formulae as a result.

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.

 

[Robert Davidson]

May I trouble you again as I have taken this formula further? Traditionally one shows undiscounted income and costs in separate columns (see picture - columns E & F) then the sum of these two columns are discounted and shown in column G. How do I get the -3500 in F5, for example, to be discounted and shown in G5? The value in G5 would be -3299.08.

The formula in D3 is:
=LET(s,SEQUENCE(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),0)))))

 

[Peter_SSs]

Can you show that sample data again but with the first 12 rows of results manually filled in with the desired results and explain how you get the figures in the 'Discounted' column?