|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Access Amortization Lessons Welcome to Access Amortization. Total running time is 6 Hours, 45 Minutes. Lessons
Resources
Update
Questions?Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you! Subscribe for UpdatesIf you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.
IntroIn this seminar, we'll explore how to build a complete loan amortization database in Microsoft Access. You'll learn how to calculate loan payments, generate detailed amortization schedules, handle multiple loans per client or provider, and track payments including partial and extra principal amounts. We'll cover creating various reports, organizing clients and providers, marking loans as active or inactive, and producing current loan value and accounts receivable summaries. This seminar is intended for users with prior Access experience and includes techniques for flexible reporting and efficient data management.TranscriptWelcome to the Loan Amortization Seminar brought to you by AccessLearningZone.com. My name is Richard Rost.A few months ago I released a real simple Loan Amortization Database template on my website. Lots of people downloaded that template and left suggestions. Many people left feedback saying they wanted to see a video of how I built the database and, of course, they made some suggestions for features to add. This new and improved Loan Amortization Database lets you calculate loan payments and generate a complete amortization schedule with flexible loan terms. This is an upgrade from the last database. You can specify monthly, weekly, biweekly, or quarterly for your loan payments. The new database lets you specify multiple loans per client. There is a client table and a provider table, so you can track loans per provider, bank, lending agency, or whatever it is. I added a bunch of new reports. There is a main menu now where you have a list of clients. You can double-click to open up a client's record. For the template, I only put first name and last name, but you can put all the fields you want in here - phone number, address, etc. To add a loan for a client, just click on the add new loan. Pick a provider, for example, XYZ Mortgages. Add a description if you want, such as home loan, and some notes if you want to put notes in. For the loan amount, let's say 100,000, and interest rate, how about 5. Set a start date, let's say 1-1. Pick a frequency - this is new - let's go with monthly. You can type in the number of payments if you want, or come over here and type in the number of years, how about a 30-year mortgage. Click on the years button; it will do the math for you. It's simple, but it's a nice little feature. Calculate the monthly payment - there it is. Then come down here and click on create payment schedule. You can see all of the payments are automatically generated and added to the subform here: the due date, the beginning balance, the amount due (which is the same for each payment), and the amount actually paid (which we will get to in a second). There is regular and extra: the regular amount toward the actual monthly payment, and then any extra they want to pay toward principal to pay the principal down. Then you have how much of that payment is principal and how much is interest, and your ending balance. There are totals down at the bottom. Now you can see because of the new data, this is the amount that is due today because we started the loan two months ago. Zero payments have been loaded in, so the past due is 1610. Let's put a payment in. We can track the entire payment schedule over here - the payments that have been made on this loan. So I will click on add a payment. What is the payment amount? Let's put in the default payment here, which is 536.82. What is the date of the payment? Let's say they made this on 1-2. Then I will hit OK. You can see the amount paid is added here to the schedule, and over to the right in the payment history there is their actual payment. The past due amount up here is updated accordingly. Let's put a payment in for the second month. Add payment. Now, this time they paid a little extra. Let's say they paid an even $600, and this was the 2-2 payment. Now you can see here the amount paid was 600. Another amount, which is the amount toward this month's payment, was 536.82, and an extra $63 went to pay off their principal. That will adjust their ending balance for this period and adjust the rest of the payments accordingly. People sometimes do that to pay off their balances early. If you do not want to allow that, you can simply change this yourself. Just change this to 536.82 and then move the 63.88 down to the next line. After this, you will see in their payment history the actual amount. This way, you can track what payments came in on what dates and for which amounts. If you want to print this loan, for example, to send a copy to the customer, just click here. This gives you a nice formatted report where you can see all the information about the loan as a whole plus the complete amortization schedule. This loan goes for several pages. At the bottom, you can see all the totals and the payment history. You can mark a loan active or inactive. An inactive loan will not show up in your reports. That is for when you want to put something in, maybe to print out a quote for a customer, or perhaps a loan that is paid off that you no longer want to see. Back here on the client form, you can print all the loans for this client. Here is the report. You can see the loan shows each loan by provider, the loan ID, the amount of the loan, start date, payment amount, and the past due amount. I will add another loan for this client. There is a second loan for a motorcycle, and now when I go to print all loans, you can see the second loan shows up right here - ABC Loan Corp. for his motorcycle. Likewise, on the provider's menu, you can see each of your providers. Here is ABC Loan Corp. I have two loans in here for them. You can print active loans by provider. There you can see the same information, just reorganized so the provider can see all the loans per client. There is also an 'all active loans for all providers' option. This shows you everything in this system so you can print it out for your own records. Here is an accounts receivable option where you can show provider, clients, loans, and payments. Here is a sample accounts receivable report showing each provider, each client, and each loan, the amount due, the payments made, and, of those payments, what is less than 30 days old, less than 60 days old, less than 90 days old, or more than 90 days old. As you can see here, Joe Smith is not doing that well on that loan. If you do not want this much detail, you can simply turn off specific bands of the report. For example, let's say I do not want to see the loan amounts. There is the same data with just a breakdown by client. You can also get rid of the clients and just generate a provider report. There you go. Finally, there is a current loan values report. This shows you the current value of the loan - essentially the ending balance plus whatever is due. So if the customer comes into your office right now and wants to pay off their loan, that would be their pay off amount. The database is perfectly flexible. Through the techniques that I teach in the videos, you can add whatever kinds of reporting that you want based around all of this data. In class I use Microsoft Access 2010, but I have also tested this with every version back to 2003 and it works fine in 2013 as well. Whether you are using 2003 all the way up to 2013, the techniques in this video should work just fine for you. The sample database file for this class can be found at accesslearningzone.com/databases. On this page, you will find the actual database file, the ACCDB file. I will also put it up there in 2003 format in an MDB file. If you are a paid customer and have purchased this seminar, you will have access to the full database file. If not, there will also be an encrypted version up there that anyone can download for free. When you load up and play with the database, you will not be able to make any design changes or see the code, but you will be able to work with the database and see if you have any problems or questions with it. One bit of warning that I will give you: this is an advanced seminar. I move along pretty quickly. If you have no background with Access, you definitely want to start with my Access beginner lessons. Do not jump right into this seminar. I assume you know how to build tables and forms and reports and all that good stuff. There will be a good amount of VBA programming in this seminar. You may find it to your advantage to start with some of my expert classes, like my Access 301 or my Advanced 1 class before taking the seminar. This seminar also is informal in nature. This is not one of my polished productions like my standard Access classes or even some of my other seminars. This seminar is pretty much just me sitting down, hitting record, and building the database. I am going to walk you through everything that I did, including all my mistakes, so you can see what I did while I was designing the database. This started out initially planned as a short 30 or 40 minute seminar, and it grew into over 5 hours because I kept adding stuff. You are forewarned. It is a little informal and there is a lot of advanced material. I move along pretty quickly. But I am pretty happy with the end result, and I think you will be too. If you have any questions, post them in the student forums. You will see the forums on my website, and if you are watching this online, you will also see the forum for each class pop up right next to it. If you do not understand something, you can post it in the forums, but if you want more direct help from me, you can visit my TechHelp page. Of course, if you have any questions about the seminar whatsoever, please feel free to email me. You will find contact information on my website at AccessLearningZone.com. QuizQ1. What new feature does the improved Loan Amortization Database offer regarding payment frequency?A. It allows for monthly, weekly, biweekly, or quarterly payments. B. It only allows monthly payments. C. It only allows annual payments. D. It requires manual input for each payment date. Q2. What tables were added to the database to improve tracking and reporting? A. Product and order tables B. Client and provider tables C. Employee and loan tables D. Vendor and item tables Q3. How can you add multiple loans for a single client in the new database? A. By creating a new database for each loan B. By using the provider's menu C. By selecting the client and adding a new loan record D. By importing loans from Excel Q4. What happens when you make an extra payment above the regular amount? A. The extra amount goes toward future payments B. The extra amount is ignored C. The extra amount goes toward paying down the loan principal D. The extra amount is refunded Q5. What is the purpose of marking a loan as inactive? A. To delete the loan from the database B. To prevent new payments from being entered C. To exclude it from reports and for loans that are paid off or quoted D. To transfer it to another provider Q6. Which of the following is NOT shown in the loan printing report? A. Complete amortization schedule B. Loan totals and payment history C. Detailed transaction logs for unrelated accounts D. Full loan information for the client Q7. What can be customized in the accounts receivable report? A. The interest calculation method B. Which data bands (loan amounts, clients, providers) are displayed C. The payment frequency D. The loan approval process Q8. What is shown in the current loan values report? A. The amount of interest paid last month B. The current value of the loan, including ending balance and due amount C. Only previous year's payments D. Upcoming marketing offers Q9. Is the database compatible with Microsoft Access 2010 only? A. Yes, only with 2010 B. No, it works with versions from 2003 through 2013 C. Yes, but only in Windows Vista D. Yes, but requires third-party add-ins Q10. What will you NOT be able to do with the free, encrypted version of the database? A. Enter new clients and loans B. Generate reports C. Change the database design or view the code D. See payment history Q11. What level of Microsoft Access knowledge is recommended before taking this seminar? A. No prior knowledge is needed B. Intermediate to advanced knowledge, including tables, forms, reports, and some VBA C. Only knowledge of Microsoft Excel D. Only basic knowledge of Microsoft Word Q12. How is this seminar described in terms of teaching style? A. Highly scripted and formal with no errors B. Informal with a walkthrough including mistakes C. Lacking content altogether D. Only theory, no practical demonstration Q13. Where can students go if they have questions about the seminar? A. Only call Richard Rost directly B. Post in the student forums or email for direct help C. There is no support available D. Comment on YouTube only Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-B; 9-B; 10-C; 11-B; 12-B; 13-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 covers the new Loan Amortization Database and all of the enhanced features I have added based on feedback from users. A while ago, I made a basic loan amortization template available on my website, and after many people downloaded it, they offered suggestions and requested a more detailed walk-through. So, in this seminar, I am going to show you exactly how I built the improved version, plus go over the extra features that many have requested.The new version of the Loan Amortization Database now lets you calculate loan payments and generate comprehensive amortization schedules using flexible payment terms. This includes support for monthly, weekly, biweekly, and quarterly payments. You can now track multiple loans for each client, store provider information such as banks or lending agencies, and take advantage of several new reporting options. There is now a main menu that lists all clients, and from there, you can open a client's record to view their details. The previous template only had first and last name fields, but now you can expand this to add whatever information you need, such as phone numbers, addresses, and more. To add a new loan, just enter the loan details, select the provider (for example, XYZ Mortgages), add descriptions and notes, and specify the loan amount and interest rate. Set the start date, then choose how often payments are made. A new option allows you to either enter the number of payments or the loan duration in years. When you enter the years, the database calculates the total number of payments for you. Once set, the system determines the payment amount and generates a complete payment schedule. The payment schedule shows all upcoming payments, including due dates, starting balances, payment amounts, and how payments are split between principal and interest. Within the schedule, you will also see regular and extra payment fields, so additional amounts paid toward the principal are tracked. The totals at the bottom help you keep an overview of the loan. If someone falls behind on payments, you can see the total past due at a glance. When a payment comes in, you just record the amount and date, and the system updates both the payment schedule and payment history accordingly. If extra is paid above the minimum payment, the database automatically applies the extra to the principal, which can help your clients pay off their loans sooner. If your policy does not allow extra payments, you can adjust the entry yourself to match standard payment amounts. For each loan, you can print a detailed report including all the loan information and the full amortization schedule. This is useful for providing a copy to clients. You can mark loans as active or inactive, which removes them from view in your current reports. This is helpful either for loans that are paid off or for generating quotes that you do not necessarily want to track permanently. Back on the client screen, you can print a report showing all loans associated with that client, organized by provider and including important details like loan IDs, amounts, start dates, payment amounts, and outstanding balances. You can easily add multiple loans for a client. These will then show up on all printouts and reports related to that client. In addition, there is a menu for loan providers where you can see a list of all providers, add new ones, and view or print active loans sorted by provider. This is helpful if you are working with several lending institutions and want to see which clients and loans are associated with each one. There is also a comprehensive report listing all active loans from all providers, so that you can keep a complete record for your organization. An accounts receivable report lets you break down current and overdue payments by provider, client, and loan. It shows total payments made and categorizes those payments by age, so you can see what is less than 30 days old, 60 days, 90 days, or more than 90 days overdue. You can adjust how much detail is shown in these reports, hiding certain fields or grouping information differently if you prefer a more streamlined view. Another report provided is the current loan values report. This lets you find out exactly how much is owed on any loan at the current moment, including any balances and amounts due. It is especially useful if a client asks for their final payoff amount. The database is designed for maximum flexibility, and the techniques I teach in this seminar make it easy to customize reporting according to your needs. While I use Access 2010 for the demonstrations, everything works just as well in versions 2003, 2007, and 2013. The sample database file, available on my website, can be downloaded in both ACCDB and MDB formats so that everyone can use it regardless of their Access version. Paid students get the full, unlocked database file, while anyone can download an encrypted sample version for free if they want to try it out first. The free version allows you to use the features but does not let you make design changes or access the code. I want to stress that this is an advanced seminar. I go pretty quickly and assume that you already know how to build tables, forms, and reports in Access. There is considerable VBA programming throughout the seminar, so if you are new to Access or programming, you will want to begin with my beginner and expert classes before jumping in here. My recommendation is to complete Access 301 or Advanced 1 before starting this seminar. The teaching style in this seminar is a bit informal and less scripted than my standard Access courses. I walk through the process as if you are sitting next to me, building the database step by step—including the mistakes I make along the way so you can see how I handle and correct them. The project ended up lasting over five hours since I continued to add new features based on your input. Be prepared for a fast-moving class with a lot of advanced content, but I think you will find the end product very rewarding and practical. If you have any questions during or after the seminar, feel free to use the student forums on my website. Each class has its own forum for posting questions or challenges. For direct assistance, you can also reach out to me through the TechHelp page. My contact information is always available at AccessLearningZone.com if you need to get in touch. 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 ListCalculating loan payments with flexible termsGenerating a complete amortization schedule Setting payment frequencies (monthly, weekly, biweekly, quarterly) Tracking multiple loans per client Using the client and provider tables Adding and managing client details Adding loans with provider selection and loan descriptions Calculating number of payments from loan years Automated loan payment schedule generation Viewing and managing generated payment schedules Processing and recording loan payments Tracking extra payments toward principal Adjusting principal and payment schedules after extra payments Viewing and managing payment history Printing loan amortization reports Managing loan status (active and inactive) Printing all loans for a client Printing loans by provider Printing active loans for all providers Accounts receivable reporting by provider and client Customizing report detail bands and breakdowns Reporting on current loan values and pay-off amounts Database compatibility with Access 2003 through 2013 |
||||||||||||||||||||||||||
|
| |||
| Keywords: Access Amortization Seminar PermaLink How To Build a Loan Amortization Database With Payment Schedules and Reports in Microsoft Access |
