Solved: Internal rate of return Function

[blue_bogdan]

Hello again guys and girls or girls and guys...

I have another 'small' issue... I am trying to rewrite/create a proper IRR function so I can enter primary data that represent the formation of the cash flows....

I already re-written the NPV function so it can compute the NPV for a given set of primary data...

Please advise... : pray2:

[Oorang]

From your question it sounds like you are wanting someone to write the IRR function for you, or were you being more specific?

[blue_bogdan]

From your question it sounds like you are wanting someone to write the IRR function for you, or were you being more specific?

Not necesarily write the entire function, just provide some guide-lines... at least... i know that it has to be related to the NPV function... but i do not know how...

I wrote the NPV function somewhat like this:

NPV = f(Investment, Period, Income, Cost, Tax_Rate, Discount_Rate)

Thanks again...

[Oorang]

Why aren't you just using the built in MIRR function?

[blue_bogdan]

It can not do what i require it to do... I am trying to do the IRR taking into account the same factors i took into account for the custom NPV function.

[Oorang]

:rolleyes: Well that's all good and well, but if you want anyone to help you out, I think your going to need to tell people exactly what your looking to accomplish instead of "I want a custom function that takes into account my other customer function, can I have some guidelines?" ;)

[blue_bogdan]

Yes... you are right.....

Here is my NPV function


Function F_NPV(Inv, Per, TXr, Inc, Cst, Dr)

Dim EBITDA(), EBIT(), Tax(), EAT(), CF(), Flow, VF()
ReDim EBITDA(1 To Per), EBIT(1 To Per), Tax(1 To Per), EAT(1 To Per), CF(1 To Per), VF(1 To Per)

Depr = Inv / Per
For i = 1 To Per
EBITDA(i) = Inc(i) - Cst(i)
EBIT(i) = EBITDA(i) - Depr
Tax(i) = EBIT(i) * TXr
EAT(i) = EBIT(i) - Tax(i)
CF(i) = EAT(i) + Depr
VF(i) = CF(i) / ((1 + Dr) ^ i)
Flow = Flow + VF(i)
Next i
F_NPV = Round((-Inv + Flow), 3)

End Function


I need a function for IRR that takes into account Inv, Per, TXr, Inc, Cst

Thank You

[blue_bogdan]

I have done it.... maybe is not the best function ever but it does the job

Function F_IRR(Inv, Per, TXr, Inc, Cst)

For a = 0 To 10 Step 0.1
If (F_NPV(Inv, Per, TXr, Inc, Cst, a) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, a + 0.1) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, a) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, a + 0.1) < 0) Then
Exit For
End If
Next a

For b = a To a + 0.1 Step 0.01
If (F_NPV(Inv, Per, TXr, Inc, Cst, b) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, b + 0.01) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, b) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, b + 0.01) < 0) Then
Exit For
End If
Next b

For c = b To b + 0.01 Step 0.001
If (F_NPV(Inv, Per, TXr, Inc, Cst, c) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, c + 0.001) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, c) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, c + 0.001) < 0) Then
Exit For
End If
Next c

For d = c To c + 0.001 Step 0.0001
If (F_NPV(Inv, Per, TXr, Inc, Cst, d) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, d + 0.0001) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, d) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, d + 0.0001) < 0) Then
Exit For
End If
Next d

For e = d To d + 0.0001 Step 0.00001
If (F_NPV(Inv, Per, TXr, Inc, Cst, e) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, e + 0.00001) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, e) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, e + 0.00001) < 0) Then
Exit For
End If
Next e

For f = e To e + 0.00001 Step 0.000001
If (F_NPV(Inv, Per, TXr, Inc, Cst, f) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, f + 0.000001) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, f) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, f + 0.000001) < 0) Then
Exit For
End If
Next f

For g = f To f + 0.000001 Step 0.0000001
If (F_NPV(Inv, Per, TXr, Inc, Cst, g) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, g + 0.0000001) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, g) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, g + 0.0000001) < 0) Then
Exit For
End If
Next g

For h = g To g + 0.0000001 Step 0.00000001
If (F_NPV(Inv, Per, TXr, Inc, Cst, h) < 0 And F_NPV(Inv, Per, TXr, Inc, Cst, h + 0.00000001) > 0) Or (F_NPV(Inv, Per, TXr, Inc, Cst, h) > 0 And F_NPV(Inv, Per, TXr, Inc, Cst, h + 0.00000001) < 0) Then
Exit For
End If
Next h

If h > 5 Then
h = "Can not be displayed"
F_IRR = h
Else
F_IRR = (h + h + 0.00000001) / 2
End If

End Function