A loan service fee is .25% of the unpaid principal balance. Is there a way to write a single formula to total all of the service fees, *without* having the complete amortization table?
Transcript of the video:
Bill: Hey alright, welcome back, it's time for another Dueling Excel podcast, I'm Bill Jelen from MrExcel. I'll be joined by Mike Girvin from Excel Is Fun, this is our episode 173 - Loan Service Fee!
Like, I have to apologize in advance, this is going to be an insane one, but it's a real question that came in, and I said "Alright, I'm going to try and solve this without pulling out my copy of "CTRL+Shift+Enter". I want to see if I can just absorb what I've learned from your book, alright?" So, here's what we have, we have a bank, loan amount, a rate, a term, and of course the payment function which, you know, I've covered before, alright?
So, that all is normal, but here's the unusual thing, is every month the bank charges a service fee, and that service fee is 0.25%, but that's an annual fee. The way that they actually calculate the service fee is they take the last month's unpaid balance, multiply it by 0.0025%, and then divide by 12 to get the monthly service fee. So we have an amortization table down here, that's showing our payment and, then the I payment for how much of that is interest, the P payment for how much of that is principal, what the balance is, and then the service fee. Alright, and it told us up down here to 4314.78$ after the life of the loan. All good right? But it's a real hassle to have to build this 360 row amortization table, just to get those numbers, because after all, we can figure out the total principal using the cumulative principal, or the total interest using the cumulative interest payment.
Alright, this is saying that we're going from month 1 to month 360. Alright, we have those two functions built in, and Total Paid, that's a no-brainer, that's just the term times the payment. But how do we calculate the service fee in one single formula, without having the amortization table at the bottom?
So, you know my initial reaction is "Alright, well hey this is incredibly simple, just add up all those numbers down there in row 9 through 368, and we get the right answer." But unfortunately then, when we delete the amortization table, the service fee has gone away. So I need a single formula that's going to encapsulate all of that stuff down at the bottom. And first, to wrap my head around this I had to do, are you ready, some algebra, alright?
I had to attack this problem, and try and figure out what exactly, how can I state, what's happening down here. And so, each month what we're doing is we're taking the original balance, 100.000$, minus the cumulative principal from all of the previous ones, times .0025% / 12. That's what's going on. Oh Mike, boy, I know somewhere in calculus there was a way to say that we're going to iterate from one to 360, right, but jeez, is that an integral?
I have no idea right, it's been so long, I can kind of picture me half sleeping in class, and the professor talking about this.
So, I just kind of wrote it here, in a little Visual Basic! So from M= 1 to 360, we're going to take a 100.000$, minus the Cumulative Principal, from 1, to this month -1! Right, because it's always pointing back, seem it's always going to the previous month's balance, so for month 3 I want to see the cumulative principal from 1 to 2, in month 99 I wants to see cumulative principal from 1 to 98. Alright, and here's the funny thing about the algebra of this whole thing is, we're going to have 360 of these calculations, and in every single one, the first term is always going to be the original loan amount. So we're to take 360 times that original loan amount. That, actually, is just incredibly simple as well, that's B1, the loan amount, times the term times the rate divided by 12! Alright, so that green part is actually done, it's the blue part. How can I calculate the cumulative principal from all of the previous months?
And so, my first attack here, was just doing it without a function, incredibly easy, where it's in the first month it's the answer from column D, which is the P payment, like that.
And then each month I'm just adding the previous cumulative principal plus this month's cumulative principle, all the way down, alright? So that's just the straight math, but then I wondered "Well, you know, is there a way to do this with a function?" And I realized that up here, when I wrote this function, after I learned about cumulative principal, that I had to go through the discovery process, figuring out the cumulative principal payment. So, it feels a lot like the payment so, B3, the rate divided by 12, like we do in the payment function, the term, the loan amount, in this time you have to keep it as positive, it can't be negative, and we're going from month 12 to this month. So here, let's come down here.
So we're going from month 1 to month 7 to figure it out. And we're then, the paying at the end of the month over the beginning of the month, to match the payment function, alright? So you know, there's the formula, that for each month would calculate the cumulative principal, the huge question that I had is "Is there a way to do all 359 the first time the cumulative principal of the previous month is going to be 0?" So there are 360, but cumulative principal won't allow us to do from 1 to 0, month 1 to 0. So we're just going to do the 359, essentially from month 2 down to month 360, figuring out the cumulative principal for month 1 to month 359! Alright so, is there a way to have an array formula that will do this function 359 times, and total it all up?
Alright, so here's where I pulled out a trick from your book Mike, where we use the row from A1:A359. That will generate an array from the number 1 to 359. Is it possible to enter a formula, and instead of saying "Hey, we're going from 1-6 or 1-7 1-8 1-9..." put all the numbers from one to three hundred fifty nine? So here's the formula, and I had to wrap it in the SUM function, of course, to get it to total up. We hold down CTRL+Shift and press Enter, and remarkably it works, right? So now, I have one formula that's doing the blue part. The green part was already done, that's simple, that's just the original loan amount times 360. And now I have a formula that is doing the blue part. So down here, alright, I'm jazzed at this point because it's all working, right, take that 360 times the loan amount, minus the cumulative interest payment, times the rate, divided by 12 and I get the answer. But at this point I have the answer in three cells instead of one cell.
I want to have a single formula that does this whole thing. And, boy, I don't know is it possible to take just a really simple calculation like this, and add it to a really complex array formula like that, and then multiply the whole thing. Amazingly, take this formula, K4-K11/12 ! So we're going to go to the blue part, and we'll copy that, CTRL+C will scoop that out, as Mike says, and paste it in here.
Beautiful, of course that's going to work.
And then I'm going to take the array formula, that whole thing, except for the equal sign, scoop that out, CTRL+C, come down here and replace that in K11, hold down CTRL+Shift+Enter, and I’ll be darned! It comes up with the exact same answer that we did with looking at all 360 rows.
So, here's my answer, this insane formula right here, CTRL+Shift+Enter, and the real test, of course, is can we delete all the rows of the amortization table? E D R! It didn't work! Undo undo undo. Alright, why didn't that work? Oh, because here I'm actually referring to rows A1:A359. I'm referring to those rows, so I can't delete, but what i can do is come down here and just ALT E A A to Edit, Clear All, there we go. And now it's still pointing down to those rows. So there you go, the craziest array formula that I ever wrote without looking at CTRL+Shift+Enter.
Now Mike says "Send this to me, I'll see if i can improve it!" Let's see what you've got!
Mike: Thanks MrExcel! Oh my heavens, that is a BEAUTIFUL formula! Not only that, but I love the way you did your algebra, and your VBA, and 1 to 360! The thought process that you went into creating this, is exactly similar to the way I've done it so many times. You just, you're not sure but to just keep hacking away at it, trying different things, and then BOOM, that's beautiful. Now, I'm just going to change a few things.
I'll change this to ROW and INDIRECT, to create an array of sequential numbers. So if we delete any rows, we won't get that error. Maybe I’ll use SUMPRODUCT instead of SUM, so we don't have to use CTRL+Shift+Enter, and I always like to make my sub calculations in the cell!
Now we're going to start with that awesome function, cumulative principal. Now we're going to take the rate, and I already calculated it not 3% / 12, did that in the cell, so BOOM, comma nper, that's 360, comma present value, that's that 100k right there, and the starting period, 1, comma, and here's the ending period.
Now what I'd like to do is just go hey, 1:" , and join it to the 360! Night now that would be ridiculous, if I F9 it, you could see it's "1:360". It's sort of looking like a row reference, except for it's text, CTRL+Z while the INDIRECT function takes text that represents a reference, and converts it back to a reference. The problem with INDIRECT right now, is this is rows 1 to 360, there's a lot of cells in that range, so if I F9 it won't let me, that's too big.
So I'm simply going to believe that it actually did that and put it inside a ROW, ROW will have no problems seeing all of the rows, and giving me bone! F9, 1 to 360! Now I don't want 360, so CTRL+Z, I'm simply going to say right here: -1! That -1 will occur before &, & happens like way at the bottom of the order of operations in Excel, comma, and here's a weird thing. I just taught cumulative principal in my advanced Excel class the other day.
This type argument in most financial functions has a square bracket and it's optional, but here you have to pick end or begin, so I'm picking 0 for end, ) .
Now this will come out if we highlight this, what's happening right there, that's a function argument array operation. It's expecting a single period, we give it a bunch, so when we highlight it, it's going to give us F9, all of those different answers! Now look up here as MrExcel set -171, -374, -516. These are the cumulative principal paid off so far, so CTRL+Z. If we were to add all of these up, I'm going to use SUMPRODUCT instead of SUM, and I'm not going to put a minus because it'll pop out negative, and when I enter, that's adding up all of the cumulative as a minus! So I'm simply going to say, well if that's all the cumulatives for every single period, the original amount times 360 would be as if we had original amount that many different times, and we'll subtract that.
So I’m simply going to say 100.000 times that 360, and then add that negative, and boom. There's the full amount! If you were to CTRL+Shift+Down arrow, that's that whole amount right there, F2. And now i need to multiply that, times this 0.25% / 12, which I've already done, and Enter. You got to be kidding me!
Now let's test this, I'm going to click on the row header 14, CTRL+Shift+Down arrow down arrow, right click Ket(?), D to delete them, and this one looks like it's working, it's only pointing at these inputs up here. This one's still working a little bit, but it looks like a lot of rows got deleted there. Esc CTRL+Z. Now the one downside to using this little construction there is, INDIRECT is a volatile function, so it'll recalculate every time you do anything in this spreadsheet.
Hey, for something like this, where we have just some small inputs, that's probably not a problem, Esc. That is some amazing fun with array formulas, and some awesome creativity with that cum principal function by MrExcel.
Alright, I'm going to throw back to MrExcel!
Bill: Mike "Array Formula Master" Girvin, that was beautiful, what an excellent way to dramatically improve that formula I struggled to get. Fascinating, fascinating, alright.
So hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
[ Music ]
Like, I have to apologize in advance, this is going to be an insane one, but it's a real question that came in, and I said "Alright, I'm going to try and solve this without pulling out my copy of "CTRL+Shift+Enter". I want to see if I can just absorb what I've learned from your book, alright?" So, here's what we have, we have a bank, loan amount, a rate, a term, and of course the payment function which, you know, I've covered before, alright?
So, that all is normal, but here's the unusual thing, is every month the bank charges a service fee, and that service fee is 0.25%, but that's an annual fee. The way that they actually calculate the service fee is they take the last month's unpaid balance, multiply it by 0.0025%, and then divide by 12 to get the monthly service fee. So we have an amortization table down here, that's showing our payment and, then the I payment for how much of that is interest, the P payment for how much of that is principal, what the balance is, and then the service fee. Alright, and it told us up down here to 4314.78$ after the life of the loan. All good right? But it's a real hassle to have to build this 360 row amortization table, just to get those numbers, because after all, we can figure out the total principal using the cumulative principal, or the total interest using the cumulative interest payment.
Alright, this is saying that we're going from month 1 to month 360. Alright, we have those two functions built in, and Total Paid, that's a no-brainer, that's just the term times the payment. But how do we calculate the service fee in one single formula, without having the amortization table at the bottom?
So, you know my initial reaction is "Alright, well hey this is incredibly simple, just add up all those numbers down there in row 9 through 368, and we get the right answer." But unfortunately then, when we delete the amortization table, the service fee has gone away. So I need a single formula that's going to encapsulate all of that stuff down at the bottom. And first, to wrap my head around this I had to do, are you ready, some algebra, alright?
I had to attack this problem, and try and figure out what exactly, how can I state, what's happening down here. And so, each month what we're doing is we're taking the original balance, 100.000$, minus the cumulative principal from all of the previous ones, times .0025% / 12. That's what's going on. Oh Mike, boy, I know somewhere in calculus there was a way to say that we're going to iterate from one to 360, right, but jeez, is that an integral?
I have no idea right, it's been so long, I can kind of picture me half sleeping in class, and the professor talking about this.
So, I just kind of wrote it here, in a little Visual Basic! So from M= 1 to 360, we're going to take a 100.000$, minus the Cumulative Principal, from 1, to this month -1! Right, because it's always pointing back, seem it's always going to the previous month's balance, so for month 3 I want to see the cumulative principal from 1 to 2, in month 99 I wants to see cumulative principal from 1 to 98. Alright, and here's the funny thing about the algebra of this whole thing is, we're going to have 360 of these calculations, and in every single one, the first term is always going to be the original loan amount. So we're to take 360 times that original loan amount. That, actually, is just incredibly simple as well, that's B1, the loan amount, times the term times the rate divided by 12! Alright, so that green part is actually done, it's the blue part. How can I calculate the cumulative principal from all of the previous months?
And so, my first attack here, was just doing it without a function, incredibly easy, where it's in the first month it's the answer from column D, which is the P payment, like that.
And then each month I'm just adding the previous cumulative principal plus this month's cumulative principle, all the way down, alright? So that's just the straight math, but then I wondered "Well, you know, is there a way to do this with a function?" And I realized that up here, when I wrote this function, after I learned about cumulative principal, that I had to go through the discovery process, figuring out the cumulative principal payment. So, it feels a lot like the payment so, B3, the rate divided by 12, like we do in the payment function, the term, the loan amount, in this time you have to keep it as positive, it can't be negative, and we're going from month 12 to this month. So here, let's come down here.
So we're going from month 1 to month 7 to figure it out. And we're then, the paying at the end of the month over the beginning of the month, to match the payment function, alright? So you know, there's the formula, that for each month would calculate the cumulative principal, the huge question that I had is "Is there a way to do all 359 the first time the cumulative principal of the previous month is going to be 0?" So there are 360, but cumulative principal won't allow us to do from 1 to 0, month 1 to 0. So we're just going to do the 359, essentially from month 2 down to month 360, figuring out the cumulative principal for month 1 to month 359! Alright so, is there a way to have an array formula that will do this function 359 times, and total it all up?
Alright, so here's where I pulled out a trick from your book Mike, where we use the row from A1:A359. That will generate an array from the number 1 to 359. Is it possible to enter a formula, and instead of saying "Hey, we're going from 1-6 or 1-7 1-8 1-9..." put all the numbers from one to three hundred fifty nine? So here's the formula, and I had to wrap it in the SUM function, of course, to get it to total up. We hold down CTRL+Shift and press Enter, and remarkably it works, right? So now, I have one formula that's doing the blue part. The green part was already done, that's simple, that's just the original loan amount times 360. And now I have a formula that is doing the blue part. So down here, alright, I'm jazzed at this point because it's all working, right, take that 360 times the loan amount, minus the cumulative interest payment, times the rate, divided by 12 and I get the answer. But at this point I have the answer in three cells instead of one cell.
I want to have a single formula that does this whole thing. And, boy, I don't know is it possible to take just a really simple calculation like this, and add it to a really complex array formula like that, and then multiply the whole thing. Amazingly, take this formula, K4-K11/12 ! So we're going to go to the blue part, and we'll copy that, CTRL+C will scoop that out, as Mike says, and paste it in here.
Beautiful, of course that's going to work.
And then I'm going to take the array formula, that whole thing, except for the equal sign, scoop that out, CTRL+C, come down here and replace that in K11, hold down CTRL+Shift+Enter, and I’ll be darned! It comes up with the exact same answer that we did with looking at all 360 rows.
So, here's my answer, this insane formula right here, CTRL+Shift+Enter, and the real test, of course, is can we delete all the rows of the amortization table? E D R! It didn't work! Undo undo undo. Alright, why didn't that work? Oh, because here I'm actually referring to rows A1:A359. I'm referring to those rows, so I can't delete, but what i can do is come down here and just ALT E A A to Edit, Clear All, there we go. And now it's still pointing down to those rows. So there you go, the craziest array formula that I ever wrote without looking at CTRL+Shift+Enter.
Now Mike says "Send this to me, I'll see if i can improve it!" Let's see what you've got!
Mike: Thanks MrExcel! Oh my heavens, that is a BEAUTIFUL formula! Not only that, but I love the way you did your algebra, and your VBA, and 1 to 360! The thought process that you went into creating this, is exactly similar to the way I've done it so many times. You just, you're not sure but to just keep hacking away at it, trying different things, and then BOOM, that's beautiful. Now, I'm just going to change a few things.
I'll change this to ROW and INDIRECT, to create an array of sequential numbers. So if we delete any rows, we won't get that error. Maybe I’ll use SUMPRODUCT instead of SUM, so we don't have to use CTRL+Shift+Enter, and I always like to make my sub calculations in the cell!
Now we're going to start with that awesome function, cumulative principal. Now we're going to take the rate, and I already calculated it not 3% / 12, did that in the cell, so BOOM, comma nper, that's 360, comma present value, that's that 100k right there, and the starting period, 1, comma, and here's the ending period.
Now what I'd like to do is just go hey, 1:" , and join it to the 360! Night now that would be ridiculous, if I F9 it, you could see it's "1:360". It's sort of looking like a row reference, except for it's text, CTRL+Z while the INDIRECT function takes text that represents a reference, and converts it back to a reference. The problem with INDIRECT right now, is this is rows 1 to 360, there's a lot of cells in that range, so if I F9 it won't let me, that's too big.
So I'm simply going to believe that it actually did that and put it inside a ROW, ROW will have no problems seeing all of the rows, and giving me bone! F9, 1 to 360! Now I don't want 360, so CTRL+Z, I'm simply going to say right here: -1! That -1 will occur before &, & happens like way at the bottom of the order of operations in Excel, comma, and here's a weird thing. I just taught cumulative principal in my advanced Excel class the other day.
This type argument in most financial functions has a square bracket and it's optional, but here you have to pick end or begin, so I'm picking 0 for end, ) .
Now this will come out if we highlight this, what's happening right there, that's a function argument array operation. It's expecting a single period, we give it a bunch, so when we highlight it, it's going to give us F9, all of those different answers! Now look up here as MrExcel set -171, -374, -516. These are the cumulative principal paid off so far, so CTRL+Z. If we were to add all of these up, I'm going to use SUMPRODUCT instead of SUM, and I'm not going to put a minus because it'll pop out negative, and when I enter, that's adding up all of the cumulative as a minus! So I'm simply going to say, well if that's all the cumulatives for every single period, the original amount times 360 would be as if we had original amount that many different times, and we'll subtract that.
So I’m simply going to say 100.000 times that 360, and then add that negative, and boom. There's the full amount! If you were to CTRL+Shift+Down arrow, that's that whole amount right there, F2. And now i need to multiply that, times this 0.25% / 12, which I've already done, and Enter. You got to be kidding me!
Now let's test this, I'm going to click on the row header 14, CTRL+Shift+Down arrow down arrow, right click Ket(?), D to delete them, and this one looks like it's working, it's only pointing at these inputs up here. This one's still working a little bit, but it looks like a lot of rows got deleted there. Esc CTRL+Z. Now the one downside to using this little construction there is, INDIRECT is a volatile function, so it'll recalculate every time you do anything in this spreadsheet.
Hey, for something like this, where we have just some small inputs, that's probably not a problem, Esc. That is some amazing fun with array formulas, and some awesome creativity with that cum principal function by MrExcel.
Alright, I'm going to throw back to MrExcel!
Bill: Mike "Array Formula Master" Girvin, that was beautiful, what an excellent way to dramatically improve that formula I struggled to get. Fascinating, fascinating, alright.
So hey, I want to thank everyone for stopping by, we'll see you next time for another Dueling Excel podcast from MrExcel and excelisfun!
[ Music ]
