1. Compound Interest
Up till now, all our examples have used simple interest, where each year's
interest, either paid or received, is based only on the principal amount.
The second method of calculating interest is called compound interest.
Simply put, compound interest is just the concept of earning interest on
interest. So, rather than the interest being based on a fixed principal
amount, as it is under the simple interest approach,
under compound interest, the interest earned in one period is added to the
previous period amount, and the interest in the next period is based on
this new amount. Because the interest is being based on an increasing amount,
each interest payment gets larger and larger. Let's just dive into an example
to make things clearer. Imagine that you have a principal of $100 to
start with, and every year it earns compound interest at a rate of
5% per year. We're looking at a total of 3 years in this
example. In the first year, you'll earn 5% interest on the initial $100,
which amounts to $5. When you add this interest to your starting $100,
you clearly have $105. Moving on to the second year, the interest is
calculated on this new amount, $105, and so the interest in year 2 works
out to be $5.25. Adding this to the $105, you'll have $110.25. Now,
in the third year, you'll earn interest on the increased amount of $110.25.
The interest for this year comes in at $5.51. Adding this to $110.25, you'll
end up with 115.76 after 3 years. Let's double check our calculations using
Excel just to make sure that we've got this right.
So here we are back in our simple and compound interest template workbook,
and I've scrolled down to the compound interest section. Remember, I really
encourage you to give this a go yourselves. So feel free to watch
me then give it a go, or you might want to give this
a go then, watch to see how I do it, or you might
want to try to work along with me. However you decide to do
it is fine, but I really encourage you to give this a go
yourselves. So what were our inputs for our compound interest example?
Well the principal, the amount borrowed is 100. The compound annual rate
was 5%, and the term is 3 years.
Let's start by completing our table by bringing in the principal amount
into cell G35. So I'm just going to reference D29. Now in each
of the 3 years, the principal remains the same. So I'm going to
reference cell D29 again, and I'm just going to absolutely reference that,
so that when I highlight the column and press Ctrl D to fill down,
the principal is filled in each of the 3 years. Now what about
my interest in year 1? Well my interest in year 1 is going
to be the compound interest rate of 5%
multiplied by the principal. And when I hit tab to scroll across,
you can see that my interest in year one is 5. My total
interest is just the year one interest, so again I can reference that,
and then when I scroll along by hitting tab, I'm going to take
my total value as my principal amount, which I'm going to absolutely reference
here, and I'm going to add the total interest, which is 5, so
at the end of year 1, I'd owe 105. What's my interest in
year 2? Well my interest in year 2 is based on the compound
interest rate, and this time I'm going to absolutely reference that,
and then I'm going to multiply that by 105. And so now my
interest in year two is 5.25. And my total interest is the interest in year
1 plus the interest in year two. And if I just remind myself
what the formula is in cell G36, you can see that it's the
principal amount, which is absolutely referenced, plus the total interest.
So if I just fill this down, I should get the amount that I
owe at the end of year two, 110.25. Now then, I should have
the right formulas in each of these cells, and the absolute references and
the relative reference correct, and I can just check those, oh, not paste
names sorry, F2, you can see that that looks right, I want that
to be a relative reference, and I already know that I've got the
right formula in here, where I've absolutely referenced the total value
in G35 plus the total interest. So now, if I just highlight those three
cells, and if I hold the shift and arrow down, Ctrl D to fill down, it should
complete it for me, which it does. My interest in year three is
5.51, because it's based on my closing interest in year two. And if
I just tab along, my total interest is the interest in year three
plus the total interest from year two. And then the amount that I'd owe
at the end of the three years is the principal amount plus the
total compound interest, which is 115.76.
2. Let's practice!