Lease Calculator

LEASE CALCULATOR Inputs Client Name Agency Phone Division/Branch/Section: Fax Description of Equipment: E-mail Lease Terms Input Data Summary Information Purchase Price Implied Residual #DIV/0! #DIV/0! Total Number of Payments* Payment Periods per annum (Non-standard payments) Lease Rate per annum 0.00% #DIV/0! per period Advance (1) /Arrears (0) Benchmark Rate Lease payment per period $0.00 Other lease costs per period Lease Payments per annum $0.00 Lease Payment per period $0.00 Total Lease Payments $0.00 Total purchase cost (incl. CUC) #VALUE! Financier's Lease Margin Net Nominal benefit to leasing* #VALUE! (negative implies cost) Capital User Charge 8.00% *Maximum lease period is 60 (monthly for 5 years) *Ignores time value of money: purchasing requires upfront payment whilst leasing amortises value of asset over its life Estimated Residual* *see attached worksheet Input Data Summary Information % of Purch price $ at end of lease term Either: Minimum amount Agency would Maximum possible residual accept with certainty Minimum Possible residual Disposal Risk High if downside is >= 100% Or: Medium if downside is >= 50% and < 100% Expected Value of Residual Low if downside is < 50% Range of Residual (Downside) #DIV/0! Indicative Range of Outcomes* *see graph Certainty Equivalent Amount Amount Probability Either: Highest 10% Recommended maximum payment 20% to remove downside risk Expected Value 40% Or: 20% Maximum payment to remove Lowest (downside) 10% downside risk (input) 100% $ASQLease Calculator.xls 1 1/30/2008Financial Performance Sumary Operating versus Finance Lease: Residual Lease Rate Margin Lease as quoted: #DIV/0! 0.00% 0.00% Present value test #DIV/0! (Present value of lease payments must fall below 90% to qualify as an Operating Lease) Minimum Required for Operating Lease: #NUM! #NUM! #NUM! Effective Life Test (Life) Maximum permissible for Operating Lease: #DIV/0! 4.00% 4.00% #DIV/0! (Lease cannot cover more than 75% of Effective Life of asset) Result: #DIV/0! #DIV/0! Residual Comparison % $ Estimated (average) residual to agency Maximum Estimated Residual Lease Residual (Implied) #DIV/0! #DIV/0! Minimum Estimated Residual Residual Range Result: #DIV/0! #DIV/0! Comparison of Interest Rates Agency Discount Rate #DIV/0! Capital User Charge 8.00% Government Discount Rate #DIV/0! Asset risk premium #DIV/0! Financier Lease Rate 0.00% Implied Agency Discount Rate #DIV/0! (Lease rate is competitive if lower than Agency/Government Rate) Benchmark Rate 0.00% Financier's Lease premium 0.00% (Implied Risk premium applied to residual only) #DIV/0! Financier's Lease Rate 0.00% Result: #DIV/0! #DIV/0! Residual Risk Assessment Effective Unrisked Residual #DIV/0! #DIV/0! Certainty Equivalent Amount #VALUE! Benefit #DIV/0! Result: #DIV/0! #DIV/0! Financial Assessment Unrisked Cost of lease payments (PV) #DIV/0! The PV benefit (cost) of leasing over purchasing to the Agency is: #DIV/0! Less payment to remove risk (PV) #DIV/0! Total Cost of Lease (PV) #DIV/0! made up of #DIV/0! from difference between Agency's risk-adjusted discount rate and Financier's Lease Rate; Net Cost of Purchase (PV) #DIV/0! and (PV) #DIV/0! Benefit of leasing over purchase (PV) #DIV/0! from differences in residual value estimates Internal Rate of Return (effective cost of leasing pa) #NUM! #NUM! Agency Discount Rate (Max.acceptable cost pa) #DIV/0! #NUM! Government Discount Rate #DIV/0! Result: #NUM! #NUM! #NUM! $ASQLease Calculator.xls 2 1/30/2008Theoretical Residual Values on the Sale of Equipment -Worksheet Purchase Price Sales Value ie Residual Estimate Probability Comments Worksheet 1 $2,000.00 2% 0.025 This worksheet allows the user to input both the estimated 4% 0.050 residual and the probability of each outcome ocurring 6% 0.100 This can be used to explore more fully the range 8% 0.200 of outcomes and probable downside 10% 0.250 The results give both the "expected value" or average 12% 0.200 and the downside range which encompassing 90% of outcomes 14% 0.100 Hence 90% of outcomes lie above the downside range 16% 0.050 Downside range is given as 1.28 times the standard deviation 18% 0.025 These values can then be used in the lease calculator 1.000 10.04% Expected Residual (%) Maximum (%) 14.46% 4.42% Downside/Range (%) Minimum (%) 5.62% $200.80 Expected Residual ($) Maximum ($) $289.23 $88.43 Downside/Range ($) Minimum ($) $112.37 Worksheet 2 $2,000.00 25% 0.000 This worksheet allows the user 20% 0.100 to estimate a range of outcomes 15% 0.200 but use a predifined range of probabilities 10% 0.400 The results give both the "expected value" 5% 0.200 (or average) and the range, to be inputed 0% 0.100 into the Lease Calculator -5% 0.000 1.000 10.00% Expected Residual (%) Maximum (%) 17.01% 7.01% Downside/Range (%) Minimum (%) 2.99% $200.00 Expected Residual ($) Maximum ($) $340.22 $140.22 Downside/Range ($) Minimum ($) $59.78 Example 1 $2,000.00 30% 0.000 20% 0.000 A simple example 15% 0.000 There are two possible outcomes, 10% 0.600 a residual of 10% with 60% chance and a residual of 5% with 40% chance 5% 0.400 Therefore the expected value is 8% 3% 0.000 These are treated as two possible outcomes in a sample 0% 0.000 Imposing a normal distribution, 90% of outcomes occur above $97.29 -5% 0.000 We are prepared to pay half the downside of $62.71 to eliminate risk -10% 0.000 Thus payment to remove risk is $31.35 1.000 8.00% Expected Residual (%) Maximum (%) 11.14% 3.14% Downside/Range (%) Minimum (%) 4.86% $160.00 Expected Residual ($) Maximum ($) $222.71 $62.71 Downside/Range ($) Minimum ($) $97.29 Example 2 $2,000.00 30% 0.000 20% 0.000 Another simple example 15% 0.100 Using 10% Sales value as most likely estimate, then there is 10% 10% 0.500 upside and a 40% down side probability 5% 0.300 Expected residual value is 7.78% 3% 0.030 Therefore the downside risk is $120.72 0% 0.030 Imposing a normal distribution, 90% of outcomes occur above $34.78 -5% 0.020 We are prepared to pay half the downside of $120.72 to eliminate risk -10% 0.020 Thus payment to remove risk is $60.36 1.000 7.78% Expected Residual (%) Maximum (%) 13.81% 6.04% Downside/Range (%) Minimum (%) 1.74% $155.50 Expected Residual ($) Maximum ($) $276.22 $120.72 Downside/Range ($) Minimum ($) $34.78 Example 3 $2,000.00 20% 0.025 15% 0.050 10% 0.100 Using 5% Sales value as most likely estimate, then 5% 0.500 expected residual value is 4.13% 3% 0.150 The downside risk is 4.13% -(-10%) 0% 0.100 Imposing a normal distribution, 90% of outcomes occur above -$51.25 -5% 0.050 We are prepared to pay half the downside of $133.75 to eliminate risk -10% 0.025 Thus payment to remove risk is $66.88 1.000 4.13% Expected Residual (%) Maximum (%) 10.81% 6.69% Downside/Range (%) Minimum (%) -2.56% $82.50 Expected Residual ($) Maximum ($) $216.25Theoretical Residual Values on the Sale of Equipment -Worksheet Purchase Price Sales Value ie Residual Estimate Probability Comments $133.75 Downside/Range ($) Minimum ($) -$51.25Theoretical Residual Values on the Sale of Equipment -Worksheet Purchase Price Sales Value ie Residual Estimate Probability Comments Example 4 $2,000.00 20% 0.020 15% 0.050 Using 5% Sales value as most likely estimate, then there is 13% 10% 0.060 upside and a 16% down side probability 5% 0.710 Expected residual value is 4.70% 3% 0.060 Therefore the downside risk is $115.27 0% 0.050 Imposing a normal distribution, 90% of outcomes occur above -$21.27 -5% 0.030 We are prepared to pay half the downside of $115.27 to eliminate risk -10% 0.020 Thus payment to remove risk is $57.64 1.000 4.70% Expected Residual (%) Maximum (%) 10.46% 5.76% Downside/Range (%) Minimum (%) -1.06% $94.00 Expected Residual ($) Maximum ($) $209.27 $115.27 Downside/Range ($) Minimum ($) -$21.27 Example 5 $2,000.00 20% 0.050 15% 0.150 Using 5% Sales value as most likely estimate, then there is 43% 10% 0.230 upside and a 27% down side probability 5% 0.300 Expected residual value is 6.1% 3% 0.170 Therefore the downside risk is $158.42 0% 0.050 Imposing a normal distribution, 90% of outcomes occur above -$35.92 -5% 0.030 We are prepared to pay half the downside of $158.42 to eliminate risk -10% 0.020 Thus payment to remove risk is $79.21 1.000 6.13% Expected Residual (%) Maximum (%) 14.05% 7.92% Downside/Range (%) Minimum (%) -1.80% $122.50 Expected Residual ($) Maximum ($) $280.92 $158.42 Downside/Range ($) Minimum ($) -$35.92 Example 6 $2,000.00 30% 0.030 20% 0.120 Using 10% Sales value as most likely estimate, then there is 33% 15% 0.180 upside and a 42% down side probability 10% 0.250 Expected residual value is 6.0% 5% 0.180 Expected value of down side is 16% 3% 0.120 Therefore the downside risk is $217.17 0% 0.070 Imposing a normal distribution, 90% of outcomes occur above -$96.17 -5% 0.030 We are prepared to pay half the downside of $217.17 to eliminate risk -10% 0.020 Thus payment to remove risk is $108.59 1.000 6.05% Expected Residual (%) Maximum (%) 16.91% 10.86% Downside/Range (%) Minimum (%) -4.81% $121.00 Expected Residual ($) Maximum ($) $338.17 $217.17 Downside/Range ($) Minimum ($) -$96.17Low Risk Medium Risk High Risk Mean Std Dev(m) 30 50 120 10 3 0 #VALUE! #VALUE! #VALUE! 9.5 2.85 #VALUE! #VALUE! #VALUE! #VALUE! 9 2.7 #VALUE! #VALUE! #VALUE! #VALUE! 8.5 2.55 #VALUE! #VALUE! #VALUE! #VALUE! 8 2.4 #VALUE! #VALUE! #VALUE! #VALUE! 7.5 2.25 #VALUE! #VALUE! #VALUE! #VALUE! 7 2.1 #VALUE! #VALUE! #VALUE! #VALUE! 6.5 1.95 #VALUE! #VALUE! #VALUE! #VALUE! 6 1.8 #VALUE! #VALUE! #VALUE! #VALUE! 5.5 1.65 #VALUE! #VALUE! #VALUE! #VALUE! 5 1.5 #VALUE! #VALUE! #VALUE! #VALUE! 4.5 1.35 #VALUE! #VALUE! #VALUE! #VALUE! 4 1.2 #VALUE! #VALUE! #VALUE! #VALUE! 3.5 1.05 #VALUE! #VALUE! #VALUE! #VALUE! 3 0.9 #VALUE! #VALUE! #VALUE! #VALUE! 2.5 0.75 #VALUE! #VALUE! #VALUE! #VALUE! 2 0.6 #VALUE! #VALUE! #VALUE! #VALUE! 1.5 0.45 #VALUE! #VALUE! #VALUE! #VALUE! 1 0.3 #VALUE! #VALUE! #VALUE! #VALUE! 0.5 0.15 #VALUE! #VALUE! #VALUE! #VALUE! 0 0 #VALUE! #VALUE! #VALUE! 0.5 0.15 #VALUE! #VALUE! #VALUE! #VALUE! 1 0.3 #VALUE! #VALUE! #VALUE! #VALUE! 1.5 0.45 #VALUE! #VALUE! #VALUE! #VALUE! 2 0.6 #VALUE! #VALUE! #VALUE! #VALUE! 2.5 0.75 #VALUE! #VALUE! #VALUE! #VALUE! 3 0.9 #VALUE! #VALUE! #VALUE! #VALUE! 3.5 1.05 #VALUE! #VALUE! #VALUE! #VALUE! 4 1.2 #VALUE! #VALUE! #VALUE! #VALUE! 4.5 1.35 #VALUE! #VALUE! #VALUE! #VALUE! 5 1.5 #VALUE! #VALUE! #VALUE! #VALUE! 5.5 1.65 #VALUE! #VALUE! #VALUE! #VALUE! 6 1.8 #VALUE! #VALUE! #VALUE! #VALUE! 6.5 1.95 #VALUE! #VALUE! #VALUE! #VALUE! 7 2.1 #VALUE! #VALUE! #VALUE! #VALUE! 7.5 2.25 #VALUE! #VALUE! #VALUE! #VALUE! 8 2.4 #VALUE! #VALUE! #VALUE! #VALUE! 8.5 2.55 #VALUE! #VALUE! #VALUE! #VALUE! 9 2.7 #VALUE! #VALUE! #VALUE! #VALUE! 9.5 2.85 #VALUE! #VALUE! #VALUE! #VALUE! 10 3 #VALUE! #VALUE! #VALUE! #VALUE! Distribution of Possible Residual Values 0.0% 10.0% 20.0% 30.0% 40.0% 50.0% 60.0% 70.0% 80.0% 90.0% 100.0% 0 #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Probability (%) The higher the risk of attaining the expected residual, the greater the dispersion of possible outcomes about the centre and the flatter the curve These curves demonstrate that 90% of outcomes will occur above the minimum residual specified and only 10% of outcomes will be worse.Possible Residual Values #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Residual Value Low Risk Medium Risk High Risk$ASQLease Calculator.xls 1/30/2008 SENSITIVITY OF SAVINGS FROM LEASING TO GOVT DISCOUNT RATE ------------0000111111 5% 6% 7% 8% 9% 10% 11% 12% 13% 14% 15% Government Discount Rate Savings from Leasing (PV$ per $000 ) Higher Government Discount Rates are associated with a greater level of risk in achieving the expected residual value. As the risk /discount rate rises, an agency will pay more to remove risk and leasing becomes increasingly attractive.