I have been tasked with creating a program that will generate an amortization schedule. I have only done a bit of research so far, but I need to calculate out payments, interest per payment and principal per payment. Can any one point me in the right direction to figure this out? While I will be writing this in RPG, I am sure others could make use of this algorithm in the future.
(Update) Okay, so how do I calculate based on a 365 day year?
There are plenty of sites that offer these formulas. One is below: http://www.math4finance.com/financial-formulas.php
No fancy algorithms are needed, as the formulas are generally quite simple.
Brian is right. The formulas are quite simple.
If you want your solution to be a good one, you'll pay attention to roundoff error. If you just let roundoff error accumulate and propagate you can off by several cents at the end of the schedule. With careful programming, you'll minimize the error.
Also, if you have MS Excel on your computer, you can download an amortization template from MS office online.
When I was buying my first home recently, I wrote a JavaScript app for my personal use. [Edit: Link removed.]
Here's a modified snippet of my code to determine the monthly payment:
var balance = 200000; // for example
var periods = 360; // 30 years
var monthlyRate = (0.065)/12; // 0.065= APR of 6.5% as decimal
var monthyPayment = (monthlyRate /(1-(Math.pow((1+monthlyRate),-(periods)))))*balance;
for (var i=0; i<360; i++) {
var interestForMonth = balance * monthlyRate;
var principalForMonth = monthlyPayment - interestForMonth;
balance -= monthlyPayment;
// output as necessary.
}
Pretty easy to make an amortization table after that. (As others have mentioned, do watch out for rounding errors, I didn't adequately deal with this issue in my implementation.)
Here is what I ended up creating. I have posted the whole test program. It is written in RPG, but should be easily be figured out for any other languages.
H ActGrp(*caller) BndDir('MODULES') DftActGrp(*no)
//*********************************************************************
// Program . . . . . AMORT
//*********************************************************************
// Printer/Display Files
FAMORTDF CF E WORKSTN sfile(SFL01:rrn01)
//*********************************************************************
// Named Constants
/copy modules/qcopysrc,statuscopy
// Named Indicators
D indicatorPtr S * Inz(%Addr(*IN))
D DS Based(IndicatorPtr)
/copy modules/qcopysrc,scrncopy
// Subfile Fields
D rrn01 S 4P 0 inz(0)
//*********************************************************************
// Misc Fields
D* Monthly Payment
D m S 12P 2
D* Principal
D p S 12P 2
D* Interest
D i S 5P 3
D* Length (in Years)
D l S 3P 0
D* Monthly Interest
D j S 10P10
D* # of Months
D n S 5P 0
D* Current Monthly Int.
D h S 12P 2
D* Current Principal
D c S 12P 2
D* New Balance
D q S 12P 2
//*********************************************************************
// External Program Procedures
// Internal Subprocedures
D Init PR
D Main PR
D SubfileFilled PR N
D ClearScreen PR
D IsValidData PR N
D LoanPayment PR 12P 2
D principal 12P 2
D interest 5P 3
D loanPeriod 3P 0
D paymentsYear 3P 0
// External Subprocedures
///copy modules/qsrvsrc,p.string
//*********************************************************************
// Entry Parms
D AMORT PR extpgm('AMORT')
D AMORT PI
//*********************************************************************
/free
Init();
Main();
*inlr = *on;
/end-free
P*--------------------------------------------------
P* Procedure name: Init
P* Purpose:
P* Returns:
P*--------------------------------------------------
P Init B
D Init PI
/free
pgm = 'AMORT';
sflDsp = *off;
return;
/end-free
P Init E
P*--------------------------------------------------
P* Procedure name: Main
P* Purpose:
P* Returns:
P*--------------------------------------------------
P Main B
D Main PI
/free
dow (not F3) and (not F12);
write OVR01;
exfmt CTL01;
ClearScreen();
if (IsValidData()) and (not F3) and (not F12);
// Fill the header information
dPayment = LoanPayment(dLoanAmt:dIntRate:dLoanPrd:dPayYear);
dNumPaymnt = dLoanPrd * dPayYear;
m = dPayment + dExtraPay;
p = dLoanAmt;
q = p;
// Fill the table
if (SubfileFilled());
sflDsp = *on;
endif;
endif;
enddo;
return;
/end-free
P Main E
P*--------------------------------------------------
P* Procedure name: SubfileFilled
P* Purpose: Fill the subfile
P* Returns:
P*--------------------------------------------------
P SubfileFilled B
D SubfileFilled PI N
D isFilled S N
D x S 4P 0
D intCume S 12P 2
D extraPayCume S 12P 2
D payDate S D
D payment S 12P 2
D extraPayment S 12P 2
/free
isFilled = *on;
sflClear = *on;
write CTL01;
sflClear = *off;
rrn01 = 0;
x = 0;
// Setup the work fields
payment = dPayment;
extraPayment = dExtraPay;
payDate = dStartDate;
// Create records until there is a zero balance
dow (q > 0);
x += 1;
eval(h) h = p * j; // Monthly Interest
// Adjust for final payment
if (p < m);
m = p + h;
payment = p;
extraPayment = h;
endif;
// Calulate Principal
c = m - h;
// Calulate the new balance
q = p - c;
// Accumulate the interest and extra payments
intCume += h;
extraPayCume += extraPayment;
// Determine the next pay date
select;
when dTerms = '1'; //Yearly
payDate += %years(1);
when dTerms = '2'; //Semi-Annual
payDate += %months(6);
when dTerms = '3'; //Quarterly
payDate += %months(3);
when dTerms = '4'; //Monthly
payDate += %months(1);
when dTerms = '5'; //Bi-Weekly
payDate += %days(14);
endsl;
// Fill the subfile
sPayNum = x;
sPayDate = payDate;
sBegBal = p;
sSchedPay = payment;
sExtraPay = extraPayment;
sTotPay = m;
sInterest = h;
sPrincipal = c;
sEndBal = q;
sCumeInt = intCume;
// Move the End balance to the beginning balance
p = q;
rrn01 += 1;
write SFL01;
enddo;
// Return the calculated information to the header
dActPaymnt = x;
dTotInt = intCume;
dTotEPay = extraPayCume;
if (rrn01 < 1);
isFilled = *off;
endif;
return isFilled;
/end-free
P SubfileFilled E
P*--------------------------------------------------
P* Procedure name: ClearScreen
P* Purpose:
P* Returns:
P*--------------------------------------------------
P ClearScreen B
D ClearScreen PI
/free
c = 0;
h = 0;
i = 0;
j = 0;
l = 0;
m = 0;
n = 0;
p = 0;
q = 0;
dPayment = 0;
dNumPaymnt = 0;
dActPaymnt = 0;
dTotEPay = 0;
dTotInt = 0;
return;
/end-free
P ClearScreen E
P*--------------------------------------------------
P* Procedure name: IsValidData
P* Purpose: Validate the data on the screen
P* Returns: True or False
P*--------------------------------------------------
P IsValidData B
D IsValidData PI N
D isValid S N
/free
if (dLoanAmt <> 0) and (dIntRate <> 0) and (dLoanPrd <> 0) and
(dPayYear <> 0) and (dStartDate <> %date('0001-01-01'));
isValid = *on;
else;
isValid = *off;
endif;
return isValid;
/end-free
P IsValidData E
P*--------------------------------------------------
P* Procedure name: LoanPayment
P* Purpose: Calculates the payment
P* Returns:
P*--------------------------------------------------
P LoanPayment B
D LoanPayment PI 12P 2
D principal 12P 2
D interest 5P 3
D loanPeriod 3P 0
D paymentsYear 3P 0
D retMonthlyPayment...
D S 12P 2
/free
eval(h) n = loanPeriod * paymentsYear;
eval(h) j = interest / (paymentsYear * 100);
eval(h) m = principal * (j / (1 - (1 + j) ** -n));
return m;
/end-free
P LoanPayment E
This code has been tested against our loan calculator. If test vs. Excel, it was off about 32 cents on a $200,000 loan. I am sure that is do to rounding issues.