Exponential Growth in Excel

shakeydude

Hi,

Can someone help me with creating a growth curve in excel which I want to follow an exponential growth path.

I have my initial value, zero, and I know that in 10 years time I will have a value of 260,000. Can Excel fill in the gaps such that it follows an exponential curve?

Sorry if this question is a bit dense, I used to have a good grasp of Maths but all this working lark has taken it from me:o

Shakeydude

(Also if anyone has some good on-line resources for Excel let me know)

MathsManiac

Well, for a start, your initial value of 0 is no use, because no amount of exponential growth will make 0 any bigger than 0.

Suppose your initial value was 100. Then you want to find an annual "growth factor" r such that 100 * r^10 = 260000. You can solve this for r:
r^10 = 2600
r = 2600^(1/10)
r = 2.195317477.

If you wanted to see each year in the growth, you could put 100 in cell A1, then in cell A2, put =A1*2.195317477, then fill down from cell A2 to cell A11.

You could make this more general. I've taken the liberty in the attached. Is this the kind of thing you want?

bnt

Can I assume that it's some kind of investment plan, where you make regular payments? I mean, there's no interest rate that will turn an initial value of zero in to anything! A pure exponential function cannot pass through zero, ever.

If it's some kind of investment calculation, with compound interest, Excel has a class of functions specifically for that under the Financial heading. You have a Present Value (PV) of zero, a Future Value (FV) of 260,000, and a time of 10 years. The calculation is not going to work without some more information: the interest rate (and its period), or the payment per period. If you have one, you can calculate the other, given the Present and Future values.

For example, say the interest is 5% per year, calculated annually; then your number of periods is 10. The PMT function will give you the payment if you give it the interest, number of periods, PV and FV: =PMT(5%,10,0,260000) result: -20671.19 per annum: divide by 12 if you want monthly payments (-1722.60).

The other way round is the RATE function e.g. if you know you want to put in 20000 per year, you say =RATE(10,-20000,0,260000), and that tells you the rate must be 5.706% if it's going to hit 260,000 after 10 years.

If the interest is applied monthly, you adjust the periods and the interest accordingly: the functions use interest per period and number of periods, and they don't care what the period actually is - fortnight, month, year, whatever e.g 10 years is 120 months, but then you'd need to use the interest rate per month e.g. APR divided by 12.

Once you have all the figures, you can draw up a table, add up the interest on the principal and the payment. This is a simple example, but you'll see Value follow an exponential curve upwards.

Period | Value | Interest | Payment
0 | 0.00 | 0.00 | 20,671.19
1 | 20,671.19 | 1,033.56 | 20,671.19
2 | 42,375.94 | 2,118.80 | 20,671.19
3 | 65,165.92 | 3,258.30 | 20,671.19
4 | 89,095.41 | 4,454.77 | 20,671.19
5 | 114,221.37 | 5,711.07 | 20,671.19
6 | 140,603.63 | 7,030.18 | 20,671.19
7 | 168,305.00 | 8,415.25 | 20,671.19
8 | 197,391.44 | 9,869.57 | 20,671.19
9 | 227,932.20 | 11,396.61 | 20,671.19
10 | 260,000.00 | |

shakeydude

MathsManiac wrote: »

Well, for a start, your initial value of 0 is no use, because no amount of exponential growth will make 0 any bigger than 0.

Suppose your initial value was 100. Then you want to find an annual "growth factor" r such that 100 * r^10 = 260000. You can solve this for r:
r^10 = 2600
r = 2600^(1/10)
r = 2.195317477.

If you wanted to see each year in the growth, you could put 100 in cell A1, then in cell A2, put =A1*2.195317477, then fill down from cell A2 to cell A11.

You could make this more general. I've taken the liberty in the attached. Is this the kind of thing you want?

Thanks MM,

That is what I am looking at. When I graph your spreadsheet it follows an exponential line, how can I make it more like logistic curve?

MathsManiac

The curve is simply a consequence of the underlying rule regarding how the variable is changing over time.

Once you know either the variable as a function of time, or indeed have a rule for relating the value of the variable in one year to its value in the previous year, then you can model it with a suitable formula in Excel.

I've added some more columns that might be what you want. Is it population modelling?

shakeydude

Thanks again MM,

The problem is in terms of population growth but specifically in terms of the penetration of electric vehicles in Ireland which is supposed to be 10% (or 260,000 vehicles) by 2020.

Shakeydude

shakeydude

Actually from some reports I have seen the expected growth rate is estimated to follow "technology s-curve along a logistical sigmoid
described by: % target achieved = 2 / (1 + eT-t), where T is the length of the period from 2010 to the target date and t is the annual
progress toward that target date."

This should sort me out. Thanks for your help