Results 1 to 10 of 10
Thread: Cash Flow Function? (2000 SR1)

20031202, 20:20 #1
 Join Date
 Jun 2002
 Location
 Duluth, Minnesota, USA
 Posts
 215
 Thanks
 0
 Thanked 0 Times in 0 Posts
Cash Flow Function? (2000 SR1)
Hi again. My question today is about financial functions. I am comparing two possible investments. Both have initial cash outlays followed by possible savings over "n" periods. I am looking at the financial functions and it looks like the "PV" function comes closest to what I want. If I fill in the fields using a positive value for the "payment" field to show a savings for the period  the function returns a negative value. If I enter a negative payment, the function returns a positive value. I wound up doing the following:
(PV function)  initial purchase price = present value of the investment. Not sure this is correct. Any financial guru's out there? Am leaving the office now, but will pick up tomorrow morning. Thanks.
Douglas

20031202, 21:43 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Cash Flow Function? (2000 SR1)
Douglas, what exactly do want to measure?
If you look at Excel Help for the =PV() function you'll see that payments are indeed expected to be negative numbers because they are expected to be future outflows. =PV() is attempting to calculate the present value of a series of future outbound payments. Your description of an investment that has an intial outlay and "possible savings" needs clarification; Excel wants to know when cash comes in and when it goes out, and in each case how much. (Much like me, and much like your banker. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
Could =FV() be closer to what you are looking for?John ... I float in liquid gardens
UTC 7ąDS

20031203, 01:36 #3
 Join Date
 Oct 2002
 Posts
 1,993
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cash Flow Function? (2000 SR1)
There are some different methods when comparing investments. It seems you are after DCF, Discounted Cash Flow, with Present Value method, and Excel functions can handle that for simple CF. If you are going to compare two possible investments and analyze their future cash flow with some sort of discount rate, and not are going to set up a cash flow and discount separate flows etc, use NPV to chose the one with the highest NPV.
Compared to PV, you can add individual values for each period, PV is simple; same payment every period. You will get a negative result from PV if payment is entered as a positive number, negative number is for instance for a saving.
Normally you have an investment I, and some payments in/out (or a net flow of in and out for that period) and you also have to balance what value to discount beyond CF period.
Note: values entered to NPV is End of Period, and that's fine, but if your first payment, outflow (investment), is at time 0, i.e. in the beginning of first period (today) you have to add the investment to the result.
Ex.
Inv A
Initial payout today 1,000
Discount rate 8 %
Flow: 400, 500, 350, 550
NPV(0.08,400,500,350,550) => 1,481.15 that is present value of the flow. Now add the investment from lets say cell A1 (1,000). NPV(0.08,400,500,350,550)+A1 => 481.15. OK, at least a positive value.
Inv B
Initial payout today 1,500
Discount rate 8 %
Flow: 650, 700, 500, 725
NPV(0.08,650,700,500,725) => 2,131.80.
Add Investment NPV(0.08,650,700,500,725)+B1 => 631.80.
So compared, even though a greater amount to invest, the discounted flow of investment B gives a greater NPV.
If your initial payout is in the END of the first period you can add it into NPV function as with the following cash flow. NPV(0.08,1000,400,500,350,550)
As to FV (I have to always remind me with Excel's use of the terms. To me FV is Future Worth, FV=p*(1+i)^n. Very simple. But Excel's function FV will give you Future Worth of One, same as my function, or Future Worth of One per Period. Either you calculate the accumulated interest and add it to start value, or you also add a payment (saving) each period.). You can not put different individual payments in FV.
OK, this was a short explanation of NPV, I put my CF's together with the formulas and do not use Excel functions. I maybe have to come back to add something, since it's really late here <img src=/S/snore.gif border=0 alt=snore width=32 height=15>
Regards,

20031203, 02:11 #4
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cash Flow Function? (2000 SR1)
All of the financial functions solve a part of the following equation:
0 = Initial investment+Present Value(Payments)+Terminal Value
The present value requires the time and discount rate.
How you set things up depends on your point of view. If you are a company issuing a bond. Initially you would have a positive cash flow. Then you would have to pay cash out over time (negative numbers). If you are an investors, you pay for the bond, then receive cash over time.
The PMT function finds the payment given the time, discount rate, initial investment, and terminal cash flow (many times equal to zero).
The RATE function finds the discount rate given the time, payments, initial investment, and terminal cash flow.
The NPV function finds the present value given a schedule of payments, initial investment, terminal cash flow. The NPV function allows the payments to vary over time.

20031203, 12:03 #5
 Join Date
 Jun 2002
 Location
 Duluth, Minnesota, USA
 Posts
 215
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cash Flow Function? (2000 SR1)
Good morning all. OK, let me explain what I am trying to do and then ask for advise.
The spreadsheet I have compares the function of two methods of completing a task, Method A and Method B. Each has an initial cost and some operating expenses. Method A costs less initially, but has higher operating expenses. I want to compare the two methods and determine which is the better deal. The spreadsheet has the initial expense in one cell and the operating expenses in various other cells. I am looking for the proper way to feed those values into a function that will show the better deal.
I looked at NPV, but did not know how to feed the values for each period into the NPV function. I should note that the spreadsheet is designed to be filled in by someone wishing to compare their current method (Method A) with a possible alternative (Method [img]/forums/images/smilies/cool.gif[/img]. Depending on what information they enter and which configuration of Method B they choose, the values fed to the NPV or PV function will be different (initial cost, interest, period, etc.).
Does that help clear things up or make it muddier?
Douglas

20031203, 15:06 #6
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cash Flow Function? (2000 SR1)
I'm attaching an example. You have all cash outflows. The initial investments and operating expenses are both negative, so you want to pick the least expensive or least negative solution on an NPV basis.
You could also model this with cash savings as the years 14 amounts. Still you would pick the highest NPV.

20031203, 15:17 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Cash Flow Function? (2000 SR1)
<P ID="edit" class=small>(Edited by JohnBF on 03Dec03 09:17. Cathy reminded me how to put the outlays in the NPV function! I guess I'm rusty on these.)</P>Use =PV() if the subsequent outflows are the same each year, for example if the outlays are a constant $2000 for 5 years, and the discount rate/opportunity interest cost is 8%:
=PV(.08,5,2000)
(... at it's simplest, see Help for information on "salvage value" and whether the payments are beginning or end of period.)
Use =NPV() if the payments vary, like this (example assumes that the large initial payment is at the end of year 1, which may not be correct):
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><tr><td align=center>1</td><td>Target Annual Rate:</td><td align=right>John ... I float in liquid gardens
UTC 7ąDS

20031203, 16:09 #8
 Join Date
 Jun 2002
 Location
 Duluth, Minnesota, USA
 Posts
 215
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cash Flow Function? (2000 SR1)
All,
I finally reverted to the classic "pain" function formula because I understood how to feed it with my variables.
Ex:
i = interest (annual)
n = number of periods (years in this case)
E = initial expense (purchase price of machine)
A = annual operating expenses
NPV = E + (A * (P/A i,n)) where (P/A i, n) = (1(1+i)^n)/i
The lowest value among those compared is the most desirable.
Simple, no?
Douglas

20031203, 16:19 #9
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Cash Flow Function? (2000 SR1)
OK, but:
1. What's 'P'? (As in "P/A".)
2. Does =E+PV(i,n,A) return the same result?John ... I float in liquid gardens
UTC 7ąDS

20031203, 17:30 #10
 Join Date
 Jun 2002
 Location
 Duluth, Minnesota, USA
 Posts
 215
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Cash Flow Function? (2000 SR1)
P = Present worth, value, or amount
A = Uniform amount per interest period
(P/A, i, n) is formally defined as "Uniform Series Present Worth" and informally know as the "Pain Factor" (this is all according to reference materials used in my Engineering Economics section of the PE exam). It returns the same value as Excel's NPV function without having to enter each periods cash flow.
Douglas