Having an array based on number of months

E

EthanP

New Member
Joined
Sep 10, 2022
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1MonthAcquisitionMonth 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24Month 25Month 26Month 27Month 28Month 29Month 30Month 31Month 32Month 33Month 34Month 35Month 36Month 37Month 38Month 39Month 40Month 41Month 42Month 43Month 44Month 45
2Sale MonthMonth 1YearAcquisitionYear 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 4Year 4Year 4Year 4Year 4Year 4Year 4Year 4Year 4
3IRRMonth Ending1/5/20232/28/20233/31/20234/30/20235/31/20236/30/20237/31/20238/31/20239/30/202310/31/202311/30/202312/31/20231/31/20242/29/20243/31/20244/30/20245/31/20246/30/20247/31/20248/31/20249/30/202410/31/202411/30/202412/31/20241/31/20252/28/20253/31/20254/30/20255/31/20256/30/20257/31/20258/31/20259/30/202510/31/202511/30/202512/31/20251/31/20262/28/20263/31/20264/30/20265/31/20266/30/20267/31/20268/31/20269/30/202610/31/2026
4Equity MultipleInvestment($165,000.00)
5Preferred Return8%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
6LP Split70%Cap Rate on sale (is going in cap rate)6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%
7GP Spilt30%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
8Sale 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
9Debt 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)
10Equity 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
11values
Sheet2
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
What do you mean? It sounds like you want a single result but you have loads of result cells.
 
Upvote 0
Yes, I have a load of result cells based on the sale month
 
Upvote 0
Thats not helpful. I cant guess what you need. What goes in F11 if B2 says Month 24? Also G11?
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
1MonthAcquisitionMonth 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16Month 17Month 18Month 19Month 20Month 21Month 22Month 23Month 24Month 25Month 26Month 27Month 28Month 29Month 30Month 31Month 32Month 33Month 34Month 35Month 36Month 37Month 38Month 39Month 40Month 41Month 42Month 43Month 44Month 45
2Sale MonthMonth 1YearAcquisitionYear 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 1Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 2Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 3Year 4Year 4Year 4Year 4Year 4Year 4Year 4Year 4Year 4
3IRRMonth Ending1/5/20232/28/20233/31/20234/30/20235/31/20236/30/20237/31/20238/31/20239/30/202310/31/202311/30/202312/31/20231/31/20242/29/20243/31/20244/30/20245/31/20246/30/20247/31/20248/31/20249/30/202410/31/202411/30/202412/31/20241/31/20252/28/20253/31/20254/30/20255/31/20256/30/20257/31/20258/31/20259/30/202510/31/202511/30/202512/31/20251/31/20262/28/20263/31/20264/30/20265/31/20266/30/20267/31/20268/31/20269/30/202610/31/2026
4Equity MultipleInvestment($165,000.00)
5Preferred Return8%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
6LP Split70%Cap Rate on sale (is going in cap rate)6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%6%
7GP Spilt30%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
8Sale 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
9Debt 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)
10Equity 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
11values$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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
XIRR but values are from two different cell ranges
Replies
2
Views
1K
EthanP
E
T
  • Question Question
Calculate the Date of a Receipt Based on the Date of the Receipt in the Previous Month
Replies
0
Views
1K
Tegglet
T
F
  • Question Question
Dynamic XIRR Forumla Help
Replies
1
Views
890
frigo191
F
H
  • Question Question
Complicated Calculation
Replies
6
Views
2K
Eric W
Eric W

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top