# Loan Amortization

I am using PMT function  Pmt( rate, nper , pv [, fv ] [, type ] ) for calculation of loan \$250,000.00 on 25 years with 6.25% yearly interest paying monthly.
I am getting payment = \$2,807.00
What function is used to get payment amount = \$2,852.91.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
using the PMT() function you should get 1,649.17

Pmt(0.0625/12,25*12, -250000,0,0)
0
Where are you located (country)?

Both answers seem high. I used a financial calculator and got less than \$1,700 per month. Here is a loan calculator answer:  \$1,649.17 , \$250,000 , 6.25%, 25 years

http://www.amortization-calc.com/
0
Author Commented:
I apologize it is 10 years not 25. Sorry just morning issue.
0
The correct answer is \$2,807.   Are you in a non US country amortizing half yearly or something?
0
Commented:
adjust the formula i posted above, change 25*12  with 10*12

=PMT(0.0625/12,10*12, -250000,0,0)
will give you
2807.00
0
Author Commented:
How I get \$2,852.91???
0
Start with a new worksheet and test it very simply as we have outlined above. Excel, HP Financial Calculator, Loan Amortization site all give \$2,807. Just start again and check.
0
Accountant/DeveloperCommented:
I get \$2,807 as well ....

ET
0
MIS LiasonCommented:
FWIW, BankRate also gives 2807
See Here

Check ALL your input values very carefully, ...there may have been some incorrect rounding previously for the interest rate, ...or perhaps the interest rate was calculated differently (Monthly, weekly, daily, continuously, ...etc)
Also check your amount, 255000 (a mistype of 250000) will get you 2,863.14

Make sure you type in your values directly, ...and not use variables.

JeffCoachman
0
Author Commented:
For the first payment there is this numbers:
Frist payment was made with 15 days difference between Loan disbursed date (08/15/2014  and  loan start date first payment 09/01/2104 looks like this.
Payment Amount = 2852.91
Interest paid = 726.91
Principal paid = 2073,50
There is amount of 52.50 insurance fee.

End Balance = 247.926.50
Not sure how interest and Principal are calculated???
0
\$2,807 plus \$52.50 is \$2,859.50 and 15 days is not exactly 1/2 month. They use be using daily interest.

Fees are not part of a standard amortization either.
0
Author Commented:
I am using this to calculate that interest but still not getting :
rs!Interest = BeginningBal * (Nz(Me.txtInterestRate, "") * DateDiff("d", Me.LoanDisbursedDate, Me.txtStartDate) / 360)
I tried with
rs!Interest = BeginningBal * (Nz(Me.txtInterestRate, "") * DateDiff("d", Me.LoanDisbursedDate, Me.txtStartDate) / 365)
Not getting that \$ 726.91
0
Did you adjust the insurance fee out of the calculation?
0
MIS LiasonCommented:
Taras,

Please do as the other Experts have recommended.
First load the standard values into Excel or Access and verify that you are getting 2807 using the PMT function

In your above post you are using your own formula, a recordset, Variables, and form controls to calculate your value...
When in your original post you stated:
I am using PMT function  Pmt( rate, nper , pv [, fv ] [, type ] )

If you are not getting 2807, ...then either your custom formula is incorrect,...(or as John Hurst stated), you are including other factors that are not part of a standard PMT calculation...

I will leave this to the other experts here to avoid confusion...

JeffCoachman
0
Author Commented:
“Did you adjust the insurance fee out of the calculation?”
What exactly you mean?
0
The \$2,852 payment includes insurance and should not be included in the loan amortization schedule as it was not added to the principal balance, just added as a extra fee.
0
Author Commented:
Jeff I am using that Pmt function and I am getting 2807, however client brought to me those numbers  and said to me that  loan was released at 15th (it could be 8,9 12)  of the month and start date is after that date.

He  asked not be charged for full month interest I do not see what is problem with it.
He brought to me this numbers I do not have formula how it is calculated,  I need to do my calculation and be sure that it is right with this scenario ?
That is reason that I put  that part of my  code to show  how I tried to calculate this interest for that part of number of days in  month to handle those 15 days (it could be 20,13 or any number )
0
Unless you or he are trying to work to the penny, charge 1/2 month interest for the first period. Adjust the principle amount and then amortize monthly for 9 years and 11 months.
0
MIS LiasonCommented:
Taras
OK, thanks for the clarification...

I am sure John can get you sorted...
0
Accountant/DeveloperCommented:
Taras ...

Try this ... The payment is due on 09/01/2014 which means the first payment is for the month of August, is that correct??  Sounds like you are wanting the August payment to only include interest for the 17 days starting with 08/15/2014 to 08/31/2014.  See calculations below.

Calculate August payment as normal and calculate August daily interest.  Subtract the sum of August daily interest for the 17 days from the August payment to get the principal reduction for August.

ET
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks a lot I will come back for calculation of interest on overdue payment:-)
0