Solved

Amortized Loan Schedual Plan

Posted on 2004-08-01
10
465 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
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:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

691 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