Collaborate on Free Billing Software in Excel for Banking with Ease Using airSlate SignNow

Free billing software in excel for Banking

hello this is randy with excel for freelancers  and welcome to the loan manager training   in this week's exclusive training i'm going  to show you how you can create your very own   loan manager and that's going to be complete  with a full featured dashboard the ability to add   unlimited loans and select them on a click  are going to be able to add payments to any   particular loan and a full featured calendar  that is going to show us payments due   on the date it's going to be an incredible  training i can't wait let's get started all right thanks so much for joining me today  another week amazing training this week a high   demand training many of you have asked me for a  loan manager so i've gone about and done it in my   own special way not just any type of loan manager  but you're gonna be able to create loans whether   you're taking specific loans or whether you're  loaning out you're going to be able to add in   additional percentages create unlimited loans with  principal amounts loan dates set up monthly weekly   or any type of a payment schedule and we can  use this single click loan to select any type of   loan that you might have it's going to be a great  training i want to make sure that you always get   the updated training as soon as they're out the  best way to do that is to subscribe to my channel   if you like you can just click on the subscription  click button below we'll go ahead and get you   those as long as you click on the notification  that's going to ensure that you get the trainings   right away i create these each and every week this  application is absolutely free all you need to do   is click on the links down below i'll include  both for facebook and for your email just click   on put your name email for email or just click for  facebook and we'll get this workbook sent right   to you i'm really happy to create these trainings  for you each and every week we've done over 175 of   them and the best way to get these workbooks in  bulk is with my amazing 100 application workbook   i actually have more than that available to you  on the page if you want to get more but 100 is   just 37 i've got 150 available for 56 and now  actually 175 workbooks for sixty six dollars i   may not always have this exact offer available so  make sure you click on the link below and get your   150 work pack that's all of my best workbooks  in a single zip file i hope you pick that up   let's get started on the training because i've got  so much to show you in this training we're going   to be going over lots of great functionality we've  got some vba to show you we've got a single click   dashboard so let's get right into it let  me show you about it it's not necessarily   complex we have a relatively easy we just have  two database we have where we're going to keep   track of all of our loans this is our basic loan  information then all i've got is the tracking   all the payments per those loans so we track those  payments loans keep in mind each loan has a unique   id so loan home loan is unique id one the freder's  loan is two and the car loan is three so we can   create unlimited loans all we need to do is just  click the add new it's gonna let us add a specific   loan if we wanna load a specific loan all we need  to do is just click on here it's going to load   all the information for that loan including down  below any payments associated with that loan and   the date of those payments the amount also we will  have the total amount the principal amount and the   interest amount we're going to show you how to  calculate interest and principle and all that   great stuff so now of course we can get a lot more  detailed and loan managers it can be a lot complex   i've tried to keep it relatively simple but still  provide a full featured rich you can take this   and make it your own by adding all sorts of  features so this kind of gives you an idea of what   you can do and we have a dashboard the dashboard  is going to show all the loaned out loans that you   have all the interest that you might have earned  if you're offering those loans and if you have any   loans of your own that you've taken out all the  money you owe and all the interest that you've   paid in this little dashboard that's going to help  keep track of everything i've also got the loans   here payments will be able to add new payments or  select or edit payments if you have a payment here   you want to we're going to provide double click  feature so if we double click on this feature   we can then go ahead and change that easily just  clicking save and update it's going to save that   payment when you go back into the loans we'll see  the payments been updated if we double click on it   here it's automatically load so we're going to be  able to add payments we'll know the principal and   the entrance for each of those payments a payment  date and all sorts of things so it's really cool   let's get into it because i've got a lot to show  you i've got a really cool menu feature and a very   very simple way of creating these single click  menus something that i haven't shown you before   although we have done menus on click but i want to  show you a really cool feature that saves you even   more time so that's really cool i want to show  you that i'm going to get to that right now all   right great well let me just fix so basically what  we have is the shapes for each one in fact i need   to make a fix notice that these icons disappeared  i want to keep them right where they are so what   we'll do is we don't need to group them i've got  a group here called the dashboard group and that's   really important that group of shape is only  shown for the dashboard but i'm going to ungroup   that and i'm going to exclude these particular  icons i only want those for the main menu i want   those to appear always so what i did is i hold  down the control unclick those and i'm going to   regroup that we're going to give it the same name  dashboard group right here and now of course when   we click on we're going to be able to see those  icons that's what i want i want those icons to   remain consistent okay great so we've got that so  what do we have here we'll go over this a little   bit later on because this part is relatively  simple and it's basically summing out data   that we have already created so we're going to go  step right into the loan and step right into these   single click features when i make a single click  on these i want to load several macros basically   the one that's going to show us our payment  we also have some conditional formatting so   if you have seen my videos before you understand  that i usually leave about two columns columns   a and b for admin and what we'll do is we'll hide  these columns and that's a and b and these columns   are going to let us know what we have going on  we're going to go over the first part of it the   loan information here so when i load a specific  loan like when i load it here i want this to go to   true and then back to false so that's going to be  important i'll show you why a little bit later on   because basically i want to differentiate between  the changes when a user makes a change in here   or when the change that vba is making by loading  it all the details on that loan and basically what   it is doing is it's going to pull the data from  this database and it's going to load it directly   into this form here and we're going to use data  mapping something you may have seen before if not   no worries i'm going to show you exactly how we  do that so no problem if you have seen it before   a little repetition won't hurt okay so i want to  also know the menu right if i select on a specific   row including let's say row three here i want to  know that that row has been selected if i select   on loans i wanted this to change to five so this  is going to show the specific row that we have   selected and one of the reasons i want to do that  is because i want to use conditional formatting   if you'll notice this menu the select item looks  a little bit different in fact we can even make   it bold so how do we do that well the first  thing what i'll do is i'm going to highlight   these i'm going to go into the home conditional  formatting and i'm going to manage these rules   and when i take a look at it you see here we've  got two different things i've got one for the   color and one for the border so let's take a look  at this one right here because they're separate   because we've got different cells one's column c  one's column d so let's take a look at column d   and if we edit that rule in fact if we see that  if b2 equals the row then we want to conditionally   format it and basically what i want to do is i  want to give it this dark blue to light blue and   click in the fill effects we see it's basically  this dark blue here all the way to this light blue   here light medium blue and then what we're going  to do is we're going to use a vertical variant   here and we're going to fade it from dark to light  so that's it i also want to make it bold i've   also added some borders so let's take a look you  can't see these borders but they're actually white   notice the color what is selected so for that i  basically colored the top and the bottom borders   here i don't believe it called the right but i  can't see it because it's white which is you know   excel should change the color of the background  with white is selected i think that would be a   nice feature font i made it bold here so that's  nice we've got it the bold and the color is white   on the font that's going to help us out we could  probably do bold italic to make sure it stands   out a bit and then we're going to click ok apply  so that's it for that and now what we can do also   we're going to make those font changes those fonts  are based on actually on c not d because that's   where we're located so i'll update that also here  let's take a look on c what do we have here we see   b2 throw the same rule and what do we have here we  have a border there's actually i want the left the   top and the bottom colored white and the right  i want left empty right left empty just the way   it is now this is the one where i want the font  actually i want to change this to bold and i want   to change this to white and click apply because it  is see where actual text is taking place so let's   click ok click apply and now we see it applied  because our text is actually not in column d it is   in column c i've just moved it over to the right  using the tab so i've added the indent feature so   we can move that over so keep that in mind that's  why it won't affect here the text says no there's   no actual text in column d so we can actually  remove this because it's not really helpful at all   so it doesn't matter here what it is because we're  only focused in column c okay so we have that we   understand that y based of course on our number  located in row 2 here so that's it right here b2   is what we're focused on so that's how we're going  to know now we can easily select between it okay   but what about the macro that's actually going  to change that notice if here we have only simply   column e visible here we have columns f through  and then all the way over to actually p and in   this case we have payments which is going to show  up q through x and calendar is going to show us   y all the way through let's say a f or a g so how  do we do that well we do that inside vba that's   based on the selection change because when we  actually make a selection change we want something   to happen so let's take a look inside vba and see  just how we did that so inside the developers tab   we'll click on visual basic you can also click alt  f11 that is the shortcut that will get you there   if for some reason you don't have the visual basic  tab you can find it just click on the options   customize ribbon and you click the developers make  sure that is selected that's going to get you into   the vba we're going to focus on the sheet itself  loan manager this is where our code is going   to run we're going to focus on selection change  when that means when we actually make a selection   change first of all if the user selects a large  number of cells more than two what i want to do   is exit the sub that'll prevent any issues from  coming up now however if they make a selection   between c3 and d6 that's when i want something  to happen c3 to d6 well if they make a selection   there that's something to happen now keep in mind  this is a merged cell it should be a merged cell   this is a merged cell these this one's not this  one's not but if there are merged cells this one   shouldn't be if they are merged cells and we also  make a selection if this is greater than one and   it's a merged cell will exit out of the sub so  keep that in mind if you're trying to select on   a merged cell and you have this greater than one  it will not continue down because we're exiting   out any cells greater than one so keep that in  mind so if we make a selection c3 through d6   what do we want to happen well what i want to  happen is i want to take that target row and i   want to place it in b2 that's going to strike our  conditional formatting aloud then what i want to   do is i want to run a macro and this is a really  cool thing now the long way of doing it is running   one macro when we click here running another macro  a different macro here and here now essentially   we're running four different macros but what i  don't want to do is i don't want to write a line   of code saying if the user selects on d3 run  this macro if the user selects on c4 or d4 run   this macro this macro and this macro so that would  be four different lines of code right but what if   i want to do it all with a single line of code i  can do that if i name the macros properly take a   look at these tab macros right here these are the  macros that are run i have one called section one   one called section two section three and section  four those are for the four different lines that   i have now since they're so similar all i need to  do is change the number on the macro name section   one for the dashboard section two for the loans  section three for the payments if we look here   let's bring this over here section three for the  payments and section four for the calendar okay so   now it's nice to have if you run tab calendars tab  but if you don't you can actually reduce some code   using this little trick so what we do knows our  macro name is going to start with section we know   that regardless of what then all we need to do is  figure out which one we're selecting if i select   on row 3 and i want to get to section 1 how do i  do that i just subtract 2. if i'm selecting loans   but i want to run this macro section 2 then all  i need to do is determine the target row minus 2   and that's just what we've done inside our loan  manager so what we're going to do is we're going   to run a specific macro that macro is going to  start with the name section then we're going to   base the rest of that macro name on the target  row minus two so if they've selected three it's   gonna run the macro called section one if they've  selected row four it's going to run the macro   called section 2. so with this one line of code  here we can run unlimited types of macros assuming   and this is a very important subject assuming  that you've named your macros like this so this   is a great way if you have a huge number of items  in your menu it's a great way to reduce the code   just by naming now the downside of this is when  you look at this macro automatically you don't   really know what what that is right but you  could easily add a simple comment just say this   loan section so that's also true you can do that  just add a little comment because the name itself   doesn't isn't really helpful doesn't tell you  what you're running but just by adding a comment   here it's going to help you do that so that's  great i'm glad i got to show that to you so the   important thing is when we do run one of those  the first thing we do is run a macro that's going   to close everything clear all the taps so i've  created a macro and basically what that's going to   do is going to create all the shapes so notice in  the loan we have a group shape called loan group   in the payments as you can figure it out we have  a group of shapes and that is called payment group   so we've got called payment group here our  payments group and of course on the calendar   we have another one here called schedule group so  what i want to do is i want to hide all that first   and i also want to hide all the columns starting  in column e all the way moving over to column a   g i want to hide everything first then we're going  to only show what we need to then we're only going   to show either column e or we're going to show  column g through uh or whatever it is then we're   going to only show that so that's just how we do  that that prevents a lot less code so the first   thing we do is we create a macro called clear all  tabs regardless of whichever one they select one   two three or four we are going to run this macro  because the macro starts out with clear all tabs   clear all tabs and what that's going to do again  as i said it's going to hide the entire column all   the columns get hidden e through af entire column  hidden equals true that's going to hide everything   then what we're going to do is we're going to hide  the four individual group shapes the dashboard   group is the group of shapes associated with the  dashboard loan group associated with the loan   section and so on and so forth so this will hide  all the groups now that hides everything of course   what does it look like when i just run that when  i look like it run it does just that so there's   nothing here as you can see nothing that's not  really helpful but it is very very helpful because   it does everything we need to do in one swoop  then all we need to do is show ingly so if i   click on dashboard it's only going to show column  e at that point and it's only going to show the   dashboard group so that's exactly what i want to  happen so section one first the next thing we'll   do is we're going to unhide column e using hidden  equals false that's going to unhide it we're   going to show the display group of shapes called  dashboard group i want that display using visible   msoc true and then of course we're going to select  something inside that image just so that we can   select i want to select off this right when we use  selection change i want the user to easily click   it again if they wanted to so we select something  inside for example in this one we're selecting e3   in loans we are selecting i5 in payments we  are selecting s5 calendar we are selecting in   this case ac3 so i'm selecting something other  than the menu item that's really important so   that's what we do so each of those macros are  relatively the same all we're doing is we are   unhiding the associated columns we are unhiding  the associated shapes and then we're selecting   something so each of those sections we're doing  exactly that the only thing is in section two   if existing loan i want to reload the loan i want  to reload if for example if i make a payment to if   i add a payment let's say i create a new payment  and i want to let's just check on the loan see   what's loan home loan okay so if i want to add a  payment to the home loan and i click on home loan   and i add a payment the suggested principal is  1481.83 not add the payment but save the payment   save that save and update so when i save that  payment when i go back into load i want that   refresh and i want that payment to show up here  the one that i just did that's important that's   this one right here so i want to make sure that  we're actually reloading that refreshing loan   so that all the payments even the ones that  i just made refresh so that's important there   so we've got that there so that's why if sheet1  b4 does not equal empty what is b4 that's what   we're going to do in the next one b4 is going  to let us know the row that is the row that's   associated with this and i'm going to go through  these in just a moment that is basically the row   assuming that we have a row that's associated  with the database item of that specific loan   i want that loan to refresh if there's no database  associated with that for example we click add new   there's actually no there's no row associated  because it's a brand new one right so in that   case when we go back from payments and we select  on here there's nothing to reload because it's new   so we wouldn't want it so we're going to check  is you know b4 empty if it's if it's not empty   then we can reload the loan okay so but of course  all we need to do is just select on a loan and do   that also i should add new right we also have  conditional formatting here i'm going to remind   myself that when we add a new we want to take  off the conditional forwarding for the selected   loan this of course is based on the selected loan  row and it's going to be here i believe selected   loan rule so we also want to clear that out too  right we have a new loan i'll make sure to do that   i want to make sure that the selected loan  also gets deleted so we clear that out   it clears the conditional formatting with that  we also have conditional formatting for that   so let's continue on we're going to go into that  right now so we understand that's it for the tabs   it's relatively simple clearing them out showing  and displaying the hidden columns and the shapes   associated with that everything else is pretty  much the same so we don't need to go any more into   that that's cool for now all right let's continue  on inside the lone macros this is where there's a   lot of really cool things happening so i'm going  to show you exactly how we add new loans how we   calculate everything but let's move a little  bit down here so when i select on a loan what   do i want to do i want to know the menu row we  went over that i want to know the loan id i want   that to load automatically that's the id that's  associated with that each loan has a unique id   and now we're just going to start with one  so that's what the unique id is for the loans   and next up what i want to show you is the row  that's associated there we have some named ranges   that are going to help us let's go over to  some of the named ranges that i've created   so we can understand how we're going to we're  just going to focus on the loan name ranges   then we're going to get into the payment name  branches so we go into the name manager and then   we go into loan we're going to focus on we have  loan amount and it's relatively self-explanatory   the loan amount is going to be in using a dynamic  name grains we're always going to start with the   header row because that way if there's no data we  won't have any issues so we're starting with e3   the header row here so we can see that then  we're going to move one row down we're not   going to move any columns over i want to know how  many columns we're going to associate count the   number of columns but we're going to use column  a to count because a is always required that is   the loan id that is required we want to count with  the required field which is the loan id absolutely   of course names also required that's important  there's many required but it's always good to use   the id that's going to give us the total number so  we use the tab key we tab into it we see that it's   a loan amount so we have loan amount we have loan  date we have loan id we have loan name we have the   loan next payment date that's the next payment  that's scheduled that's going to be saved   inside here we also have the loan type what  type is it is the loan that's been taken out   or is it something that you have loaned out loan  that you've taken maybe for a car or house or   credit card or if you've loaned a friend money you  would be loaned out so we can keep track of both   of those types so when we add a new loan we're  going to keep track both of those so that we want   to know the loan out type all right so we've got  that i also have some named ranges for the months   i've got something for the calendar months  i've got weekdays here for the calendar here   and of course we've got years so this can help  us in the calendar but pretty basic there so far   okay so that's it so when i set the loan id that's  going to be set here it's going to be placed   there by vba and i'll show you how that's done  once we have that loan id we can determine the   loan database row if error we want to wrap it on  we're going to run a match based on that loan id   based on the named range loan id we're going to  want an exact match and we're going to add 3.   why do we add 3 because i'm looking for the row if  we look here if i if i get 1 right our first value   our match returns a one i don't want one i want  the row number so we need to add three because our   first value starts in row four so we always add  three to that we want to look for the row number   all right so we understand that because that  once we have that row we can then load up all the   information or save all the information using that  and then the next loan id in this case it's max   max i want the max of all the loans make sure  they're numerical values max plus one that's   going to get us our next one so when i click add  new and i create it when i save it all i need to   do is take this loan and just place it right in  here place it in b3 and just paste it in the first   available here located in a7 that's what i know  because i know four is going to be our next one   okay so what else we have a new loan whether  this is new or not true or false it's going to   help us know if it's a new loan we click add  new this should go true maybe i'm not going   to use this it may not be necessary why do we  need this sometimes and sometimes not because   i know if it's new or not why do i how do i  know because there's no row associated with   that so sometimes i use this sometimes not i just  put it there as a placeholder because maybe you   might want to know it in certain instances but if  this is blank if there's no row associated with   this we automatically know it is a new loan if we  select on an existing we know that there's a row   associated with this we know it's an existing so  we may not use this here all right so the selected   loan row i want to know the row associated with  that that's of course based on the conditional   formatting here if i select the different load i  want to use conditional formatting if i highlight   this go into conditional formatting manage the  rules you're going to see of rows very similar   basically this is the row i want to bold it i want  to use this fade and it's going to be based on b7   that's the selected row so if we go into the  format we can see basically i've given it this   fill effect from this medium to light blue i've  used vertical and that way it's going to blend   into our background here which is what i want and  then of course we can just put on a border if we   want also we can do the font bold if we want so  i've got that covered basically based on whatever   is in b7 so that's how we do it now when we make  a selection in f what we want to do is we want   to take that row and we want to place it right  in b7 so that's in again in selection change so   based on our loan management here but this time  we're focused on this f right if f4 through f99   f is the column we're focused on  starting with four that is going to be   loan selection selection of loan name we want  that loan to load so let's write that in selection   of loan name load loan but we can only do that  if f contains a value if they make a selection   on anything that's not a value there's nothing  that can be done so we want to make sure that f   actually contains a value it is not blank so we  do that here if not intersection f4 through f99 is   nothing nothing and not are double negatives they  cancel each other out so basically what we're   saying is if the user makes a selection change  between f4 through f99 then do something but also   there's another condition i need to make sure that  f actually contains a value if it's blank there's   nothing we can do so assuming that it is not empty  then what we want to do is b7 is going to take on   our target row then all we're going to do is run  the macro which i'm going to go over shortly it's   going to load that macro and that's it that's all  we need to do so that's how we get the conditional   formatting and then that row located in b7 i also  want a little lone name database row this is a   little bit different this is the same database row  but this one is based of course on the id selected   this one is based on the name right the name of  it so if we look here it is going to be based on   the name of whatever they select right if i select  here i need to find the name i need to know what   the name is how do i find the name well we know  the row is here and now what i want to do is i   want to get the name and i want to get the row  associated with that name what do i mean by that   i'll say i want to look up the names here and i  want to determine the row 4 5 or 6 based on the   name because that's going to help us load it when  i select on something i want that to load inside   here so how do we do that well we do that here  with just what we can use is an index formula   here and a match inside that so again we're going  to match i'm going to index f41 through 46 or   whatever we want to put in there can go down as  far as i'm going to use the row number as b7 we   know the row this is going to pull the name so  we're going to index we're getting the name out   of that and i'm going to match that name with  the loan name remember our named ranges for our   loan name these are all the loan name rates then i  want an exact match plus three remember plus three   this is going to get us our database row based on  the selected loan name once i have that i know i   can actually load that information i'll walk you  through that once we have that all i need to do it   then just place the id here and then load it up  so i'll show you that inside the macro but this   basically important thing is this is going to  help us based on the name selected it's going   to help us load that particular loan details  also i want to know the payments we're going   to use the payments we'll get into that once we  get into the loan payments per year i might as   well do it now i've got an admin here it's going  to help us out this is our frequency table i've   created a named range called frequency based on  these frequencies annual semi and i also want to   know the number of payments associated with that  frequency in an entire year so this is basically   one uh particular payment a year would be one  annual payment or weekly would be 52 payments   a year then i want to know the days associated  with that of course in annual 365 weekly so i   need to know the weekly the days associated with  that that's going to help us calculate our loan   data all right so we've got that just that one  frequency that's all we may be adding to this in   the future if you want or you can download you can  add lots of things to this admin moving on so what   i want to do is i want to know the payments per  year number of payments per year how do we do that   well what i'm going to do is i'm going to run an  index based on the admin c5 to c12 right i want to   know the number of payments so if i'm going to  admin if i'm going to run an index based on c5   through c12 what i'm going to do is i'm going to  look this up i'm going to base it on the selected   frequency that the user has made that and i'm  going to return whatever so if they've selected   semi-annually i want it to return true if they've  selected monthly i want to return 12. so that's   just what we did in here notice if i change this  to quarterly this number is going to change right   so it's based on an index on that so that's what  i want to notice then we're indexing the admin   we just went over that we're going to base it on  the match this is going to give us the row number   frequency we're going to match the frequency right  we have that frequency i showed you this named   range based on those frequencies i know i'm moving  quick but we got a lot to cover feel free to watch   this video as many times as you want slow down  if necessary so we're going to get an exact match   that's going to get us our row number remember  we're here inside the index we want the row number   then we want the column number since we're  indexing and we want to return the value   located in those weeks c 5 1 is our column  and if there's an error we want to show blank   this will return our payments for year i also  want to know the loan type if there's a loan   type this is going to be for the payments so i'll  show you that based on the selected payments so   as soon as we select a payment uh let's we can  go in here select a payment here then what we   can do is we see we and now have a loaned out so  this is going to be based on a selected payment   how many low what did this particular loan here  we'll go over this but it's going to show what   type it is so keep that in mind and then select  the payment days we also want to know the days   how many days in that again we're going to  use index d5 here this time what i want to do   is i want to determine the days just like we did  here but this time the days associated with that   so back into the loan here we go i want to know  the days associated with this loan so how do we   know that no this this is 30. if i change this of  course to annual it's going to change the 365. all   we're going to be doing is running another index  based on this right but this time i'm returning   the number of days that's going to help us in  our formula so it's returning whether if it's an   annual it's going to return 365 monthly 30 and so  on and so forth that's going to help us calculate   our total interest and our all of our information  that's going to help us so we have that so now we   understand at least this section let's go into  the loan we'll get into this section we go over   the payments this is going to cover the loan so  how do we actually load that loan up how do we   get this information in from this table well what  we're going to be doing is we're going to be using   a specific data mapping so let's take a look  at this i5 is over here our loan name i5 if we   look over here in the data mapping we see l5 is  associated with our loan date l5 here so what we   can do is when i want to save this information to  our database whether it's an existing row or a new   row all i need to do is look in here see what's in  here and place it in the associated column whether   it's d e so all i need to do is run a loop from  here to here and then just look here whatever's on   the share place it here whatever is in i7 place it  here l7 place it here it's called data mapping it   removes a lot of code no matter how many columns  you have you really only need three lines of code   to bring that data into the database and also  to take the data from the database and load it   inside your form it's a great way so i hope you'll  use data mapping and i go over it very very often   because it's so powerful so let's get into the  database and show you how we both load this how   we select on and actually load it bring the data  in and of course how if we make any changes how   we're going to save it so if i want to make that  change to six and then we can save it update it's   gonna automatically save that loan it's going to  be updated to six year terms so that's going to be   inside the module called loan macros this is the  one we're going to focus and we're going to focus   on the first one called save update and i'm going  to move a little quick because we've got a lot to   cover in this training i try to keep them within  an hour but this one will go a little bit longer   but stick with me i've got a lot to show you so  first of all we're going to say the application   worksheet function count a this is a great  way to know if any of the fields required are   empty so that's what i want to know i want to  know when i add a new loan these are required   fields we want to make sure that we have those so  what i'm going to do is i'm going to check any of   these to see if they're required i don't think  next payment date is required we'll go into home   conditional formatting manage rules we're just  going to delete that rule but it's helpful for   calculating the next payment but it's not required  so let's click ok so what about these if any of   those are blank so what i want to do is i want to  count all the values in here and if it's less than   in this case 8 i want to know if it is and let  the user know so that's just what we do in here   application worksheet function count a basic i5  through i11 l5 through l11 so all we're going to   do is count everything from i5 to l11 l5 and count  all the text in there if that text the number of   cells are less than 8 then let the user know so  it's a great way to check for required fields in   a range like that count a using the function this  is the range if it's less than 8 then please make   sure to fill in all the required fields before  saving so now what i want to do is i want to   determine if it's the new loan or an existing loan  now remember correctly we had told you that this   loan database is b4 is going to be empty if it's  a new loan if it's an existing it's going to be   full so we can use b4 to let us know if that is  so if range b4 equals mp we know it's a new loan   in that case what i want to do a few things i'm  going to take the next available loan id that's   this 4 right here i want to do one thing i want  to place it in here and then i also want to get   the first available row in this case seven and  place that four right in here on a new loan so   that's what we're going to do right here lone row  is going to be the first available raw in c2 using   end excellent plus one that's going to give us  our first available row then what i'm just going   to take that next loan id and b5 and place it  right in b3 then again last thing we're going to   do is going to in column a of course c2 and the  loan row that is our loan database we're going   to place that new loan id these three items we're  going to only do for new loans if it's an existing   loan wheel i need to do one thing and that is  to extract the loan row from whatever's in b4   once i have all the information everything else  we're going to use data mapping everything else   regardless these three lines what it's going to do  is going to take all of the information from these   fields here and place them in the associated row  whether it's a new or an existing row it's going   to place everything there regardless if it's new  or not and so we do that just in the code 2 to 13   we're going to map we know we just placed the loan  id if it's an existing it's already there if it's   new we already placed it there so what i want  to do is i'm going to loop so if we take a look   at this this is column two of course column b is  column 2. then what i want to do is i want to loop   all the way to column 13 here i want to take this  information run this loop i want to look in i 5   which is here and i want to place it right in  column b i want to place whatever's in l5 and put   in column c and so on and so forth so that's just  what i do inside the data mapping so we're going   to do that sheet 2 the loan row we've already  associated the loan row either here or here the   loan column which is variable from 2 to 13. we're  going to take that information and we're going to   put inside there whatever is in the range remember  that range where is it found it's found in row one   of the long column it's found inside row one that  is our range i5 l5 i7 so we're going to look in   that and place it there that's going to save us  update that's going to run it and then we just   want to message box letting the user know the loan  was saved pretty simple great so let's take a look   inside this load manager now we know how to save  it to the database but let's look at some of the   calculations here this is really cool so we've got  the number of payments i want to know the number   of payments in our loan summary how do we get that  well we know already we know the term l9 is going   to be the term the number of years but i also want  to know how often we're paying if we're paying   monthly and that's going to be 12 we're going to  pay that over 30 years it's going to be 360 number   of payments but a simpler loan such as a car loan  three years monthly is going to be total of 36   payments so how do we determine that well that's  of course just based on this number here b9 is   going to remember it's going to payments period  this is based on this monthly so we that's why   we extract this 12 because i can multiply this 12  by the number of years and then we can determine   exactly how many number of payments so that's  the number of payments next up what i want is   the estimated payment amount estimated payment  we're going to use a really cool formula called   payment this is a very very cool formula let's  take a look inside this payment and see what it is   i'm going to click on here and that's going to go  over the excel help and that way we can show you   exactly what this particular formula is basically  what we're looking at is the rate the npr which is   the total number of payments for the loan the pv  which is otherwise known as the present value or   also known as the principal value fv  which is optional type which is optional   so the rate of course is the interest rate we're  going to put that now the interest rate of course   if you have an annual interest rate of 4.5 but  it's on monthly we do need to divide it by the   number of months the nper required this of course  is the total number of payments for the loan so we   have that we need to know that and we also want to  know the pv this is the present value or the total   amount of that series future payments worth also  known as the principal right principal value or   present value of the loan so understanding that  we have just that in here so we've got we first   need to know the rate right the rate of course  is based on i9 divided by b9 remember number   of payments in this case 12. so that's going  to get us our rate of course our nper that's   going to be 05 as we can see here is the number of  payments we've already calculated that that's 36   then what we want to have is the present value now  it's going to be in the negative right make sure   it's a negative because that's going to actually  turn it to be a positive because it's money that   you owe but in this case we want to show it as a  positive so we're reversing that l7 is that this   formula is going to automatically calculate the  estimated payment amount pmt very very helpful   now what about when i want to get the total  estimated payments the total estimated payment   is simply just that we're going to multiply l9 in  this case our estimated payment amount times b9   how many actual payments are we going to go times  06 times the amount of that payment this is going   to give us the total estimated payments i want the  total estimated payments here that's going to get   us in this case 16 000.063 34 cents that's going  to get us our total estimated payments what about   the interest well if we look in here it's 07 minus  l7 07 here minus l7 here of course we know that   if we know that our total payments are here but  we know our loan amounts here we can easily get   the interest amount based on that so we know that  we're going to be paying 1 000 in interest because   if our payments over time are 446 per payment that  means we're paying a total of 16 063 but our loan   amount is only 53 we know the interest amount is  there now we have the total number of payments i   want to know how many payments have been made  already all we need to do is to determine the   payments we're going to go over these named ranges  but again we're going to basically be based this   on the payment loan id all the loan id based on  b3 and that means i want to know all the payments   for this loan id b3 what does that mean that means  when i go into the payments here payments have a   payment id and they also have a loan id associated  with so i want to know the total payments for that   specific loan id loan one loan two or whatever so  all i need to do is sum the payments based on the   loan id located in b3 that is going to help us  so we can do that so we know the total payments   i also want to know the actual interest and the  principal applied let's go over the formulas real   quick on this payment since we're focused on that  payments now we're focused on payment named ranges   these name ranges are associated with the payments  database here so if we tab over using the tab key   we're going to see that the loan id is just that  the loan id payment associated with the payment   the payment amount is the total payment amount the  payment date is the date that's been associated   with that payment we have the payment id this is  the unique payment id for per payment i also have   the payment interest amount i want to break it  down how much interest was paid for that payment   and i want to know the loan type of course for  that payment whether it's a loan taken or a loan   given out and also i want to know the payment  principal amount that's right here the principal   amount associated with that so that's going to  help us calculate all that so if i want to know   the actual interest all i need to do is sum all  the payment based on the loan id and the payment   interest amount so it's going to sum all the  interest amounts based on that specific and of   course you can imagine the principle is the same  thing but in this case we're actually summing the   principal amount that named range now we know all  of the total payments all the interest and all the   principal that's been applied for this specific  loan that's it so let's continue on we already   focused on in our load we already took care  of the save and update but what about the low   new when i added low new all again i want to  do is just clear the contents and remember we   add a new loan there's one more thing i want to  add to this when we add a new loan i want to make   sure that this selected loan is also cleared up  based on that so selected make sure we add b7   to that cleared contents so i'm going to do  that right now b7 we're going to add to that b   7 and then we'll use a comma here and that's  going to do so we're going to clear out all the   associated fields then we're going to select i5 so  now when i click add new that's going to clear out   the selective so when i select it add new is going  to clear it up that's what i want so it's pretty   simple the add new there's no button change here i  kept it really simple as far as the button simply   add new and save and update try to keep it simple  on the button sets moving on now that we have what   about load loan load loaner that's going to take  the information from this database and load it   into this form right here so when we load a loan  we certainly need to make sure that we actually   have a loan name database row or something  i want to make sure here if i select a loan   this database row is critical because it's  based on the name that we select so without this   here we can't do anything we can't load any loan  so we want to make sure that b8 actually contains   a value so we do that that's the first thing sheet  one if b8 equals mp then please enter a correct   loan name or so i should say select probably but  i doubt it'll be empty but just in case select   a correct loan name so next up i want to put  application calculation to manual we've got a   lot of calculations in this application so we want  to turn that off at least while loading then of   course make sure that you set it back to automatic  before the macro ends that's really important   i'm going to set the load load to true in b1 i  want this to change from true or false based on   that before the end of the macro we're going to  set it back to false so moving on once we have   that i want to clear any payments that have been  associated i want to clear all the way this is   going to be the hidden this will be the id that's  associated with the payment so i want to clear out   all the way from g17 on out i want to clear any  payments that were associated with loan so that's   very important so after we clear those payments  out then what i want to do is i want to put the   loan row into a variable from v8 that's going to  be into a long variable then what i'm going to do   is run reverse data mapping this is basically 1  to 11 why all the way 1 to 11 and we notice here   was 213. why the different rows well the first  thing i want to do is in this case i want to load   the loan id for into b3 and i don't want to  load the total payment and total days these are   calculated amounts these are calculated so i don't  i want to stop here at column 11 so i want to go   from one to column these are calculated meaning i  don't want to put 360 back into here right because   they're calculated in fact it would come to here  i don't want to put it back in here into o5 notice   why is that because look 05 is where we have it if  i put it back inside here it's going to erase this   formula i don't need it here right it's a formula  i don't want to put it back i only want to take   this 360 and i want to put it inside the database  but i don't want to reverse it so anything i don't   want to reverse i don't want to put 360 back in  here because it's calculated that'll clear out   my formula i don't want that so we're only going  to go from 1 to 11 it's going to stop right here   this column column k it's not going to bring  in column and i don't need it because they're   calculated properly so moving on so 1 to 11 again  all i'm going to do is i'm going to take whatever   is in here and put it in i5 whatever is here  l5 and so on and so forth that's going to fill   up all these fields once they're filled all i need  to do is then determine all the payments that have   been made with that specific load i've got all the  payments in this database but what i do i need to   extract only for example the one like for example  if we're loading loan one i only want to know the   one so what we'll do is we'll put that through an  advanced filter we need the criteria for that it's   going to be based on the loan id so that means i  want the results only those payments associated   with this specific loan how do we know the loan id  let's look at it in b3 so all i need is a straight   formula bringing it in loan id so this is our  criteria this is our results notice this is a   good trick i want you to pay attention to this  this is very important because it's going to help   you out a lot look at this take a look at this  column here this is two columns notice we have   principal amount occupies two columns just kind  of the way it worked out so when we're bringing   information in i've got actually look it's one two  three four five columns of bringing it in but i've   got in the payments we've only got really one  two three four and five here but five including   the payment id is six if we add in the payment  id it's gonna actually be six here's the six   so we've gotta have six values and five columns  so how do we do that all we need to do is match   it in other words there's a blank column here here  there's essentially a blank column here here's our   column with the value here's our blank column so  if we match the column mapping we can just bring   it all in one shot to do that we need to make sure  that our origination matches our destination this   is our destination we need to match the number  of columns all the way here exactly like it if   there's a blank column put there's a blank column  so in this case there's a blank column here   after the principal amount there's a blank  column and then the interest amount so after   the principal amount there's a blank column then  the interest amount i want to match it exactly   but to do that in an advanced filter what we need  to do is we need to make sure that if our results   have a blank column we need to make sure that our  original database has a blank column so when i run   that advanced filter i'm going to include column  j column j is blank that's exactly what i want   if it's not blank if i run our advantage here and  it says there's it's going to tell me there's an   issue there's a blank column in our results but  there's no blank column in our original data so if   we include that blank column in our original data  then run our criteria then run our results we can   do that it's all it's going to do is return blank  that's what i want then all i need to do is just   copy this without the headers copy this and bring  it right all the way here very simply in one line   of code so keep that in mind so i'm going to walk  you through the steps as we do that inside the   code so we've done our data mapping now what we're  going to do is we're going to set the loan load   to false b1 set that so now what we're going to do  is if we're going to select on something before we   load in the papers i just want to select something  but keep in mind that if i run this mac you ever   have the problem you're running a mac let me just  comment this out so you can see what that would   look like so i'll show you an err and then i'll  show you the way around it so here's the error   now what i'm going to do is i'm going to run this  macro here i've just commented this part out of it   now i'm going to run this macro and we're going to  get this method of range of class failed so debug   why do i have this this line of code works  all i'm doing is selecting why doesn't it work   it doesn't work because i'm not on sheet1 i'm on  what what sheet am i on i'm on the loans sheet and   then if you see the loan sheet of sheet2 that's  why we wanted to share i cannot select a cell   unless we're on the sheet if i click here and now  i continue on it's going to allow us but the best   way to avoid that especially is just to check  to see if you're currently on the sheet so we   can do that if the active sheet code name equals  sheet1 then select it so now if i add it and i go   i'm on this sheet and i run this macro we're not  going to have the error because we're checking is   the active sheet sheet1 if it's not don't select  it right we can't select a cell if we're not on   actually on that sheet so it's a great way to  avoid that error and check especially when you're   producing these columns you don't know what sheet  we're going to be active on or when you're running   through and especially when you're programming it  you might want to look at the data and you don't   want to be on that sheet but you want to run the  full code so it's a great way to do that so now   we're ready to load the payments we're going to  focus primarily on sheet three that's where our   payments database are located the first thing  i want to do is i want to calculate everything   and that is because i want to make sure that  everything we have here we've got a lot of   associates the all these formulas here i want  to make sure they're all calculated properly so   we do that calculation it's important because  we've turned off calculation here but i still   want to calculate some value so we're going to  calculate here we'll turn it back on down here   so the first thing what i want to do is i  want to get the last payment row if we're   going to run our last in this case 11 we're  going to run that advanced filter based on   all the way on our first column starting on  a3 all the way through i and then including j   and the last row we need to run that  advanced filter so that's just what we do   inside the code i'll bring this down so we can  see the data as well as we work through the code   so we're going to calculate it m4 clearly constant  i'm going to clear the prior results this is not   always necessary but it's a good practice to do  that so we're going to clear any prior results so   that's important then what i want to do is in case  there's no data if the payment row is less than   four that means there's no payment so we're going  to skip all of that and go right here then we're   going to do calculations so assuming that there is  data we can then move on so the first thing i want   to do is run that advanced filter from a3 remember  we're including that blank column including column   j very important going to run our criteria is  going to be k2 through k3 that's our criteria   so now we just want our results all i'm going to  do is put our results from m 2 through r2 that's   where our results are going to go that's just what  i've done here inside the advanced filter here   results are going to go to criteria copy 2 range  m2 through r2 unique equals false so that's fine   case we have two payments on the same date for  the same amount fair enough that's okay too next   up i want to get the last results row if the last  results was less than three that means we have no   payments otherwise if it is last row i want to get  it the last row once i have the last one i then   want to sort the payments i want to put the most  recent date at the top and the least recent date   at the bottom sort of once i'm finished sorting  i'm going to bring all that data over including   the paym