Using Spreadsheets for Equivalency Computation

The easiest single-cell spreadsheet functions to apply to find P, F, or A require that the cash flows exactly fit the function format. The functions apply the correct sign to the answer that would be on the cash flow diagram. That is, if cash flows are deposits (minus), the answer will have a plus sign. In order to retain the sign of the inputs, enter a minus sign prior to the function. Here is a summary and examples at 5% per year.

§  Present worth P: Use the PV function = (i%, n, A, F) if A is exactly the same for each of n years; F can be present or not. For example, if A = $3000 per year deposit for n = 10 years, the function = PV (5%, 10, -3000) will display P = $23,165. This is the same as using the P/A factor to find P = 3000(P/A,5%,10) = 3000(7.7217) = $23,165.

§  Future worth F: Use the FV function = FV (i%, n, A, P) if A is exactly the same for each of n years; P can be present or not. For example, if A = $3000 per year deposit for n = 10 years, the function = FV (5%, 10, -3000) will display F = $37,734. This is the same as using the F/A factor to find F = 3000(F/A, 5%, 10) = 3000(12.5779) = $37,734.

§  Annual amount A: Use the PMT function = PMT(i%, n, P, F) when there is no A present, and either P or F or both are present. For example, for P= $-3000 deposit now and F = $5000 returned n = 10 years hence, the function =-PMT (5%, 10, -3000, 5000) will display A= $9. This is the same as using the A/P and A/F factors to find the equivalent net A= $9 per year between the deposit now and return 10 years later.

A = -3000 (A/P,5%,10) + 5000 (A/F,5%,10) = -389 + 398 = $9

§  Number of periods n: Use the NPER function = NPER(i%, A, P, F) if A is exactly the same for each of n years; either P or F can be omitted, but not both. For example, for P = $-25,000 deposit now and A = $3000 per year return, the function = NPER(5%,3000, -25000) will display n = 11.05 years to recover P at 5% per year. This is the same as using trial and error to find n in the relation 0 = -25,000 + 3,000 (P/A,5%, n).

When cash flows vary in amount or timing, it is usually necessary to enter them on a spreadsheet, including all zero amounts, and utilize other functions for P, F, or A values. All spreadsheet functions allow another function to be embedded in them, thus reducing the time necessary to get final answers. Example (2-9) illustrates these functions and the embedding capability. Example (2-10) demonstrates how easily spreadsheets handle arithmetic and percentage gradients and how the IRR (rate of return) function works.

Carol just entered college and her grandparents have offered her one of two gifts. They promised to give her $25,000 toward a new car if she graduates in 4 years. Alternatively, if she takes 5 years to graduate, they offered her $5000 each year starting after her second year is complete and an extra $5000 when she graduates. Draw the cash flow diagrams first. Then, use i = 8% per year to show Carol how to use spreadsheet functions and her financial calculator TVM functions to determine the following for each gift offered by her grandparents.

1)      Present worth P now

2)      Future worth F five years from now

3)      Equivalent annual amount A over a total of 5 years

4)      Number of years it would take Carol to have $25,000 in hand for the new

car if she were able to save $5000 each year starting next year.

Spreadsheet: The two cash flow series, labeled Gift A (lump sum) and Gift B (spread out), are in Figure (2-21). The spreadsheet in Figure (2-22a) lists the cash flows (don’t forget to enter the $0 cash flows so the NPV function can be used), and answers to each part using the PV, NPV, FV, or PMT functions as explained below. In some cases, there are alternative ways to obtain the answer. Figure (2-22b) shows the function formulas with some comments. Remember that the PV, FV, and PMT functions will return an answer with the opposite sign from that of the cash flow entries. The same sign is maintained by entering a minus before the function name.

Figure (2–21): Cash flows for Carol’s gift from her grandparents, Example (2-9)

1)      Rows 12 and 13: There are two ways to find P; either the PV or NPV function. NPV requires that the zeros be entered. (For Gift A, omitting zeros in years 1, 2, and 3 will give the incorrect answer of P= $23,148, because NPV assumes the $25,000 occurs in year 1 and discounts it only one year at 8%.) The single-cell PV is hard to use for Gift B since cash flows do not start until year 2; using NPV is easier.

2)      Rows 16 and 17: There are two ways to use the FV function to find F at the end of year 5. To develop FV correctly for Gift B in a single cell without listing cash flows, add the extra $5000 in year 5 separate from the FV for the four A = $5000 values. Alternatively, cell D17 embeds the NPV function for the P value into the FV function. This is a very convenient way to combine functions.

3)      Rows 20 and 21: There are two ways to use the PMT function to find A for 5 years; find P separately and use a cell reference or embed the NPV function into the PMT to find A in one operation.

4)      Row 24: Finding the years to accumulate $25,000 by depositing $5000 each year using the NPER function is independent of either plan. The entry = NPER (8%, -5000,25000) results in 4.3719 years. This can be confirmed by calculating 5000(F/A,8%,4.3719) = 5000(5.0000) = $25,000 (The 4.37 years is about the time it will take Carol to finish college. Of course, this assumes she can actually save $5000 a year while working on the degree.)

Calculator: Table (2-3) shows the format and completed calculator function for each gift, followed by the numerical answer below it. Minus signs on final answers have been changed to plus as needed to reflect the same sense as that in the spreadsheet solution. When calculating the values for Gift B, the functions can be performed separately, as shown, or embedded in the same way as the spreadsheet functions are embedded in Figure (2-22). In all cases, the answers are identical for the spreadsheet and calculator solutions.

Figure (2–22): (a) Use of several spreadsheet functions to find P, F, A, and n values, and (b) format of functions to obtain values, Example (2-9).

             Table (2–3): Solution Using Calculator TVM Functions, Example (2-9)

Bobby was desperate. He borrowed $600 from a pawn shop and understood he was to repay the loan starting next month with $100, increasing by $10 per month for a total of 8 months. Actually, he misunderstood. The repayments increased by 10% each month after starting next month at $100. Use a spreadsheet to calculate the monthly interest rate that he thought he was to pay, and what he actually will pay.

Figure (2-23) lists the cash flows for the assumed arithmetic gradient G = $10 per month, and the actual percentage gradient g = 10% per month. Note the simple relations to construct the increasing cash flows for each type gradient. Apply the IRR function to each series using its format = IRR (first_cell: last_cell). Bobby is paying an exorbitant rate per month (and year) at 14.9% per month, which is higher than he expected it to be at 13.8% per month.

If needed to solve a problem, the tables in the rear of this text provide the numerical value for any of the six common compound interest factors. However, the desired i or n may not be tabulated. Then the factor formula can be applied to obtain the numerical value; plus, a spreadsheet or calculator function can be used with a “1” placed in the P, A, or F location in the function. The other parameter is omitted or set to “0.”

For example, the P/F factor is determined using the spreadsheet’s PV function with the A omitted (or set to 0) and F = 1, that is, = -PV(i,n,,1) or = -PV(i,n,0,1). The minus sign makes the result positive. If a calculator is used, the functional notation is PV(i,n,0,1) for the function PV(i,n,A,F ). Table (2-4) summarizes the notation for spreadsheets and calculators. This information, in abbreviated form, is included inside the front cover.

Figure (2–23): Use of a spreadsheet to generate arithmetic and percentage gradient cash flows and application of the IRR function, Example (2-10).

When using a spreadsheet, an unknown value in one cell may be required to force the value in a different cell to equal a stated value. For example, the present worth of a given cash flow series is known to equal $10,000 and all but one of the cash flow values is known. This unknown cash flow is to be determined. The spreadsheet tool called GOAL SEEK is easily applied to find one unknown value.