Excel Formula

[7points]

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.

Answers:

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.

 

[texasalynn]

try this

Excel Workbook
	A	B	C	D
2	*	*	*	*
3	1st period	1,000,000	0	1,000,000
4	2nd period	1,000,000	100,000	1,100,000
5	3rd period	1,100,000	100,000	1,200,000
6	4th period	1,200,000	100,000	1,300,000
7	5th period	1,300,000	100,000	1,400,000
8	6th period	1,400,000	100,000	1,500,000
9	7th period	1,500,000	100,000	1,600,000
10	8th period	1,600,000	100,000	1,700,000
11	9th period	1,700,000	100,000	1,800,000
12	10th period	1,800,000	100,000	1,900,000
13	11th period	1,900,000	100,000	2,000,000
14	12th period	2,000,000	100,000	2,100,000
15	13th period	2,100,000	100,000	2,200,000
16	14th period	2,200,000	100,000	2,300,000
17	15th period	2,300,000	100,000	2,400,000
18	16th period	2,400,000	100,000	2,500,000
19	17th period	2,500,000	100,000	2,600,000
20	18th period	2,600,000	100,000	2,700,000
21	19th period	2,700,000	100,000	2,800,000
22	20th period	2,800,000	100,000	2,900,000
23	21st period	2,900,000	100,000	3,000,000
24	22nd period	3,000,000	100,000	3,100,000
25	23rd period	3,100,000	100,000	3,200,000
26	24th period	3,200,000	100,000	3,300,000
27	25th period	3,300,000	100,000	3,400,000
28	26th period	3,400,000	100,000	3,500,000
29	27th period	3,500,000	100,000	3,600,000
30	28th period	3,600,000	100,000	3,700,000
31	29th period	3,700,000	100,000	3,800,000
32	30th period	3,800,000	100,000	3,900,000
33	31st period	3,900,000	100,000	4,000,000
34	32nd period	4,000,000	100,000	4,100,000
35	33rd period	4,100,000	100,000	4,200,000
36	34th period	4,200,000	100,000	4,300,000
37	35th period	4,300,000	100,000	4,400,000
38	36th period	4,400,000	100,000	4,500,000
39	37th period	4,500,000	100,000	4,600,000
Sheet3

then copy the formulas down

HTH

 

[7points]

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.

 

[7points]

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.

 

[texasalynn]

??? what suggestions? Did you cross post?

 

[Dave Patton]

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)

 

[7points]

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!!

 

[7points]

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.

 

[Ragnar1211]

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.

 

[Dave Patton]

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.