Re: Excel PMT Calculation
This WebDNA talk-list message is from 2001
It keeps the original formatting.
numero = 37967
interpreted = N
texte = The Excel calc is the correct one. I got this from http://www.tcalc.com. All other online calcs seem to come up with the same response, however they do disclaim that rounding errors may occur. Maybe you can use [Format] to get the correct final result Fixed Rate Loan Payments Calculate the payments and interest for a fixed rate loan, using monthly interest compounding and monthly payments. Enter the purchase price, number of monthly payments, and interest rate, and the payment calculator computes the payment amount for you.Purchase price: $20,000 Down payment amount: $0.00 Start date:8/17/2001 Number of months:36 Interest rate: 8% Payment amount: $626.73 Financial Details:Loan amount: $20,000.00 Payment amount: $626.73 Interest rate: 8.000% Interest compounding: Monthly Total amount financed: $20,000.00 Total payments: $22,562.15 Total finance charge: $2,562.15 Payment schedule: Amortization Schedule Powered by TValue ©1998-2000 Event Date Payment Interest Principal Balance Loan 08-17-2001 20,000.00 1 09-17-2001 626.73 133.33 493.40 19,506.60 2 10-17-2001 626.73 130.04 496.69 19,009.91 3 11-17-2001 626.73 126.73 500.00 18,509.91 4 12-17-2001 626.73 123.40 503.33 18,006.58 2001 Total 2,506.92 513.50 1,993.42 5 01-17-2002 626.73 120.04 506.69 17,499.89 6 02-17-2002 626.73 116.67 510.06 16,989.83 7 03-17-2002 626.73 113.27 513.46 16,476.37 8 04-17-2002 626.73 109.84 516.89 15,959.48 9 05-17-2002 626.73 106.40 520.33 15,439.15 10 06-17-2002 626.73 102.93 523.80 14,915.35 11 07-17-2002 626.73 99.44 527.29 14,388.06 12 08-17-2002 626.73 95.92 530.81 13,857.25 13 09-17-2002 626.73 92.38 534.35 13,322.90 14 10-17-2002 626.73 88.82 537.91 12,784.99 15 11-17-2002 626.73 85.23 541.50 12,243.49 16 12-17-2002 626.73 81.62 545.11 11,698.38 2002 Total 7,520.76 1,212.56 6,308.20 17 01-17-2003 626.73 77.99 548.74 11,149.64 18 02-17-2003 626.73 74.33 552.40 10,597.24 19 03-17-2003 626.73 70.65 556.08 10,041.16 20 04-17-2003 626.73 66.94 559.79 9,481.37 21 05-17-2003 626.73 63.21 563.52 8,917.85 22 06-17-2003 626.73 59.45 567.28 8,350.57 23 07-17-2003 626.73 55.67 571.06 7,779.51 24 08-17-2003 626.73 51.86 574.87 7,204.64 25 09-17-2003 626.73 48.03 578.70 6,625.94 26 10-17-2003 626.73 44.17 582.56 6,043.38 27 11-17-2003 626.73 40.29 586.44 5,456.94 28 12-17-2003 626.73 36.38 590.35 4,866.59 2003 Total 7,520.76 688.97 6,831.79 29 01-17-2004 626.73 32.44 594.29 4,272.30 30 02-17-2004 626.73 28.48 598.25 3,674.05 31 03-17-2004 626.73 24.49 602.24 3,071.81 32 04-17-2004 626.73 20.48 606.25 2,465.56 33 05-17-2004 626.73 16.44 610.29 1,855.27 34 06-17-2004 626.73 12.37 614.36 1,240.91 35 07-17-2004 626.73 8.27 618.46 622.45 36 08-17-2004 626.60 4.15 622.45 2004 Total 5,013.71 147.12 4,866.59 -----Original Message----- From: Richard Kirsner [mailto:richmk@dwx.com] Sent: Friday, August 17, 2001 9:47 AM To: Webcatalog Talk List Subject: Excel PMT Calculation I'm trying to calculate the payment for a loan that yields the same result as Excel using their PMT formula. The variables are the interest rate, number of periods for the term of the loan in months, and the principal amount of the loan.The problem is that my results are not the same as those produced by Excel even though I'm using the same formula that Excel uses.The Excel formula is: =(PMT(rate,number of payments,loan amount)) The Excel docs say that the formula is: PMT=(rate*(FV+PV*(1+rate)^nper))/((1+rate*type)*(1-(1+rate)^nper)) The type is 0 if the payment is at the end of a period or 1 at the beginning. It is 0 in this case. I'm using the following where: Interest rate is 8% ([rate]=.08) Term is 36 months ([nper]=36) Loan Amount is 20,000 ([PV]=20000)I calulate the monthly payment ([mpayment]) using:[math][nper]/1[/math][text]numperM=[math]([nper]/1)[/math][/text][text]mrate=[math][rate]/[numperM][/math][/text] [text]mpayment=[math]([mrate]*([PV]*(1+[mrate])^[numperM]))/((1+([mrate]*0)) *(1-(1+[mrate])^[numperM]))[/math][/text]This produces a monthly payment of $578.69, where Excel produces a monthly payment of $626.73.I'm unable to figure out why there is a difference. Is is a rounding of variables problem or some other weirdness by Excel?Any help would be appreciated.Richard Kirsner------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list
. To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/
Associated Messages, from the most recent to the oldest:
The Excel calc is the correct one. I got this from http://www.tcalc.com. All other online calcs seem to come up with the same response, however they do disclaim that rounding errors may occur. Maybe you can use [format] to get the correct final result Fixed Rate Loan Payments Calculate the payments and interest for a fixed rate loan, using monthly interest compounding and monthly payments. Enter the purchase price, number of monthly payments, and interest rate, and the payment calculator computes the payment amount for you.Purchase price: $20,000 Down payment amount: $0.00 Start date:8/17/2001 Number of months:36 Interest rate: 8% Payment amount: $626.73 Financial Details:Loan amount: $20,000.00 Payment amount: $626.73 Interest rate: 8.000% Interest compounding: Monthly Total amount financed: $20,000.00 Total payments: $22,562.15 Total finance charge: $2,562.15 Payment schedule: Amortization Schedule Powered by TValue ©1998-2000 Event Date Payment Interest Principal Balance Loan 08-17-2001 20,000.00 1 09-17-2001 626.73 133.33 493.40 19,506.60 2 10-17-2001 626.73 130.04 496.69 19,009.91 3 11-17-2001 626.73 126.73 500.00 18,509.91 4 12-17-2001 626.73 123.40 503.33 18,006.58 2001 Total 2,506.92 513.50 1,993.42 5 01-17-2002 626.73 120.04 506.69 17,499.89 6 02-17-2002 626.73 116.67 510.06 16,989.83 7 03-17-2002 626.73 113.27 513.46 16,476.37 8 04-17-2002 626.73 109.84 516.89 15,959.48 9 05-17-2002 626.73 106.40 520.33 15,439.15 10 06-17-2002 626.73 102.93 523.80 14,915.35 11 07-17-2002 626.73 99.44 527.29 14,388.06 12 08-17-2002 626.73 95.92 530.81 13,857.25 13 09-17-2002 626.73 92.38 534.35 13,322.90 14 10-17-2002 626.73 88.82 537.91 12,784.99 15 11-17-2002 626.73 85.23 541.50 12,243.49 16 12-17-2002 626.73 81.62 545.11 11,698.38 2002 Total 7,520.76 1,212.56 6,308.20 17 01-17-2003 626.73 77.99 548.74 11,149.64 18 02-17-2003 626.73 74.33 552.40 10,597.24 19 03-17-2003 626.73 70.65 556.08 10,041.16 20 04-17-2003 626.73 66.94 559.79 9,481.37 21 05-17-2003 626.73 63.21 563.52 8,917.85 22 06-17-2003 626.73 59.45 567.28 8,350.57 23 07-17-2003 626.73 55.67 571.06 7,779.51 24 08-17-2003 626.73 51.86 574.87 7,204.64 25 09-17-2003 626.73 48.03 578.70 6,625.94 26 10-17-2003 626.73 44.17 582.56 6,043.38 27 11-17-2003 626.73 40.29 586.44 5,456.94 28 12-17-2003 626.73 36.38 590.35 4,866.59 2003 Total 7,520.76 688.97 6,831.79 29 01-17-2004 626.73 32.44 594.29 4,272.30 30 02-17-2004 626.73 28.48 598.25 3,674.05 31 03-17-2004 626.73 24.49 602.24 3,071.81 32 04-17-2004 626.73 20.48 606.25 2,465.56 33 05-17-2004 626.73 16.44 610.29 1,855.27 34 06-17-2004 626.73 12.37 614.36 1,240.91 35 07-17-2004 626.73 8.27 618.46 622.45 36 08-17-2004 626.60 4.15 622.45 2004 Total 5,013.71 147.12 4,866.59 -----Original Message----- From: Richard Kirsner [mailto:richmk@dwx.com] Sent: Friday, August 17, 2001 9:47 AM To: Webcatalog Talk List Subject: Excel PMT Calculation I'm trying to calculate the payment for a loan that yields the same result as Excel using their PMT formula. The variables are the interest rate, number of periods for the term of the loan in months, and the principal amount of the loan.The problem is that my results are not the same as those produced by Excel even though I'm using the same formula that Excel uses.The Excel formula is: =(PMT(rate,number of payments,loan amount)) The Excel docs say that the formula is: PMT=(rate*(FV+PV*(1+rate)^nper))/((1+rate*type)*(1-(1+rate)^nper)) The type is 0 if the payment is at the end of a period or 1 at the beginning. It is 0 in this case. I'm using the following where: Interest rate is 8% ([rate]=.08) Term is 36 months ([nper]=36) Loan Amount is 20,000 ([PV]=20000)I calulate the monthly payment ([mpayment]) using:[math][nper]/1[/math][text]numperM=[math]([nper]/1)[/math][/text][text]mrate=[math][rate]/[numperM][/math][/text] [text]mpayment=[math]([mrate]*([PV]*(1+[mrate])^[numperM]))/((1+([mrate]*0)) *(1-(1+[mrate])^[numperM]))[/math][/text]This produces a monthly payment of $578.69, where Excel produces a monthly payment of $626.73.I'm unable to figure out why there is a difference. Is is a rounding of variables problem or some other weirdness by Excel?Any help would be appreciated.Richard Kirsner------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/
Sal D'anna
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
Text data with spaces in them... (1997)
WebCat2 beta 11 - new prefs ... (1997)
Return records from another (1997)
WebCat2b15MacPlugin - [protect] (1997)
WebCat BeOS (2002)
displaying New products (Yikes! it's Fixed!) (1997)
Serial Number Question (1997)
Jail Break...Please! (2000)
Re[2]: [OT] Help Webcat 213 win (2000)
WebCatalog Eating 200% of the CPU (2002)
[WebDNA] HTTP Streaming -- impossible? (2010)
Timer Values on [redirect] (1998)
[WebDNA] WARNING Do not upgrade to OS X Server 5.04 (2015)
Why WebMerchant not working? (1999)
WebCatalog seems to choke on large (2meg) html files. (1998)
international time (1997)
limit to listwords (2001)
mass mailing (1998)
WebCat2 Append problem (B14Macacgi) (1997)
WebCat2b13MacPlugIn - [include] doesn't allow creator (1997)