Writing an IRR function in VBA

[annawilliams7227]

Hey guys,

I am wondering if there is an easy way to write your own IRR function using VBA.
Say if I call it Function MYIRR(cfs,per). Where cashflows occur in the periods specified..
i.e
Periods: 0 1 3 4 6
Cashflows: -1000 300 500 100 200

I'm having trouble making sure the initial cash outlay and then cash inflows assumptions hold (problem with the -/+ sign).
I've been told by a friend to try and use abs() function, 'For..Next' loop construct inside a 'Do...While' loop.
but have got myself very confused.

Thanks so much!

 

[Norie]

Why write your own function when you can access IRR and MIRR using Application.WorksheetFunction?

 

[annawilliams7227]

i wish it was that easy!
i have to write my own VBA for it, not having any luck at all

 

[Norie]

Why do you need to write your own code for something that is available through built-in functionality?

 

[jim may]

There is an XIRR() function (args)-->> (CashFlow, Dates) see Help...

As suggested your could use code as follows:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/16/2009 by Jmay
'
' With Cell D2 as Activecell (Target for your formula)
'and
'
    ActiveCell.FormulaR1C1 = "=XIRR(RC[-3]:R[19]C[-3],RC[-2]:R[19]C[-2])"

End Sub

XIRR_Function_Used.xls
	A	B	C	D
1	(OutFlow)ORInFlow	Date		XIRR
2	-1000	1/1/2009		71.36%
3	300	2/1/2009
4	500	3/1/2009
5	100	4/1/2009
6	200	5/1/2009
7
Sheet1

 

[jim may]

sorry, failed to change previous range, s/b

"=XIRR(RC[-3]:R[4]C[-3],RC[-2]:R[4]C[-2])"

 

[MickG]

Hi, See if this helps. This is based on My understanding of the "IRR" function and relates to the Example in Excel Help.
Data as shown Below with My code to simulate the function.
Nb:- The Help example Answer = 9% and if you remove the last value (26000) The answer = -2%

[COLOR=royalblue][B]Column(A) [/B][/COLOR][COLOR=royalblue][B]Column(B)                      [/B][/COLOR]
-70000     Initial cost of a business     
12000      Net income for the first year  
15000      Net income for the second year 
18000      Net income for the third year  
21000      Net income for the fourth year 
26000      Net income for the fifth year

[COLOR=navy]Sub[/COLOR] MG16Aug25
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, Rng2 [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] x, P, Rs [COLOR=navy]As[/COLOR] Double
    '[COLOR=green][B]Set rng coloumn "A"[/B][/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    '[COLOR=green][B] First cell in rng = "Principle" Amount +-[/B][/COLOR]
    '[COLOR=green][B]Rng2 = column "A" range starting row 2[/B][/COLOR]
[COLOR=navy]Set[/COLOR] Rng2 = Rng.Offset(1).Resize(Rng.Count - 1)
'[COLOR=green][B]Percentage rate "x" starts as minus value[/B][/COLOR]
x = -1
'[COLOR=green][B]Loop until Final Value - Last value Is within +- 10% of Last value[/B][/COLOR]
[COLOR=navy]Do[/COLOR] Until Rs <> 0 And Rs > 0 - (Rng(Rng.Count) * 0.05) And Rs < 0 + (Rng(Rng.Count) * 1.01)
    '[COLOR=green][B]P = principle[/B][/COLOR]
    P = Abs(Rng(1))
        Rs = 0
            x = x + 0.01
'[COLOR=green][B]Continally loop through rng until Criterial on "Do loop" Met[/B][/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng2
    '[COLOR=green][B]Rs = result at after each period[/B][/COLOR]
    Rs = (P * (1 + x)) - Dn
        '[COLOR=green][B]P value increased every loop[/B][/COLOR]
        P = Rs
[COLOR=navy]Next[/COLOR] Dn
'[COLOR=green][B]Set P to Zero after each "Do Loop"[/B][/COLOR]
P = 0
[COLOR=navy]Loop[/COLOR]
 x = Format(x, "0.00%")
MsgBox x
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[annawilliams7227]

Great, thanks guys.
Apparently I'm required to write a function, but will use sub routine and try and see if i can manipulate it into a function.. will most likely have no luck but worth a try!

Help is greatly appreciated.

 

[MickG]

Hi, Here's the function:-
Enter as :- =MyIRR( Select range here)

Function MyIRR(Rng [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] x, P, Rs [COLOR="Navy"]As[/COLOR] Double
 [COLOR="Navy"]Set[/COLOR] Rng2 = Rng.Offset(1).Resize(Rng.Count - 1)
x = -1
[COLOR="Navy"]Do[/COLOR] Until Rs <> 0 And Rs > 0 - (Rng(Rng.Count) * 0.05) And Rs < 0 + (Rng(Rng.Count) * 1.01)
    P = Abs(Rng(1))
        Rs = 0
            x = x + 0.01
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng2
    Rs = (P * (1 + x)) - Dn
        P = Rs
[COLOR="Navy"]Next[/COLOR] Dn
P = 0
[COLOR="Navy"]Loop[/COLOR]
 x = Format(x, "0.00%")
MyIRR = x
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[annawilliams7227]

Thank you so much mike,
That looks like a very intense function.
Unfortunately I haven't had any luck with getting an answer, it just comes up with value.
My periods and cashflows are presented horizontally so that could be a problem.
I also just assumed that the range would be my periods and cashflows so i highlighted from E18:I19.

Thank u for all ur help.