?
Solved

Amortized Loan Schedual Plan

Posted on 2004-08-01
10
Medium Priority
?
475 Views
Last Modified: 2011-10-03
I'm creating a web application in php that allows customers to apply for loans, manage accounts and more. The problem I'm having is calculating the payment schedual for an Amortized loan. I need to know how to calculate a payment schedual.

If you could give me an example payment schedual for a loan of $50,000.00 @ 5.2% Intrest for 60 Months. The monthley payments need to include amount of principal paid, amount of intrest paid.The monthley payment has to be a set ammount, examply $576.90. You also need to include the calculations to get the payment schedual.
0
Comment
Question by:rtcomp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
10 Comments
 
LVL 8

Accepted Solution

by:
CoolATIGuy earned 672 total points
ID: 11689854
Here's a free one that I think fills-the-bill (with interest, of course. ;-) - http://www.pc-calculators.com/online-calculators/

JIC, here are a some more, too: http://www.hotscripts.com/PHP/Scripts_and_Programs/Calculators/


CoolATIGuy
0
 
LVL 3

Assisted Solution

by:travisjhall
travisjhall earned 664 total points
ID: 11690111
Gack! I haven't actually worked with the formulae since high school.

Okay, general principles...
P(x) is the outstanding principle after x repayments. (I'd use subscripts, but I don't want to go to the trouble to code them up to explain this.)
I is the interest on a repayment basis - for your example, this would be 0.052/12 = 0.004333... (because the repayments are monthly, while interest rates are normally annual, and there are 12 months in a year).
R is the monthly repayment.

P(x+1) = P(x) + I*P(x) - R = (1+I)*P(x) - R

From this, we extrapolate:
P(x+2) = (1+I)P(x+1) - R = (1+I)*((1+I)*P(x) - R) - R = (1+I)^2 * P(x) - (1+I)*R - R = (1+I)^2 * P(x) - ( (1+I) + 1)*R
P(x+3) = (1+I)^3 * P(x) - ( (1+I)^2 + (1+I) + 1) * R
and so on and so forth.

In your example, P(0) = 50000 and P(60) = 0.
P(60) = (1+I)^60 * P(x) - ( (1+I)^59 + (1+I)^58 + (1+I)^57 + ... (1+I)^2 + (1+I) + 1) * R
So,
0 = (1+I)^60 * 50000 - ( (1+I)^59 + (1+I)^58 + (1+I)^57 + ... (1+I)^2 + (1+I) + 1) * R
=> (1+I)^60 * 50000 = ( (1+I)^59 + (1+I)^58 + (1+I)^57 + ... (1+I)^2 + (1+I) + 1) * R
=> R = ((1+I)^60 / ( (1+I)^59 + (1+I)^58 + (1+I)^57 + ... (1+I)^2 + (1+I) + 1) * R) * 50000

Writing a little code to calculate ( (1+I)^59 + (1+I)^58 + (1+I)^57 + ... (1+I)^2 + (1+I) + 1) is a doddle, you just need a loop adding to an aggregator at each step. In your example, that works out to 68.3542594799438 (or thereabouts). Plugging in the rest of the numbers, the repayments work out to about $948.15 per month.

Generally, when these things are worked out, they use whole-dollar repayments, and the last repayment is made a little larger to make up for the shortfall introduced by rounding down. So, in this case, the normal repayment would be $948 per month, and a little extra tacked on to the last repayment to make up the rounding shortfall - I estimate the last repayment at about $954, but I haven't looked at it closely. Your program should handle the rounding more carefully then my quick calculations.

That's the basic maths behind it. Does that cover your needs, or do you need a hand with some of the coding? Do you need anything explained further?

One caveat: I'm not an accountant. The above calculations are built from first principles based on my own knowledge of amortized loans (which stems mostly from having bought a house). If I were you, I'd consult with an accountant before going live to the public with something like this.
0
 
LVL 2

Assisted Solution

by:Axe007
Axe007 earned 664 total points
ID: 11690359
rtcomp,

I'm a banker, but an IT Specialist too.
We have 2 options to pay.

1. Fixed principal throughout the loan period. It means variable total monthly payment. Calculation is very much easy for such schedule.

It's like this.
Monthly principal payment = 500'000/60 = $833.33

1st month -
Principal $833.33
Interest $2600.00 ($50'000 * 5.2%)
Total payment $3433.33

2nd month -
Principal $833.33
Interest $2556.67 ($49'166.67 * 5.2%)
Total payment $3390.00
...
...

So goes the calculation. Just do while(i<=$month) ... do ... function til last month, right? Interest is based on the remaining loan balance at the end of every month. I think you can figure this formula in no time. Otherwise just ask help.
Usually I do this calculation in Excel sheet, so things look easy.

2. Fixed total payment.
It like a leasing payment. You can find the calculation and formula here:
http://www.efunda.com/formulae/finance/lease_calculator.cfm

Any quesitons?
0
Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

 
LVL 2

Expert Comment

by:Axe007
ID: 11690362
Oh, I haven't thought about 5.2% being annual rate. But dividing 5.2%/12 is OK, right?
0
 
LVL 3

Expert Comment

by:travisjhall
ID: 11690627
Oh, of course, there's the step I had forgotten about in my calculations.
a + a*r + a*r^2 + a*r^3 + ... = a/(1-r)
which leads to...
( (1+I)^n + (1+I)^(n-1) + (1+I)^(n-2) + ... (1+I)^2 + (1+I) + 1) = ((1+I)^(n+1) - 1)/I
via some work which I can produce for you if required, but won't bother if you don't ask for it.

So, for the example given
P(60) = (1+I)^60 * P(0) - ( (1+I)^59 + (1+I)^58 + (1+I)^57 + ... (1+I)^2 + (1+I) + 1) * R
=> P(60) = (1+I)^60 * P(0) - ((1+I)^(59+1) - 1) * R / I
=> P(60) = (1+I)^60 * P(0) - ((1+I)^60 - 1) * R / I

P(60) = 0, P(0) = 50000 so
0 = (1+I)^60 * 50000 - ((1+I)^60 - 1) * R
R = (1+I)^60 * I * 50000 / ((1+I)^60 - 1)

That's right isn't it? It really has been too long since I've done this stuff.

And I = 0.052/12, which gives R = 948.15 (or close enough).

(Sorry about the complexity of my stuff. My training was as a mathematician, so I'm used to working everything up from first principles. Axe007, being a banker, undoubtedly just cuts to the chase and looks up the formula.)
0
 
LVL 2

Expert Comment

by:Axe007
ID: 11699659
Well travis, that's how we manage all those billions of dollars a day :D

rtcomp, here is loan calculator:
http://www.efunda.com/formulae/finance/loan_calculator.cfm
0
 
LVL 3

Expert Comment

by:travisjhall
ID: 11699842
Yeah, Axe007, I know. I'm not saying you're doing anything wrong by just looking up the formula. After all, if we insisted on doing everything the long way every time, we wouldn't be using computers to automate this stuff in the first place, would we? Can you imagine wading through the mess that I posted every time you wanted to calculate the repayments on a loan?

The world needs mathematicians to work out the formulae in the first place, and bankers to use them. :)
0
 
LVL 3

Expert Comment

by:travisjhall
ID: 11731714
rtcomp, you've been given the relevant formulae and approaches to calculating repayments. I've shown how the formulae are derived. Several examples of calculators have been given. I'm afraid you aren't going to get much more information unless you can tell us what more you are looking for. Why don't you tell us what more help you need, or close the question?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question