Cannot figure out how to interpolate revenue values with Excel

[hackman2007]

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]

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.

 

[c_m]

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]

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]

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

 

[Dave Patton]

Look at RATE function and FV.

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

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

 

[Dave Patton]

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]

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