Calculate the effect of interest rates and inflation

- [Instructor] When you analyze cash flows whether in Excel, or using another tool, you need to account for two important effects. Interest and inflation. Interest shows how much an investment grows over time. If you've ever made an investment then you know that a deposit of $10,000 will grow by a certain amount every year. That amount might be four percent, or five percent. When you want to calculate the effects of interest you use the following formula. And that is the principal, that's the amount you start with, multiplied by one plus the rate. And one plus the rate is raised to the power of time. So let's break those elements down individually. Principal is the original amount invested. That could be $10,000, $5,000, whatever. Rate is the interest rate. When you add the interest rate to one it's important that you do it as a percentage, or as a decimal. So for example, five percent would be .05 so you would end up with one plus .05. And finally, time is the number of periods. And that is the number of times that the interest is compounded. So for example, if you made a investment for three years, then it would be principal multiplied by one plus .05. So assuming a five percent interest rate with the quantity one plus rate raised to the power of three. To see another example, if you invested $1,000 at six percent for eight years, you would have $1,000 times one plus six percent with that quantity raised to the eighth power. The total, after eight years, is $1,593.85. So, as a result, you would have an additional $593.85 due to compound interest. Inflation works in the opposite direction. Inflation shows how much your money decreases in value over time. You can calculate the value of your money in today's terms given inflation using the following formula. That is principal divided by one plus rate with the quantity one plus rate raised to the power of time. Which is the number of periods. So once again, to go through the arguments that are used principal is the original amount. Rate is the rate of inflation. And a time is the number of periods. If we use the same numbers that we used in our last example, but assuming inflation rather than interest, we'll see that $1,000 is being decreased in value at a rate of one plus six percent per year. Over eight years. we would raise 1.06 to the eighth power. Use that number to divide 1,000. And find that we now have a value of $627.41. Interest and inflation are key elements in evaluating cash flow. And that's why I said the first time in this movie to give you an overview of the concepts. With that overview in mind, I'd like to switch over to Excel. And give you a little bit more detail about how the value of money changes over time. This sample file is the Interest Excel workbook. And you can find it in the Chapter one folder of the Exercise Files collection. I have copied over the data that I had from the previous worksheet. So I have my starting principal of $1,000. And I'm assuming an annual interest rate of six percent. If I want to find the value after eight periods or eight years, of the money growing due to interest, then I would click in cell C five and type the formula equal. Then C two which is my principal multiplied by the quantity of one plus the interest rate that's in C three. Then a right parentheses and I'm going to raise the quantity in parentheses to the power of eight which is the number of periods. And enter. And I get the same value as I showed in the PowerPoint slide of $1,593.85. If I want to see how that value progresses by year, then I can form my calculations in cells C eight through C 16. I'll start by typing the value of $1,000 which is my principal. In cell C eight. So I have $1,000. And then the value in cell C nine would be the value of $1,000 multiplied by 1.06. Which is, one plus six percent. So I'll type equal. Then C eight. Multiplied by 1.06 and enter. And I get an extra $60. Which is six percent of $1,000. Now if I copy this formula down by clicking cell C nine and then double clicking the fill handle, at the bottom right corner of the selected cell, it copies down. And I get my final value of $1,593.85. But I can see how the value has increased over the years. We can do the complimentary calculation for inflation. So I'll click in cell F five. And, type in equal sign. I have my principal which is in cell F two. But now instead of multiplying it I'm dividing it by one plus the interest rate quantity raised to the power of the number of periods, which is eight. So left parentheses. One plus the inflation rate. Which is in F three. Right parentheses. Carry for (mumbling) and eight for the number of periods. And enter. And I get the value of $627.41. Now to see how that value changes over time I will start in cell F eight. And type in the value of 1,000. And enter. Now, rather than multiplying I will divide by one plus the inflation rate, or interest rate, in this case. So, in cell F nine I'll type an equal sign. And I'll have F eight divided by quantity one plus the interest rate, which is in cell F three. I don't want that cell reference to change so I'll press F four to make it an absolute reference. Then, a right parentheses to close out the expression. And enter. And I get the value of $943.40. Now I can make the same copy actually that I did before. I'll click cell F nine. And then double click the fill handle at the bottom right corner of the cell. The formula fills down to match the items in column E. And I see that my final value is 627.41 which is what we had before. And I can see how inflation has eaten away at the value of my money over time.