Quick Amortization Schedule

by Barry Rochford

Recently someone asked me if I knew of an Amortization Schedule written for Alpha. I said, "No I didn't., but it's very easy to do". Well, I sat down and programmed a very simple Amortization Schedule.

Alpha very thoughtfully provides six "Financial" Functions for your use. They are Future Value (FV & FV2), Present Value (PV & PV2) and the only one needed for Amortization, Payment (PAYMENT & PAYMENT2).

You need several figures in order to calculate an Amortization Schedule:

• Loan Amount - $1000 (example)
• Interest Rate - 7%
• Number of years - 1

Using the above three values, you can calculate the Payment required to amortize the loan with the PAYMENT2 function.
PAYMENT2(loanAmount, (InterestRate/12),(Years*12))
gives the Monthly Payment.

Using the example, here is my expression:
Mo_payment=PAYMENT2(1000.00,(.07/12),(1*12))
the Monthly Payment will be $86.53.

Any Amortization Schedule breaks down the Monthly Payment into two parts, how much is applied to the Principal and how much is applied to the Loan Balance. NOTE: The amount applied to principal during the early years of a Mortgage will bring you to tears.

Now that we have the Monthly payment, we have everything necessary, let's use the 1st payment and walk through the calculations.

Step 1. Calculate how much interest is due for the month, remember the Payment is $86.53.
To_int=(remaining*rate)/12
'remaining starts out as the Loan Amount, rate is 7%(.07)
'the interest amount for the 1st month is $5.83 for this example

Step 2. Calculate how much gets applied to the remaining loan balance.
To_prin=payment - to_int
'subtract the interest from the payment, that goes to Principal.
'$86.53 minus $5.83=$80.69. No, it's not $80.70, if you round,
'you will be off at the end of the loan.

Step 3. Decrement Remaining Loan Balance.
Previous Loan Balance was $1000, we applied $80.69 to the Balance, giving us a new Remaining Loan Balance of $919.31.

Step 4. Go back and repeat Step 1 through 3 until the Loan is Paid.

Ok, now we know how to do it, lets make it into an Alpha Mini-application.

I created two tables, the first is AMORT_START. This table has one dummy field in it. Its primary use is to enable me to attach a Form to it. As an enhancement, I could have saved a Customer Name and the basic loan data to display in the browse and on the Printout.

Fig 1. "AMORT_START" table My second table, AMORT_EXT, will hold the calculation results.

Fig 2. "AMORT_EXT" table Before anyone points out that I'm violating the "Database Normalization Rules" by saving calculated total amounts, let me explain. I calculated a 30 year loan and when I displayed it in a browse having 3 Running Total Calculated fields, the delay was really very noticeable. If this happens on my PC, a PII 450, it would really be noticeable on a slower model. This is why I added the total fields to the table. Besides, in "real life" you don't ALWAYS stay within the lines.

Let's design a very basic, quick form to enter in our Data. I called my Form "Amort_entry". The only unusual feature is that every object on the form is a Variable. I chose to make the Payment Amount Field and it's corresponding Text Object "Hidden".

Fig 3. – "AMORT_ENTRY" form in design mode. I added a short Xbasic Script to the Term field OnChange Event to calculate the Monthly Payment amount and display it on the form.

''XBasic			Term OnChange Event
dim mo_payment as n
mo_payment=payment2(amount,(rate/12),(term*12))	'Calculate the Payment
var->payment=mo_payment
text6.show()			'show the Payment & Text Object on the form
payment.show()
payment.refresh()		'refresh the payment amount field
end

To the "Calculate Amortization" Button, I added an Xbasic Script to its OnPush Event. My Pseudo code follows:

Declare the Variables. 
Clear the existing "Amort_Ext" table.
Set up a Loop. 
Calculate Interest & Principal distribution for a Payment.
Reduce Remaining Balance, increment the Total fields.
Write a summary record to the "Amort_Ext" table.
When finished, close the table and display the "Amort_Browse" form.

The actual Xbasic script looked like this:

''XBasic			Calculate Amortization OnPush Event
dim t as p
dim pmt_date as d
dim remaining as n
dim crate as n
dim cpayment as n
dim to_int as n
dim to_prin as n
dim no_of_pmts
dim pmt_total as n
dim int_total as n
dim prin_total as n
dim count as n
pmt_date=start		'set up the Variables
remaining=amount
crate=rate
cpayment=payment
no_of_payments=term*12
count=0
pmt_total=0
int_total=0
prin_total=0
t=table.open("amort_ext",file_rw_exclusive)	'ZAP the extract table		‘clear the table
safety=.t.
t.zap(safety)
while count < no_of_payments		'setup the Loop
to_int=(remaining*rate)/12		'get int. amount
to_prin=payment-to_int			'get principal amount
pmt_total=pmt_total+cpayment		'update total fields
int_total=int_total+to_int
prin_total=prin_total+to_prin
t.enter_begin()				'create extract record
	t.pmt_date=pmt_date
	t.loan_bal=remaining
	t.rate=crate
	t.pmt_amt=cpayment
	t.to_interest=to_int
	t.to_principal=to_prin
	t.tot_pmts=pmt_total
	t.tot_int=int_total
	t.tot_prin=prin_total
t.enter_end(.t.)
remaining=remaining-(payment-to_int)	'update remaining balance
pmt_date=addmonths(pmt_date,1)		'increment pmt date
count=count+1				'increment count
end while
t.close()
form.view("amort_browse")
end

I wanted to display the calculation results on the screen, so I set up another form containing an embedded browse of the AMORT_EXT table. Alpha allows you to change Column Titles, but you cannot insert a Title longer than the Column width. I went to the Browse properties, and un-checked Column Titles. Then I was able to insert Column Titles with more detailed wording.

Fig. 4 - AMORT_BROWSE Form. Note there is a "Print" Button on my form. There is currently no Printout. This would certainly be required, and if you were to use this Mini-Application, you may want to make some enhancements in order to display and print possibly a Customer Name and/or Description, current Date, and the Loan Information on your Printed output.

NOTE: While I do not show any OnError coding, you should always include an Error Routine any time you are manipulating a Table behind the scenes. This way you will intercept errors, such as someone else using the Table, and allow a graceful exit or re-try for your User.

Click here to download the complete amortization mini-application.

Barry is an Alpha Five Developer living in Newtown, PA (believe it or not, PA has 2 Newtowns, he lives in the one that is just a couple of miles from where Washington crossed the Delaware). He can be reached at brochford@enter.net

4/12/99

Don't forget, we need your feedback to make this site better!

Return to home