WEBVTT
00:00:01.633 --> 00:00:04.199
In this Excel tutorial I'm going to be talking to you
00:00:04.200 --> 00:00:09.366
about how frequently compounding occurs can affect your present worth.
00:00:09.900 --> 00:00:12.300
In this example we're going to be looking at a present worth
00:00:12.300 --> 00:00:14.200
when we compound interest monthly,
00:00:14.600 --> 00:00:16.333
or compound the interest once a year.
00:00:16.800 --> 00:00:20.500
So in our project information we have monthly interest rate is 1 percent.
00:00:20.966 --> 00:00:22.332
On an annual basis,
00:00:22.566 --> 00:00:23.599
let's calculate that.
00:00:23.600 --> 00:00:26.733
So let's review our effective annual interest rate.
00:00:27.333 --> 00:00:42.599
That equals, 1 plus r, or I mean I'm sorry 1 plus i raised to the m which is B6 minus 1
00:00:42.900 --> 00:00:47.666
and you can see that the 1 percent monthly is 12.68 percent annually.
00:00:48.233 --> 00:00:50.733
Our initial cash flow is $50,000,
00:00:50.800 --> 00:00:52.400
the annual amount is $150,
00:00:52.533 --> 00:00:55.133
since we are compounding monthly we have 12 months
00:00:55.300 --> 00:00:58.966
or if we're going to look at it on an annual basis we have a year 1.
00:00:59.600 --> 00:01:01.666
Now let's look at our intermediate calculations.
00:01:01.933 --> 00:01:05.566
At year 0 we have that initial cash flow of $50,000.
00:01:05.933 --> 00:01:07.899
To look at this compounding monthly,
00:01:08.166 --> 00:01:11.766
I've got to break that $150,000 into a monthly amount.
00:01:12.533 --> 00:01:13.733
So notice here,
00:01:14.233 --> 00:01:17.566
I've taken the $150,000 which is B5
00:01:17.600 --> 00:01:21.533
and divided it by the 12 months per year in B6.
00:01:21.900 --> 00:01:25.100
Notice I locked the cells which allows me to grab this corner
00:01:25.100 --> 00:01:27.533
and drag it down and get the same answer at the bottom.
00:01:27.800 --> 00:01:31.866
So the $150,000 breaks out to 12,500 per month.
00:01:32.633 --> 00:01:35.199
Now that we have this information, we can go in and calculate.
00:01:35.300 --> 00:01:36.700
Let's calculate our present worth
00:01:37.000 --> 00:01:38.800
when we have a monthly interest rate.
00:01:39.266 --> 00:01:40.966
To do that that would equal,
00:01:41.100 --> 00:01:42.533
net present value,
00:01:43.166 --> 00:01:46.299
our rate monthly is B2,
00:01:47.866 --> 00:01:52.499
our cash flow that we're looking at is C11 to C22
00:01:53.733 --> 00:01:56.399
and then we're going to add back in our year 0.
00:01:58.233 --> 00:02:02.166
And you can see that you get a negative $190,688.
00:02:02.566 --> 00:02:05.232
Now I just want to show you something real quick about formatting.
00:02:05.466 --> 00:02:07.732
If you don't want to right click and format cells,
00:02:08.066 --> 00:02:09.932
you can go up here to where it says number
00:02:09.933 --> 00:02:12.533
and notice I can increase the decimal
00:02:12.900 --> 00:02:14.733
which will give me the cents
00:02:14.733 --> 00:02:16.233
or I can decrease the decimal
00:02:16.233 --> 00:02:17.266
and take the cents away.
00:02:17.600 --> 00:02:21.000
You can also format currency, other things up in here,
00:02:21.000 --> 00:02:24.433
so if you don't want to right click, go up here and it'll do things for you.
00:02:24.933 --> 00:02:26.233
Now back to what we were looking at,
00:02:26.600 --> 00:02:28.866
that's if the interest rate is compounded monthly,
00:02:29.133 --> 00:02:31.366
let's look what happens when we compound yearly.
00:02:31.933 --> 00:02:33.766
This would equal present value,
00:02:34.433 --> 00:02:37.466
our rate yearly is in B3,
00:02:38.500 --> 00:02:40.800
we are going only for one year
00:02:41.333 --> 00:02:45.599
and our payment was in B5
00:02:46.166 --> 00:02:48.599
so we are going to make it negative B5 to make the formula work
00:02:49.033 --> 00:02:54.066
and then we're going to add in our initial cash flow.
00:02:55.700 --> 00:03:00.600
And you can see that on an annual basis when we compound, at just once a year,
00:03:00.933 --> 00:03:07.299
notice how our present worth has change to a less negative value of 183,117.
00:03:08.300 --> 00:03:11.533
What this video was wanting to explain to you is
00:03:11.533 --> 00:03:16.033
that the frequency of compounding can alter your present worth.