- Right, there are a lot of ways that you can calculate
interest on a loan.
We're going to go over three of them right now.
And the difference between them
is the way that they count days.
And the way that they count these days
ends up making you pay a different amount of interest.
I'll see you inside the video.
(gentle music)
If you want to dive a little deeper on the DAYS360 function,
just that component of what we're looking at,
I'll link to a video right now
that goes through how that works.
And there's a few different parameters
that you can change on it if you get into the details.
Right, so if you look down at the bottom of the spreadsheet,
we have four tabs.
The first three of them are going
to be the different day count conventions
that we're going over.
And then the last one's going to summarize
the difference between them,
just gonna total up the interest
and look at which ones cost more or less.
But in order to compare them,
let's talk a little bit first about how they're constructed.
So these are all just a $200,000 loan,
2% interest, and 30 years.
Those 30 years happens to be 360 months,
and we're going to do all of these three calculations.
They look kind of similar.
They're just amortization tables on a monthly basis,
so there's 360 rows of data down here.
The difference in all these really resides in the way
the interest is being calculated.
So literally, the formula that's in this box.
So we'll talk about the first one now,
and this is the 30/360 method.
And what this does is every month
that interest is calculated,
it assumes that the month has 30 days
and that the year has 360 days.
And this is really the most simple way,
if you think about it because this is the same
as taking the interest rate and just dividing it by 12.
So every single month is just 30/360.
And if you wanna get a copy of these to follow along,
visit the link and the description.
You can get your own Google Sheet
with the exact same numbers.
So that's 30/360.
Pretty easy to understand.
The next one is actual over 365.
It's calculating the interest every month
by the actual days in the month divided by 365.
All right, so what this means
is it's just more accurate matching of the interest expense
for every month.
So if it's a longer month, you would have more interest.
Shorter month, you'd have less.
But every year is also 365,
regardless of whether or not it's a leap year,
which is a little bit odd,
but that's just the way this works.
So the denominator is always 365,
and then we're just counting the days in the months.
We're doing that here with the day function.
We're looking at column B,
where we just put the day of the month
and we repeated it.
And so we're extracting that.
So this one, one has 31 days.
So it results in 31 divided by 365
times the interest rate.
And then times the beginning balance.
All right, so this one moves back and forth a little bit.
You can see February's shorter, has less interest.
And March is longer, again, it has more interest.
Let's take a quick look at the Date field.
To increment this every month,
we use something called the EO month function.
And all that does is return the last day of the month
a specified number of months away from the previous month.
So you just say, hey, poll January, 2022,
and advanced one month and gimme the last day of that month.
So that's a great function to just do a sequence
of full months, regardless of the number
of days in each month.
All right. So that's the actual over 365.
So I'd like to take a minute to thank a few of my viewers.
YouTube has a feature where you can give a creator
what's called a Super Thank.
So it's kind of like a tip.
I've gotten a few of those over the past several months.
They've really helped to support my channel,
which enables me to create more content like this.
Appreciate it.
All right, and then we'll move on to the last method.
This is actual number of days over 360.
So it's a combination of the two concepts
because you take the denominator from 360,
but you use enumerator concept from actual over 365.
So these first two methods, they kind of make sense.
The first one's really simple.
The second one is more of a reflection
of the actual number of days,
but then this one they combine kind of the worst
of two worlds, and end up charging you
a little bit more interest.
So let's look at the formula that we're using.
If I double-click in cell F8,
we're using the DAY function
to pull out the number of days for each month.
So that's the actual part of the calculation.
So for example, January has 31 days.
So the DAY function's pulling out 31,
and we're going to just place that over 360.
We're multiplying the result of that calculation
times the interest rate
times the beginning balance for the period.
And if you wanna dig down into the real nitty gritty
of the 360-day convention,
you can look at the DAYS360 function
because actually, every one of these months,
when you use that convention, doesn't have exactly 30 days.
Some will have less, some will have more,
but the total for the year will be 360.
So it chose to keep this formula a lot more simple,
but check out the link to see more about DAYS360.
But now let's look at the end comparison
of the three of these rates.
So let's go to the Total Interest worksheet.
Click on that.
And this is a total interest for all three of the methods.
They're pretty similar, but as you move through them
with the actual over 365,
you pay a little bit more interest,
and actual over 360, you pay even more.
So let's find a better way to visualize this.
We'll highlight the three of those values,
and we'll just click on the Explore button.
So Explore always tries to get the best type of chart
for the dataset.
I think it did a pretty good guess here.
We'll shrink this down
so we can get it all on your screen.
And you can see that the amount of interest
between the three, when considering the entire amount,
is pretty close.
But let's kind of zoom in if you will,
and we're going to customize that chart.
We'll change the vertical axis minimum and maximum.
So we'll say the minimum is, well, let's look
at these numbers.
We'll say the minimum's 66,000,
and the maximum 68,000.
Move out of there, and you can already see now,
let's close this,
and you can see now this is a difference
between the three methods.
So the moral of the story is watch out
for the actual over 360 method.
It's probably not going to break the bank,
but it's going to charge you a little bit more.
All right, if you like that video,
I'm going to bring you to another spreadsheet video
that's going to show you how to create a column
of random numbers that automatically adjust
for the number of rows that you need.
I'll see you in that next video.