Annuities

 where i = the interest rate per period N = the total number of payments made FV = the future value Payment = the payment in each period If there are m payments to be made each year you should divide each interest rate by m and multiply the number of years by m.  In our situation the formula becomes

 You can see that this nets you quite a bit of money.  A Pattern When Using Formulas.  Whether or not you've noticed it, there is an important pattern involved in almost every class where you make use of formulas.  When I went through most of my year of Physics classes in college I was not what anyone would consider to be real motivated.  For example, I couldn't have discussed even the tiniest bit about how electrons are supposed to move through fields.  But I noticed something important when preparing for tests.  Even though I had to know Calculus to get through my Physics classes the basic substance of what I needed to know in order to get through my tests could be broken down to a few things and a process. Knowing the formulas that we were studying. Knowing the units on the formulas. Reading the problems and determining what we were supposed to figure out. Reading the problems and determining what was given in the problem statement. Figuring out what formula related what was known to what was unknown and then likely as not solving for the unknown. What does it mean to solve for the unknown?  Maybe this is best illustrated by using annuities.  Consider the formula above for the FV of an annuity.  There are four variables in the formula, FV, Payment, N and i.  Theoretically, at any given point in time you could use this formula to determine one of the variables given you have values for the other three.  While this is often easier said than done, it is the basis of solving for the unknown.  The following table illustrates this further.

 Unknown Knowns Formula Excel Function FV = Future Value Payment, N, i =FV Payment FV, N, i =PMT N = Number of Payments FV, Payment, i =NPER i = Interest Rate FV, Payment, N No closed form solution exists, needs a numerical method =RATE

 It is unimportant to know these formulas for this class, they are included to illustrate my point.  It is possible that you are completely unfamiliar with the ln( ) function, the inverse function of exponentiation with a particular base.  This is not the least bit important in this class.  Notice that each of these scenarios has a built in Excel function which will give us the desired solution. More About the Excel Annuity Functions.  Ultimately, our goal is going to be to create a user friendly form that will make the computation of the solutions of these annuity related questions relatively effortless for the user.  We will focus specifically on that issue in the next webpage.  Currently I want to include more discussion about the built-in Excel functions.  The following four tables contain a discussion of each of the functions and the inputs.  Inputs in bold letters are required.

 = FV( i, N, Payment, PV, Type ) Input Description i = Interest Rate This is the per payment period interest rate.  It is often the annual rate divided by 12 if the payments are monthly. N = Number of Payments This is the total number of payments. It is often the number of years times 12 if the payments are monthly. - Payment You must enter the negative of the per period payment amount. PV The present value of the annuity which is assumed to be 0 if omitted. Type 0 = payment at end of period (default) 1 = payment at beginning of period

 = PMT( i, N, PV, FV, Type ) Input Description i = Interest Rate This is the per payment period interest rate.  It is often the annual rate divided by 12 if the payments are monthly. N = Number of Payments This is the total number of payments. It is often the number of years times 12 if the payments are monthly. PV The present value of the annuity. FV The future value or cash amount you want to attain when completes.  This is assumed to be 0 if omitted. Type 0 = payment at end of period (default) 1 = payment at beginning of period

 = NPER( i, Payment, PV, FV, Type ) Input Description i = Interest Rate This is the per payment period interest rate.  It is often the annual rate divided by 12 if the payments are monthly. - Payment You must enter the negative of the per period payment amount. PV The present value of the annuity. FV The future value or cash amount you want to attain when completes.  This is assumed to be 0 if omitted. Type 0 = payment at end of period (default) 1 = payment at beginning of period

 = RATE( N, Payment, PV, FV, Type, Guess ) Input Description N = Number of Payments This is the total number of payments. It is often the number of years times 12 if the payments are monthly. Payment PV The present value of the annuity. FV The future value or cash amount you want to attain when completes.  This is assumed to be 0 if omitted. Type 0 = payment at end of period (default) 1 = payment at beginning of period Guess Ordinarily this is omitted, but you can put in something like 0.10 if 10% is a reasonable guess.

 Now we will develop a UserForm that will allow a user to input certain values for an annuity and calculate what is unknown.  This will be developed in the next webpage.