Financial Functions 2

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

The Rate Function

The Rate function will return the interest rate for a loan with a fixed rate. the parameters are:

• nper: the number of payments for the loan.
• pmt: the amount of each payment.
• pv: the Present value, the amount being loaned.
• fv: (optional) the Future value, the amount that's left over after the loan is paid.
• type: (optional) if left out or set to 0 the payments are at the end of each period. If set to 1 the payments are at the beginning of each period.
• guess: (optional) Your guess as to what the rate will be. this is not often used and is left out of the function wizard altogether.

The example is of a loan of $35,000 for 5 years with payments of $665.31 a month. To find what the interest rate would be the rate function is used.
the first parameter is the No. of years in B5 is multiplied by 12 to get the total number of payments.
next parameter is the payment amount in B3 as a minus because the money is being subtracted from the account of the loanee.
The third parameter is the loan amount in B1.
The last 3 parameters are not used in this caluculation, the payment is at the end of each pay period and there is no future value left at the end of the payments.
The function is then multiplied by 12 to get the Annual rate.

The NPER function

The NPER function finds the number of periods it will take to pay off a loan at a constant interest rate. The paramters are:

• rate: the periodic rate of the loan.
• pmt: the periodic payments made on the loan.
• pv: the amount of the loan.
• fv: (optional) the future value. The amount left over at the end of the payments.
• type: (optional) if left out or set to 0 the payments are at the end of each period. If set to 1 the payments are at the beginning of each period.

In the following example a credit card balance of $5,000 with an interest rate of 19% is being paid off with a $125 payment every month. to figure how long it will take the NPER function is used:

The first parameter, rate, is the APR divided by 12 to get the monthly rate.
The next parameter is the amount of the payments, this is a negative amount because the money is being taken from the loanee.
the third parameter is the amount of the loan in B1.
The answer is 47.761 months which is about 4 years.

FV function

To find out how much a savings account with a constant interest rate will yield use the FV function. the parameters are:

• rate: The periodic interest rate.
• nper: The number of payments.
• pmt: the amount being put in every month. Should be entered as a negative amount.
• pv: (optional) the present value, the amount already in the account.
• type: (optional) if left out or set to 0 the payments are at the end of each period. If set to 1 the payments are at the beginning of each period.

For example a savings account that yields 10% interest with monthly payments of $100 for 10 years:

In the example the first parameter rate is the Interest rate in B2 divided by 12 to get the periodic rate.
The next parameter, nper, is multiplied by 12 to get the total number of periods.
The Third parameter is the monthly payments being paid into the savings, this is a negative amount because the money is going into the savings account.
The last 2 parameters are not needed because there is no money already in the savings and the payments are at the end of each period.