CODEWITHSUNDEEP
vbaexcel
socool111
socool111

Reputation: 113

solving for a variable, based on when another variable equals a specific number

I have the below vba function. It currently is a NPV function, but I need to convert it to an IRR function, which returns a rate for when the NPV = 0 (in other words, what will the rate be, to make the below function's sum = 0)

I know you can use solver in VBA, but any research I have done has got me lost in terms of the syntax.

Is there a way to do this without solver (perhaps and IF statement, or something of the likes). I am not familiar enough with VBA to know all the argumentive operations available.

I would prefer a written answer of what is the best way to approach, as oppose of doing it for me (so I can learn VBA)...if I must use solver, then I do need help with the syntax of it

Public Function myIRR(cashFlows As Range) As Double

Dim myArray(50) As Double 
Dim i As Integer         
Dim sum As Double         
Dim rate As Single

i = 0                    

For Each elem In cashFlows                             
    myArray(i) = elem.Value                               
    sum = sum + myArray(i) / (1 + rate) ^ i
    i = i + 1
Next Elem
End Function

Upvotes: 0

Views: 212

Answers (1)

steveo40
steveo40

Reputation: 931

Ok, I was a bit bored this afternoon (waiting for stuff to calculate). So here's my quick and dirty approach. It uses a bisection method, which isn't very efficient, but should get you to the answer. I haven't tested this other than confirming that it gets the same answer as the IRR function in Excel using the little example from Wikipedia (http://en.wikipedia.org/wiki/Internal_rate_of_return), so make sure you test it thoroughly.

Public Function MyIRR(cashflows As Range) As Double
'doing a binary search - there are way more efficient ways of solving this

Dim rate As Double, rateLow As Double, rateHigh As Double, npv As Double
Dim counter As Long

rateLow = 0
rateHigh = 1
rate = 0.5

Do Until counter > 100

    npv = MyNPV(cashflows, rate)

    If Abs(npv) < 10^ - 9 Then
        MyIRR = rate
        Exit Function
    End If

    If npv < 0 Then
        rateHigh = rate
    Else
        rateLow = rate
    End If

    rate = (rateHigh - rateLow) / 2 + rateLow

    counter = counter + 1
Loop

'if it gets here then something has gone wrong
MyIRR = xlErrNum

End Function

Public Function MyNPV(cashflows As Range, rate As Double) As Double
'assumes the cashflows are stored vertically in a range, and the first one happens at period 0

Dim i As Long

For i = 1 To cashflows.Rows.Count
    MyNPV = MyNPV + cashflows(i, 1) * (1 + rate) ^ -(i - 1)
Next

End Function

Upvotes: 1

Related Questions