SOLVER Function Problem

I am trying to workout a method for calculating the annual compound rate of return for an investment with a single cashflow held over a number of years.

For example:

An initial investment of £20,000
Held for 20 years.
Value after 20 years = £53,065.95

In cell A1 I have put the initial investment.
In cell B1 I have put the value after 20 years
In cell C1 I have put the interest rate
In cell D1 'The Target Cell' I have put the equation =B15*(1/(1+C15)^20). I have also added that this must equal value £20,000 (It really needs to be able to equal cell A1). I have also added a constraint stating A1=D1.

Now this works fine but I need to be able to change cells A1 & B1 and for it to produce the interest rate without running SOLVER everytime.

Not sure if this makes sense but does anyone have any ideas?

You may be able to do this the way you want with a macro. You would need to use the SheetChange Event to trigger the procedure whenever there is a change on the worksheet :

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)
' runs when a sheet is changed
End Sub

(Got the above form the Visual Basic Help files. (Tools|Macro|Visual Basic Editor)

Within this you would need to use the Solver functions. Since I don't have any experience with the using solver either in macros or simply from the worksheet commands/toolbars, maybe you could look at the Visual Basic Help files under 'solver' - it appears to me that it would cover what you need.

Sorry I can't be more specific with the solver side of things.

Hi,

Might be a bit simplistic, but can't you use i=nth root of (FV/PV)-1 to give interest per n periods?

i.e., =((C1/A1)-1)^(1/B1) in cell D1?

Sorry if I'm not helping,

Dave

If I understand u correctly you just want to work out the interest rate, given the starting value, end value and the number of terms

A is initial value
B is end value
C is years

interest=(B/A)^(1/C)-1 by rearranging the formula you do not need solver

=(B1/A1)^(1/C1)-1 should work, in the example the rate is 5% if this is true

Regards

Dav

Thanks guys

That works great.

I have used (B1/A1)^(1/No. of Years)-1

Much appreciated.