Stephen, You're quite correct. I threw those functions together to give Anthony something to work from, rather than attempting to write a bullet-proof algorithm (not my strength anyway).
A couple of points to note (which I passed on off-list): i) Ideally, the algorithm should narrow in on the correct value for IRR using increments that progressively decrease rather than what I've shown - which is a straight incremental change. ii) It is possible to calculate two or more 'optimum' solutions for IRR if the sign of the cashflows change, excluding the first cashflow, which is typically negative anyway. iii) The algorithm works best if you can give it a starting point that is approximate to the final solution. iv) The optimal value for IRR should be positive rather than negative. v) The aim is to get a value for IRR that gives NPV=0, but I was a bit too rushed to try getting the calculation working that well, which would also have required me using the progressively decreasing increment approach described above. Observant readers might also have picked up on the fact that other numerical methods of interpolation would work better than the approach I've supplied - that is they would narrow in on the solution with less iterations. Again, I beg a lack of time and talent ;-) Best Regards, Steve Baty ---------------------------------------------- steve baty senior analyst red square :: internet design & development www.redsquare.com <ph>+612 8596 4030 <fax>+612 8596 4001 <mob>+61 417 061 292 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stephen Parengkuan Sent: Thursday, 26 June 2003 11:50 To: CFAussie Mailing List Subject: [cfaussie] Re: OT : How to IIR in CF or Access ? Yes, the Javascript code is basically the formula to calculate IRR (which is the rate of discount that makes NPV = 0) by trial and error. However, when I tried to run it using IE 5.5 I got an "Out of stack space" error. I suspect because the calculateIRR function is calling itself recursively. There were other problems: the for loop in function calculateNPV stops one array element too short so the NPV for the last array element is not calculated, and the call to calculateIRR that is commented out should be re-instated, but with the correct parameters. After modifying the code and simplifying it a bit I got it working, see attached file. Converting this to CF shouldn't be a problem. It's basically just a loop inside a loop. You don't have to use functions, just move the code of the calculateNPV function to the place where it is being called and you can get rid of the first call to calculateNPV by setting npv to 1. Note that in some situations depending on the test "while abs(NPV)>=1" and the increment value (currently 0.0001) the NPV might never reach "less than 1" and will loop forever as it changes back and forth from, say, -1.1 to +1.1. This happened when I changed the test to abs(NPV)>=0.1 in order to get a more accurate result (NPV closer to zero). The solution was to reduce the increment from 0.0001 to 0.00001. But this will make the trial and error process run even longer. So just be aware. If processing time in CF is an issue you can make it smarter by first using a higher increment and gradually reducing the increment as soon as there is a change from positive NPV to negative NPV and vice versa. You can also make the starting discount rate a parameter that can be varied by the user to a value that is closer to the expected outcome, instead of always starting with 10%. Hope this will help to solve your IRR calculation problem. Regards, Stephen Parengkuan [EMAIL PROTECTED] 0419 578899 On 25 Jun 2003 at 9:43, Steve Baty wrote: From: "Steve Baty" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Subject: [cfaussie] Re: OT : How to IIR in CF or Access ? Date sent: Wed, 25 Jun 2003 09:43:25 +1000 Send reply to: "CFAussie Mailing List" <[EMAIL PROTECTED]> > Here's a rough and ready algorithm in Javascript. It might give you > something to work with. > > Steve > > ---------------------------------------------- > steve baty > senior analyst > red square :: internet design & development > www.redsquare.com > <ph>+612 8596 4030 > <fax>+612 8596 4001 > <mob>+61 417 061 292 > > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrew > Dickinson > Sent: Wednesday, 25 June 2003 9:28 > To: CFAussie Mailing List > Subject: [cfaussie] Re: OT : How to IIR in CF or Access ? > > Yes, Steve, you got it. > I misunderstood my supervisor's accent. And he's now found some of the > help in Access and Excel about it. > > The basic issue is that IRR (got it right this time) is calculated by > function, which CF4 cannot handle. Of course, this will fall nicely into > place when we upgrade to MX, but we need it working by Tuesday (July > 1st, > when the new legislation comes into effect). > > If anyone has a blazingly brilliant idea of how to do a function-type > calculation in CF4, we'd love to hear it. In the meantime, we're > considering non-CF alternatives, even (*gasp* *horror*) using > ASP........ > > > > Just to clarify, are you after IIR or IRR (internal rate of return)? > > > > ---------------------------------------------- > > steve baty > > senior analyst > > red square :: internet design & development > > www.redsquare.com > > <ph>+612 8596 4030 > > <fax>+612 8596 4001 > > <mob>+61 417 061 292 > ---------------------------------------- > > > IIR is a financial industry term used in the calculation of compound > > > interest. Apparently it's either tricky or not possible to calculate > > > using > > > CF, but may be possible with MSAccess. > > > > > > Anyone with finance industry experience know of any routines which > > work > > > for this calculation ? We are trying to calculate Comparison Rates, > in > > > order to comply with some recent government legislation (briefly, to > > > show > > > an interest rate on the loan which reflects all fees and costs). > > > > > > --- > > > You are currently subscribed to cfaussie as: > > [EMAIL PROTECTED] > > > To unsubscribe send a blank email to > > [EMAIL PROTECTED] > > > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > > > > > ************************************************************************ > > **** > > If this communication is not intended for you and you are not an > > authorised > > recipient of this email you are prohibited by law from dealing with or > > relying on the email or any file attachments. This prohibition > includes > > reading, printing, copying, re-transmitting, disseminating, storing or > > in > > any other way dealing or acting in reliance on the information. If > you > > have received this email in error, we request you contact ABN AMRO > > Morgans > > Limited immediately by returning the email to > [EMAIL PROTECTED] > > and destroy the original. We will refund any reasonable costs > associated > > with notifying ABN AMRO Morgans. This email is confidential and may > > contain > > privileged client information. ABN AMRO Morgans has taken reasonable > > steps > > to ensure the accuracy and integrity of all its communications, > > including > > electronic communications, but accepts no liability for materials > > transmitted. Materials may also be transmitted without the knowledge > of > > ABN > > AMRO Morgans. ABN AMRO Morgans Limited its directors and employees do > > not > > accept liability for the results of any actions taken or not on the > > basis > > of the information in this report. ABN AMRO Morgans Limited and its > > associates hold or may hold securities in the companies/trusts > mentioned > > herein. Any recommendation is made on the basis of our research of > the > > investment and may not suit the specific requirements of clients. > > Assessments of suitability to an individual's portfolio can only be > made > > after an examination of the particular client's investments, financial > > circumstances and requirements. > > > ************************************************************************ > > **** > > > > > > --- > > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > > To unsubscribe send a blank email to > > [EMAIL PROTECTED] > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > > --- > You are currently subscribed to cfaussie as: [EMAIL PROTECTED] > To unsubscribe send a blank email to > [EMAIL PROTECTED] > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
