|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access Amortization Template 1.0 Microsoft Access Loan Amortization 1.0 Template A Loan Amortization is one of the more complex things to calculate properly. Sure, you can do basic amortization in an Excel sheet, but dealing with all of the rounding errors and applying payment overages to principal only is a nightmare. I cover the basics of Loan Amortization in my Excel Expert 6 class, and I have a more complex Loan Amortization Seminar which shows how this database was built. This database template is available for people who want just the database template, but don't need the video instruction. Features
Update
Sample DatabaseIf you'd like to download a sample copy of the database template, you can do so here. This is a non-editable version of the template which will let you see it work in action. Sample databases are available for 32-bit and 64-bit versions of Access 2007 or higher, and there is also a version for Access 2003.
Full Developer DatabaseOnce you have purchased the full developer database template, come back to this page and click on the Download button below. This will give you access to the ZIP file containing the Template ACCDB file. The full version will run on 32-bit or 64-bit Access 2013 or higher (possibly 2007, but I haven't tested it). There is also an older database for Access 2003 users.
Full Video SeminarIf you would like to learn how this database was built, again, get the Loan Amortization Seminar. It covers everything step-by-step. Please note that the full Seminar database is completely different from the simpler Template database available for purchase on this page. The Seminar database is much more advanced and includes extra features such as a different payment frequency (monthly, bi-weekly, etc.). Don't confuse the two. On this page you're buying just the Template database. If you want the more advanced database, you need to purchase the Loan Amortization Seminar. Support Not ProvidedPlease note that technical support is not provided to people who just purchase the Template. That's what the Seminar is for: to explain how the database works. If you have questions, you will just be forwarded to the Seminar page. LicenseFor internal use only. This template does NOT come with a royalty-free license. You may only customize the template for your business needs and for use within your organization at one location ONLY. You may not resell or distribute any form of this template to others without express written permission. Contact me for additional information on obtaining a license to distribute if you plan on including this template in a product you are reselling. Additional licenses are required if you plan on using this template with more than ten (10) employees or in multiple locations within your organization. Not a Finished ProductPlease keep in mind that most of my template databases are not designed to be finished products that are ready to go in a working environment. My templates are meant as starting points for you to customize for yourself so you don't have to reinvent the wheel. This requires that you have a basic understanding of Microsoft Access development. To work on most of my databases you should have completed my Access Beginner series and the first couple levels of my Expert series at a minimum. Most of my databases require knowledge of SQL and VBA as well. Keep in mind that most of my templates are not like off-the-shelf software. They're starting points for developers to be able to modify and use as their own. It really is going to be in your benefit to watch all of the prerequisite videos so you understand the database and how it works moving forward. This is not like QuickBooks where you just install it and use it. My templates are mostly designed for people who already have an understanding of how Microsoft Access works. Customize For Your NeedsIf you would like to discuss customizing this template for your needs, and integrating it into your current setup, please see my consulting page for details. While I no longer accept custom jobs that are specific to a single user, I may include your features in a future version of this template if they have mass appeal. If you are looking for custom enhancements made to this template just for you, visit my Developer Network. Technical SupportPlease note that technical support is NOT guaranteed for any of my courses, seminars, or templates. If you require help with modifying this template, you may post a question in the Forums, however an answer to your question is not guaranteed. If this template comes with an accompanying Seminar, then you should purchase that Seminar to see how the database was constructed. If not, then you should have taken the suggested courses. Most of my templates are designed on a Developer level and you should have a thorough understanding of SQL and VBA before attempting to modify them. If you have a problem with one of my databases, I will only support the unmodified database exactly as it's downloaded from my site. If you have modified it in any way, it may not be something I can help you with. You can post in the Forums, and I may be able to help you, but if the issue doesn't exist in my unmodified database, it's not something I can support. Questions?Got sales or customer service questions about this template? Feel free to contact me. If you have technical "how do I" questions about this template, you discover a bug, or want to suggest a new feature, then please post your comments below.
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #templates, microsoft access loan amortization template
IntroIn this video, I will walk you through the TEMP Access Amortization Template 1.0, a Microsoft Access database designed to manage loan amortization schedules with accuracy and flexibility. We will look at entering loan details, automatically generating payment schedules, tracking payments including overpayments and underpayments, handling rounding errors, updating schedules, and understanding how extra payments are applied toward the loan principal. I will also show you the main features of the template, demonstrate its navigation, and discuss how you can get either a free sample or a full editable copy for your own use.TranscriptWelcome to an Access Database Template walkthrough brought to you by AccessLearningZone.com.This loan amortization database was designed to get around some of the problems that simple Excel spreadsheets have. Now, you can calculate the monthly payment based on the loan amount, the interest rate, and the number of years, and Excel can do that just fine. You can also generate a full amortization schedule. However, one of the problems I always have with Excel is that rounding error. Sometimes, if you do not round properly, that final payment can be off by a cent or two, sometimes even a couple of cents. This database takes care of that by rounding properly. I have also covered loan amortization in some of my other Access classes, like Access324, but that was only a very simple amortization schedule. This database shows the beginning balance and ending balance for each period, and the principal and interest for each payment period as well. This database will track each payment made, and it will allow you to assign payment overages to go against the principal balance of the loan. For example, if their monthly payment is only $1,000 and they pay $1,200, you can put that extra $200 toward paying down the principal. The interest amount for each month is automatically recalculated if that happens, and the database keeps track of putting all the payments where they belong, into the correct payment period, principal versus interest, and so on. Now, let's take a quick walk through the database, and I will show you all the features. When you first open up the database, you will see a previous sample loan that I had entered in. You will see the loan information is up here: the interest rate, number of years, and loan amount. The individual payments go down here. Up to the right, you will see the total due as of today's date, and the regular payments made and the past due. This purple area over here is a payment tracking history, so you can see the exact payments that the person has made on the loan. Down at the bottom, you will see all the totals. I will go over what all these things are in just a second. Let's enter in a new loan. Come down here to the bottom set of navigation buttons and go to the next record. There is only one record in the database to start with. This will go to a new blank record. The bottom set of navigation buttons are for each loan, and this set here is for the individual payments. You can turn this off if you want to, because when you have multiple payments in here, you will get a scroll bar to go up and down. I will start by entering in the loan amount, let's say 10,000, the interest rate of about 5, and the number of years. I am just going to put one year in here. You can put as many as you want. As soon as you type all that information in, you will see the monthly payment is automatically calculated for you, and the number of payments is simply just the number of years times 12. If enough people want to see it, a future modification could be to allow a non-monthly payment. If you have bi-monthly payments or a quarterly payment, that could easily be entered into the database. It does not support it right now as it is, because there are some other calculations. But if enough people want to see that, I can certainly upgrade the database to include that in the future. You can change the start date right here if you want to, using a little calendar. Let's say it started in November. Now, the Calculate Payment button really is not needed. I put that on there when I was building the form. There is an after-update event in each one of these fields here that automatically recalculates the payment if this changes. So, if this goes to 6 for example, you can see the payment automatically updates. When you have the loan information that you want in there, come down here and click on Create New Schedule. This will create a brand new payment schedule. You will get a warning message because if you have previous data in there, this will erase that data. So, make sure you do not click on this afterwards. I will say yes, and you will see all the payment information automatically fills into the database. If you have a long mortgage, like a 30-year mortgage, that can take a few seconds to run. I wanted to do this without using advanced programming concepts like RecordSets, which would make things a little more elegant. Instead, I am just using DoCommand, GoToControl, and GoToRecord commands in a loop to simply add these records one at a time. There are more elegant solutions like direct SQL injection and things like that. But I went with a simple option that would be easier for people to follow along with. Here we have the payment number, the due date of each payment, the beginning balance at the beginning of that period, and the amount due. One of the things that aggravates me about most loan amortization templates, whether they are Excel spreadsheets or Access databases, is that they always come up with rounding errors. They do not round off the monthly payment. This might be like 10.8-something-something-cents. So, all of these are off. Then calculating the interest and such gets off. You will find that each one of these is exact to the penny rounding. The very last payment may be plus or minus a couple of cents to make that come out even, so the exact amount due is to-the-penny correct. Then we have the amount paid, the amount that the person actually paid for that period. That gets broken down into the regular payment and the extra payment. We will talk about extra payment in a second. They can pay more in a period to try and pay down their balance. The principal and interest for each one of the payments are properly backloaded, of course. Then you will see the end balance over here. The end balance over here is the same as the start beginning balance over here. Down at the bottom, you will see all your totals. This should be correct to the penny. Here is the exact amount of the loan. That is the total amount of principal. The total amount of interest paid is over here. Over here, we have the total due as of today. Since I backed this up, this started back in December of 2012. It is currently January 9th, so both of these payments are due, about $2,500. That will show you the amount due as of today, and regular payments made as of today, so you will see how far they might be. Here is their payment history. People do not always make payments that are exactly what their payments will be. Sometimes they overpay, sometimes they underpay, so this will give you an exact history of all of their payments, and you can see the exact date they made the payment on. Let's log a payment. I will come down here, click on Record New Payment. You get a couple of prompts. First, the payment amount, which defaults to the amount due. That is what their payment should be. I will hit OK. Then the payment date, which again defaults to today's date. Let's say this was December's payment. I am putting in some old information. So, let's say 12-15-12, and then I will hit OK. Now, here is what happens. First, the payment gets logged over here, so you can see the total amount of payments. Each payment gets logged, then it takes the money and applies it to the mortgage. Their 1284.11 is the amount paid, and that is exactly the amount of the payment, so it is the regular payment, no extra amount, and nothing here has changed. If they underpay, let's say it is the next month now. Let's say they underpay. Record a new payment. Let's say they only pay $500, and they pay it on the 13th. OK, the payment gets tracked over here, so you can see $500 was paid on the 13th. The amount paid was $500, regular payment $500. Now, let's say they come in with the rest of the payment. Again, record a new payment. This time, let's say they pay $900. OK, let's look at what happened. They paid $900 on the 19th. The amount paid for this period is actually $1,400. The regular amount is the amount due, and then an extra payment of $115.89 is now applied as an extra payment. This brings down their principal, because that amount goes against the principal due, which brings down the total amount of interest they owe as well. Notice that last payment now is even less, because they are paying down their principal. Now, it has been suggested as an option instead of making that extra payment go toward the principal, to apply it toward the next period. I know my car loan works that way, the car loan that I just finished paying off. If I pay more, it actually goes toward the next payment; they do not pay down my principal. That is an option; if anyone really wants that, let me know and I can possibly add that as an upgrade to the database for the future. As of right now, payment overages go against the principal. That is how most bank mortgages work, even though my car loan does not do that. But that could certainly be an option up here. Extra payments go toward the principal or the next payment, so we just leave the 1284 here and put the next amount down here for the next period. That is really a matter of what you want to do with that extra money. There is an Update Schedule button down here. You should not ever really have to click on that, because when a payment is applied, it automatically runs the update schedule. You can manually adjust these if you want to. For example, let's say this $900 check bounces. You just come in here and clear that, and then get rid of the information in here. I would click Delete, confirm that, and then come back over here and change this back to $500, and it corrects everything for you. So, that is the database in a nutshell. That is how it works. The copy that you download off the website is a working copy that you can play with, but you cannot actually edit it. For a small fee that is listed on the website, you get a full downloadable copy that you can edit. You can right click in here, go into Design View, change all these things, and play around with them. You can incorporate this into your own database. You can view my source code, come in here, and make whatever changes you want. You can distribute it freely if you want to, as long as you are not simply just reselling this. If you are going to customize it for your own purposes, that is fine. If you would like pricing on having me customize this for you, or if you have some custom changes you want to make, I would be happy to make them for you. Please contact me. The information is on the webpage on how to get in touch with me to make customizations for you. Also, if enough people want to see a video tutorial on how I built this database, I would be more than happy to put it together as a seminar. There is lots of good stuff in here. It took me about a day to build this. It took me probably a good solid six or seven hours. A lot of the code has to deal with the rounding and making sure that last payment comes out even, and applying payments where they belong. There is some tricky logic in there, but I would be more than happy to put together a seminar if enough people are interested. If so, contact me again; there is a link on the website, and let me know that you are interested in seeing that video seminar. If not, if you have any other questions about this database and how it works, please feel free to contact me. Thank you. If you are interested in obtaining a copy of my loan amortization database template, you can find it on my website at 599cd.com or AccessLearningZone.com under the templates section. You can download a free sample working copy of the database, and for a small fee, you can get a full, editable, royalty-free copy of this database as well. If you have any questions, please contact me at AccessLearningZone.com. Thank you. QuizQ1. What primary problem with Excel spreadsheets does this Access loan amortization database address?A. Difficulty in showing payment due dates B. Inability to calculate monthly payments C. Issues with rounding errors in calculations D. Problems exporting data to Word Q2. In this database, what happens if a payment is made that is larger than the required monthly amount? A. The overpayment is ignored B. The extra is applied to next month's payment by default C. The extra amount is applied toward the principal balance D. The extra triggers an error Q3. What feature does the database provide for tracking a borrower's history? A. Automatic payment reminders B. A payment tracking history showing exact payments and dates C. Real-time credit score monitoring D. Bi-weekly payment scheduling Q4. When creating a new loan record, which event causes the monthly payment to recalculate automatically? A. Clicking the Print button B. Entering or editing any of the loan amount, interest rate, or years fields C. Changing the database theme D. Viewing the payment history Q5. Why is there a "Create New Schedule" button in the database? A. To archive old loans B. To print the current amortization schedule C. To generate and fill in a new payment schedule based on current loan details D. To delete payment history Q6. What happens if you click "Create New Schedule" with existing payment data present? A. The existing data is updated B. Nothing happens C. You get a warning because it will erase the previous schedule data D. The payment amounts are doubled Q7. Which programming technique did the developer intentionally avoid in this database for simplicity? A. RecordSets B. Macros C. SQL injection D. Data normalization Q8. How does the database handle rounding in payment calculations? A. It rounds to the nearest dollar only B. It ignores fractions of a cent C. It rounds each payment and the final balance to the nearest cent so totals are accurate D. It accumulates rounding differences in the first payment Q9. What happens if a borrower underpays and then pays again within the same period? A. Only the first payment is recorded B. The excess payment is refunded C. The two payments are combined, with extra applied as an extra payment to principal if applicable D. The loan is marked as default Q10. What optional feature does the presenter mention could be added if enough people request it? A. Weekly payment support B. Applying extra payments toward future periods instead of principal C. Automatic SMS reminders D. Exporting reports to PowerPoint Q11. Which of the following is true about the sample database's editing options between the free sample and paid versions? A. Both allow unlimited editing B. The free sample allows editing but not saving C. The paid version allows full editing and customization, while the free version is non-editable D. Editing is only available with special admin credentials Q12. If a payment check bounces, how does the user correct the records in the database? A. Nothing can be done B. Delete the payment from the history and adjust the period's payment amount accordingly C. Edit the principal directly only D. Add a negative payment entry Answers: 1-C; 2-C; 3-B; 4-B; 5-C; 6-C; 7-A; 8-C; 9-C; 10-B; 11-C; 12-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on a walkthrough of a loan amortization Access database template. I designed this database to address some of the common issues that can arise when creating amortization schedules in Excel, mainly the problem of rounding errors that sometimes leave the final payment off by a cent or two. This Access database handles rounding correctly, ensuring all payments are accurate down to the last penny.I've covered loan amortization in some of my previous Access classes as well, like Access324. However, those were more basic examples. This template provides a much more detailed amortization schedule. It not only shows the beginning and ending balances for each payment period, but also breaks down both the principal and interest portions of every payment. It also tracks all payments made and allows overpayments to be applied directly to the principal. For example, if the scheduled payment is $1,000 and a borrower pays $1,200, that extra $200 is immediately used to pay down the principal. The interest for the following months is recalculated automatically when this happens. This way, every payment is allocated properly between interest and principal, for every payment period. When you first open the database, you'll see an example loan already entered. The main loan information, such as interest rate, years, and loan amount, is located near the top. Individual payments are displayed below. To the right, you'll see the total due as of today, the regular payments made, and any past due amounts. There's also a section showing payment tracking history, allowing you to review all recorded payments in detail. Totals are listed at the bottom for quick reference. To add a new loan, simply use the navigation buttons to move to a blank record. The lower set of navigation buttons lets you scroll through loans, while another set is for viewing the individual payments. Once you enter the loan amount, interest rate, and number of years, the database automatically calculates the monthly payment and the total number of payments, which is the number of years times twelve. In its current version, the template is set up for monthly payments only. However, I am open to expanding it to support different payment periods, such as bi-monthly or quarterly, based on user feedback. You can adjust the loan start date as needed. The form is set up so that whenever you change the interest rate, loan amount, or years, the monthly payment recalculates automatically. While there is a Calculate Payment button on the form, it's not actually necessary because of these built-in recalculations. Once all the information is entered, use the Create New Schedule function. This generates the complete amortization schedule for the loan. Note that if any schedule already exists, this will erase and replace it, so use care when creating schedules for existing loans. The schedule generation uses straightforward commands in a loop to keep things simple and easy to understand, rather than more advanced programming approaches. The resulting schedule displays the payment number, due date, beginning balance, and payment amount. Many templates, whether in Access or Excel, neglect to properly round monthly payments, causing minor discrepancies over time. By contrast, this template ensures each payment is rounded to the nearest cent and only the very last payment might be off by a small amount to make the total come out even. For each period, you can see what was paid, which is separated into the regular and extra amounts. Overpayments are applied to the principal, reducing total interest and potentially changing the final payment. The template automatically updates the relevant figures to keep everything in balance. Totals for the loan principal and interest paid are shown at the bottom. There's also an up-to-date summary of amounts due and past due as of today. Payment history is tracked carefully because real-life payments aren't always made exactly on schedule or for the scheduled amount. You can see the exact date and amount of every payment made. To log a payment, use the Record New Payment feature. Enter the amount paid (which defaults to the scheduled amount) and the payment date (which defaults to today). The payment is then logged into the system, applied accordingly, and reflected in the payment history and amortization schedule. The template handles underpayments and overpayments by splitting them properly into regular and extra amounts and updating future payments and interest calculations. If an overpayment is made, the extra amount always goes toward reducing the principal, which is standard for most mortgages. However, some loans, like many vehicle loans, apply extra payments toward the next payment instead. If users would like that functionality, I'm open to adding it as an upgrade if there is enough interest. There is an Update Schedule option, but you rarely need to use it manually because the database updates automatically each time a payment is recorded. If you need to adjust or remove a payment, just do so in the payment area, and the database will recalculate everything for you. You can download a sample working copy of the database from my website, though the sample is locked from editing. For a small fee, you can obtain the full, editable version to modify as you wish or incorporate into your own projects. The full version allows you to enter Design View, see the code, and customize all features. You are free to use it for your own purposes, but not simply resell it as-is. If you would like me to make custom modifications for you, feel free to contact me for pricing and details. Additionally, if there is enough interest, I would be happy to create a full video tutorial showing the step-by-step process of building this database, especially focusing on the logic behind the rounding and payment processing. For more information, or to get a copy of the loan amortization database template, visit my website at AccessLearningZone.com in the templates section. There you can download a free sample or purchase the full editable version. If you have any questions or need help, let me know through the contact information provided on my site. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListOverview of loan amortization database templateAvoiding Excel rounding errors in loan calculations Entering loan details including amount, interest, years Automatic monthly payment calculation Displaying full amortization schedule Tracking beginning and ending balance per period Tracking payment history for each loan Assigning payment overages to principal Automatic recalculation of interest after extra payments Navigation between multiple loans and payments Entering new loan records in the database Changing the loan start date using a calendar Creating a new payment schedule for a loan Warning and data replacement when generating new schedule Using form events to recalculate payment on field change Filling payment schedule with payment periods and due dates Proper rounding of payments to avoid errors Logging loan payments into the system Breaking down payments into regular and extra payment Applying extra payment to principal to reduce balance Handling underpayments and updating payment history Manually editing payment entries and correcting errors Viewing total principal and total interest paid Displaying total due and past due as of current date Deleting and correcting bounced payments in the database Updating the loan schedule after payments and changes |
||||||||||||||||||
|
| |||
| Keywords: templates access microsoft access loan amortization template PermaLink Microsoft Access Amortization Template 1.0 |
