Spreadsheets are integral to creating many types of financial and similar applications, including calculating many types of business and consumer loans. This article will show you how to use SpreadJS, a JavaScript spreadsheet, and its included 500+ function Calc Engine to create a dynamic mortgage amortization table.
Let's get started!
Ready to Get Started Now? Download Our Free Trial of SpreadJS Today!
Amortization Table Concept
When we talk about the amortization table in the context of a loan, it is a way of providing information by spreading the loan into a series of payments over a period of time. Using this process, the loan balance will fall with each payment, and the borrower will pay off the balance after completing the scheduled payments. Every Amortization table contains the below information:
- Scheduled payments
- Principal repayment
- Interest expenses
Keep following this tutorial, where we will show you how easily you can create and calculate your loan payments using Javascript Spreadsheet.
Using the Designer Component
As mentioned earlier, we will use the SpreadJS Designer component in this tutorial to create our amortization table for a property mortgage. The Designer component is an optional add-on that is highly customizable, flexible, and could be easily embedded into your web application.
In the first section, we will show you the loan details and how you can insert them.
In the second section, we will go through the amortization table.
Loan Details
The Mortgage Amortization provides a monthly amortization schedule. It also calculates the monthly payment amount and determines the portion of one's payment going to interest. To create it, we need to specify the details listed below:
- Property Price
This indicates the price of the property you want to own. We will use the Spin Button control from the Form Controls section under the Insert tab to specify the property price.
Spin Button allows you to easily increase or decrease a value and by a predetermined amount. You can define spinButton options by using the Format Shape dialog box. Control options give you the capability to define the spin button options and assign values as below:
After specifying the font, color, and alignment, the Property price will look as below:
- Down Payment
The down payment represents a portion of the total purchase price (in our case, the property price). To specify the down payment, we will use data validation. To insert a data validation, go to the Data tab and choose Data Validation.
After choosing the Data Validation type “list”, it will look as below:
- Mortgage Rate
Mortgage Rate indicates the rate of interest a borrower pays on his/hers mortgage. After inserting the data validation, it looks as below:
- Loan Term
Loan terms help you determine the monthly payments and interests that you, as a borrower, will have to pay for different time periods. We have limited our Loan Term to 15 years for tutorial purposes, as shown below:
After finishing, the Loan detail section will look as below:
Amortization Table
You will build your amortization table in the simplest way possible using our SpreadJS designer. The amortization table will list information about the beginning balance after each payment, all monthly payments, interest expenses, extra expenses (if any), and ending balances after each payment in relation to the specified term.
- Beginning Balance → It will indicate the starting Balance of the Loan Amount. After each payment, the Ending Balance will be the starting Balance for the next payment using this formula: ='Amortization Table'!J17
- Interest → It indicates how much of each payment goes toward loan interest. To calculate the Interest for each payment in the amortization table, you need to multiply the beginning balance of each payment with the mortgage rate divided by 12 months as in the formula: =F17*$H$9/12.
- Monthly Payment → It shows the amount paid each month to pay off the loan within the specified time period. To calculate the monthly payment, you need to use the formula below:
- Ending Balance → The ending balance is the net remaining balance. An ending balance is derived by adding up the transaction totals in an account and then adding this total to the beginning balance. Its amount is defined by the sum of the beginning balance and interest from which the monthly payment and extra payment (if any) amount is deducted. Use the following formula to determine the ending balance after each payment: =SUM(F19:G19)-SUM(H19:I19)
Finally, you have created the Dynamic Mortgage Amortization Table.
Download the example file here.
Importing to SpreadJS
You can import this file to SpreadJS by clicking on the File Menu>Import section and selecting “Import SSJSON File” as shown below:
Select the AmortizationTable_download.ssjson file.
You successfully imported the “Dynamic Mortgage Amortization Table” created in SpreadJS.
Learn more about Importing Files to SpreadJS here.
Contact us to learn more at us.sales@grapecity.com and visit the SpreadJS page.
Get the Free Trial if you'd like to learn more about what SpreadJS offers!
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
Kristina Ismail