An algebra/programming question

[Karl]

I'm working on an Excel model and the algebra has me stuck as much as the
programming.

Given the following values

Rate1= 0.833333%

A= 999,999.97

CashFlow1= 10,000.00
CashFlow2= 20,000.00
CashFlow3= 162,860.92

Num1= 2 (i.e. CashFlow1 repeats twice)
Num2= 4
Num3= 6


And this equation:


A=((((CashFlow3*((1-(1+Rate)^-Num3)/Rate))*(1+Rate)^-Num2)+(CashFlow2*(1-(1+Rate)^-Num2))/Rate)*(1+Rate)^-Num1)+(CashFlow1*(1-(1+Rate)^-Num1)/Rate)


I'm trying to rearrange it to solve for "Num2".

Can someone assist me?

These points/observations may be helpful:

Above pattern will repeat from 1.N times

That is, it is nested. The inner most nesting is this:

(CashFlow3*((1-(1+Rate)^-Num3)/Rate))


The next level of nesting takes that result from the above and uses it
in this (2nd level of nesting):

(((CashFlow3*((1-(1+Rate)^-Num3)/Rate))*(1+Rate)^-Num2)+(CashFlow2*(1-(1+Rate)^-Num2))/Rate)


I know that if there were only one CashFlow and one Num to solve for that
the equation would be:

Num = - (ln(1-(A*Rate)/CashFlow) / ln( 1+Rate))

I guess this is going to take a VBA loop. I'm comfortable enough with the
VBA. I don't understand what the algebra is though.

TIA.

 

[John]

At first glance this looks like an internal rate of return problem which
can get pretty dicey. Try "internal rate of return" in google. I forgot
a long time ago.

John

 

[Karl Thompson\(PGS\)]

No, it's not the rate. That's solved by iteration. We have the rate. It's
10% per annum..

 

[John]

Internal rate of return refers to any problem involving uneven cash
flows. What you solve for, the rate, the 3rd cash flow, etc. doesn't matter.

Originally it was a method to find the present value of a series of
investments and returns... all different and at different intervals.

It sounds like you want to solve for cash flow number 2. That is still
an Internal Rate of Return problem.

It is like saying I will invest $10 the first year, 15$ the 3rd and 4th
years, take $5.00 out the fith year. If I end up with $50.00 at the end
of the 6th year, what intrest rate was I earning?

Then... If I want to end up with $60.00 what do I have to invest in the
2nd year. what if I wait till the 4th year. These are all internal rate
of return questions.

There are many methods of solving them... most of them using
approximation methods as I remember. I think it is the equivilant of
solving n deminsional equations... if you have five years you have
somthing like x to the fifth + 22*x to the fifth + etc. etc. They aren't
solvable and have to be approximated...


John

 

[John]

You've set it up in a very difficult form... almost impossible to solve.

try setting it up like this

10,000*(1+rate)^10 + 10,000*(1+rate)^9 + 20,000*(1+rate)^8_
+20,000*(1+rate)^7... and so on.

The whole thing = 999999.97. If you look at it this way, solving for any
one missing dollar amount is trivial.


I'm not sure what you mean by solve for Num2 since you seem to say Num2
is 4. If 4 doesn't work and you are trying to find what number would
work for Num2, then it is a tad more difficult. If that's the case tell
me... it can be done.


John

 

[Dana DeLouis]

Hi Karl. I think the problem is a little hard to do in one shot. I
couldn't really follow your equation.
Most of the other cash flow info can be factored out. I would then just
work on your CashFlow #2 problem separately.
This would make it more flexible and easier to use.
Lets just work with #2.
We know the npv of cash flows (k) over so many months z is...

npv = Sum[k/(1 + r)^m, {m, 1, z}]

(k - k/(1 + r)^z)/r

But we really want to back this up 'm months to our time period zero.
If given a starting value s, and the Cf's start in m months, we really want
to solve for z.
z is Num2 in your question:
Errr..I'm going to have to cheat here. It's late...

Solve[s*(1 + r)^m == npv, z]

z -> -(Log[1 - (r*(1 + r)^m*s)/k]/Log[1 + r])


Now that we have that, here's a macro that will hopefully solve your
equation and return 4 months.

Sub Demo()
Dim r, k, t, z, s
Dim Answer As Single

r = 0.1 / 12

'Group 3
k = 162860.92
z = 6
t = (k - k / (1 + r) ^ z) / r
'Back it up 6 months to time 0
s = t / (1 + r) ^ 6

'Group 1
k = 10000
z = 2
t = (k - k / (1 + r) ^ z) / r
'It's at time 0 already
s = s + t

'Now Group 2
'Find remaining balance
s = 999999.97 - s

k = 20000
t = 2 'It starts in 2 months.
Answer = -(Log(1 - (r * (1 + r) ^ t * s) / k) / Log(1 + r))
End Sub

The answer I get is indeed 4.0 :>)

HTH. :>)

 

[Dana DeLouis]

Just another option might be to use PV.

Sub Demo()
Dim r, k, s, t
Dim Answer As Single ' For small round off error.

r = 0.1 / 12 'Monthly Rate

With WorksheetFunction
s = .PV(r,2,-10000) 'First CashFlow
t = .PV(r,6,-162860.92) '2nd CF.
t = .PV(r,6,0,-t) 'Back up to time 0
End With
s = 999999.97 - s - t

k = 20000
t = 2 'CashFlow starts in 2 months.
Answer = -(Log(1-(r*(1+r)^t*s)/k)/Log(1+r))
End Sub

I get 4.0 Months

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007

<snip>

 

[Dana DeLouis]

Answer = -(Log(1-(r*(1+r)^t*s)/k)/Log(1+r))

Hmmm. I didn't make any assumptions on the variables.
Lets go back and adjust -Log(x) to +Log(1/x).
I like this better. Hence...

Sub Demo()
Dim r, k, s, t
Dim Answer As Single ' For small round off error.

r = 0.1 / 12 'Monthly Rate

With WorksheetFunction
s = .PV(r,2,-10000) 'First CashFlow
t = .PV(r,6,-162860.92) '2nd CF.
t = .PV(r,6,0,-t) 'Back up to time 0
End With
s = 999999.97 - s - t

k = 20000
t = 2 'CashFlow starts in 2 months.
Answer = Log(k/(k-r*(1+r)^t*s))/Log(1+r)
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007

<snip>

 

[Karl]

Thanks Dana & John,

I was out of town and away from this NG for a couple of days. I'm going
to look at your suggestions and I think I'll be able to take it from there.

Thanks again.