Customizing templates to fit your unique needs

- If you're about to create a business type application, say a loan amortization schedule, don't overlook the fact that Excel has built into it quite a few different templates, they're called. And this is one of them. I've modified this slightly. I want to show you not only how to open one of these but also how to make changes to it that could be to your advantage. The Loan Amortization Schedule that we're seeing here is flexible, in that the heart of it actually is the list that we see starting in Row 18. It shows the month-by-month payment, in this case, a payment on a house loan, over a thirty year period.

First of all, how do we find this? On the File tab in the ribbon, we can choose New. Now, I've opened this recently so it appears in my list here. You wouldn't see this unless you had opened it recently and you might not see it at all as you look up and down this list of templates. The beauty of these is that they have nice designs and they've got a lot of built in formulas. You don't have to do much work with them at all. If you're not seeing the one you want, you can click in the panel right here. Type, for example, loan, Enter.

And you're likely to see Loan Amortization Schedule. Now, there's some variations on them here too. And you'll might see some other kinds of calculators that might grab your eye. Just double click one of these. And here's the generic Load Amortization Schedule. And, without knowing much about it, if you were just looking at this, you could type in a loan amount, say it's 400,000 dollars for a house, 4.5%, we'd type in the next entry, below it, for the interest. Loan period in years. Let's say it's a thirty year loan.

Number of payments per year, twelve. Starting date of this particular loan, perhaps, is January 1st, 2015. And as soon as I press Enter here, watch the lower part of the screen, from Row 18 downward, we see the month-by-month breakout, and then off to the right, a loan summary, number of payments, total interest. Now, if we add Optional payments, right here, I'm gonna type in a hundred. Suppose we decided to spend $100 extra per month. Let's say we can afford that. So, this is going to change the loan.

The length is gonna be shorter. No longer will it be 360 months. It's gonna be 327. Now, when you start to click on these cells, you realize you can't click on them. That's because a lot of templates, the thought behind a lot of templates, is that you shouldn't need to be altering the formulas. So, the Worksheet is protected. If you want to start manipulating some of the formulas, you can certainly go to the Review tab, in the ribbon, and choose Unprotect Sheet. There's no password. Now we can click on these various formulas, possibly change them.

What I'm going to suggest is a simple change here that shows us the final payment date and also the length of the loan. And I've done this already in a different Worksheet. I've also changed the font to make it a bit more readable. So, for the moment, we're seeing the same kind of data as before. We got some new information over here. If this is the amount of the loan, same numbers we saw before, 30 years. If we're not making an Optional payments, the length is 30 years. Our last payment date would be January 1st, 2045.

Let's put in that extra $100 though. In cell D10. All the formulas react. Now, you don't have to understand all the formulas. You can certainly explore them if you wish. But now we have a different length. It's 327 months, we saw that before. But what we now see on the screen and more useful, we're seeing the loan length, 27 years and 3 months, a final payment date. Now the calculations behind these are not difficult. So, I'll display the one right here. How did we come up with the 27 years? If the number of payments is 327 then we simply take that value divided it by twelve.

We throw away the decimal portion of it. INT means integer. So that's what happens there. And let me make this Row a bit taller here. Both of these. The 3, here, comes by using the MOD function. It simply takes that 327 value, in J7, divides by twelve, and tells us what the remainder is. In other words, how many months are left over after we divided by twelve. So, that's how many months we have. These two cells, right here, are simply picking up these two items and accompanying them with Years or Months.

But more important, perhaps, is the ending date. And this is using a function you may not be too familiar with. I'll scroll a little bit to the right here. EOMONTH means end of month. It's gonna take the loan starting date, that's January 1st, 2015, and then using a calculation multiplying by twelve and ... You can experiment with this on own if you wish. You can come up with the idea that we want to know when that final date is. So, it's adding on to the starting date the number of months that we're using here. This is coming out of cell H10 and H11.

The 27 and the 3 that we're currently seeing. So, you don't have to worry too much about the details unless you really are interested in how formulas are created. And certainly you might be. So, we can make some other changes. Maybe we've decided that this is going to be something we can afford, $250 a month. We'll make a change there. And, again, I think we interested here in seeing how much these additional payments are going to shorten the loan and also reduce our total interest. So, as I press Enter, for $250 a month, that takes us to 24 years, length of the loan, so our last payment is January 1st, 2039.

So, simply by taking the basic Loan Amortization Schedule, adding a few formulas, we see them right here, we can make changes and get to the information that I think a lot of us would need. We're customizing the Loan Amortization Schedule. And what I'll do here momentarily is display these formulas. You may want to try these on your own. So, simply by pressing Control tilde, we can see all Worksheet formulas. The ones we're most interested in are the ones right here, in these cells right here. So, those are the ones you would use to take the basic Loan Amortization Schedule and customize it to come up with this additional information.