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:

OK, here goes... (1997) 'RequiredField' Question (1998) Don't do this! (was: Crashing Type 3) (1999) Sorting problem (1997) New public beta available (1997) Grep and removing text (2002) WebCat2 - storing unformatted date data? (1997) [WebDNA] Silly question (2016) Passing info in [Addlineitem] (1997) Wanted: More Math Functions (or, Can You Solve This?) (1997) File upload in Mac OS X (2002) IIS3.0 Microsoft's Fix (1998) Mac: LModelDirector bug fix (1997) WC Database Format (1997) Beta (?) version of WebCatalog 2.1 (1998) how is this possible (2000) sort without the or a (1998) [ShowNext] feature in 2.0 (1997) Webmerchant PC auth (1998) [WebDNA] Adding Graphics to Directory (2009)