A loan with a balloon payment at the end is caluculated using the pmt function.
For Example: here is a table set up for a loan of $50,000 at 5.3% for 5 years with a balloon payment of $5,000 at the end.
The payment function the parameters would be:
The Rate: this is the Annual percentage rate divided by the number of periods, periods are monthly, so B2 divided by 12
The Nper: the total number of periods, years multiplied by number of months in a year, B3 multiplied by 12.
The present value: the amount being loaned, B1.
The future value: Amount left after the payment term is over, this is the balloon payment entered as a minus because it will still be owed when the payments are over.
Type: optional, the loan is being paid at the end of each period which is the default for PMT, so parameter is left out.
Rate Function
The Rate function will use the payments, loan amount and number of payments to find the interest rate.
the parameters are:
Nper: Total number of payments
Pmt: the payment amount
PV: The present value: the amount of the loan
FV: Future Value (optional) an amount left over after the payments are finished. If left out amount is 0.
Type: (optional) Type: (optional) if left out or set to 0 payments are at the end of each period, if set to 1 payments are at the beginning of each period.
Guess: (optional) Calc makes an estimate of the interest value.
The following uses the previous example's data without the interest rate to determine the interest rate. Rate returns the periodic rate, so to get the annual rate the result is multiplied by 12.
Nper Function
The Nper function will calculate how many periods are required to pay off a loan. For example a credit card with an annual rate of 19%
and a balance of $5000. Monthly payments of $150 are made:
The Nper function takes 5 parameters the final 2 are optional.
Rate: the periodic percentage rate. The annual rate divided by 12.
PMT: The payment amount.
PV: The present value, the amount of the loan.
FV: (optional) future value, an amount left over after the payments are finished. If left out amount is 0.
Type: (optional) if left out or set to 0 payments are at the end of each period, if set to 1 payments are at the beginning of each period.
