Using the Financial Features Q&A's financial functions deal with payments made for a certain number of periods at a given interest rate per period. The functions use interest rate in decimal form, so if interest is given in percent, it must first be divided by 100. Similarly, the functions that return interest rate, @CGR and @IR, return the rate in decimal form, so their values must be multiplied by 100 if percent is desired. Also, care must be taken to use interest rate per period. For example, if interest is compounded monthly, use interest rate per month instead of interest rate per year. The list of financial functions includes: @CGR Compound growth rate @FV Future value @PMT Payment @PV Present value @IR Interest rate The @ in front of each acronym indicates that each function returns a value that can be used in a programming statement. Each financial function takes three parameters. These parameters may be numbers, field IDs, or expressions. @CGR(pv, fv, np) Purpose: Find the rate of return of an investment, given the present value, future value, and number of periods. Example: Find the CGR per year for a person who invests $10000 and 20 years later receives $30000. If #1 = $10000, #2 = $30000, and #3 = 20, then #4 = 100 * @CGR(#1,#2,#3) sets field #4 to 5.647, the interest rate per year of this investment. Formula: CGR = ((Future Value/Present Value) ^ (1/Number of Periods)) - 1.00 Restrictions: Number of periods may not equal 0. Present and future value must have the same sign. Q & A Application Note Rev. 6/92 #2114 Page 1 of 4 Using the Financial Features (cont.) @FV(pa, i, np) Purpose: Find the future value of a stream of payments, given the payment, interest rate per period, and number of payments. Example: Find the value of an account when $50 a month is deposited for 10 years at 1% interest per month. If #1 = $50, #2 = .01, and #3 = 120, then #4 = @FV(#1,#2,#3) sets field number #4 to $11501.93. Formula: FV = Payment * ((1 + Int)^(No. of Payments)) - 1.00 Interest Using the Financial Features (cont.) Restrictions: Interest must be >= -1. For interest = 0, FV = Payment * Number of payments. Important: @FV finds the future value of a stream of payments, not the future value of an amount. The future value of an amount is given by: FVA = (Present Value) * @EXP(1 + Interest, Number of Periods) @PMT(pv, i, np) Purpose: Calculate the payments due on a loan, given the loan amount (present value), the interest rate per period, and number of payments. Example: Find the monthly payments due on a loan of $100,000 for 15 years at 1% interest per month. If #1 = $100000, #2 = .01, and #3 = 180, then #4 = @PMT(#1,#2,#3) sets field #4 to 1200.17. Q & A Application Note Rev. 6/92 #2114 Page 2 of 4 Formula: PMT = Principal * Interest 1 - (1 + Interest)^(-No. of Payments) Restrictions: Interest must be >= -1. Number of payments may not equal 0. Using the Financial Features (cont.) Important: Due to compounding, 1% per month is not equal to 12% per year: To convert monthly interest to yearly interest use Yearly Interest = @EXP(1 + Monthly Interest, 12) - 1. To convert yearly interest to monthly interest use Monthly Interest = @EXP(1 + Yearly Interest, 1/12) - 1. From these equations, we see that 1% per month, compounded monthly is 12.68% per year and 12% per year equals .95% per month compounded monthly. @PV(pa, i, np) Purpose: Find the present value of an annuity, given the payment, interest rate per period, and number of payments. Example: How much money should be invested in an oil well that will pay a return of $100 a month for 20 years, if a return of 1.2% a month is desired? If #1 = $100, #2 = .012, and #3 = 240, then #4 = @PV(#1,#2,#3) sets #4 to 7857.46. Formula: PV = Payment * 1 - (1 + Interest)^(-No. of payments) Interest Restrictions: Interest must be >= -1. For interest = 0, PV = Payment * Number of payments. Q & A Application Note Rev. 6/92 #2114 Page 3 of 4 @IR(pv, pa, np) Purpose: Find the interest rate on a loan given the loan amount (present value), the payment, and the number of payments. Example: If the payments on a 20 year mortgage valued at $75,000 are $850 per month, what is the yearly interest? Let #1 = $75000, #2 = $850, and #3 = 240. The monthly interest is #4 = @IR(#1,#2,#3), which equals .0104 or 1.04%. The yearly interest rate (see important note below @PMT description) can be obtained by letting #5 = 100 * (@EXP(1 + #4, 12) - 1), which equals 13.197%. Formula: This cannot be expressed as formula like the other financial functions, but the interest rate is the number that can be used in @PV to get the present value that is now given. Restrictions: Present value and payment must be positive. Number of payments must be >= 1. Q & A Application Note Rev. 6/92 #2114 Page 1 of 4