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.
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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

Pmt(0.0625/12,25*12, -250000,0,0)
0
JohnBusiness Consultant (Owner)Commented:
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
TarasAuthor Commented:
I apologize it is 10 years not 25. Sorry just morning issue.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

JohnBusiness Consultant (Owner)Commented:
The correct answer is $2,807.   Are you in a non US country amortizing half yearly or something?
0
Rey Obrero (Capricorn1)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
TarasAuthor Commented:
How I get $2,852.91???
0
JohnBusiness Consultant (Owner)Commented:
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
Eric ShermanAccountant/DeveloperCommented:
I get $2,807 as well ....

Amort1
ET
0
Jeffrey CoachmanMIS 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
TarasAuthor 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
JohnBusiness Consultant (Owner)Commented:
$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
TarasAuthor 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
JohnBusiness Consultant (Owner)Commented:
Did you adjust the insurance fee out of the calculation?
0
Jeffrey CoachmanMIS 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
TarasAuthor Commented:
“Did you adjust the insurance fee out of the calculation?”
What exactly you mean?
0
JohnBusiness Consultant (Owner)Commented:
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
TarasAuthor 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
JohnBusiness Consultant (Owner)Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
Taras
OK, thanks for the clarification...

I am sure John can get you sorted...
0
Eric ShermanAccountant/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.

Amort2
ET
0

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
TarasAuthor Commented:
Thanks a lot I will come back for calculation of interest on overdue payment:-)
0
JohnBusiness Consultant (Owner)Commented:
You are very welcome and we can watch for overdue interest issues
0
Eric ShermanAccountant/DeveloperCommented:
Thanks for the points.

ET
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.