An Amortization Table Using VBA - Our First Steps
Well, I hope everyone had a great 4th of July this past weekend! I had a fantastic weekend, and this week is turning out to feel fairly short - so that's always nice. Anyway, let's get to the business at hand. I wanted to pass along a file to everyone that I created some time ago, and was recently used to create an amortization table for a project I was doing. Actually, this is a slightly modified version with lots of comments and no error checking. For the original (and more robust) file, see here. The first is for learning, the second is for using. The file is very simple, and you should not be intimidated; it simply asks you for a few pieces of information, you press a button and voila! an amortization table is created. I wanted to share this with you because it demonstrates a few techniques we've been discussing over the past month (named ranges and macros) as well as a few other items that I thought you might find interesting (VBA and data validation).
Lets start with the ones we already know. If you open the file and make cell B4 the active cell, you'll note that in the upper left hand corner of your screen there is the word "Principal" in your menu bar; this was done via named ranges. Furthermore, if you examine the formula in cell B10, you'll notice that there is a (very longwinded) formula containing references to several named ranges: Principal, Int_Rate, and n. This was done to help clarify the formula as well as to make it easier to examine later if I forget how to calculate a loan payment. To understand what each of the named ranges refers to, simply click Ctrl-F3 (if this doesn't work, go to "Tools | Protection | Unprotect Sheet" and then try it again). You can examine what each named range refers to ... you could alternatively pick a cell (again - beware of protection) and type "=Int_Rate" (or whichever item you want to see) and the value will be displayed for you after you hit Enter. Try it out.
Now, onto the next "easy" topic (before we touch on VBA) - data validation. You'll notice if you try to enter a negative interest rate, the spreadsheet won't allow it. Or if you tried to enter a string such as "Kevin" into the Pymt Freq, it also won't allow it. The underpinnings of this clever little trick lies in data validation. To understand what is allowed and not allowed, pick a cell and go to "Data | Validation" (again, unprotect if necessary). You'll easily follow what is valid information and what is not. Use this technique whenever you update something on a continual basis (weekly, monthly, etc.) and only certain values are allowed.
Finally, onto the real mean-and-potatoes of the spreadsheet - VBA. If you have no interest in VBA, feel free to stop reading here :) Otherwise, hit Alt-F11 and find the name of the spreadsheet in the data pane to the left (my file is called "Amort_BareBones.xls"). Within that tree, you will find a section called "Modules" - double click on the module named "GenerateSched". What you find in the middle of the screen is all VBA code that dynamically creates the amortization schedule for you. But note, that much of what was done here was accomplished via macros, and then expanded with VBA. To understand the code, simply read the comments - I tried to put a ton in so you could see what was going on. Notice, however, that there are only 8 lines of code (!), and the rest are comments. One of the lines of code isn't even strictly necessary (turning off the cut / copy mode). If you downloaded the more robust file, there are not as many comments, a lot more code, and you will probably not understand much of it if you have little or no experience with VBA. But feel free to try it out, experiment, delete, add, etc. You can always close without saving if you mess up :)
Hopefully the comments have helped you out, but if you have questions, post comments or send me an email. Comments are much preferred, however, because then everyone gets to see replies and / or questions that will hopefully help them learn. Good luck!
Lets start with the ones we already know. If you open the file and make cell B4 the active cell, you'll note that in the upper left hand corner of your screen there is the word "Principal" in your menu bar; this was done via named ranges. Furthermore, if you examine the formula in cell B10, you'll notice that there is a (very longwinded) formula containing references to several named ranges: Principal, Int_Rate, and n. This was done to help clarify the formula as well as to make it easier to examine later if I forget how to calculate a loan payment. To understand what each of the named ranges refers to, simply click Ctrl-F3 (if this doesn't work, go to "Tools | Protection | Unprotect Sheet" and then try it again). You can examine what each named range refers to ... you could alternatively pick a cell (again - beware of protection) and type "=Int_Rate" (or whichever item you want to see) and the value will be displayed for you after you hit Enter. Try it out.
Now, onto the next "easy" topic (before we touch on VBA) - data validation. You'll notice if you try to enter a negative interest rate, the spreadsheet won't allow it. Or if you tried to enter a string such as "Kevin" into the Pymt Freq, it also won't allow it. The underpinnings of this clever little trick lies in data validation. To understand what is allowed and not allowed, pick a cell and go to "Data | Validation" (again, unprotect if necessary). You'll easily follow what is valid information and what is not. Use this technique whenever you update something on a continual basis (weekly, monthly, etc.) and only certain values are allowed.
Finally, onto the real mean-and-potatoes of the spreadsheet - VBA. If you have no interest in VBA, feel free to stop reading here :) Otherwise, hit Alt-F11 and find the name of the spreadsheet in the data pane to the left (my file is called "Amort_BareBones.xls"). Within that tree, you will find a section called "Modules" - double click on the module named "GenerateSched". What you find in the middle of the screen is all VBA code that dynamically creates the amortization schedule for you. But note, that much of what was done here was accomplished via macros, and then expanded with VBA. To understand the code, simply read the comments - I tried to put a ton in so you could see what was going on. Notice, however, that there are only 8 lines of code (!), and the rest are comments. One of the lines of code isn't even strictly necessary (turning off the cut / copy mode). If you downloaded the more robust file, there are not as many comments, a lot more code, and you will probably not understand much of it if you have little or no experience with VBA. But feel free to try it out, experiment, delete, add, etc. You can always close without saving if you mess up :)
Hopefully the comments have helped you out, but if you have questions, post comments or send me an email. Comments are much preferred, however, because then everyone gets to see replies and / or questions that will hopefully help them learn. Good luck!
posted by ktr at
9:24 PM
0 Comments:
Post a Comment
<< Home