From: "Earl F. Glynn" References: <3b014d57_1@dnews> Subject: Re: Delphi's Math "PeriodPayment" does not match Excel for "ptStartOfPeriod" Date: Tue, 15 May 2001 23:01:57 -0500 Lines: 233 Organization: efg's Computer Lab X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 NNTP-Posting-Host: 65.64.125.35 Message-ID: <3b01fc30_1@dnews> X-Trace: dnews 989985840 65.64.125.35 (15 May 2001 21:04:00 -0700) Path: dnews Xref: dnews borland.public.delphi.objectpascal:188512 "Earl F. Glynn" wrote in message news:3b014d57_1@dnews... > I verified this problem in Delphi 3 and 5. I worked with Excel a bit more. In Excel, PPMT + IPMT = Payment, i.e., the principal payment + the interest payment = total monthly payment = constant. This same thing is true in Delphi: PeriodPayment + InterestPayment = Payment. This was not true with the first period ONLY with ptStartOfPeriod. Everything is OK with ptEndOfPeriod. What is brain dead about the ptStartOfPeriod solution is that you ask to borrow money, and then IMMEDIATELY make your first payment. Since the first payment is immediate, the whole payment goes only to principal. So this is really equivalent to one less period and a slightly lower initial loan. This had to be done by some MBA. Here's my test routine that you might want to explore: USES Math; procedure TForm1.Button1Click(Sender: TObject); VAR Period: INTEGER; PeriodCount: INTEGER; Interest: Double; Principal: Double; pv: Double; Payment1: Double; Payment2: Double; PaymentTime: TPaymentTime; Rate : Double; sumPrincipal: Double; sumInterest: Double; sumPayment1: Double; sumPayment2: Double; begin Rate := 0.08/12; PeriodCount := 24; pv := -808.70; PaymentTime := ptStartOfPeriod; sumPrincipal := 0.0; sumInterest := 0.0; sumPayment1 := 0.0; sumPayment2 := 0.0; FOR Period := 1 TO 24 DO BEGIN Principal := PeriodPayment(Rate, Period, PeriodCount, pv, 0, PaymentTime); Interest := InterestPayment(Rate, Period, PeriodCount, pv, 0, PaymentTime); Payment1 := Principal + Interest; Payment2 := Payment(Rate, PeriodCount, pv, 0, PaymentTime); sumPrincipal := sumPrincipal + Principal; sumInterest := sumInterest + Interest; sumPayment1 := sumPayment1 + Payment1; sumPayment2 := sumPayment2 + Payment2; Memo.Lines.Add(Format('%2d %.2f %.2f %.2f %.2f', [Period, Principal, Interest, Payment1, Payment2])) END; Memo.Lines.Add(Format(' %.2f %.2f %.2f %.2f', [sumPrincipal, sumInterest, sumPayment1, sumPayment2])) end; With the D3 and D5 math units (and I assume it's also true with D1, D2 and D4, but I haven't tested that), you get the following error: Rate := 0.08/12; PeriodCount := 24; pv := -808.70; PaymentTime := ptStartOfPeriod; <<< Note ptEndOfPeriod is OK 1 30.98 5.36 36.33 36.33 <<< ERROR in Delphi 36.33 0.00 36.33 36.33 <<< Correct answer from Excel 2 31.18 5.15 36.33 36.33 3 31.39 4.94 36.33 36.33 4 31.60 4.73 36.33 36.33 5 31.81 4.52 36.33 36.33 6 32.02 4.31 36.33 36.33 7 32.24 4.10 36.33 36.33 8 32.45 3.88 36.33 36.33 9 32.67 3.66 36.33 36.33 10 32.89 3.45 36.33 36.33 11 33.11 3.23 36.33 36.33 12 33.33 3.01 36.33 36.33 13 33.55 2.78 36.33 36.33 14 33.77 2.56 36.33 36.33 15 34.00 2.34 36.33 36.33 16 34.22 2.11 36.33 36.33 17 34.45 1.88 36.33 36.33 18 34.68 1.65 36.33 36.33 19 34.91 1.42 36.33 36.33 20 35.15 1.19 36.33 36.33 21 35.38 0.95 36.33 36.33 22 35.62 0.72 36.33 36.33 23 35.85 0.48 36.33 36.33 24 36.09 0.24 36.33 36.33 803.34 68.65 871.99 871.99 <<< ERROR in Delphi 808.70 63.29 871.99 871.99 <<< Correct answer from Excel Here are the fixes that seem to work: function InterestPayment(const Rate: Extended; Period, NPeriods: Integer; const PresentValue, FutureValue: Extended; PaymentTime: TPaymentTime): Extended; var Crp:extended; { compound(rate,period-1)} Crn:extended; { compound(rate,nperiods)} Arn:extended; { annuityf(rate,nperiods)} begin if (Rate <= -1.0) or (Period < 1) or (Period > NPeriods) then ArgError('InterestPayment'); IF (PaymentTime = ptStartOfPeriod) AND (Period = 1) THEN InterestPayment := 0 ///// FIX ////// ELSE BEGIN Crp:=Compound(Rate,Period-1); Arn:=Annuity2(Rate,Nperiods,PaymentTime,Crn); InterestPayment:=(FutureValue*(Crp-1)-PresentValue*(Crn-Crp))/Arn; END end; function PaymentParts(Period, NPeriods: Integer; Rate, PresentValue, FutureValue: Extended; PaymentTime: TPaymentTime; var IntPmt: Extended): Extended; var Crn:extended; { =Compound(Rate,NPeriods) } Crp:extended; { =Compound(Rate,Period-1) } Arn:extended; { =Annuity2(...) } begin if Rate <= -1.0 then ArgError('PaymentParts'); IF (PaymentTime = ptStartOfPeriod) AND (Period = 1) ///// FIX ///// THEN BEGIN IntPmt := 0; PaymentParts := Payment(Rate, NPeriods, PresentValue, FutureValue, PaymentTime); END ELSE BEGIN Crp:=Compound(Rate,Period-1); Arn:=Annuity2(Rate,NPeriods,PaymentTime,Crn); IntPmt:=(FutureValue*(Crp-1)-PresentValue*(Crn-Crp))/Arn; PaymentParts:=(-FutureValue-PresentValue)*Crp/Arn; END end; So with these fixes, the result matches Excel: "FIXED" version 1 36.33 0.00 36.33 36.33 2 31.18 5.15 36.33 36.33 3 31.39 4.94 36.33 36.33 4 31.60 4.73 36.33 36.33 5 31.81 4.52 36.33 36.33 6 32.02 4.31 36.33 36.33 7 32.24 4.10 36.33 36.33 8 32.45 3.88 36.33 36.33 9 32.67 3.66 36.33 36.33 10 32.89 3.45 36.33 36.33 11 33.11 3.23 36.33 36.33 12 33.33 3.01 36.33 36.33 13 33.55 2.78 36.33 36.33 14 33.77 2.56 36.33 36.33 15 34.00 2.34 36.33 36.33 16 34.22 2.11 36.33 36.33 17 34.45 1.88 36.33 36.33 18 34.68 1.65 36.33 36.33 19 34.91 1.42 36.33 36.33 20 35.15 1.19 36.33 36.33 21 35.38 0.95 36.33 36.33 22 35.62 0.72 36.33 36.33 23 35.85 0.48 36.33 36.33 24 36.09 0.24 36.33 36.33 808.70 63.29 871.99 871.99 And just to regression test with ptEndOfPeriod: Rate := 0.08/12; PeriodCount := 24; pv := -808.70; PaymentTime := ptEndOfPeriod; 1 31.18 5.39 36.58 36.58 2 31.39 5.18 36.58 36.58 3 31.60 4.97 36.58 36.58 4 31.81 4.76 36.58 36.58 5 32.02 4.55 36.58 36.58 6 32.24 4.34 36.58 36.58 7 32.45 4.12 36.58 36.58 8 32.67 3.91 36.58 36.58 9 32.89 3.69 36.58 36.58 10 33.11 3.47 36.58 36.58 11 33.33 3.25 36.58 36.58 12 33.55 3.03 36.58 36.58 13 33.77 2.80 36.58 36.58 14 34.00 2.58 36.58 36.58 15 34.22 2.35 36.58 36.58 16 34.45 2.12 36.58 36.58 17 34.68 1.89 36.58 36.58 18 34.91 1.66 36.58 36.58 19 35.15 1.43 36.58 36.58 20 35.38 1.20 36.58 36.58 21 35.62 0.96 36.58 36.58 22 35.85 0.72 36.58 36.58 23 36.09 0.48 36.58 36.58 24 36.33 0.24 36.58 36.58 808.70 69.11 877.81 877.81 -- efg efg2@efg2.com Earl F. Glynn, Overland Park, KS USA efg's Computer Lab: http://www.efg2.com/Lab Mirror: http://homepages.borland.com/efg2lab/Default.htm