Financial Functions 1

Calc spreadsheet file used in video: Right-click to download file

PMT function

Excel's payment function is used to find what the payments would be on a loan with a fixed interest rate. For example A car loan. The amount borrowed from the bank after down payment is $35,000. The interest rate is 6.5%, and the loan is being taken out for 4 years.
This is how to set it up on a worksheet:

The PMT function takes 5 parameters, the last 2 are optional.

• Rate: This is the periodic rate, in the example this would be the monthly interest rate, the annual rate is divided by 12.
• Nper: the number of periods. this is the total number of payment periods, the payments are monthly in the example so the No of Years is multiplied by 12.
• PV: the present value. this is the amount being borrowed, in the example this is the amount in B2.
• FV: (optional) the future value, this is the amount left after the payments are finished. If the parameter is left out it is zero, which is the case in the example.
• Type: (optional) if this is left out or zero, the payments are made at the end of each period, which is the usual for these type of loans. If it is one, the payments are made at the beginning of each period.

In the example the last 2 parameters were not needed in the example. The formula is =PMT(B3/12,B4*12,B2). The result is a negative amount, it represents money thats being taken out of your account.

Amortization Table

An amortization table will display the amount going to interest and the amount going to pay off a loan. It's easy to creat one in excel. This is the way the headers are set up from A7 to D7:

First, put the beginning balance into D8 by typing = (equals)
click cell B2 (the beginning balance)
Next type into A9 1
Press Ctrl Enter
Now on the Home ribbon under Fill choose Series or use the shortcut: Hold down Alt and press H F I S To bring up the Series dialog.
In the series dialog choose Series in Columns. Step value 1. Stop value 48. There will be 48 payments (4 years multiplied by 12). press OK
Click B9 and type = (equals).
Click D8 (the beginning balance)
type * (Asterisk to multiply).
Click B3(the APR)
Make it an absolute reference press F4 This needs to be divided by 12 to get the periodic interest.
type /12
Press Tab
Type =- (equals then minus sign).
Click B5 (The payment amount needs to be positive so the minus sign was placed before it).
Make it an absolute reference press F4 Type - (minus sign) Click B9 (The payment amount minus the amount to interest gives the amount that goes to paying off the loan for that period).
Press Tab
Type = (equals).
Click D8 then type - (minus sign).
Click C9 (the previous balance minus the amount going to pay off the loan).
Press Enter
Drag and highlight B9 to D9, then click the fill handle.
This is what the formulas in the cells should look like:

Another kind of loan that the PMT function can be used for is where a lump sum is due at the end of the payments, a balloon payment. This is where the future value parameter comes in. Here is an example of how you could set up a spreadsheet for a loan of $50,000 with a 5.5% APR for 5 years at the end of the payments a balloon payment of $5000:

The formula would be:
=PMT(B2/12,B3*12,B1,-B4)
The Interest rate is divided by 12 to get the periodic rate in the first parameter.
For the second parameter, the No of Years is multiplied by 12 to get the total number of periods for the loan.
The third parameter is the Loan Amount in B1.
The fourth parameter is FV the future value, this is the Balloon payment, it is entered as a negative amount because it will still be owed when the payments are over.
The last parameter was left out because the payments are at the end of each period.