CS 100 (Learn) — CS 100 (Web) — Module 04
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video we are going to go through a very practical example that highlights some of the things we have learned so far.
Let's say you have just graduated and you are excited to buy your first new car, but you are trying to work out if you can afford the monthly payments.
We are going to write an Excel spreadsheet to calculate what those monthly payments would be.
I have already started this spreadsheet, [and] I am going to walk us through how to do [the] calculations.
The price of the car is going to be (for example) $23,899. What I probably want to do is format that as a dollar amount, so it's going to add the dollar sign and the commas so it looks a little pretty.
The car company is telling me they can do an annual interest rate of 4.5%. We are not going to pay the loan annually, so this is a bit of a detail that I am going to gloss over, but the simple fact is that you pay your car payments monthly.
What I need to know is: "what is my monthly interest rate?" [What] the banks do is take your annual interest rate and divide it by twelve. I am going to take the contents of [the cell with the annual rate] and just divide it by twelve. I am going to format that as a percentage and make sure I can see all the decimal places [necessary] and now [we can] see our monthly interest rate is 0.375%.
I want to finance the car and pay it off in ten years. But, as I said, we need to know how many months that is because you are going to be making monthly payments. Again, I am going to take the number of years and multiply that by twelve.
I have entered all the data I need for this calculation.
One little trick I am going to do is simply format the cells differently where I actually enter the information and so we are going to leave the calculated cells just white, because we want to be able to distinguish [between] cells we enter in [data] and cells that are automatically calculated for us.
The power of Excel is that there [are many] built-in functions, and one of those functions is a function called payment (PMT).
As [I type in the name of the function, shows that it] "calculates the payment for a loan based on positive payments and constant interest rate": That's exactly the function you want.
I am now [going] to use this function... I [typed =pmt and then an open bracket] and it shows me [the parameters: the rate, number of periods and principal value].
There are some optional parameters I am not going to worry about right now.
If you are confused, you can jump to help and learn about the function. [There are many] different functions you can learn about in Excel and read [about] them and the instructions for how to use them.
I am just going to go ahead and use this one: [I will now reference] my monthly interest rate, the number of periods I have to pay (120), and the amount I have to pay off is the price of the car, which is $23,899.
I enter that all out and it tells me that each month I we have to pay $247.69. We now know what our monthly payments are going to be now.
What if I wanted to see how much my monthly payments [would] be if I pay them over fifteen years... our monthly payments go down to $182. What if I want to pay it off in three years? Oh, $700 a month... I can't handle that... what about five years? $445 is a lot per month, but I think I can stretch it.
[What if] another bank will give me a different interest rate... what if they give me a three percent interest rate? That sounds much better now my monthly payments go down to $429.
What if I get more expensive car? No, I definitely can't afford that... what if I get a [less expensive,] used car? That's much more reasonable.
Now we can really see the power of Excel and why people use Excel: it gives us the ability to try different scenarios out and have calculations [performed] in a straightforward and simple way.