To calculate the future value of an investment, you can use the FV() function. The syntax of this function is:
FV(Rate, Periods, Payment, PresentValue, PaymentType)
To calculate the number of periods of an investment or a loan, you can use the NPer() function. Its syntax is:
NPer(Rate, Payment, PresentValue, FutureValue, PaymentType);
The Pmt() function is used to calculate the regular payment of loan or an investment. Its syntax is:
Pmt(Rate, NPeriods, PresentValue, FutureValue, PaymentType)
In the following example, a customer is applying for a car loan. The cost of the car will be entered in cell C4. It will be financed at a rate entered in cell C6 for a period set in cell C7. The dealer estimates that the car will have a value of $0.00 when it is paid off.
When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount.
The IPmt() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment. It is important to understand what this function calculates. Suppose a customer is applying for a car loan and the salesperson decides (or agrees with the customer) that the loan will be spread over 5 years (5 years * 12 months each = 60 months). The salesperson then applies a certain interest rate. The IPMT() function can help you calculate the amount of interest that the lending institution would earn during a certain period. For example, you can use it to know how much money the company would earn in the 3rd year, or in the 4th year, or int the 1st year. Based on this, this function has an argument called Period, which specifies the year you want to find out the interest earned in.
The syntax of the IPmt() function is:
IPmt(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)
The Rate argument is a fixed percent value applied during the life of the loan.
The PresentValue is the current value of the loan or investment. It could be the marked value of the car, the current mortgage value of a house, or the cash amount that a bank is lending.
The FutureValue is the value the loan or investment will have when the loan is paid off.
The NPeriods is the number of periods that occur during the lifetime of the loan. For example, if a car is financed in 5 years, this value would be (5 years * 12 months each =) 60 months. When passing this argument, you must remember to pass the right amount.
The Period argument represents the payment period. For example, it could be 3 to represent the 3rd year of a 5 year loan. In this case, the IPmt() function would calculate the interest earned in the 3rd year only.
The PaymentType specifies whether the periodic (such as monthly) payment of the loan is made at the beginning (1) or at the end (1) of the period.
The FutureValue and the PaymentType arguments are not required.
While the IPmt() function calculates the amount paid as interest for a period of a loan or an investment, the PPmt() function calculates the actual amount that applies to the balance of the loan. This is referred to as the principal. Its syntax is:
PPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)
The argument are the same as described in the previous sections.
The PV() function calculates the total amount that future investments are worth currently. Its syntax is:
PV(Rate, NPeriods, Payment, FutureValue, PaymentType)
The arguments are the same as described earlier.
Suppose a customer comes to your car dealer and wants to buy a car. The salesperson would first present the available cars to the customer so the customer can decide what car he likes. After this process and during the evaluation, the sales person may tell the customer that the monthly payments would be $384.48. The customer may then say, "Wooooh, I can't afford that, man". Then the salesperson would ask, "What type of monthly payment suits you". From now on, both would continue the discussion. Since the salesperson still wants to make some money but without losing the customer because of a high monthly payment, the salesperson would need to find a reasonable rate that can accommodate an affordable monthly payment for the customer.
The Rate() function is used to calculate the interest applied on a loan or an investment. Its syntax is:
RateE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)
All of the arguments are the same as described for the other functions, except for the Guess. This argument allows you to give some type of guess for a rate. This argument is not required. If you omit it, its value is assumed to be 10.
The IRR() function is used to calculate an internal rate of return based on a series of investments. Its syntax is:
The Values argument is a series (also called an array or a collection) of cash amounts that a customer has made on an investment. For example, a customer could make monthly deposits in a savings or credit union account. Another customer could be running a business and receiving different amounts of money as the business is flowing (or losing money). The cash flows don't have to be the same at different intervals but they should (or must) occur at regular intervals such as weekly (amount cut from a paycheck), bi-weekly (401k directly cut from paycheck, monthly (regular investment), or yearly (income). The Values argument must be passed as a collection of values, such as a range of selected cells, and not an amount. Otherwise you would receive an error.
The Guess parameter is an estimate interest rate of return of the investment.
The NPV() function uses a series of cash flows to calculate the present value of an investment. Its syntax is:
NPV(Rate, Value1, Value2, ...)
The Rate parameter is the rate of discount in during one period of the investment.
As the NPV() function doesn't take a fixed number of arguments, you can add a series of values as Value1, Value2, etc. These are regularly made payments for each period involved. Because this function uses a series of payments, any payment made in the past should have a positive value (because it was made already). Any future payment should have a negative value (because it has not been made yet).
|S17||Use the Control Toolbox to add controls|
|S31||Create a calculated field|
Watts A loan
|Previous||Copyright © 2002-2007 FunctionX, Inc.|