Chapter 5 Video: Annual vs. Monthly

In this Excel tutorial I'm going to be talking to you about how frequently compounding occurs can affect your present worth. In this example we're going to be looking at a present worth when we compound interest monthly, or compound the interest once a year. So in our project information we have monthly interest rate is 1 percent. On an annual basis, let's calculate that. So let's review our effective annual interest rate. That equals, 1 plus r, or I mean I'm sorry, 1 plus i raised to the m which is B6 minus 1 and you can see that the 1 percent monthly is 12.68 percent annually.

Our initial cash flow is $50,000, the annual amount is $150, since we are compounding monthly we have 12 months or if we're going to look at it on an annual basis we have a year 1. Now let’s look at our intermediate calculations. At year 0 we have that initial cash flow of $50,000. To look at this compounding monthly, I've got to break that $150,000 into a monthly amount. So notice here, I've taken the $150,000 which is B5 and divided it by the 12 months per year in B6. Notice I locked the cells which allows me to grab this corner and drag it down and get the same answer at the bottom. So the $150,000 breaks out to 12,500 per month. Now that we have this information, we can go in and calculate.

Let's calculate our present worth when we have a monthly interest rate. To do that that would equal, net present value, our rate monthly is B2, our cash flow that we're looking at is C11 to C22 and then we're going to add back in our year 0. And you can see that you get a negative $190,688. Now I just want to show you something real quick about formatting. If you don't want to right click and format cells, you can go up here to where it says number and notice I can increase the decimal which will give me the cents or I can decrease the decimal and take the cents away.

You can also format currency, other things up in here, so if you don't want to right click, go up here and it'll do things for you. Now back to what we were looking at, that’s if the interest rate is compounded monthly, let's look what happens when we compound yearly. This would equal present value, our rate yearly is in B3, we are going only for one year and our payment was in B5, so we are going to make it negative B5 to make the formula work, and then we're going to add in our initial cash flow. And you can see that on an annual basis when we compound, at just once a year, notice how our present worth has changed to a less negative value of 183,117. What this video was wanting to explain to you is that the frequency of compounding can alter your present worth.