Computation - Operations Management/Industrial Engineering

Computation

Investment Economics

Time-Value-of-Money Factors

Functions

	Investment Economics
	-Time-Value-of-Money Factors

User-Defined

The add-in installs a number of user defined functions that compute time value of money factors. Investments usually involve cash flows that occur at different times. For example the investment in a bond requires an outflow of money for the purchase of the bond at one time, usually called time 0, inflows of money in the form of periodic interest payments and an inflow at some later time that is the redemption value of the bond. The cash flows associated with the investment occur at different points in time. To measure the acceptability of the investment or to compare it against alternative uses of capital, we may want to compute the equivalent value of the cash flow at time 0 or at some specified future time. We compute equivalent values using time-value-of-money factors. Although Excel has a variety of native functions to perform these calculations, they typically do not use the language of investment analysis texts and they do not include all the familiar factors. The add-in provides the most familiar functions in the form of user defined functions.

One way, among many others, to place a function in a cell is to select the cell and choose insert function from the Excel Insert menu. The dialog box below appears. The User Defined category is at the button of the list. When that category is selected, the set of user-defined functions currently available appears at the right. The list shown below includes some of the functions provided by the Economics add-in. We prefix most of the functions with E_ to distinguish them from others that may be resident.

Just below the list, we see the function currently selected. The particular one illustrated is the A/F factor. This computes the future value of a uniform series. The notation shows the number of arguments, 2 in this case, but not their identity. There are no Help files available for these functions. You can learn about them and their arguments in the following discussion.

Cash Flows

The figures below show the five kinds of cash flow components that are manipulated by the time-value-of-money factors. Each factor takes a value expressed in one of the cash flows and changes into an equivalent for another. It is important to notice the timing of the payments because the factors are derived particularly for this timing. The cash flows involving more than one payment occur at the end of periods. Thus the payment for period t occurs at time t + 1.

Fig. 1. A single cash flow at time t

Fig. 2. A single cash flow at time t+n

Fig. 3. A uniform series of n payments, starting at time t and ending at time t+n

Fig. 4. An arithmetic gradient of payments with the first at time t+2 and the last at time t+n

Fig. 5. A geometric gradient with the first payment at time t+1 and subsequent payments increasing by the factor (1 + g), where g is a positive quantity normally less than 1.

Factors

The following sections describe each factor by its traditional notation and name. The second row, shows the Excel function name and the arguments of the function. The third line provides an example to illustrate its use. The answer to the example is provided in an image from an Excel worksheet. The image shows the function calculation using the traditional notation followed by the calculation via the user-defined functions.The yellow areas show the formulas using the user-defined functions. Arguments are entered as references to worksheet cells, however, they could have been entered directly as numbers. Using pointers to cells is preferred because the parameters of the problem are clearly documented on the worksheet.


	(F/P, i, n): Single payment compound amount factor
	E_FP(interest rate per period, number of periods)
	At age 5 you were left $10,000 from the fortune of a favorite aunt. Your parents put the money in a trust fund earning 10% interest. You are now 25 years old and may draw from the trust fund. How much do you have?

	You can withdraw $62,275 from the bank.

	(P/F, i, n): Single payment present worth factor
	E_PF(interest rate per period, number of periods)
	You win the lottery and the government promises to pay you $1,000,000 in ten years. Your minimum acceptable rate of return on investments is 10%. What is the prize worth to you now?

	With a minimum return of 10%, you should accept no less than $385,543.29.

	(A/F, i, n): Sinking Fund factor
	E_AF(interest rate per period, number of periods)
	You are 20 years old and just got married. Your spouse and you agree that you want to retire at age 60 with $1,000,000. How much do you have to put away each year if you earn 10% on your investments.

	You must put away $2259.41 per year, or less than $200 per month to be a millionaire by the time you are 60 years old.

	(F/A, i, n): Uniform Series Compound Amount factor
	E_FA(interest rate per period, number of periods)
	You are a parent. Your daughter starts college in 18 years. If you put away $100 each month for 18 years, how much will you have when she is ready to begin college? The CD's you invest in return 6% per year compounded monthly. Since the payments are monthly, we use a monthly interest rate in the factor and express the number of periods in months.

	The deposits will grow to $38,735.

	(A/P, i, n): Capital Recovery Factor
	E_AP(interest rate per period, number of periods)
	You finally have a job after 4 years of college. To escape the high rent of the Austin area you buy a house for $100,000. You finance the full amount with a 30 year mortgage. The interest rate is 9% a year, and your payments are monthly. What are the total of all payments if you make all payments as scheduled? Since the payments are monthly, we use a monthly interest rate in the factor and express the number of periods in months.

	The total you will pay is almost three times the amount of the loan. The interest is the difference between the total payments and the loan amount, $189,664.

	(P/A, i, n): Uniform Series Present Worth Factor
	E_PA(interest rate per period, number of periods)
	You can afford $300 a month to purchase a car. If the interest rate is 6% a year and the loan is for 60 months, how much can you finance? Since the payments are monthly, we use a monthly interest rate in the factor and express the number of periods in months.

	The present value of the loan payments is the amount that you can borrow.

	(P/G, i, n): Arithmetic Gradient Present Worth Factor
	E_PG(interest rate per period, number of periods)
	You are a freshman in college and you just paid $1000 for tuition and fees for the University. Assuming the cost goes up by $100 per semester for the remaining 7 semesters of your education, how much must you have in the bank right now to cover the remaining charges? Assume your investments earn 3% every six months. Since the payments are twice a year, the interest rate is the six month rate and the number of periods is in semester intervals. Note than the gradient series is on-top-of a uniform series, so the present worth formula has two terms.

	You must have $8648.79 in the bank to pay your remaining tuition bi.

	(A/G, i, n): Arithmetic Gradient to Uniform Series Factor
	E_AG(interest rate per period, number of periods)
	You are a freshman in college and you just paid $1000 for tuition and fees for the University. Assume the cost goes up by $100 per semester for the remaining 7 semesters of your education. Your parents will send you a fixed amount every semester to cover your fees. What payment every semester will provide your tuition through your college career. Assume your investments earn 3% every six months.

	Assuming you invest the excess, a uniform payment of $1388.19 will take care of your tuition for the remaining 7 semesters.

	(P/G, i, g, n): Geometric Series Present Worth Factor
	E_PGeo(interest rate per period, percentage increase per period, number of periods)
	What is the amount of 10 equal annual deposits that can provide five annual withdrawals, when a first withdrawal of $1,000 is made at the end of year 11, and subsequent withdrawals increase at the rate of 6% per year over the previous year's if the interest rate is 8%, compounded annually?

	An annual payment of $307.96 for ten years will provide the amount necessary for the withdrawals.