Calc spreadsheet file used in video: Right-click to download file
Calc offers many useful financial functions, one of the most commonly used is the PMT function. PMT calculates the payment for a loan that has constant payments and a fixed interest rate. it takes 5 parameters:
The rate is the monthly interest rate. Most loans specify an annual interest rate, so the monthly rate is 1/12 the annual rate. Usually the length of a loan is given in number of years, so this needs to be multiplied by 12 in the function. an example would be a car loan of 25,000 for 3 years at an annual interest rate of 5.5%. Payment is due at the end of each period. Here's how the cells in the spreadsheet are arranged:
The Formula is =PMT(B2/12,B3/*12,B1)
In the first parameter B2 (the APR) is divided by 12 to get the monthly rate.
in the second parameter B3 is multiplied by 12 to get the number of periods, 12 periods per year.
the last parameter is the loan amount.
the final 2 parameters were left off because there is no balloon payment at the end of the loan and the payments are at the end of each period.
The payments come out as negative because this is money that is going out of your pocket each month.
You can see how much money is going to pay off the loan as opposed to how much money is going to interest each payment by creating an Amortization table. on the same sheet as the example above:
The formula in column B takes the balance and multiplies it by the interest rate in B2 which is divided by 12 to get the monthly interest rate. Column C subtracts the money going to interest in column B from the payment amount which is changed to a positive by putting a minus sign in front of the reference to cell B4. Column D subtracts the amount to loan from the previous balance. If you chose to pay off the loan before end of the 36 months the amount in column D is what you would owe at any particular period. If you paid the loan at the start of the period. the payment amount would be less:
The formula would be =PMT(B2/12,B3*12,B1,,1) The 4th parameter is blank because there's no Balloon payment and the 5th parameter is set to 1 to indicate the first payment starts at the beginning of the period. You would create the amortization table the same way, but for the first period the amount going to interest would be 0 because it's paid when the 1st period starts.
