J

#### John Anon

FV = PV * (1 + r) ^ t

where

FV = 105

PV = 100

t = .5

Solving for r goes something like this:

105 = 100 * (1 + r) ^ .5

105/100 = (1 + r) ^ .5

ln (105/100) = ln (1 + r ^ .5)

ln (105/100) = .5 * ln (1 + r)

ln (105/100) / .5 = ln (1 + r)

exp (ln (105/100)/.5) = 1 + r

exp (ln (105/100)/.5) - 1 = r

r = 10.25%

But let's say that there are two equations, rather than one. Of course, there have to be two unknowns also, rather than one. So let's say we have the following two equations:

FV[1] = PV[1] * (1 + r) ^ t[1]

FV[2] = PV[2] * (1 + r) ^ t[2]

But instead of knowing FV[1], FV[2], PV[1], PV[2], t[1] and t[2] all you know is PV[1], PV[2], t[1], t[2] and FVt where FV[1] + FV[2] = FVt.

Given that FV[2] = FVt - FV[1] one can rewrite the above two formulas as:

FV[1] = PV[1] * (1 + r) ^ t[1]

FVt - FV[1] = PV[2] * (1 + r) ^ t[2]

We can use goal solver with the following to solve for r, given that:

FVt = 210

PV[1] = 100

PV[2] = 101

t[1] = .5

t[2] = .75

we find that r = 7.2453%

Can I develop an algebraic solution using LN, *, /, + and - so that my spreadsheet doesn't need goal solver to arrive at the value for r?

In the real world example I am working on there are a finite (certainly less than 100) but significant number of formulas, rather than the two shown above.

The generalized version of the formulae are:

FV[1] = PV[1] * (1 + r) ^ t[1]

FV[2] = PV[2] * (1 + r) ^ t[2]

FV[..] = PV[..] * (1 + r) ^ t[..]

FV[x] = PV[x] * (1 + r) ^ t[x]

Where all PV[1..x] and t[1..x] are known while FVt is known where FV[1] + FV[2] + ... + FV[x] = FVt

Any help would be greatly appreciated.

Yes, I know I could develop a VBA UDF to help, but I'd rather keep this to native excel.

Thanks

John