Solved

Amortized Loan Schedual Plan

Posted on 2004-08-01
10
453 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
  • 4
  • 3
10 Comments
 
LVL 8

Accepted Solution

by:
CoolATIGuy earned 168 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 166 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 166 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now