This sheet describes issues associated with modelling leases and the risks assocated with residual value. It applies to leases of batteries, leases of hydrogen equipment and corporate PPA’s. It especially applies to buildings and energy efficiency programs. On this page I begin by describing how to compute the lease rates given capital expenditures, the target IRR and an estimate of the residual value. Once the lease rate is established through alternative structuring of the initial lease, risk analysis of the lease can be established. The risk analysis can include variation during the first term and the risk of residual value variation. Third, with the risk analysis established, taxes and actual data is introduced. Finally, the leases are put together into a portfolio. The file assocated with the analysis, macros and the videos below is attached to the button below.
Analogy to Corporate PPA’s
A corporate PPA typically has a tenor that is less than the life of the plant (e.g. a solar project) . The IRR (and value) depends a lot on what happens after the PPA term (i.e., merchant prices). Another way to look at the value is to assess the value of a solar project at the end of the PPA term. If you have a value at the end of the tenor that is a lot higher than the value of building a new project, something is wrong.
- There can be a lot of different structures for a corporate PPA:
- Price at the hub (resource risk and basis price risk)
- Fixed revenue contract (no resource risk)
- Standard PPA type contract with fixed price per kWh úStandard PPA type contract with escalating prices
No matter what type of contract is initially used, the value after the contract depends on the physical operation of the plant and the market condition
- Formula for achieving Target IRR:
- PV of Lease Payments in Initial Term + PV of Residual Value = Capital Expenditures
- Use the Target IRR as the Discount Rate
- Do not Need Goal Seek
- Value at Renewal Depends on degradation over first lease and expected degradation Obsolescence úInflation úCompetitive Conditions at end of lease term
- Level Payment for Initial Lease (No Taxes): Need Capital Expenditures, Target IRR, Lease Term, Value at Renewal úTarget IRR can be adjusted later for degradation, inflation etc.
- PMT(Target IRR, Lease Term, Capital Expenditures – Value at Renewal)
Step 1: Get the Level Lease Payment with PMT úFlat Nominal Lease = PMT(Target IRR, Life, Capital Expenditure)
Step 2: Find the Remaining Life after Initial Term úRemaining Life = Total Life – Lease Term
Step 3: Compute the Value at the Terminal Date with PV úSimple Residual = PV(Target IRR, Remaining Term, Flat Nominal Lease)
Step 4: Make a series of adjustments to Simple Residual for Obsolescence, Degradation and Inflation úAdjusted Residual = Simple Residual x Factors
Step 5: Compute Initial Lease Rate from Adjusted Residual using PMT úInitial Lease = PMT(Target IRR, Lease Term, Capital Expenditure – Adjusted Residual)
Making the SUM(Start:End!k10) Function Flexible
When you consolidate the different projects you can use the SUM function that grabs data from different sheets. But the
Sub sum_fix() Application.Calculation = xlCalculationManual Dim new_formula As String start_cell = Selection.Address rows_to_adjust = Range("sum_formulas").Rows.Count Sheets("Consolidated").Select For Row = 1 To rows_to_adjust
current_formula = Selection.Formula
If Left(current_formula, 2) <> "=S" Then GoTo end_of_loop: new_cell_reference = Range("sum_cells").Cells(Row, 1) new_formula = "=Sum(start:end!" & new_cell_reference & ")"
Range("sum_formulas").Cells(Row, 1) = new_formulaend_of_loop: Next Row
Range("sum_formulas").Select Range(Selection, Selection.End(xlToRight)).Select Selection.FillRightRange(start_cell).Activate End Sub