Budget Array Formula

mgirvin

Well-known Member
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?

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Dave Patton

Well-known Member
consider regular formula

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

pgc01

MrExcel MVP
Hi Mike

Another option:

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

mgirvin

Well-known Member
Dear pgc01 and Dave Patton,

Thank you very much for your great formulas!!

Replies
1
Views
108
Replies
3
Views
212
Replies
1
Views
434
Replies
5
Views
183
Replies
4
Views
143

1,191,552
Messages
5,987,232
Members
440,086
Latest member
Mahi786

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.

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

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