The HP Palmtops come with tools for analyzing cash flows. The HP 100/200LX have a Cash Flow program built into HP Calc. The 95LX lacks this feature, but comes with a built-in Lotus 1-2-3 cash flow worksheet (_CFLOW.WK1). These tools offer a quite sophisticated level of cash flow analysis, suitable for financial analysts, realtors, and individual investors.
A Quick Review
Before we tackle more complicated problems, let's look at a simple example for those who have never used the Cash Flow programs on the HP Palmtops.
Suppose a bank offers a savings account that yields 3.25% annual interest, and you deposit $10,000 now and $30,000 at the start of each of the next five years. How much will these deposits be worth at the end of the sixth year?
One way to picture this transaction is to use a cash flow diagram. The following diagram, created in 1-2-3, shows that you deposited $10, 000 initially and $30,000 at the start of each of the next 5 years.
The bars descend below the axis to show that you had to dip into your pocket to pay out the money to the bank. The last bar on the right shows the total amount that you can take out of this bank account, your deposits plus the accumulated interest paid by the bank. To find the exact value of this last cash flow you can use the _CFLOW.WK1 <Available ON DISK> worksheet on the HP 95LX or the Cash Flow Application on the HP 100/200LX.
CASH FLOW ON THE HP 95LX WITH LOTUS 1-2-3
On the 95LX, start 1-2-3 and press (MENU) File Retrieve, type in or tab to C:\_CFLOW.WK1 and press (ENTER) (_CFLOW.WK1 <Available ON DISK>). This worksheet has a number of built in Lotus macros to speed up the clearing of data, data entry, and other functions. (You access a Lotus macro by pressing (ALT) first.)
Press (ALT)-Erase Yes (ENTER) to clear the worksheet. Then press (ALT)-(S) 5 (ENTER) to set the size of the list. Now you're ready to enter the cash flow data.
Press (ALT)-(G) -10000 (ENTER) 1 and (ENTER) to enter the first negative cash flow 10,000. Then press (ALT)-(G) -30000 (ENTER) 5 (ENTER) to enter the next five negative cash flows of 30,000 each. Press (ALT)-(N) and after a couple of seconds the display will show the values for NPV, NUS, NFV, TOTAL, etc. Put 3.25 in cell B10 (the cell to the right of "I%= " ) and the cell to the right of "NFV= " will show the Net Future Value, -171,806.17. (The NFV is displayed as a negative value because this equation does not assume that it will be paid out.)
CASH FLOW ON THE HP 100/200LX USING HP CALC'S CASH FLOW
If you have an HP 100/200LX, start HP CALC and press (CTRL)-(F) to use the Cash Flow application. If necessary, press (MENU) Clear Data (ENTER) to clear any existing data.
Enter -10000 as the Init Flow and -30000 for Flow# 1. Tab to the column to the right of the -30,000 and key in the number 5. Next, key in 1 and press (F9) to set P/YR (periods per year). Key in 3.25 and press (F8) to set the I%YR (Interest Per Year). Finally, press (F7) to calculate the NFV (Net Future Value) of -171,806.17 (ignore the negative sign).
COULD HAVE USED TVM
The example above illustrates the simplest kind of cash flow: one that has regular, uniform flows. As a matter of fact, the cash flow example given is so uniform that you could have used HP CALC's Time Value of Money (TVM) program to find the answer. (TVM is based on a formula that assumes regular, uniform cash flows.) Just start HP Calc and go to the Time Value of Money application. Clear any previous data on the HP 95LX by pressing (MENU) Erase Data. Clear HP 100/200LX data by pressing (MENU) Clear Data.
In TVM set N=5, I%YR=3.25, and PV= -10000. (You can key in 10000 quickly using exponential notation by pressing (1)(L)(E)(4)(F8).) Next set PMT= -30000 and P/YR=1. Press (F4) until the Begin/End Mode is set to End. Finally, press (F10) to solve FV = 171,806.17.
The TVM application gives the same number for Future Value as did the cash flow analysis, but it reverses the sign of the number. The TVM program assumes that this money will be paid to you, whereas the cash flow application makes no such assumption. However, in the case of financial transaction without uniform cash flows, you'll have to set aside the TVM program and "go with the flow."
A typical problem for a financial analyst might be: "If I invest in a real estate project, will I make more money in five years than I would if I put my money in a mutual fund during the same period?" These folks often deal with problems in which the cash flows occur at regular intervals: monthly, quarterly, or annually, as in mortgages, insurance, annuities, etc. However, the problem stated at the beginning of this paragraph requires the comparison of two different investments with uneven cash flows. The resolution to this problem is based on the idea that you can compare the value of future payments by "discounting" them to their present value using the compound interest formula.
For example, in the savings account problem discussed earlier, Cash Flow or TVM were used to "appreciate" an investment to its Future Value ($171,806.17) by adding the earned interest ($11,806.17) to the amount invested ($160,000). We can also use Cash Flow and TVM to depreciate or "discount" cash flows to their comparable Present Value by subtracting the accumulated compound interest from a cash flow.
Present Value of a cash flow is not all that hard to understand. In the savings account example above, we know that we have to initially deposit $10,000. Since it is a cash flow that occurs at the present moment, the Present Value of that cash flow is $10,000.
In the same example we know that next year (and every subsequent year for four years) we have to deposit $30,000. These are future cash flows and we have to come up with that money from somewhere. For simplicity's sake we assume that we have that money right now in some other investment. Let's say that other investment earns 2.25% interest per year. We would need $29,339.85 in that other investment, right now, so the other investment would be worth $30,000 in one year. The $29,339.85 is the Present Value of year-from-now $30,000.
You can compare the Present Values of cash flows and calculate the actual interest rates earned by these investments.
Transactions without uniform cash flows
Try this example. A real estate broker tries to interest you in the following investment. He says that if you buy a small rental property for $80,000, up-front, you'll earn $5,000 by the start of the next year, additional increments of $10,000 by the start of three succeeding years, another $15,000 by the start of the sixth year, and you can sell the property for $80,000 at the end of the sixth year. He claims that you'll earn the equivalent of 10% interest with this investment. The graph of his proposed investment's cash flow would look something like this.
Non Uniform Cash Flow Diagram: Graphic
The above graph, created in 1-2-3, shows the first cash flow as negative, since it is money flowing out of your account. The other flows are positive, showing money coming into your account.
The investment sounds good, but how can you verify that you'd actually make 10% on this deal? The answer -go with the flow. That is, use the cash flow applications built into the HP palmtops.
LOTUS 1-2-3 ON THE HP 95LX
Call up the _CFLOW.WK1 worksheet as described earlier and press (ALT)-(E) Yes (ENTER) to clear the worksheet. Then press (ALT)-(S) 6 (ENTER) to set the number of cash flow periods (there will be 6 periods, not counting the initial investment). Then enter the cash flows into each cash flow period. Key in by pressing the following keys:
(ALT)-(G) -80000 (ENTER) 1 (ENTER) (ALT)-(G) 5000 (ENTER) 1 (ENTER) (ALT)-(G) 10000 (ENTER) 3 (ENTER) (ALT)-(G) 15000 (ENTER) 1 (ENTER) (ALT)-(G) 80000 (ENTER) 1 (ENTER)
Finally, press (ALT)-(N) and wait a couple of seconds. The display will show the values for NPV, NUS, NFV, TOTAL, etc. If necessary, put 10 in cell B10 (the cell to the right of "I%=") and observe that the cell to the right of "NPV=" will show the Net Present Value, 1,624.93. The cell to the right of IRR%= will show 10.47. The significance of these numbers is explained below. (If these numbers are displayed to greater than two decimal places, you can press (MENU) Worksheet Global Format Fixed 2 (ENTER) to set the display to two places.)
Cashflow Program from HP 95LX : Graphic
HP CALC CASH FLOW ON THE HP 100/200LX
If you have an HP 100/200LX, start HP CALC and press (CTRL)(F) (Cash Flow). Press (MENU) Clear Data (ENTER) to clear any existing data. Then, key in -80000 as the Init Flow and 5000 for Flow# 1). Key in 10000 for Flow# 2), use the arrow keys to move the cursor to the right of 10,000.00 and key in 3 (the number of 10,000 cash flows). Move the cursor down to Flow# 3) and key in 15000 and for Flow# 4) key in 80000.
Then set P/YR = 1 by keying in 1 and pressing (F9). Set I%YR = 10% by keying in 10 and pressing (F8). Finally, press (F4) to see that IRR%/YR = 10.47 and press (F5) to see that NPV = 1,624.93.
What is the NPV and IRR%?
In the above example, the cash flow applications took each of the positive cash flows and "discounted" them by 10% per year back to the present time. This gives the Present Value, which is the Future Value minus the accumulated compound interest (10% in this case).
Next it subtracted the initial investment from the total of these positive, discounted cash flows (Present Values). This would be zero if 10% was the actual interest rate earned.
The net result of this is the Net Present Value (1,624.03 in this case). The fact that the NPV is greater than zero indicates that you would earn more than the proposed 10%. If NPV was negative that shows the cash flows would earn less than 10%, and if zero they earn exactly 10%. The Net Present Value serves as a check to see if the proposed investment will earn more than another, imaginary investment that would earn 10%. The number 1,624.03 tells you that your initial investment would need to be $81,624.03 instead of $80,000 in order to earn these cash flow with a 10% interest rate.
The IRR% tells you the actual rate of interest that you would earn on this investment, 10.47%. To see if this is true, use the HP 100/200LX to solve for IRR%/YR, again, and then press the (F8) to put the 10.47 rate in I%YR. Then solve for NPV and you'll see a number that is very close to zero. Because of rounding error the number is not exactly zero. A NPV number of zero or close to zero tells you that the stated interest rate, I%YR, is the same as the IRR%YR, the exact interest you would earn. (On the HP 95LX press (MENU) Range Value B8 (ENTER) B10 (ENTER) and the NPV will show 0.00.)
The HP Palmtop User's Guides show more examples of cash flows (see "cash flow calculations" in the User's Guide index for page reference). But each of the examples involve cash flows that are either all positive or all negative, except for the initial flow.
Flip-flopping cash flows
What happens if the cash flows flip-flop so that some of them are negative and some are positive? Can you use the cash flow program or _CFLOW worksheet to compute any meaningful results? Let's check this out with the following example.
Suppose you have $100,000 in an interest-bearing account, earning 3.25% per year.
You hear about a rental property that is selling for $170,000. The income from the property is $30,000 a year. The owner wants a down payment of -$30,000 and -$70,000 at the start of the first and second year (any money out of your pocket has a negative sign in front of it). You plan to remodel in the fourth year. The cost for that will be -$50,000. Early in the sixth year you'll sell the property for $170,000. The owner has used the _CFLOW worksheet, on his HP 95LX, and predicts that you'll earn more than a 15% on this investment. That sounds pretty good, so you ask him how he came to that conclusion.
He shows you a diagram and a list of the proposed cash flows.
Init Flow: -30,000 1 ( 30,000) Flow#1: -40,000 1 (30,000 70,000) 2: -40,000 1 (30,000 70,000) 3: +30,000 1 (30,000) 4: -20,000 1 (30,000 50,000) 5: +30,000 1 (30,000) 6: +170,000 1 (170,000)
The numbers in parentheses reflect the income from the property minus the amount you'll pay out. You only need to key in the cash flows at the left and the number of times the cash flow appears. Here's how to duplicate his solution for this investment.
LOTUS 1-2-3 ON THE HP 95LX
In your _CFLOW worksheet again, press (ALT)-(E) Yes (ENTER) to clear the worksheet. Then key in the cash flows as you did before. (Cash Flows# 1 & 2 are both -40000. Press (ALT)-(G) and key in -40000 once and enter 2 when the macro asks you the number of times it occurs.)
When you've entered the data, press (ALT)-(N). When the answer screen appears, key in 15 in the I%= field (i.e. cell B10). You should see that the NPV is 1,673.06 and the IRR% is 15.45). So it appears that he is correct. The actual rate is 15.45, which is even better than the 15% she promised.
Have your cash flows graphically displayed by pressing (ALT)-(B). A bar chart similar to the following will appear.
Bar Chart Display of Property Rental Problem: Graphic
You can press (+) or (-) to zoom in and out. (This problem is found in RENTAL.WK1 <Available ON DISK>.)
HP CALC CASH FLOW ON THE HP 100/200LX
You can get the same results on the 100/200LX by using the Cash Flow application. Clear any existing data and key in the cash flow amounts shown above. Then press 1 (F9) to set P/YR and press 15 (F8) to set I%YR. Finally, press (F4) to get IRR%/YR = 15.45 and press (F5) to see that NPV = 1,673.06. (RENTAL.CFL <Available ON DISK> contains this cash flow).
Results Misleading?
Before plunking down your hard earned cash you decide to get a second opinion. Your investment counselor tells you that the above results are misleading. She shakes her head and mumbles something about "amateur financial analysts" and points out that some of the cash flows are negative and some are positive. She tries to explain that this particular type of transaction requires a more involved analysis called the "Financial Management Rate of Return" (FMRR).
Financial Management Rate of Return
You won't find any guidance on this subject in the HP User's Guides. The best explanation of FMRR can be found in a small book called An Easy Course In Using the HP 19B-II, from GrapeVine Press. The book is written for the HP 19B-II financial calculator, but since HP Calc is a close match to the HP 19B-II, the book is well suited for users of the HP Palmtops. The chapters on the TVM, CFLO and LIST applications are particularly useful. The chapter on using HP Solve is a mother lode of re-usable code. It contains over 25 different, Solver equations that deal with residential and commercial real estate, personal finance, and business management, among other things.
The only problem with the book is that there is no accompanying disk. You have to key in the equations by hand. In addition, you sometimes have to make adjustments for the different keystrokes on the two machines. (See the side bar on page 52 for some helpful tips on transforming HP 19BII code to HP Calc/Solver code.)
The Financial Management Rate of Return procedure reduces the problem of many positive and negative cash flows down to two cash flows; one negative "initial investment" at the beginning of the investment and one positive "final payment" at the end of the investment period. Then an accurate rate of return can be easily calculated from these two cash flows. The negative cash flows are "discounted" (subtract accumulated interest) back to the initial investment and the positive cash flows are "appreciated" (add accumulated interest) forward into the final payout.
In order to discount or appreciate these cash flows you have to make some assumptions about the interest earned on money paid into or taken out of the investment.
The first assumption is that the money used to pay the negative cash flows is kept in an insured account that earns a lower rate of interest, 3.25% in our case. We'll call this account a "safe" account. (It is also called a "liquid" account, because it's easy to withdraw money from it.)
The second assumption is that whenever there's a positive cash flow, that income is used to pay any future negative cash flows, and earns the "safe" account rate of 3.25% until paid out. Any money left over is put into another risky investment that earns the same rate of interest as the proposed investment, 15% in our case. This is called the "risk rate" account.
In the FMRR scenario, you're dealing with two different rates. You earn a lower, "safe account" rate on the money that is to be paid into the investment, until it is actually paid. And you earn a higher "risk account" rate on money that the investment pays out, until the end of the investment period.
FMRR Example
To solve this cash flow problem by hand you'd start with the last cash flow in the series and work backwards to the first, using the TVM application to perform the computations.
In the rental property example, the last cash flow is positive. So you'd leave it as is and move to the second to last flow. Since the 30,000 cash flow is also positive, "appreciate" it by 15% for one period and add the result to the final 170,000 flow. (You can add 15% to 30,000 or in the more general case use TVM to do this. Set N = 1, I%YR = 15, PV = 30,000, P/YR = 1, B/E = END, and solve for FV = -34,500.) Change the sign and add this to 170,000 to get 204,500.
Now go to the previous cash flow, -20,000. The flow is negative, according to our assumption this money is coming from the "safe" account earning 3.25% interest. So, we "discount" it back one period and combine it with the previous cash flow. Use the TVM application, this time put 3.25, the "safe" interest rate, in the I%YR (Annual interest) field of TVM and put -20,000 in the FV (Future value) field. Then set N (Number of periods) to 1, B/E = BEGIN, and solve for PV (Present value) = 19,370.46.
This negative cash flow, we then combine with the previous positive cash flow, (30,000-19,370.46 = 10,629.54). Now, since this result is a positive number, we "appreciate" it forward by three periods and add that result to the final cash flow. (Again, you can use TVM to do this. Set N = 3, I%YR = 15, PV = 10,629.54, P/YR = 1, B/E = END, and solve for FV = -16,166.83.) Change the sign and add this to 204,500 to get 220,666.83. The first few cash flows are all negative, so you'd have to "discount" them by 3.25% and combine the discounted value with the previous cash flow, repeating the process until you have your initial investment value. (Again, use the TVM: Set N = 1, I%YR = 3.25, FV = -40,000, P/YR = 1, B/E = BEGIN, and solve for PV = 38,740.92. Change the sign and add this to the previous -40,000 to get -78,740.92. Set FV = -78,740.92 and solve for PV = 76,262.39. Change the sign and add to -30,000 to get the initial investment of -106,262.39.)
Eventually, what you wind up with are just two cash flows that are equivalent to the combined effects of all the separate cash flows. You'd have one negative initial investment (sometimes called the Minimum Initial Investment), -106,262.39, and one final, positive cash flow (sometimes called Future Wealth), 220,666.20.
The question then becomes, what is the compound interest rate that would give you the Future Wealth in return for your Minimum Initial Investment? When you find the answer to that, you've found the Financial Management Rate of Return (FMRR). To finish the calculation: on the 100LX go back to Cash Flow and press (MENU), Clear, Data. Set Init Flow = -106,262.39, Flow# 1) = 0, for 5 periods, Flow# 2) = 220,666.20, I%YR = 15, P/YR = 1, and solve for IRR%YR = 12.95. That is the Financial Management Rate of Return. It's a more accurate figure than the IRR because it takes into account earnings and losses rather than just earnings.
Notice that the FMRR% (12.95%) is less than the 15% promised. So this deal will not perform as well as the property owner predicted. However, if the best alternative investment you can find yields only 10%, then the proposed investment might still be worth a second look, even though it only returns 12.95%
The FMRR equation
If all this leaves you scratching your head, scratch no more: the FMRR Solver equation in the sidebar on page 52 does all these computations for you automatically. Here's how to use the equation. (The following instructions assumes you have loaded FMRR-123.EQN <Available ON DISK> or have keyed into HP Calc Solver the equation shown in the second column of the sidebar on page 52.)
First, on the HP 100/200LX, key the financial data into a blank 1-2-3 worksheet. Start 1-2-3 and if necessary press (MENU) W Erase Yes to clear the worksheet. Then enter the cash flows in column A with the number of times the flow appears in the adjacent cell in column B. The worksheet looks like this.
A B
1. -30000 1
2. -40000 2
3. 30000 1
4. -20000 1
5. 30000 1
6. 170000 1
Use the command: (MENU) Range Name Create CFLOW (ENTER) use the arrow keys to set the range as A1..B6 and press (ENTER).
Next, switch HP CALC. Go to the Solve, (MENU) Applications Solver; (MENU) File Open and select FMRR-123.EQN. In the Solver Catalog screen and press (F9) (Calc) to start the FMRR equation. Set the Pmts\Year equal to 1. Set the SafeRate equal to 3.25 and the RiskRate equal to 15.00. Then solve for IRR%, FMRR%, Min.Init.Inv, and FutureWealth. The results should be IRR% = 15.45, FMRR% = 12.95, Min.Int.Inv = -106,262.39 and FutureWealth = 220,666,20. (If you use the Min.Init.Inv. value for PV and the Future Wealth value for FV in the TVM application, and set N = 6 and P/YR = 1, you'll get I%YR = 12.95.)
ON THE HP 95LX
To use this equation on the HP 95LX, you can use the _CFLOW worksheet to hold the cash flows. In this case, since you've already entered the cash flows above, just put the number 1 in cell C44 and copy it down the column to cell C50. This puts in the number of times the flow appears in column C so the same FMRR equation will run on both Palmtops.
Then use the command (MENU) Range Name Create CFLOW and use the arrow keys to set the range to B44..C50. Leave the worksheet and start Solver. After you've loaded the FMRR equation, press (ENTER) to start it running. Set the SafeRate equal to 3.25 and the Risk Rate equal to 15 and solve for the other variables. The answers should be the same as those given above.
Conclusion
To sum up, you've seen how to use the cash flow programs in the HP Palmtops to deal with a couple of financial scenarios. You've also seen an example of how you might "re-use" code written for the HP 19B-II financial calculator to increase the power of all the HP Palmtop computers. We hope that you've found this worth the investment of your time and effort.