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:
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)