Solving two equations with known exponent

[John Anon]

I know how to solve for the exponent when there is one equation. For example:

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

 

[isabelle]

hi John,

knowing that:

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

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

then we know that:

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

and we may conclude that:

(210-100)*(1+ r )^0.5 = (101)*(1+ r )^0.75

(210-100)*(1+ r )^0.5/101 = (1+ r )^0.75

ln((210-100)*(1+ r )^0.5/101)/0.75 = ln(1+ r )

EXP(LN((210-100)*(1+ r )^0.5/101)/0.75)-1 = r

we also know that r is between 0.1 and 0.99999999999999
then we are looking for:

EXP(LN((210-100)*(1+ r )^0.5/101)/0.75)-1 -r = 0


Sub Macro1()
Dim i, y, r, z, p, a, a1, a2, b, b1, b2, FVt, PV1, PV2, t1, t2

FVt = 210
PV1 = 100
PV2 = 101
t1 = 0.5
t2 = 0.75


'r = 0.40697132235729

For i = 1 To 9
p = Exp(Application.Ln((FVt - PV1) * (1 + (i / 10)) ^ t1 / PV2) / t2) - 1 - (i / 10)
If p < 0 Then Exit For
Next

a = i - 1
a1 = CDbl((a) & Application.Rept(0, 7))
a2 = CDbl(Left(a, 1) & Application.Rept(9, Len(a) + 6))

For y = a1 To a2
r = y / 100000000
p = Exp(Application.Ln((FVt - PV1) * (1 + r) ^ t1 / PV2) / t2) - 1 - r
If p < 0 Then Exit For
Next

b = y - 1
b1 = CDbl((b) & Application.Rept(0, 6))
b2 = CDbl(Left(b, 1) & Application.Rept(9, Len(b) + 5))

For z = b1 To b2
r = z / 100000000000000#
p = Exp(Application.Ln((FVt - PV1) * (1 + r) ^ t1 / PV2) / t2) - 1 - r
If p < 0 Then Exit For
Next

MsgBox "r = " & (z - 1) / 100000000000000#
End Sub

now we know that : r = 0.40697132235729 either: 40.697132235729% and not
of 7.2453%

http://cjoint.com/?DCmdtNPsEIq

isabelle

 

[John Anon]

hi John,

knowing that:

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

then we know that:

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

and we may conclude that:

(210-100)*(1+ r )^0.5 = (101)*(1+ r )^0.75

Have to stop you there. The term "(210-100)" is wrong (and therefore the rest of your derivation is also incorrect.

Besides, I can do this with a UDF or a macro easy. I'm looking for a solution I can implement on a worksheet without involving VBA. If you insist on starting that term with a left parenthesis the term has to be "(210)-(100)"so that the term "(1+r)^0.5" is multiplied by -100 but is NOT multiplied by 210.

I guess it just can't be done.

Thanks anyway.

John

 

[isabelle]

ok, the final formula is:

Exp (Ln((FVt - PV_1 * (1 + r_value) ^ t_1) / PV_2) / t_2) - 1

process:
____________________________________________________________________
FVt -PV_1 * (1 + r_value) ^ t_1 = (PV_2) * (1 +
r_value) ^ t_2

(FVt - PV_1*(1+r_value)^t_1)/PV_2 = (1+r_value)^t_2

Ln((FVt - PV_1 * (1 + r_value) ^ t_1) / PV_2) / t_2 = Ln(1 + r_value)

Exp (Ln((FVt - PV_1 * (1 + r_value) ^ t_1) / PV_2) / t_2) - 1 = r_value

=0.0724582318483014
____________________________________________________________________

isabelle

 

[joeu2004]

I guess it [an algebraic solution] just can't be done.

I believe your conclusion is correct. However, FYI....

I know how to solve for the exponent when there is one equation.

Apparently not.

For example:
FV = PV * (1 + r) ^ t
where
FV = 105
PV = 100
t = .5
Solving for r goes something like this: [....]
exp (ln (105/100)/.5) - 1 = r
r = 10.25%

That does work. But more simply:

r = (FV/PV)^(1/t) - 1

for PV<>0, FV<>0, t>0, and sign(PV)=sign(FV).

Nevertheless, that really does help, IMHO.

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]

Equivalently, but better for generality purposes (see below):

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

And one more critical step eliminates FV[2] as well as FV[1], reducing the
problem to one equation with one unknown, to wit:

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

Thus:

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

and

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

I will call the latter the "goal formula".

Even though we have one equation and one unknown, I do not believe we can
solve for r algebraically because we have two exponential factors, to wit:
(1+r)^t[1] and (1+r)^t[2].

We can use goal solver [...], given that:
FVt = 210
PV[1] = 100
PV[2] = 101
t[1] = .5
t[2] = .75
we find that r = 7.2453%

"Goal solver"?!

I get 7.24503560936774% with Goal Seek in Excel 2010. But with that, the
"goal formula" evaluates to 0.000966499420542277, not zero, presumably due
to Goal Seek operational limitations, which do not seem to be tunable.

On the other hand....

I get 7.2458231848293% with Solver and some option changes in Excel 2010.
With that, the "goal formula" evaluates to about 9.66E-13.

With default options, I get 7.24582902509202% for r and -7.17E-06 the first
time, and 7.2458223738745% and 9.95E-07 the second time.

The point is: apparently 7.2458% is the better approximation.

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]

So, the general derivation is:

FVt - Sigma(FV,i=2,...,n) = PV[1]*(1+r)^t[1]

Thus:

FVt = Sigma(PV*(1+r)^t,i=1,...,n)

Again, I do not believe there is an algebraic solution for r because we have
multiple exponential factors of the form (1+r)^t.

 

[isabelle]

Le 2014-03-12 17:41, John Anon a écrit :

Besides, I can do this with a UDF or a macro easy. I'm looking for a solution I can implement on a worksheet without involving VBA.

i can find r (0.01 to 0.9999999999999) to solve any equation equal to 0 with a
macro but not with a UDF,
you tell me: it is easy for you, can you tell more about this udf

here is my solution with a macro but i'm not capable to transform it to a
general function resolvent an equation equal to 0

Sub test()
Dim i, z, a1, a2, r
FVt = 210
PV1 = 100
PV2 = 101
t1 = 0.5
t2 = 0.75
a1 = "0.0"
For i = 0 To 9
r = CDbl(Str("" & a1 & i))
z = Exp(Application.Ln((FVt - PV1 * (1 + r) ^ t1) / PV2) / t2) - 1 - r
If z < 0 Then
a1 = Str("" & a1 & i - 1)
i = Empty
If a2 = z Then Exit For
End If
a2 = z
Next
MsgBox CDbl(a1)
End Sub

here is what i try with Evaluate and name range but is not good

Function r_value(FVt, PV1, PV2, t1, t2, r, equation)
a1 = [r]
For i = 0 To 9
[r_v] = CDbl(Str("" & a1 & i))
z = Evaluate(equation)
If z < 0 Then
a1 = Str("" & a1 & i - 1)
i = Empty
If a2 = z Then Exit For
End If
a2 = z
Next
r_value = CDbl(a1)
End Function

Thanks you for spending time to help me

isabelle