Excel Formula


New Member
Mar 16, 2009
I have been trying to figure out how to solve this in Excel:

Initial investment= 1,000,000 (E17), incremental increase 100,000 (F17), for a total of 4 periods (B17). no interest involved.


1st period: $1,000,000 + 0 = $1,000,000
2nd period: 1,100,000 + 100,000 = 1,100,000
3rd period: 1,100,000 + 100,000 = 1,200,000
4th: 1,200,000 + 100,000= 1,300,000

final answer is = $4.6 Million

Thanks so much if someone could help me.

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
try this

Excel Workbook
31st period1,000,00001,000,000
42nd period1,000,000100,0001,100,000
53rd period1,100,000100,0001,200,000
64th period1,200,000100,0001,300,000
75th period1,300,000100,0001,400,000
86th period1,400,000100,0001,500,000
97th period1,500,000100,0001,600,000
108th period1,600,000100,0001,700,000
119th period1,700,000100,0001,800,000
1210th period1,800,000100,0001,900,000
1311th period1,900,000100,0002,000,000
1412th period2,000,000100,0002,100,000
1513th period2,100,000100,0002,200,000
1614th period2,200,000100,0002,300,000
1715th period2,300,000100,0002,400,000
1816th period2,400,000100,0002,500,000
1917th period2,500,000100,0002,600,000
2018th period2,600,000100,0002,700,000
2119th period2,700,000100,0002,800,000
2220th period2,800,000100,0002,900,000
2321st period2,900,000100,0003,000,000
2422nd period3,000,000100,0003,100,000
2523rd period3,100,000100,0003,200,000
2624th period3,200,000100,0003,300,000
2725th period3,300,000100,0003,400,000
2826th period3,400,000100,0003,500,000
2927th period3,500,000100,0003,600,000
3028th period3,600,000100,0003,700,000
3129th period3,700,000100,0003,800,000
3230th period3,800,000100,0003,900,000
3331st period3,900,000100,0004,000,000
3432nd period4,000,000100,0004,100,000
3533rd period4,100,000100,0004,200,000
3634th period4,200,000100,0004,300,000
3735th period4,300,000100,0004,400,000
3836th period4,400,000100,0004,500,000
3937th period4,500,000100,0004,600,000

then copy the formulas down

Upvote 0
I was hopeful there was an equation that would do this for "n" periods. Like in one formula. The time periods, initial cost and incremental costs change constantly. I may have to use the chart method you show and just add up the ending figures to get a total. Thanks.
Upvote 0
I am new to the boards and even though i had searched the three places recommending, i didn't find the answer. But now i have and I will be more capable of finding answers to common questions without posting. Sorry for posting something that was previously posted. I know I always hate when people do that.
Upvote 0
Your question isn't very clear to me. 4 Periods?

A2 Initial ...... B2 .. 1,000,000
A3 Increment B3 100,000
A4 Final ........B4 4,600,000

A5 Periods =(B4-B2)/B3 or =NPER(0,B3,B2,-B4)
Upvote 0
Instead of using periods i will restate the question differently. That is probably confusing the way i have it.

I have a product which costs $1.0 million. As i purchase additional units, the cost increases $100,000 for each unit. The number i purchase varies.

If i purchase 4 units I want to get the total cost. This is what i got for this example:

Unit 1: $1,000,000
Unit 2: 1,100,000
Unit 3: 1,200,000
Unit 4: 1,300,000

For an answer of $4,600,000. I was trying to come up with an answer where each of these would vary and still get a total.

I thought i had it, but now i'm getting #REF1 and am confused. Will do some more research.

Thanks for any help!!
Last edited:
Upvote 0
I'm not sure what cross posts mean, but i didn't post it elsewhere. I just searched and finally found something i thought might answer the question, now i've lost it. Am still searching.
Upvote 0
formula = (base amount * years) + (bonus * (years * (years -1))/2

formula = (1,000,000 * 4 ) + (100,000 * (4 * 3)/2

Answer = 4,600,000

I've checked this formula all the way to 15 years, it works good. The only time the formula fails is if it is the first year and there is no bonus amount.
Last edited:
Upvote 0
Using Arithmetic =Initial+Increment*(GrossUnits-1) 1,300,000 or

Using Excel Function =-FV(0,GrossUnits-1,Increment,Initial) 1,300,000

If this doesn't help, please provide a clear example or use tools mentioned above the first message to post a concise example.
Upvote 0

Forum statistics

Latest member

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