how to calculate payments on a loan using MS Excel?

Excel has lots of useful built-in functions, and PMT is one of them that can calculate payments on a loan for you. Here is a simple example:

If I get a loan of $5000 from a bank, with the annual interest rate of 8%, and I am supposed to pay it off in 3 years, how much should I pay (OR they will ask me to pay) each month?

And here is how to do the calculation:
Input following into any cell of a Excel worksheet:

=PMT(0.08/12, 3*12, 5000)

and press Enter.

The value shown in the cell ($156.68 ) is the money I am going to pay to the bank each month. Here 0.08/12 is used because the 8% is annual rate, so divide it by 12 to get the monthly rate. And in 3 years, there are 3*12 months, which means the number of payment is 36.

Then, what is the total I pay to the bank? As you might have guessed, it is $156.68*36 (=$5640.55). So the bank will earn $640.55 in this deal at the end. Well, that is a lot of money. Now I am thinking about how to own a bank...


P.S: If you didn't buy MS Excel, that's OK! The Google Docs spreadsheet will do the same thing for you! It is right here: docs.google.com


P.S: this has nothing to do with Matlab, but it is still interesting (at least to me) and useful.