• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 122
  • Last Modified:

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.
0
Taras
Asked:
Taras
  • 8
  • 7
  • 3
  • +2
2 Solutions
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 8
  • 7
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now