Re: Excel PMT Calculation

This WebDNA talk-list message is from

2001


It keeps the original formatting.
numero = 38004
interpreted = N
texte = Thanks Scott. You're right. I've got everything working now.Richard > -----Scott Anderson Wrote----- > Subject: Re: Excel PMT Calculation > Date: Fri, 17 Aug 2001 17:26:45 > From: Scott Anderson > 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 Kirsnerp------------------------------------------------------------- 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)
Thanks Scott. You're right. I've got everything working now.Richard > -----Scott Anderson Wrote----- > Subject: Re: Excel PMT Calculation > Date: Fri, 17 Aug 2001 17:26:45 > From: Scott Anderson > 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 Kirsnerp------------------------------------------------------------- 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/ Richard Kirsner

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:

Draft Manual, Tutorial, and more (1997) Rumpus/Typhoon modules included in Typhoon ... (1997) Now you see it now you donīt (1997) Press Release hit the NewsWire!!! (1997) select multiple 2 more cents (1997) NT version and O'reily's WebSite (1997) calculating unique time (1998) [authenticate] was [TIME] not working (2007) replacing founditems ... (2002) Can't save email prefs (2003) FYI: virus alert (1996) Quit revisited (1997) Re[2]: Re[2]: WebCatalog keeps quiting on Solaris, and suppo (2000) WCS Newbie question (1997) syntax question, not in online refernce (1997) WebCat2b13MacPlugIn - [include] doesn't allow creator (1997) Bad Cookie / Internet Option / Internet Explorer (2004) ListFiles then delete by ModDate (2002) HELP WITH DATES (1997) How do I get Google to crawl a WebCat site? (2003)