Budget Array Formula

[mgirvin]

Dear Most Amazing Excel Masters,

I have some assumptions for a Jan. to Feb. budget in the cell range A1:B8:

Sales Jan Start 5,000.00
Monthly Sales % Increase 0.05
Ex1 0.025
Ex2 0.2125
Ex3 0.1
Ex4 0.135
Ex5 0.05
Ex6 0.065

In cell D1 I have calculated the Net Income to be 32,829.07 with the array formula:

{=SUM(B1*(1+B2)^(ROW(INDIRECT("1:12"))-1))-SUM(SUM(B1*(1+B2)^(ROW(INDIRECT("1:12"))-1))*B3:B8)}

Since I am using the formula element, SUM(B1*(1+B2)^(ROW(INDIRECT("1:12"))-1)), twice, is there a more efficient, compact way to create this array formula?

 

[Dave Patton]

consider regular formula

=FV(B2,12,-B1)*(1-SUM(B3:B8))

 

[pgc01]

Hi Mike

Another option:

=B1/B2*((1+B2)^12-1)*(1-SUM(B3:B8))

 

[mgirvin]

Dear pgc01 and Dave Patton,

Thank you very much for your great formulas!!