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/
>
<html>
<head>
<title>Calculating IRR</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<script language="JavaScript">
/*
var cashflows = new Array(5);
cashflows[0] = -10000;
cashflows[1] = 5000;
cashflows[2] = 3000;
cashflows[3] = 3000;
cashflows[4] = 5000;
*/
var cashflows = new Array(6);
cashflows[0] = -7500;
cashflows[1] = 2000;
cashflows[2] = 2000;
cashflows[3] = 2000;
cashflows[4] = 2000;
cashflows[5] = 2000;
var discountRate = 0.1;
function calculateNPV(cfs,discount){
npv = cfs[0];
for (i=1; i<cfs.length;i++){
npvtemp = cfs[i]*Math.pow(1+discount,-i)
npv = npv + npvtemp;
}
return true;
}
function calculateIRR(cfs,startDiscount){
var irr = startDiscount;
calculateNPV(cfs,irr);
while (Math.abs(npv) >= 1) {
if(npv < 0){
irr = irr - 0.0001;
} else if(npv > 0){
irr = irr + 0.0001;
}
calculateNPV(cfs,irr);
}
return irr;
}
alert (calculateIRR(cashflows,discountRate));
</script>
</head>
<body>
</body>
</html>
---
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/
