Problem with the Pmt function.

[Guest]

I am trying to set an expression to give me the monthly repayments on a
mortgage using the Pmt function, I have it as

Pmt(sum([InterestRate]/1200),sum([MortgageLength]*12),-[MortgageAmount],0,0)

However the result is always 333.33 regardless of what data is entered in
the various fields. I have tried to use the IPmt to verify the result as this
is purely the amount of interest this gives me the same figure.

As a test I am using the following data

InterestRate - 5
MortgageLength - 25
MortgageAmount - 100,000

The results should be

Pmt function - 584.59
Ipmt function - 416.67

The functions work correctly in Excel so where am I going wrong in Access?

 

[Arvin Meyer]

Why are you using the Sum function? The Pmt function uses Rate, Number of
Periods, and Present Value. So:

=Pmt(([InterestRate]/12), ([MortgageLength]*12),[MortgageAmount])
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Thanks was still having the problem when I suddenly realised that I had the
Total: set as an expression not a sum

Dave

Why are you using the Sum function? The Pmt function uses Rate, Number of
Periods, and Present Value. So:

=Pmt(([InterestRate]/12), ([MortgageLength]*12),[MortgageAmount])
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

I am trying to set an expression to give me the monthly repayments on a
mortgage using the Pmt function, I have it as

Pmt(sum([InterestRate]/1200),sum([MortgageLength]*12),-[MortgageAmount],0,0)

However the result is always 333.33 regardless of what data is entered in
the various fields. I have tried to use the IPmt to verify the result as this
is purely the amount of interest this gives me the same figure.

As a test I am using the following data

InterestRate - 5
MortgageLength - 25
MortgageAmount - 100,000

The results should be

Pmt function - 584.59
Ipmt function - 416.67

The functions work correctly in Excel so where am I going wrong in Access?