# Cannot figure out how to interpolate revenue values with Excel

#### hackman2007

##### New Member
Hey everyone, I have a question on data interpolation with Excel 2007. Normally, this wouldn't be too much of an issue for me, but for some reason I cannot figure it out.

I'm working on an Income Statement, which is designed for 5 years. I have values 1 and 5, which are given to me (B7: 3,500,000 and F7: 5,200,000). I'm trying to figure out the other 3 years of revenue assuming a growth trend. The values should fill the series B7:F7.

Can anyone help me out on this?

Thanks.

#### hackman2007

##### New Member
The values you posted are results of linear trend. So I am not sure why are you looking for non-linear behavior?

The difference between the different years is not exactly the same, wouldn't linear be a set increase? Each year after it gains by slightly more.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### c_m

##### Well-known Member
try this:

=EXP(INTERCEPT(LN({3500000;5200000}),{1;5})+SLOPE(LN({3500000;5200000}),{1;5})*RIGHT(C6,1))

so i guess its exponential trend..huh?

#### hackman2007

##### New Member
try this:

=EXP(INTERCEPT(LN({3500000;5200000}),{1;5})+SLOPE(LN({3500000;5200000}),{1;5})*RIGHT(C6,1))

Thanks!

That formula worked perfectly.

#### Armando Montes

##### Well-known Member
Try this one: =((5200000/3500000)^(1/4)-1)*100

#### Dave Patton

##### Well-known Member

Look at RATE function and FV.

In P7 =RATE(4,0,3500000,-5200000)

C7 =FV(\$P\$7,1,0,-B7)

#### Dave Patton

##### Well-known Member
You can calculate the Growth Rate and then apply the rate as you prefer

Growth Rate in P7 =RATE(4,0,3500000,-5200000)

C7 =FV(\$P\$7,1,0,-B7) or =(1+Growth)*B7 or =(1+\$P\$7)*B7

#### matrix99999

##### New Member
normaly the interpolation should be

Year1 : A1
Year5: A5
Value1: B1
Value5: B5

for year2
(B\$5\$-B\$1\$)*(A2-A\$1\$)/(A\$5\$-A\$1\$)+F\$7\$

this formula is the good one for interpolation

Replies
7
Views
134
Replies
4
Views
117
Replies
5
Views
233
Replies
8
Views
190
Replies
4
Views
120