Page 1 of 2 12 LastLast
Results 1 to 10 of 20

NPV for Operating Lease

This is a discussion on NPV for Operating Lease within the Excel Questions forums, part of the Question Forums category; I've really never worked with NPV nor with calculations dealing with an Operating Lease. So I'm seeking out some advice ...

  1. #1
    Board Regular FinancialAnalystKid's Avatar
    Join Date
    Oct 2004
    Location
    Glendale, CA
    Posts
    761

    Default NPV for Operating Lease

    I've really never worked with NPV nor with calculations dealing with an Operating Lease.

    So I'm seeking out some advice and help.

    Does anyone have something on Excel that might be useful to me?

    Much appreciated!

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    "Does anyone have something on Excel that might be useful to me"

    A somewhat more specific question would probably be a start

  3. #3
    Board Regular FinancialAnalystKid's Avatar
    Join Date
    Oct 2004
    Location
    Glendale, CA
    Posts
    761

    Default Re: NPV for Operating Lease

    LOL
    Template maybe? Something someone may have used? I will be trying to put something together this weekend but would like to see what others have used. I saw some stuff online for like $129 but I'm not about to do that at the moment.


  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Adelaide, Australia
    Posts
    426

    Default Re: NPV for Operating Lease

    What sort of lease?

    Company Lease, Car Lease etc?

    If its a company lease make sure you use the cost of debt as the discount rate, not the WACC.

    Cheers
    Dave

  5. #5
    Board Regular FinancialAnalystKid's Avatar
    Join Date
    Oct 2004
    Location
    Glendale, CA
    Posts
    761

    Default Re: NPV for Operating Lease

    Company/Property lease.

  6. #6
    Board Regular FinancialAnalystKid's Avatar
    Join Date
    Oct 2004
    Location
    Glendale, CA
    Posts
    761

    Default Re: NPV for Operating Lease

    Here's some data.

    ******** ******************** ************************************************************************>
    Microsoft Excel - Operating Leases Log.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    *DescriptionLife*(mo)Term*(mo)Monthly*PmtTotal*PmtsValueNPV*
    2
    *Item*16036*****2,959.52**106,542.72**106,000.00***
    3
    *Item*26036*****1,598.00****57,528.00****51,764.97***
    4
    *Item*36036*****1,044.00****37,584.00****37,694.97***
    5
    *********
    Sheet2*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    I need to include some sort of user inputs, possibly for different monthly payments or interest, etc. How do I figure this out?

    I could use Excel's NPV formula but need to setup the table for monthly payements, etc.

  7. #7
    Board Regular FinancialAnalystKid's Avatar
    Join Date
    Oct 2004
    Location
    Glendale, CA
    Posts
    761

    Default Re: NPV for Operating Lease

    Any thoughts this fine Sunday Morning?

    Can I have a user enter number of periods and still use Excel's NPV function?

    NPV(I,Range)+period 0

    What is the actual formula for the Range.

    If a user enters 24 periods, how would I use that in the Range instead of say A2:A23 with A1 period 0?

    It can't be Range = (24-1)*monthly payments can it? How do you enter the formula NPV = C0 + C1/1+r + C2/(1+r)^2+...+Ct/1+r)^t

  8. #8
    Board Regular FinancialAnalystKid's Avatar
    Join Date
    Oct 2004
    Location
    Glendale, CA
    Posts
    761

    Default Re: NPV for Operating Lease

    Here's what I got...

    This is just a portion due to HTML makers limits.

    ******** ******************** ************************************************************************>
    Microsoft Excel - NPV Test.xls___Running: xl2002 XP : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    B
    C
    D
    E
    33
    240.102**$*****2,959.52**$*********300.47*
    34
    250.092**$*****2,959.52**$*********273.15*
    35
    260.084**$*****2,959.52**$*********248.32*
    36
    270.076**$*****2,959.52**$*********225.75*
    37
    280.069**$*****2,959.52**$*********205.22*
    38
    290.063**$*****2,959.52**$*********186.57*
    39
    300.057**$*****2,959.52**$*********169.61*
    40
    310.052**$*****2,959.52**$*********154.19*
    41
    320.047**$*****2,959.52**$*********140.17*
    42
    330.043**$*****2,959.52**$*********127.43*
    43
    340.039**$*****2,959.52**$*********115.84*
    44
    350.036**$*****2,959.52**$*********105.31*
    45
    360.032**$*****2,959.52**$***********95.74*
    46
    ****
    47
    *Estimate*$*******542.72**
    48
    **NPV*$****(77,362.18)
    49
    **NPV*Check*$****(77,362.18)
    50
    ****
    51
    *Discount*Rate**=10.00%*
    52
    ****
    53
    *Montly*Payment*$*****2,959.52**
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Period 0 = -106,000

  9. #9
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: NPV for Operating Lease

    Hello,

    There's no significance to the underlying agreement that is causing cash flows to occur, from a quantitative standpoint, this is semantics that detracts from the issue at hand.

    Get back to cash flows and use the NPV function as mentioned here:

    http://www.mrexcel.com/board2/viewtopic.php?t=118586

    Also, I might disagree with Dave a touch (with all due respect). Your cost of capital can imply opportunity cost. You could have that cash sitting in an equity fund making 13% per annum, but you can't, because you have elected to invest otherwise. Still, a real opportunity that is forgone...

    Resources are scarce while investment opportunties are ample, they should all be considered against the same measure. I.e., I would argue to use the WACC.

    Also, you don't want an NPV, which implies inflows and outflows (Net), you simply want a present value of future outflows (PV).

  10. #10
    Board Regular
    Join Date
    Feb 2003
    Location
    Adelaide, Australia
    Posts
    426

    Default Re: NPV for Operating Lease

    Nate, I think we are generally in agreement....

    A lease is a financing decision which is separate to the investing decision.

    WACC is used to make a decision as to whether to invest or not, ie on unleveraged cashflows. But once the decision is made to invest then there is a secondary decision as to how to finance the investment. This is where the issue of whether or not to enter into an operating lease comes in

    When evaluating a contract or lease, the cost of debt is the appropriate discount rate (taking into account risk, termination clauses etc) to determine whether entering into the contract is the correct decision

    For example, a google of "NPV lease WACC cost of debt" turns up this quote from www.uic.edu/classes/fin/fin512/FMT8/no3_500.pd]Fin link[/url]

    A lease payment is like the debt service on a secured bond issued by the lessee, and the discount rate should be approximately the same as the interest rate on such debt. (Recall: The discount rate should reflect the risk of the incremental cash flows.)

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com