Cannot figure out how to interpolate revenue values with Excel

hackman2007

New Member
Joined
Nov 7, 2008
Messages
7
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
Joined
Nov 7, 2008
Messages
7
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.
 

Some videos you may like

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.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,365
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 15, 2002
Messages
4,365
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Nov 8, 2008
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,550
Messages
5,548,688
Members
410,865
Latest member
siglertl
Top