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