ESBPCS for VCL
Excel_Rate Function
Thread Starter: Chris Started: 10/30/2007 1:16 AM UTC
Replies: 2
10/30/2007 1:16 AM UTC
Excel_Rate Function
Reply Quote

Chris

Hi All,

I know I'm overlooking something very obvious but:

I am using the Function Excel_RATE(const NoPeriods: Double; const Pmt, PV: Currency; const
FV: Currency = 0; const PeriodType: Byte = 0; const Guess: Double = 0.5): Double;

To keep it simple, whenever the Future Value is less then the Present Value, the rate comes back as zero.

For example, PV = 9000, FV = -8000, NoPeriods = 1, Pmt = 0, and PeriodType = 0. This returns the rate as zero.
Rate := Excel_Rate(1, 0, 9000, -8000, 0);

I tried this in Microsoft Excel and the rate comes back as -11.111.

Any help would be appreciated.
Thanks in advance.

Chris V.
10/30/2007 1:59 AM UTC
RE: Excel_Rate Function
Reply Quote

Glenn Crouch

For example, PV = 9000, FV = -8000, NoPeriods = 1, Pmt = 0, and PeriodType
=
0. This returns the rate as zero.
Rate := Excel_Rate(1, 0, 9000, -8000, 0);
I tried this in Microsoft Excel and the rate comes back as -11.111.

Chris,

I didn't realise that MS Excel allowed for negative rates - shall look into
this and get back to you...


Glenn Crouch, mailto:glenn@esbconsult.com
ESB Consultancy, http://www.esbconsult.com
Home of ESBPCS, ESB Calculators, ESBStats and ESBPDF Analysis
Kalgoorlie-Boulder, Western Australia
10/30/2007 2:16 AM UTC
RE: Excel_Rate Function
Reply Quote

Glenn Crouch

Try the following replacements, they seem to give the result you expected -
we still need to do some more testing on them:

function Excel_RATE (const NoPeriods: Integer; const Pmt, PV: Currency;
    const FV: Currency = 0; const PeriodType: Byte = 0; const Guess: Double
= 0.5): Double;
const
    Iterations = 100;
var
    Y, Z: Extended;
    Count: Integer;
    Upper, Lower, Middle: Double;
    Done: Boolean;
    Negative: Boolean;
begin
    Negative := FV < PV;

    if Negative then
    begin
         Lower := -2.0 * Guess;
         Upper := 0;
    end
    else
    begin
         Lower := 0;
         Upper := 2.0 * Guess;
    end;
    Done := False;
    Count := 0;
    repeat
         Middle := (Upper + Lower) / 2.0;

         Y := XtoY ((1 + Middle), NoPeriods);
         Z := PV * Y + Pmt * (1 + Middle * PeriodType) * (Y - 1) / Middle +
FV;
         if Abs (Z) < 0.0000001 then
              Done := True
         else if Z > 0 then
              Upper := Middle
         else
              Lower := Middle;
         Inc (Count)
    until Done or (Count > Iterations);
    Result := Middle;
end;

function Excel_RATE (const NoPeriods: Double; const Pmt, PV: Currency;
    const FV: Currency = 0; const PeriodType: Byte = 0; const Guess: Double
= 0.5): Double;
const
    Iterations = 100;
var
    Y, Z: Extended;
    Count: Integer;
    Upper, Lower, Middle: Double;
    Done: Boolean;
    Negative: Boolean;
begin
    Negative := FV < PV;

    if Negative then
    begin
         Lower := -2.0 * Guess;
         Upper := 0;
    end
    else
    begin
         Lower := 0;
         Upper := 2.0 * Guess;
    end;
    Done := False;
    Count := 0;
    repeat
         Middle := (Upper + Lower) / 2;

         Y := XtoY ((1 + Middle), NoPeriods);
         Z := PV * Y + Pmt * (1 + Middle * PeriodType) * (Y - 1) / Middle +
FV;
         if Abs (Z) < 0.0000001 then
              Done := True
         else if Z > 0 then
              Upper := Middle
         else
              Lower := Middle;
         Inc (Count)
    until Done or (Count > Iterations);
    Result := Middle;
end;