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:

    
  1. Re: Excel PMT Calculation (Richard Kirsner 2001)
  2. Re: Excel PMT Calculation (Scott Anderson 2001)
  3. Re: Excel PMT Calculation (Sal D'anna 2001)
  4. Excel PMT Calculation (Richard Kirsner 2001)
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:

auto number (2005) WebCatalog 4.0 has been released! (2000) Hiring: Web programmer in Seattle area (2000) AuthorizeNet Declines (2005) Format of Required fields error message (1997) Add to a field (1998) watch out for format_to_days on NT (1997) send mail problem? (1997) bug in [SendMail] (1997) Another question (1997) SmithMicro FTP problems (2002) What port is Email on ? (2000) Date Calulation (1997) Error.html (1997) Nested tags count question (1997) ShowNext for method=POST (1997) [WebDNA] WebDNA Hosting (2008) Emailer (WebCat2) (1997) Remote administration (1998) Converting a Magazine to a Web Site with WebCat (1999)