Amortized Loan Schedual Plan

Posted on 2004-08-01
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.
Question by:rtcomp
  • 4
  • 3

Accepted Solution

CoolATIGuy earned 168 total points
ID: 11689854
Here's a free one that I think fills-the-bill (with interest, of course. ;-) -

JIC, here are a some more, too:


Assisted Solution

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
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.

Assisted Solution

Axe007 earned 166 total points
ID: 11690359

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:

Any quesitons?
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.


Expert Comment

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

Expert Comment

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.)

Expert Comment

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

rtcomp, here is loan calculator:

Expert Comment

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. :)

Expert Comment

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?

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

Suggested Solutions

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
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 learn how to dynamically set the form action using jQuery.

777 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