Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cash Flow Function? (2000 SR-1)

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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

  3. #3
    Silver Lounger
    Join Date
    Oct 2002
    Posts
    1,993
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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,

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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 1-4 amounts. Still you would pick the highest NPV.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cash Flow Function? (2000 SR-1)

    <P ID="edit" class=small>(Edited by JohnBF on 03-Dec-03 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

  8. #8
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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

  10. #10
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Duluth, Minnesota, USA
    Posts
    215
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cash Flow Function? (2000 SR-1)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •