Re: Excel PMT Calculation
This WebDNA talk-list message is from 2001
It keeps the original formatting.
numero = 37975
interpreted = N
texte = Your monthly rate [mrate] calculation in incorrect. It should be:[text]mrate=[math][rate]/12[/math][/text]> -----Original Message----- > From: WebCatalog Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On > Behalf Of Richard Kirsner > Sent: Friday, August 17, 2001 9:47 AM > To: WebCatalog Talk > 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:
Your monthly rate [mrate] calculation in incorrect. It should be:[text]mrate=[math][rate]/12[/math][/text]> -----Original Message----- > From: WebCatalog Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On > Behalf Of Richard Kirsner > Sent: Friday, August 17, 2001 9:47 AM > To: WebCatalog Talk > 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/
Scott Anderson
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:
Date Range Sorting (1997)
New WebDNA Example from Olin -- (1998)
Multiple cart additions (1997)
how would you trigger something to happen once a week - every (2001)
return missing item (was:WebCat Sales) (1997)
Reversed words (1997)
RE: [WebDNA] Tables... (2008)
using showpage and showcart commands (1996)
docs for WebCatalog2 (1997)
Test (2003)
Lots of bounce errors (2007)
foreign character sets and conversions (1998)
Narrowing NT Crashing error (2000)
Need some Java Script Code (2003)
[WebDNA] talklist archives don't search/find recent posts? (2008)
[OT] html background att (2003)
FORMS: Returning a specific page (1997)
Country & Ship-to address & other fields ? (1997)
Doing VALIDCARD right? (1998)
Locking up with WebCatalog... (1997)