Excel help

[Corsair 115]

Hopefully some Excel guru here will help me out with this. Here's the situation:

I have some numeric values which correspond to certain years, but I only have values for the starting and ending year. I'm trying to find the average yearly growth rate which will yield the final year's value.

For example, say I have a starting value of $100 in 1970, and a value of $123 for 1977. What I want to know is that is the average annualized growth rate which will take an initial starting value of $100 in 1970 and result in a value of $123 in 1977 (in this case, the answer is 1.03).

So far I've been figuring this sort of thing out manually, but there must be a formula or worksheet function which will do the job. Any suggestions?

 

[Yalius]

A1=End value
A2= Beginning Value
B1= End year
B2=Start year

AnnualRate=Power((A1/A2),(1/(B1-B2)))

What this does is take the xth root (where x is the number of years elapsed) of the ratio of the final value to the initial value; since Excel doesn't (to my knowledge) have a direct function for arbitrary roots, it exponentiates to 1/x.

 

[rjh01]

You rang? I know everything (almost, well I know how to spell it) about Excel, not about Maths though.

Click on Insert / Function then click on Financial. There are a number of functions that calculate interest rates. One of which is the RATE function. The help says in part

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.

Syntax

RATE(nper,pmt,pv,fv,type,guess)

For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.

Nper is the total number of payment periods in an annuity.

Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.

Pv is the present value — the total amount that a series of future payments is worth now.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type is the number 0 or 1 and indicates when payments are due.
<snip>
Example

To calculate the rate of a four-year $8,000 loan with monthly payments of $200:

RATE(48, -200, 8000) equals 0.77 percent

This is the monthly rate, because the period is monthly. The annual rate is 0.77%*12, which equals 9.24 percent.

 

[shadron]

Hopefully some Excel guru here will help me out with this. Here's the situation:

I have some numeric values which correspond to certain years, but I only have values for the starting and ending year. I'm trying to find the average yearly growth rate which will yield the final year's value.

So far I've been figuring this sort of thing out manually, but there must be a formula or worksheet function which will do the job. Any suggestions?

You only need the starting value, the ending value and the time interval between them in terms of the interest period. The basic formula is (Compound Interest^WP):

where FV is the final amount, PV is starting (principle) value, i is the simple interest rate per period, and n is the number of periods per year. To compute the rate, algebraic manipulation yields

which is essentially the equation that Yalius gave you above. In Excel, you can use the RATE function as rjh01 mentions. Rather than trying to solve the equation in a closed form, RATE uses a numerical iteration method, and a good guess value given it will help the result converge quickly to the correct value.

As an aside, I have never been able to straighten out in my mind what the difference between simple and compound interest was. Business has always been a hurdle for me - don't even ask me about insurance. In examining this question, it finally dawns on me that the difference is only in the way that the interest rate is defined. As I saw on a wiki discussion page:

You are misunderstanding the difference between simple and compound. When the compounding period is one year, they are the same, because the point of differentiating them is to ensure we can translate one to another. Think of them as being different languages to measure the same thing. The 'thing' (interest) doesn't change. But they use different words (measures of %) to express the same thing.

Simple interest is denoted with a fixed rate of interest per period for a number of periods. Compound interest has a somewhat different rate in terms of years rather than periods, and the periods are assumed to be an integral subdivision of years (like quarters or months). In the two cases the FV, PV and i are all the same. I thank you for driving me to this understanding (and, yes, for those MBA types out there, "duh".) Another voyage into the unknown for me complete. Love this forum.

 

[mooseypops]

May I suggest Tools/Goal Seek - read up on the Help files, but basically it helps you work out certain numbers if certain circumstances exist. Scenarios may also be an option.

 

[Corsair 115]

Yalius, thanks! That works great. Thanks too to everyone else for their contributions.