Having an array based on number of months

EthanP · Sep 17, 2022

In row 11 I would like to display cash flow values from row 5 starting at column F based on the sale month in cell B2. For example, if the sale month is Month 24 than I want month 1 thru 24 cash flows to appear in row 11 starting at column F.

Experiment Valuator REV B.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

1

Month

Acquisition

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

Month 13

Month 14

Month 15

Month 16

Month 17

Month 18

Month 19

Month 20

Month 21

Month 22

Month 23

Month 24

Month 25

Month 26

Month 27

Month 28

Month 29

Month 30

Month 31

Month 32

Month 33

Month 34

Month 35

Month 36

Month 37

Month 38

Month 39

Month 40

Month 41

Month 42

Month 43

Month 44

Month 45

2

Sale Month

Month 1

Year

Acquisition

Year 1

Year 2

Year 3

Year 4

3

IRR

Month Ending

1/5/2023

2/28/2023

3/31/2023

4/30/2023

5/31/2023

6/30/2023

7/31/2023

8/31/2023

9/30/2023

10/31/2023

11/30/2023

12/31/2023

1/31/2024

2/29/2024

3/31/2024

4/30/2024

5/31/2024

6/30/2024

7/31/2024

8/31/2024

9/30/2024

10/31/2024

11/30/2024

12/31/2024

1/31/2025

2/28/2025

3/31/2025

4/30/2025

5/31/2025

6/30/2025

7/31/2025

8/31/2025

9/30/2025

10/31/2025

11/30/2025

12/31/2025

1/31/2026

2/28/2026

3/31/2026

4/30/2026

5/31/2026

6/30/2026

7/31/2026

8/31/2026

9/30/2026

10/31/2026

4

Equity Multiple

Investment

($165,000.00)

5

Preferred Return

8%

Cash Flow

$1,969.93

$1,983.15

$1,996.40

$2,009.69

$2,023.02

$2,036.38

$2,049.78

$2,063.21

$2,076.68

$2,090.19

$2,103.73

$2,117.31

$2,130.92

$2,144.58

$2,158.27

$2,171.99

$2,185.75

$2,199.55

$2,213.39

$2,227.26

$2,241.17

$2,255.12

$2,269.11

$2,283.13

$2,297.19

$2,311.29

$2,325.43

$2,339.60

$2,353.82

$2,368.07

$2,382.36

$2,396.68

$2,411.05

$2,425.45

$2,439.90

$2,454.38

$2,468.90

$2,483.46

$2,498.06

$2,512.70

$2,527.37

$2,542.09

$2,556.85

$2,571.64

$2,586.48

6

LP Split

70%

Cap Rate on sale (is going in cap rate)

6%

7

GP Spilt

30%

Net Operating Income

$4,694.12

$4,707.52

$4,720.96

$4,734.43

$4,747.93

$4,761.47

$4,775.05

$4,788.67

$4,802.32

$4,816.01

$4,829.73

$4,843.50

$4,788.67

$4,802.32

$4,816.01

$4,829.73

$4,843.50

$4,857.30

$4,871.13

$4,885.01

$4,898.92

$4,912.87

$4,926.85

$4,940.87

$4,954.94

$4,969.03

$4,983.17

$4,997.35

$5,011.56

$5,025.81

$5,040.10

$5,054.43

$5,068.79

$5,083.20

$5,097.64

$5,112.12

$5,126.64

$5,141.20

$5,155.80

$5,170.44

$5,185.12

$5,199.83

$5,214.59

$5,229.39

$5,244.22

8

Sale Price

$78,235.41

$156,694.11

$235,376.71

$314,283.80

$393,416.00

$472,773.90

$552,358.11

$632,169.25

$712,207.92

$792,474.74

$872,970.31

$953,695.25

$955,270.98

$956,850.95

$958,435.17

$960,023.65

$961,616.40

$963,213.43

$964,814.76

$966,420.39

$968,030.34

$969,644.62

$971,263.23

$972,886.20

$975,657.33

$978,435.90

$981,221.94

$984,015.47

$986,816.51

$989,625.07

$992,441.18

$995,264.85

$998,096.11

$1,000,934.97

$1,003,781.45

$1,006,635.59

$1,009,497.38

$1,012,366.86

$1,015,244.04

$1,018,128.95

$1,021,021.61

$1,023,922.02

$1,026,830.22

$1,029,746.23

$1,032,670.06

9

Debt left on Loan

($411,904.76)

($411,306.54)

($410,705.33)

($410,101.11)

($409,493.87)

($408,883.60)

($408,270.27)

($407,653.88)

($407,034.41)

($406,411.84)

($405,786.15)

($405,157.34)

($404,525.38)

($403,890.27)

($403,251.97)

($402,610.49)

($401,965.80)

($401,317.89)

($400,666.73)

($400,012.32)

($399,354.64)

($398,693.67)

($398,029.40)

($397,361.80)

($396,690.87)

($396,016.58)

($395,338.92)

($394,657.87)

($393,973.41)

($393,285.54)

($392,594.22)

($391,899.45)

($391,201.20)

($390,499.47)

($389,794.22)

($389,085.45)

($388,373.13)

($387,657.25)

($386,937.80)

($386,214.74)

($385,488.07)

($384,757.77)

($384,023.82)

($383,286.19)

($382,544.88)

10

Equity After Loan Payoff

($333,669.35)

($254,612.43)

($175,328.62)

($95,817.31)

($16,077.88)

$63,890.30

$144,087.84

$224,515.37

$305,173.51

$386,062.90

$467,184.16

$548,537.91

$550,745.60

$552,960.68

$555,183.19

$557,413.15

$559,650.60

$561,895.54

$564,148.02

$566,408.07

$568,675.70

$570,950.95

$573,233.84

$575,524.40

$578,966.46

$582,419.32

$585,883.03

$589,357.60

$592,843.09

$596,339.53

$599,846.95

$603,365.40

$606,894.90

$610,435.50

$613,987.23

$617,550.14

$621,124.25

$624,709.61

$628,306.25

$631,914.21

$635,533.53

$639,164.25

$642,806.41

$646,460.04

$650,125.18

11

values

Sheet2

steve the fish · Sep 17, 2022

What do you mean? It sounds like you want a single result but you have loads of result cells.

EthanP · Sep 17, 2022

Yes, I have a load of result cells based on the sale month

steve the fish · Sep 17, 2022

Thats not helpful. I cant guess what you need. What goes in F11 if B2 says Month 24? Also G11?

EthanP · Sep 17, 2022

I would like it to look like what's below and be dynamic base on the "sale Month" in cell B2.

Experiment Valuator REV B.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

AU

AV

AW

AX

1

Month

Acquisition

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

Month 13

Month 14

Month 15

Month 16

Month 17

Month 18

Month 19

Month 20

Month 21

Month 22

Month 23

Month 24

Month 25

Month 26

Month 27

Month 28

Month 29

Month 30

Month 31

Month 32

Month 33

Month 34

Month 35

Month 36

Month 37

Month 38

Month 39

Month 40

Month 41

Month 42

Month 43

Month 44

Month 45

2

Sale Month

Month 1

Year

Acquisition

Year 1

Year 2

Year 3

Year 4

3

IRR

Month Ending

1/5/2023

2/28/2023

3/31/2023

4/30/2023

5/31/2023

6/30/2023

7/31/2023

8/31/2023

9/30/2023

10/31/2023

11/30/2023

12/31/2023

1/31/2024

2/29/2024

3/31/2024

4/30/2024

5/31/2024

6/30/2024

7/31/2024

8/31/2024

9/30/2024

10/31/2024

11/30/2024

12/31/2024

1/31/2025

2/28/2025

3/31/2025

4/30/2025

5/31/2025

6/30/2025

7/31/2025

8/31/2025

9/30/2025

10/31/2025

11/30/2025

12/31/2025

1/31/2026

2/28/2026

3/31/2026

4/30/2026

5/31/2026

6/30/2026

7/31/2026

8/31/2026

9/30/2026

10/31/2026

4

Equity Multiple

Investment

($165,000.00)

5

Preferred Return

8%

Cash Flow

$1,969.93

$1,983.15

$1,996.40

$2,009.69

$2,023.02

$2,036.38

$2,049.78

$2,063.21

$2,076.68

$2,090.19

$2,103.73

$2,117.31

$2,130.92

$2,144.58

$2,158.27

$2,171.99

$2,185.75

$2,199.55

$2,213.39

$2,227.26

$2,241.17

$2,255.12

$2,269.11

$2,283.13

$2,297.19

$2,311.29

$2,325.43

$2,339.60

$2,353.82

$2,368.07

$2,382.36

$2,396.68

$2,411.05

$2,425.45

$2,439.90

$2,454.38

$2,468.90

$2,483.46

$2,498.06

$2,512.70

$2,527.37

$2,542.09

$2,556.85

$2,571.64

$2,586.48

6

LP Split

70%

Cap Rate on sale (is going in cap rate)

6%

7

GP Spilt

30%

Net Operating Income

$4,694.12

$4,707.52

$4,720.96

$4,734.43

$4,747.93

$4,761.47

$4,775.05

$4,788.67

$4,802.32

$4,816.01

$4,829.73

$4,843.50

$4,788.67

$4,802.32

$4,816.01

$4,829.73

$4,843.50

$4,857.30

$4,871.13

$4,885.01

$4,898.92

$4,912.87

$4,926.85

$4,940.87

$4,954.94

$4,969.03

$4,983.17

$4,997.35

$5,011.56

$5,025.81

$5,040.10

$5,054.43

$5,068.79

$5,083.20

$5,097.64

$5,112.12

$5,126.64

$5,141.20

$5,155.80

$5,170.44

$5,185.12

$5,199.83

$5,214.59

$5,229.39

$5,244.22

8

Sale Price

$78,235.41

$156,694.11

$235,376.71

$314,283.80

$393,416.00

$472,773.90

$552,358.11

$632,169.25

$712,207.92

$792,474.74

$872,970.31

$953,695.25

$955,270.98

$956,850.95

$958,435.17

$960,023.65

$961,616.40

$963,213.43

$964,814.76

$966,420.39

$968,030.34

$969,644.62

$971,263.23

$972,886.20

$975,657.33

$978,435.90

$981,221.94

$984,015.47

$986,816.51

$989,625.07

$992,441.18

$995,264.85

$998,096.11

$1,000,934.97

$1,003,781.45

$1,006,635.59

$1,009,497.38

$1,012,366.86

$1,015,244.04

$1,018,128.95

$1,021,021.61

$1,023,922.02

$1,026,830.22

$1,029,746.23

$1,032,670.06

9

Debt left on Loan

($411,904.76)

($411,306.54)

($410,705.33)

($410,101.11)

($409,493.87)

($408,883.60)

($408,270.27)

($407,653.88)

($407,034.41)

($406,411.84)

($405,786.15)

($405,157.34)

($404,525.38)

($403,890.27)

($403,251.97)

($402,610.49)

($401,965.80)

($401,317.89)

($400,666.73)

($400,012.32)

($399,354.64)

($398,693.67)

($398,029.40)

($397,361.80)

($396,690.87)

($396,016.58)

($395,338.92)

($394,657.87)

($393,973.41)

($393,285.54)

($392,594.22)

($391,899.45)

($391,201.20)

($390,499.47)

($389,794.22)

($389,085.45)

($388,373.13)

($387,657.25)

($386,937.80)

($386,214.74)

($385,488.07)

($384,757.77)

($384,023.82)

($383,286.19)

($382,544.88)

10

Equity After Loan Payoff

($333,669.35)

($254,612.43)

($175,328.62)

($95,817.31)

($16,077.88)

$63,890.30

$144,087.84

$224,515.37

$305,173.51

$386,062.90

$467,184.16

$548,537.91

$550,745.60

$552,960.68

$555,183.19

$557,413.15

$559,650.60

$561,895.54

$564,148.02

$566,408.07

$568,675.70

$570,950.95

$573,233.84

$575,524.40

$578,966.46

$582,419.32

$585,883.03

$589,357.60

$592,843.09

$596,339.53

$599,846.95

$603,365.40

$606,894.90

$610,435.50

$613,987.23

$617,550.14

$621,124.25

$624,709.61

$628,306.25

$631,914.21

$635,533.53

$639,164.25

$642,806.41

$646,460.04

$650,125.18

11

values

$1,969.93

$1,983.15

$1,996.40

$2,009.69

$2,023.02

$2,036.38

$2,049.78

$2,063.21

$2,076.68

$2,090.19

$2,103.73

$2,117.31

$2,130.92

$2,144.58

$2,158.27

$2,171.99

$2,185.75

$2,199.55

$2,213.39

$2,227.26

$2,241.17

$2,255.12

$2,269.11

$2,283.13

Sheet2

steve the fish · Sep 25, 2022

Sorry havent been online for a while. Did you sort?

EthanP · Sep 26, 2022

No, I have not.

steve the fish · Sep 26, 2022

Try this:

=IF(COLUMNS($F$11:F11)>MATCH($B$2,$F$1:$AX$1,0),"",F5)

copied across your values row.

It assumes that the months are text values rather than formatting. If not will need a tweak.

Having an array based on number of months

EthanP

New Member

Excel Facts

steve the fish

Well-known Member

EthanP

New Member

steve the fish

Well-known Member

EthanP

New Member

steve the fish

Well-known Member

EthanP

New Member

steve the fish

Well-known Member

Similar threads

Forum statistics

Share this page

Having an array based on number of months

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock