Hey guys this is Eddie the Magic Monk. Welcome to another technology tutorial
today I want to talk about how to build a compound interest calculator in Excel
so we're going to utilize everything that we have learnt from the first
lesson which is on how to put formulas into Excel so make sure that you have
seen the first lesson if you haven't seen that already so what we're going to
do is put in compound interest calculator on the top left hand side of
the spreadsheet so we know what we're trying to calculate now the first thing
we're going to put in is the principal which is how much money we have in the
beginning now you can imagine right now that we're saving this money in the bank
okay so we have the principal and we also have the interest rates per annum
okay so for example let's start with saving $2,000 in the bank at an interest
rate of 5% per annum okay now we're going to put this information into the
spreadsheet so in cell b6 I'm going to put in the year okay so we have year 1
year 2 year 3 year 4 and so on okay then we have the principal at the beginning
of the year so how much money you have in the bank at the beginning of the year
initially we start with the $2,000 okay so I just pressed equals and I move I
use the arrow keys to move the box to cell b3 and I press
enter so now this cell refers to cell b3 so whatever number that I update
in cell b3 so for example if I change it to 2300 it'll automatically update cell c7
as well okay and then the next thing I do is I calculate how much interest I
earn during that year so what I'm going to do is I'm going to equal the
principal at the beginning of the year times by the interest rate which is 5%
so that year I'm going to earn a hundred and fifteen dollars and then what's
going to happen is at the end of the year how much principal I have - end
of year principal and that's going to be my principal at the beginning of the
year plus the interest that I'm earning okay so this year originally I had two
thousand three hundred dollars I add on five percent which is one hundred and
fifteen dollars and I ended up with two thousand four hundred and fifty okay and
then in the second year I will start with the principal that I have at the
end of the first year so I press equals E seven and now that's a principal I
have at the beginning of year 2 the interest that I earn will be equal to
the principal at the end of the second sorry at the beginning of the second year
times by the interest rate which is b4 and then the end of year principal will
equal the principal at the beginning of the year plus the interest
and it's 2535 now you must be asking a question well do I have to keep entering
this every single line and the answer is no you don't have to keep doing that all
you have to do is you can highlight these three cells and you can see that
there is a black solid dot at the bottom right hand corner and all you have to do
is drag that dot down and you can see it automatically fills all the cells now
here is a big problem you can see here that an error has occurred and that's
because when you select some cells and you feel down what Excel does is it
looks at the formula that you have entered in the cells and you can see
here I have E7 so what Excel does when you fill down is Excel keeps the
letter the same so E will still be there but it increases that number by one
every row so you can see here in the next box it says E8 and we have E9
okay so that part is okay because our principal at the beginning of the year
is going to equal the principal at the end of the previous year so that is okay
but if we look at the interest so when we fill down you can see here I'm
originally doing the principal which is c8 multiplied by the interest which is b4
so that is okay but when you fill down it's turned into C9 times by B5
now there is nothing in B5 so we want to change this b4 somehow so Excel
knows that when we fill down we don't want it to change the number four we
want to make the number 4 stay there so I'm going to lock it to that cell by
putting a dollar sign in front of the number 4
and then when you fill down so I'm going to fill down and you can see that all
the errors are fixed because now every formula is now referring to this box so
it's the principal multiplied by the interest rate in this box okay so this
is now working as well so that's what the dollar sign refers to it refers to
absolutes referencing okay which means that we don't adjust the formula when we
are filling down okay now let's look at the end-of-year principal so we're just
getting the principal from the beginning of the year plus the interest gives you
the end of the year principal so once you have filled down a few cells what
I'm going to do is I highlight the bottom two cells and I just keep filling
down because Excel now has an idea of what I'm trying to do so I can fill down
to 25 years and you can see here approximately if I save okay
instead of 2300 I'm just going to save $1,000 so you can see the impact so if I
say $1,000 and the bank gives me 5% interest per year then what's going to
happen at the end of twenty five years is I'll end up with three thousand three
hundred and eighty six dollars okay so for those of us that understand the
maths quite well this interest rate is compounded yearly okay if you want to do
a monthly interest rate you'll have to change it to months and then you'll have
to make other adjustments but right now this is a interest calculator based on
the fact that the interest rate is compounded yearly okay thanks for
watching guys see you next time.