This Financial Model User Guide supplements the underlying financial model and video courses by Renewables Valuation Institute.

Students and users of the financial model can gain maximum value from using the financial model by additionally reading and applying concepts explained in this document and taking our modeling video courses.

This User Guide explains the purpose of every sheet of the model and their underlying sections in depth. Additionally, at the end of the document, there are plenty of resources regarding Excel and renewables essentials that should be helpful to anyone aiming to master financial modeling at the highest industry standards.

Building an advanced financial model for renewable energy investments can be a challenging but rewarding experience that will enhance your financial modeling skills and set you apart from your peers. By reading and applying the concepts of this User Guide and the underlying financial model, you will gain a valuable skillset that is highly sought after in the finance industry.

With the increasing demand for renewable energy projects, mastering financial modeling for these investments is crucial for professionals looking to stay competitive and positively impact the world. Building an advanced financial model is not only intellectually stimulating but also offers a unique opportunity to contribute to sustainable development while advancing your career.

Whether you're a seasoned finance professional or just starting your career, mastering financial modeling will give you a competitive edge and set you up for success in the rapidly growing renewable energy industry.

Renewables Valuation Institute’s mission is to enhance and disrupt the current financial modeling standards within this challenging and exciting industry.

We wish you lots of modeling success and hope you enjoy the content!

2        Overview of the General Model Structure

Before getting into the specifics of each sheet and its purpose within the financial model, it is essential to understand the general model structure from a bird’s eye perspective.

First, let’s have a look at the worksheets tab of the model:

Generally, the model is separated into three different general blocks.

1. Inputs: The model has three input sheets, of which two consider time-independent (Inputs_TI) and time-dependent (Inputs_TD) inputs, which are displayed in a light blue color in the worksheet's tab. The blue color indicates that the end-user can adjust these sheets to consider different project-specific inputs. The Tech inputs sheet does not have blue coloring as it only considers technical inputs, which typically do not change.
2. Processing: The five sheets, Timing, Calculations, Charts, Macro, and Checks, are processing-related worksheets. Depending on the project-specific inputs, the processing sheets process all inputs given and run diverse calculations resulting in various investment-specific outputs.
3. Output: The model has two output sheets, of which the Valuation sheet is of quantitative nature, while the Dashboard visualizes all key investment metrics from the Valuation sheet using different graphs and tables reflecting the most crucial investment financials.

The below figure should help visualize the general structure of the model. Understanding which of the three general categories (Inputs, Processing, Output) each sheet belongs to is essential when using the financial model.

2.1       Cover Page

After opening the financial model, the Cover Page will appear.

The Cover Page includes a detailed description of the financial model specification and a legal note. Please read this legal note carefully before using this financial model and making any investment-related project decisions based on this model and its User Guide.

This financial model and its user guide are for educational purposes only.

All content is Copyright material of Renewables Valuation Institute.

No representation or warranty of any kind, expressed or implied, is or will be made in relation to this Model's accuracy, reasonableness, attainability, or completeness. Furthermore, no responsibility or liability of any kind is or will be accepted by Renewables Valuation Institute, which expressly disclaims any and all liabilities which may be based on, or may derive from any assumptions, information, errors, omissions or misstatements contained in this Model.

The Model has not been audited or verified by an independent party.

2.2       Dashboard

The investment dashboard is the output heart of the model. It gives decision-makers an instant overview of the essential investment metrics any decision-maker should consider before investing in or developing a renewable energy asset.

Below is a preview of what the dashboard looks like. However, this User Guide will explain the Dashboard in much more depth in a later section.

Investment Dashboard

When opening the financial model, the dashboard will be displayed in a more considerable outlay making it easier to read and digest the key performance indicators than a condensed screenshot as the one above.

3        How to use the Model

This section of the Financial Model User Guide is dedicated to an in-depth walk-through of every single sheet of the model and all underlying sections within such sheets. RVI recommends using this User Guide as a substitute when building project-specific financial models to ensure a deep understanding of the modeling concepts and the intended use behind the pre-determined model structures.

3.1       Inputs_TI – Inputs Time Independent

The Inputs Time Independent Sheet is the first sheet that should be considered when building a project-specific financial model. 

From a bird’s eye perspective, this sheet is the first sheet of the first model structure block Inputs.

3.1.1       Header

The header of the Inputs_TI sheet displays the integrity & signals checks of the model. 

In addition, in cell M3, the Live Case of the model is displayed and can be adjusted via a drop-down menu. A light blue coloring of the cell indicated that the cell contains an input that may be adjusted. 

Using the Name Manager (For more info, read chapter 3.11.1, "How to use the Name Manager in Excel?"), the live case has been named Scenario_live_case. Naming the live case of the model is essential for the macro functionality. 

If you would like to change the model's Live Case, do it via the drop-down menu in cell M3. Currently, Case 2, “10 year PPA” is selected – but if you would like to run the scenario on a fully merchant basis, the live case scenario can be adjusted to Case 3, “Fully merchant” using the drop-down menu. 

However, please read the next section to ensure you correctly use the implemented macros when selecting and valuing different cases.

3.1.2       Scenario selection & Valuation dashboard

Before getting into the details of how to use the following two sections in the Inputs_TI sheet, we recommend reading “How to value a wind farm or solar plant?” if you do not yet understand how investors view the value of a renewable energy asset.

The scenario selection & valuation dashboard are powerful sections that can help to understand how a potential investor might view the asset’s value and how a project developer can decide whether this project would be worth the development effort considering the project’s Net Present Value (NPV).

Let’s first see how to use the model from a developer’s perspective.

To turn on the developer’s perspective, select “Off” using the switch for the target IRR valuation via the drop-down menu in G13.

The model has two master macros integrated into this valuation dashboard section. Depending on the perspective (investor vs. developer), the macro, which needs to be executed (pressed on) after each input change of the model, will be highlighted in green. 

So if you want to change any input in the model, always press the highlighted green macro button afterward!

In cell G18, the valuation case can be selected via a drop-down menu. So if you would like to value the asset from either perspective, please select the relevant valuation case here. The drop-down menu in cell M3 can be manually adjusted and represents the current live case in the model. However, after executing any of the two master macro buttons, the macro will value the selected case in cell G18. After the macro has been successfully executed, the case selected in G18 will also be the live case in the model selected in M3. 

If you are confused about how the macros work, don’t worry! The sections further below give an in-depth explanation of the macro's functionality. Additionally, our video courses on building an advanced renewable energy financial model help to understand all these concepts practically.

Next, let’s see how to use the model from an investor’s perspective.

To turn on the investor’s perspective, select “On” using the switch for the target IRR valuation via the drop-down menu in G13.

After selecting the investor’s perspective, the Macro incl. IRR target needs to be executed!

Please note that this macro is doing a highly iterative calculation process which depending on your computing power, can take multiple minutes. 

This macro will set the NPV of the investment to zero given a selected IRR target which is a manual input in cell G14. Please note that the NPV @Target IRR in cell G14 still displays a value of 1. The entire model is set up in thousands of EUR, but the NPV is still at approximately 1,000 EUR. The macro is set up so that a few thousand EUR NPV difference from zero is acceptable. Considering the circumstances that valuing a renewable energy asset incorporates many highly unpredictable variables and future cash flows, this is an acceptable tolerance.

It is also noteworthy that from a developer’s perspective, the Enterprise Value was calculated as 44.360 mEUR plus an NPV of 18.362 mEUR, summing up to 62.722 mEUR. 

However, after turning on the investor’s perspective, the Enterprise Value is displayed as 74,811 mEUR. Don’t worry; this is not an error! This significant difference in Enterprise Values is due to different capital structures in both cases. The investor’s perspective uses significantly more senior debt with lower capital costs than equity. However, from the developer’s perspective, the future free cash flows to equity are discounted at the selected 6.00% target IRR to determine the NPV @target IRR. These effects cause such a discrepancy in both valuation approaches.

Consequently, from a developer's perspective, the NPV @target IRR can only be interpreted as a proxy for whether developing this asset is worth the effort. Only if the NPV is positive the asset should be realized; however, if a developer wants to understand how an investor might value the asset given a certain IRR return expectation, we recommend switching to the investor’s perspective and executing the respective master macro.

For more info, refer to section 3.12.1, “How to value a wind farm or solar plant?".

3.1.3       General inputs

This section of the Financial Model User Guide, for general inputs, considers the timing and working capital assumptions. 

Timing:

1. Model start/construction start: Choose any date, but always use the day at the end of the month! The model timeline will start on the first day of the following month after that. 
2. Construction time in quarters: Choose the number of quarters the construction period will take. As this is a quarterly model, ensure only to use integers, i.e., full quarters, for this input. Within the Inputs_TD sheet, you will have the opportunity to implement a detailed payment plan during the construction period on a monthly basis.
3. Operating lifetime in years: Choose any value of up to 40 years in quarter steps, i.e., 30 / 30.25 / 30.50 / 30.75 / 31 years will work as input as the model is set up on a quarterly basis.

Only adjust the cells highlighted in blue. You can overwrite the formula in row 43 with a hard input if you want to consider different lifetime assumptions for different cases. Make sure to adjust the cell style as a blue input so that all model users can clearly see which cells include hardcoded values.

Working Capital:

Typically, in a project finance deal, revenues and costs are not instantly paid as billed, causing a lag between the moment revenues and costs are considered in the income statement and then actually flow through the cash flow statement.

A common assumption is to use 30 days, i.e., one month lag between the issuance of bills and the moment of payment for both the revenues and costs of a transaction.

Please ensure that you do not use more than 90 days of working capital adjustment as input, as this would require a rather complex adjustment of the general model structure.

Live Case in column M:

You may notice that the inputs that flow into the following sheets of the model come from column M. Depending on the Live case selection in cell M3 an INDEX function pulls in the relevant case information from the corresponding case column. 

If you do not know how to apply the INDEX function yet, we recommend reading section 3.11.4, “INDEX Function – Financial Modeling."

3.1.4       Initial investment and construction cost

This section of the Financial Model User Guide considers the time-independent inputs for the initial investment and construction costs.

Construction & development cost

First, name the different categories of construction & development costs in D52 to D57.

O52 to O57 serve for the total construction & development cost inputs. In addition, for the detailed underlying payment profile during the construction & development period, there are monthly payment profile inputs in the Inputs_TD sheet.

Additionally, if contingencies (cost overruns) in percentage terms should be considered, cells O61 to O66 allow implementing a relative percentage cost overrun to the inputs in O52 to O57. Considering contingencies is especially helpful if the EPC is still under negotiation and it is unclear what the final pricing will be. For more information read section 3.12.2, “EPCM vs. EPC agreement – what’s the difference?”.

Important note: The entire row 51 & 60 should not be adjusted and has technical purpose only. If the investor’s perspective in the Valuation Dashboard is turned on, the switch in column H in row 51 for the Enterprise Value (for IRR solver) will be turned on, and all other switches will be off. This is because the master macro, from an investor’s perspective, adjusts the Enterprise Value until the target IRR is achieved.

Next, the model will give total flexibility to integrate three different depreciation categories for accounting and tax purposes. Again, depending on your jurisdiction, we recommend advising a tax advisor and accountant for these inputs.

In cells D69 to D71, three depreciation categories can be named. Here these have been named long-term, short-term, and financing. Right next to it in columns H and I, via dropdown, the user can select whether straight-line depreciation or reducing balance depreciation is applicable while differentiating between the depreciation treatment for accounting and tax purposes.

In O74 to O76, the number of years for straight-line depreciation is selected.

In O83 to O85, the acceleration multiple for the reducing balance is assumed. Note that this input will accelerate the depreciation selected for the straight-line method by the given multiple. This accelerated depreciation percentage will then reduce the rest book value of the asset from the previous period. Therefore, the reducing balance depreciation will have higher absolute depreciation in the early years and lower in the later years compared to the straight line method.

Lastly, in O92 to O98, the depreciation categories for the respective construction & development cost categorizations are selected via dropdown.

3.1.5       Power generation & revenue

This section of the Financial Model User Guide considers assumptions for power generation and underlying revenue.

Generation assumptions

In cell O103, the installed capacity for the power plant can be implemented. For further information on the difference between MW vs. MWh, read section 3.12.3, “What’s the difference between kW and kWh?.” 

Next, in row 103, select the P-value for energy production. Given that the first case is the debt sizing case, the P90 value has been selected. For all other equity-related cases, the P50 value is assumed. For further information on the difference between P50, P75, and P90, read section 3.12.4, “How to model P50, P75, and P90 energy yield?.”

Monthly production profile

In a project finance deal, renewable energy revenues' seasonality significantly impacts monthly cashflows. Consequently, the seasonality of energy production needs to be considered in O106 to O117. 

Typically, the technical advisor of a transaction will provide the required seasonal input for the financial model. A check in G106 will ensure that the overall monthly production figures always sum up to 100%.

For further information on seasonality, read section 3.12.5, “How to model seasonality of renewable energy revenues?.”

Availability

The availability of a renewable energy plant refers to the percentage of time the plant can generate power based on its design capacity. It takes into account downtime due to maintenance, repairs, and other factors and is a measure of the reliability of the plant. Typically, renewable energy plants have higher availability than traditional power plants. This is because they do not rely on finite resources such as fossil fuels and can often be maintained with less downtime.

The availability of renewable energy assets can change over their lifetime due to several factors. The model offers flexibility to implement five different periodic availability assumptions to distinguish between different availability periods. G121 to G125 consider the duration of the different availability periods. In O121 to O125, the user needs to implement the assumed availability within the respective period.

Important note: Some technical advisors provide the P50, P75, and P90 energy production values after availability subtractions. Therefore, the first period may need to be considered 100% availability as subtraction is already included within the energy yield assessment and, therefore in the used P50 value. This initial subtraction also needs to be considered as a base for all subsequent inputs in this model section.

This section of the Financial Model User Guide considers assumptions for degradation and net power generation.

Degradation reduces the electricity output of solar panels over time. These losses must be considered when building a financial model for a solar plant.

Degradation

Nowadays, high-quality solar panels degrade at a rate of approximately 0.25% - 0.50% per year, which reduces the overall electricity output of a solar plant by 8.0% - 15.0% over a 30-year asset lifetime.

Similar to the availability inputs, the degradation inputs also offer flexibility in distinguishing between different degradation periods to implement up to five periodic degradation assumptions. G129 to G133 consider the duration of the different availability periods. In O129 to O133, the user needs to implement the assumed degradation per annum within the respective period.

For further information on degradation, read section 3.12.6, “What is degradation and how to model it?.” 

Net generation in MWh/MWp

This section is meant for the implementation of the energy production of the power plant. The P50 value for energy production is to be considered in row 136, while the two rows below give the user the flexibility to implement any other P-values in cells D137 and D138. Please ensure to only put a numerical value in cells such as the NORM.INV function in O137 and O138 can only work out the proper energy yield with a numerical value given in D137 and D138.

The energy yield in MWh/MWp on a P50 basis is to be implemented in cell O136. This energy yield measure is also referred to as full load hours. For more information regarding full load hours, please read section 3.12.7, “What are full load hours and what is a capacity factor?”.

Lastly, the uncertainty of energy production needs to be implemented so that the other P-values in rows 137 and 138 can be calculated using the NORM.INV function.

For more information regarding the NORM.INV function, please read section 3.11.5, “NORMINV Function – Financial Modeling”.

This section of the Financial Model User Guide considers assumptions for PPA, merchant, and other income revenues.

For more information regarding PPAs, please read section 3.12.8, “PPA Offtake Agreements”. 

In rows 143 to 145, three different PPAs can be turned on or off. For the first two cases, a ten-year PPA is considered, and after that, another five-year PPA. For case 3, all PPAs are turned off to understand the project metrics from a fully merchant standpoint, i.e., no cash flows are contracted through a bilaterally concluded PPA, and all electricity produced is sold on the spot market. 

In case your project has a state-subsidy (feed-in tariff “FiT”) in place, we recommend using the PPA structure – but only if the FiT has a similar structure to a pay-as-produced PPA structure. For more information regarding pay-as-produced vs. baseload PPAs, please read section 3.12.8, “PPA Offtake Agreements”. 

In G148, the start of the PPA after the COD (Commercial Operations Date) can be postponed. In the above case, the PPA agreement starts with COD. Since this is a quarterly model, please note that the inputs for years anywhere in the model should always be 0.25 / 0.50 /0.75 / 1.00. 

In cells H148 to H150, the duration of three consecutively starting PPAs can also be selected as years. If the aim is to have a merchant period between the first and second or second and third PPA cells I149 and I150 would need to be adjusted manually in accordance with the second starting date.

Cells O148 to O150 serve for the input of the pay-as-produced fixed price. In the above case, it is assumed that for the first 10-year PPA, a fixed price of 50 EUR/MWh is achievable and 60 EUR/MWh for another five years after that.

If the PPA is also indexed with inflation assumptions, this indexation can be selected via dropdowns in rows 153 to 155.

Given that many PPAs also have a merchant portion included in the offtake structure, O158 to O160 consider the hedged portion of the electricity for which the fixed price is paid. For instance, for the first PPA in the above case, 70% of the overall produced electricity is sold under the fixed price, while 30% will be sold at the merchant price assumptions, which will be included in the Inputs_TD sheet.

Merchant price forecast

In row 163, a merchant price forecast provider can be selected via dropdown. Note that banks often use a more conservative case than investors, which is why for case 1, the “third-party provider -10%” has been selected. 

There are a few electricity price forecasting parties in the market that are well-trusted by banks and investors. These forecasters typically provide a low and central merchant price forecast curve so that banks can consider a more conservative approach. At the same time, investors can view the investment from a realistically achievable power price view.

In row 164, the inflation assumption for the electricity price forecast is selected. Again, the model offers the flexibility to assume different indexation assumptions for different cases, which comes in handy if the debt provider (bank) wants to use a more conservative inflation assumption than the investor.

Other income

The remuneration of Guarantees of Origin (GoOs) per MWh depends on the market and the regulatory framework in which they are traded. For example, in some markets, GoOs are traded separately from the electricity itself, and electricity buyers can purchase GoOs to support renewable energy production. In this case, the price of a GoO is often set by market forces and can fluctuate depending on the availability of renewable energy and the demand for GoOs.

In other markets, GoOs are integrated with the electricity market, and electricity producers must provide a certain percentage of their electricity from renewable sources. In this case, the price of GoOs is often set by the government or regulatory body and is designed to provide an incentive for renewable energy production.

Row 167 provides full flexibility to consider all relevant inputs to reflect GoOs within the business case of the financial model. The duration of the GoOs can be set in G167. By default, the duration will be the asset lifetime. Still, if the investor believes that this incentive might not be as long living as the asset, this input can be adjusted accordingly. In J167, the inflation assumption can be selected. The GoOs have been inflated with the CPI assumption in the above case. In O167, the pricing of the GoOs needs to be implemented in EUR/MWh.

3.1.6       Operational expenditures (Opex)

This section of the Financial Model User Guide considers assumptions for the operational expenditures (Opex), more specifically, the fixed costs items of Opex.

First, name the cells in column D for the fixed cost items to be considered within the project-specific business case. Note that rows 172 to 181 serve for inputs in thousands of EUR per annum while rows 184 to 193 serve for inputs given in thousands of EUR per MW per annum. These are the two most commonly used units for fixed costs.

In column G, the duration of the cost item (underlying contract) needs to be considered. Please note that column H may need to be adjusted manually in case specific contracts start after another one expires. In the above case, the land lease and O&M costs have three step-ups. Also, note that land lease payments start with the beginning of construction/development, while most other cost items start with COD.

Column I offers flexibility to assume different inflation assumptions for all contracts. In the above case, most cost items are inflated using CPI.

In column O the pricing of the operational expenditures is given in real terms (not inflated), considering the respective units mentioned above.

The following section considers variable operational expenditures.

The naming of the cost items, duration, and inflation is set up the same way as for the fixed cost items above. 

The units for the variable cost items are given in EUR/MWh, and as revenue percentage, the two most commonly used units for variable revenues.

Please note to be careful with the inflation assumed for the cost items as % of revenue. While it is reasonable to inflate costs per produced MWh in the long-term, it might be less reasonable to do so on a relative revenue basis. If done so, operational expenditures increase over time on a relative portion of revenues. Nonetheless, the inflation option has still been integrated for this part of the revenue to have full flexibility also to model such structures in case they are contractually applicable in a particular project.

3.1.7       Major Maintenance Reserve Account (MMRA)

This section of the Financial Model User Guide considers assumptions for the Major Maintenance Reserve Account (MMRA).

The MMRA ensures that a project can be maintained during specific operational years with lumpy capital expenditures without raising further capital. This requires careful planning of financial needs in the future.

Similar to a Debt Service Reserve Account (DSRA), lenders often need to implement a Major Maintenance Reserve Account to ensure an extra layer of security within a project finance deal. The purpose of the DSRA is to ensure that the debt provider receives the debt service in a given period, even though the operational cash flow falls short of sufficiently covering the debt service.

Similarly, the MMRA ensures that major components of a renewable energy plant can be exchanged once they break or are worn out, even if the operational cash flow in a given period may not be sufficient to pay for these extraordinary costs. Solar parks typically require an exchange of inverters during the asset's lifetime. Inverters often break halfway through the asset life of the plant. In addition, some of the modules of a solar park or the blades of a wind farm might need to be replaced at a certain point.

The MMRA in this model can model up to three different releases for different purposes, such as the exchange of modules, inverters, or other components. 

During the development and construction of a power plant, a technical advisor typically recommends how much cash reserve should be put aside in each period to cover upcoming expenses sufficiently. This figure is given in thousands of EUR per MWp in O213 to O215.

Rows 217 to 230 allow considering three different releases of the MMRA for the three different components that can be saved up for. The three different components are named in D213 to D125.

3.1.8       Decommissioning Reserve Account (DRA)

This section of the Financial Model User Guide considers assumptions for the Decommissioning Reserve Account (DRA).

A DRA is a type of financial account set up to ensure that funds are available to decommission and dismantle the project at the end of its useful life. The purpose of the account is to ensure that there is enough money set aside to cover the costs of removing and disposing of equipment and materials when the renewable energy project is no longer in operation.

When a renewable energy project is built, it is typically expected to operate for a certain number of years, after which it may be decommissioned and dismantled. The decommissioning process involves safely removing all equipment and materials from the project site and restoring the land to its original state.

The decommissioning reserve account is typically funded throughout the project's life through contributions from the project owner or operator. The amount of funding required is based on estimates of the cost of decommissioning the project, which can vary depending on factors such as the size and complexity of the project, the type of equipment used, and the project's location.

By setting up a decommissioning reserve account, project owners or operators can ensure that the funds will be available when the project reaches the end of its useful life. In addition, they can demonstrate to regulatory authorities and other stakeholders that they have a plan for decommissioning and responsibly dismantling the project. This can help to minimize the risk of financial liabilities and environmental impacts associated with the decommissioning process.

In cell O235, the costs for dismantling the power plant are to be implemented in thousands of EUR per MW in real terms, which will then be inflated to the expected amount at the end of operations. 

If any proceeds are expected from selling spare parts, this can be implemented in cell O236 using the same unit as above. Again, this amount will be subtracted from the overall dismantling costs, and the model will consider the net amount for the DRA.

Lastly, cell O242 allows the flexibility to choose the starting date when building up the DRA should start. In the above case, the account starts being funded 10 years before the expected decommissioning date. The necessary amount will be set aside linearly over the assumed build-up period.

3.1.9       Funding (debt & equity)

The funding section starts with the above-displayed funding outputs from other sheets to give the user an overview of what the current capital structure of the live case looks like.

This section of the Financial Model User Guide considers assumptions for debt selection, covenant requirements, and linear debt repayment tranches.

Debt selection

First, select the debt structure via the dropdown in O254. This choice will materially affect the structure of the mode, and potentially the value of the asset – so careful scenario analysis and consideration of different financing structures should take place before deciding what approach is most appropriate for a given project.

The user can select either Linear or Sculpted in O254. If Linear is chosen, all three linear tranches are activated within the Calculations sheet. On the other hand, if Sculpted is selected, the debt sizing macro will optimize the debt in accordance with given sizing parameters (explained further below), and none of the linear tranches are considered in the Calculations sheet.

For more information regarding debt sculpting, please read section 3.12.9, “How to model sculpted debt financing with DSCR target?”.

In O255 and P255, a selection of whether the debt is calculated or scheduled (“frozen”) is made. It is highly recommended to leave this structure as it is displayed above. If the lender of the debt has different assumptions for debt sizing purposes of the project than the equity investor, the debt sizing should always occur in the first case (debt sizing case). This is why “Calculate” is selected in O255. In all other cases, the calculated debt service from the first case is scheduled (“frozen”) as per calculations from the first case. The macro will calculate the required debt service based on assumptions in the first case and keep those values constant for all other cases. This ensures that the debt repayment structure and quantity are not resized based on the equity assumptions resulting in the required debt parameters from the first case not being in breach in any of the other cases.

Covenant requirements

In O258, a lock-up DSCR is defined. If the debt service coverage ratio falls below this value, no dividend distributions to the equity investor occur if the DSCR is above one but below the lock-up DSCR. For more information regarding the debt service coverage ratio (DSCR), read section 3.12.9, “How to model sculpted debt financing with DSCR target?”.

Linear debt repayment

Three tranches with different underlying tenors and financing conditions can be implemented within the model. However, if the model should use linear tranches, please choose “Linear” in O254.

In columns G and H, the tenor assumptions are implemented. Implement assumptions for the total loan tenor (which is the grace period + repayment period), the grace period (which is a granted period by the bank in which only interest payments occur but no debt repayment is required), the tenor for the debt commitment period (which should not be longer than the assumed construction/development period within the model), and lastly the starting date of the tranche in column H.

In column O, choose the amount for each tranche (in thousands of EUR), the commitment fee (which is integrated as a percentage of the all-in interest rate), and the all-in interest rate. 

If only one linear tranche is used, simply leave the amounts for the other two tranches blank.

The last section for the debt inputs is dedicated to the debt sculpting parameters and the Debt Service Reserve Account (DSRA).

Sculpted debt repayment

The input structure for the tenor assumptions are the same as for the linear tranches. Implement assumptions for the total loan tenor (which is the grace period + repayment period), the grace period (which is a granted period by the bank in which only interest payments occur but no debt repayment is required), the tenor for the debt commitment period (which should not be longer than the assumed construction/development period within the model), and lastly the starting date of the tranche in column H.

This is also the case for the commitment fee and all-in interest rate assumptions. 

However, the significant difference is that there is no input field for the overall debt amount. The overall debt amount depends on the DSCR sizing target in O294. The lower the allowable DSCR target the lending bank determines, the higher the potential debt. Banks often set a limit for the overall gearing, also referred to as leverage of the project. This maximum gearing ratio is set it O295.

For more information regarding debt sculpting, please read section 3.12.9, “How to model sculpted debt financing with DSCR target?”.

Debt Service Reserve Account (DSRA)

The Debt Service Reserve Account (DSRA) protects a lender against an unexpected decrease in cash flow available for debt service (CFADS). The DSRA ensures that the debt provider receives the debt service in a given period, even though the operational cash flow falls short of sufficiently covering the debt service. This cash reserve account is usually established during the construction period or filled up with the target amount during the first months of operation.

The DSRA releases cash each time the debt service coverage ratio (DSCR) falls below 1, specifically when there is a gap to be billed between CFADS and debt service (interest and principal payment).

During the negotiation and finalization of the facility agreement between the project SPV and the lender, a target profile for each period within the lending period is predetermined. As a result, the DSRA is typically dimensioned to cover six months of forward-looking principal and interest payments. This target dimensioning period is implemented in cell O299. Therefore, ensure only to use integers, i.e., full quarters as input.

The drop-down menu in row 300 allows a selection of whether the initial funding of the DSRA should occur from initial funding sources or operational cash flow (if the switch is turned off).

Cell O301 allows flexibility in the funding occurrence of the DSRA. For example, in the above case, the funding occurs at COD. However, a positive one as input would push the funding occurrence one quarter before COD.

For more information regarding the debt service reserve account, read section 3.12.10, “How to model a Debt Service Reserve Account (DSRA)?”.

The last input section of the Financial Model User Guide for funding purposes addresses equity assumptions. 

For a general understanding of the purpose of a shareholder loan, please read section 3.12.11, “What’s the purpose of a shareholder loan?”.

Shareholder loan gearing

In cell O305, select the overall gearing of the shareholder loan. Of the overall equity needs, the assumed percentage in this cell –above 85% – will come from the shareholder loan. 

Shareholder loan interest

In cell O308, select the charged interest rate for the shareholder loan. To understand the purpose of the SHL interest and legal limitations in charging too high of interest rates, please read section 3.12.11, “What’s the purpose of a shareholder loan?”.

Shareholder loan principal

A cash sweep percentage for the repayment of the shareholder loan over different periods can be integrated to enhance equity returns further. In the above example, only 50% of the free cash flow available for SHL repayment is used for repayment. The rest is paid out as a dividend. Such an approach will generate more SHL interest over the asset's lifetime, further reducing the tax bill. However, before implementing such a rather aggressive financing structure consult a professional tax advisor within your jurisdiction. If you are unsure whether only a partial repayment of the shareholder loan is allowed in your jurisdiction, set O311 to O313, all to a value of 100%. This will ensure that all free cash flow available for equity will actually go towards the repayment of the shareholder loan until the loan is fully repaid.

Dividends

The last section allows choosing how much of the free cash flow available for dividend payout is actually paid out in any given period, the rest of which will be kept as cash.

In row 317 100% dividend payout ratio is chosen to ensure that all cash will be distributed in the last period of the model. 

3.1.10    Tax inputs

The last section in the Inputs_TI sheet allows the modeling of complex tax structures for almost any jurisdiction.

Before implementing any tax structures in this section, we strongly recommend consulting a local tax advisor who should verify whether the chosen inputs and the dependent calculations are implemented as intended for your given jurisdiction. 

Corporate tax

Cell O321 allows the implementation of the local corporate income tax (CIT). CIT is paid in almost any jurisdiction.

Trade tax

Cell O324 allows the implementation of a different trade tax rate. The taxable income calculation for corporate income tax and trade tax is the same in the dependent calculations. However, a distinction between the two rates allows the user to distinguish both tax amounts in the dependent calculations. In addition, trade taxes are more jurisdiction-specific and may not apply to certain jurisdictions.

Other local taxes

Rows 327 to 334 allow the integration of other local tax structures depending on the four input units most commonly used – the same units also in place for the operational expenditure inputs. These local taxes can be implemented in thousands of EUR, as thousands of EUR per MW, as EUR per MWh, and as % of total revenue. For instance, the latter is relevant and applicable to the generation tax in Spain (as of 2023). 

Interest barrier

In a tax-related context, an interest barrier is a mechanism to limit the amount of tax deductions a company can claim on its interest expenses. An interest barrier provision specifies a maximum amount of interest expense that can be deducted in a given period, such as a quarter.

The primary purpose of an interest barrier is to prevent companies from using excessive debt to reduce their taxable income. By limiting the amount of interest expense that can be deducted, an interest barrier makes it more difficult for companies to use debt and shareholder loans to minimize their tax payments.

Interest barriers can take various forms, but they usually involve a calculation that compares the amount of interest paid by the company to a specific percentage of the company's EBITDA which can be included in cell O337. If the amount of interest paid exceeds the limit set by the interest barrier, the excess amount is not tax-deductible. This can lead to higher tax liabilities for the company.

In some jurisdictions, the absolute annual amount of interest expense deductible may also apply. The four switches in column G allow for turning such barriers on or off, depending on what barrier may be applicable in your jurisdiction. The model also offers flexibility to differentiate between debt interest, SHL interest, and overall interest expense from both debt and SHL for the purposes of the interest barrier calculation.

3.2       Inputs_TD – Inputs Time Dependent

The Inputs Time Dependent Sheet is the second sheet that should be considered when building a project-specific financial model. 

From a bird’s eye perspective, this sheet is the second sheet of the first model structure block Inputs.

3.2.1       Model timeline

The model timeline is, in general self-explanatory. The timeline reflects only outputs that come from the Timing sheet. More details regarding the model timeline can be found in section 3.5.2, “Model timeline”.

3.2.2       Macroeconomic assumptions

This section of the Financial Model User Guide allows for the integration of time-dependent inflation assumptions. Please note that the base index can be set in column F. In the above case, all base indices have been set at 1.000.

For the underlying model, there are only two integrated inflation profiles: CPI being flat at 2.0% p.a. and no inflation in row 20 for cost and revenue assumptions that may not be inflated.

However, the model gives the flexibility to integrate five different inflation assumptions in case an operational expenditure item or certain revenues are linked to other indices but the CPI.

3.2.3       Initial investment and construction cost

This section of the Financial Model User Guide allows for the integration of time-dependent construction & development cost occurrences. The total costs come from column M in the Inputs_TI sheet and are displayed for information purposes in column F in this sheet.

Please use the blue input cells in rows 27 onwards to integrate the relative percentages of the overall costs for each construction quarter. 

Important notes

1.      All sums per row must add up to exactly 100% - the checks in column E ensure that if there is a cost item in a given row, the payment percentages should add up to 100%.

2.      All input cells during the operation quarters will be highlighted in grey from column M onwards, and none of the percentage inputs should be implemented in these cells. The model uses conditional formatting for this purpose. After readjusting the construction length in the Inputs_TI sheet, these inputs must be verified again.

3.      We recommend assuming a turn-key payment for the Enterprise Value (IRR solver) in row 27, meaning that 100% should be assumed in the last construction quarter.

3.2.4       Electricity price forecast

The electricity price forecast section is the last part of the Inputs_TD sheet. 

This model allows three different price forecasts to be implemented in rows 39 to 41. Row 40 is not displayed as an input, as this row multiplies the input of row 39 by 90% to generate a slightly more conservative electricity price forecast for the debt sizing case.

Typically, a few forecasting parties are acceptable to lenders that offer different forecast scenarios, such as a central and low curve. These can be implemented here. Depending on what selection has been made in the drop-down menu in the Inputs_TI sheet, this case will be applied in the live case of the model. Row 42 shows the live case, which will flow into the Calculations sheet.

3.3       Technical Inputs

The third sheet that should be considered when building a project-specific financial model is the Technical Inputs – Tech Sheet. 

From a bird’s eye perspective, this sheet is the third sheet of the first model structure block Inputs.

3.3.1       Project-specific & Constants

This section of the Financial Model User Guide displays project-specific and constant technical inputs which will be made use of in the model. All these inputs should not be changed except for a few, which will be highlighted. 

Project specific

First, the project name in cell E4 can be adjusted to the project-specific name.

Constants

These are all inputs for constant numbers, such as hours per year or months per year. The purpose of displaying all these constants as technical inputs is to name their respective numerical cell using the Name Manager. In a best practice model, such constant inputs are not directly used within formulas but referred to via their given names. This ensures the robustness and flexibility of the model.

For instance, the selected cell E7 contains the value of the number of hours in a year. In the Name Box, this cell has been named “Hours_year.” So if a calculation requires the use of hours per year, a simple reference to this cell can be made instead of using the numerical value 8,760 in each calculation.

For more info regarding the Name Manager and naming cells, read section 3.11.1, “How to use the Name Manager in Excel?”. 

3.3.2       Tolerances & Lists

This section of the Financial Model User Guide displays tolerances and lists which will be made use of in the model. 

Tolerances

These are tolerances used within the rounding functions, which are mainly used within the macro and checks.

Lists

These are lists that are used within the model structure. For instance, the Range of cell E35:E36 is named “List_onoff” and can be easily integrated into drop-down menus in the input sheets.

3.4       How to use the Macro

The Macro sheet should be considered next when building and understanding a project-specific financial model.

From a bird’s eye perspective, this sheet is the first sheet of the second model structure block Processing.

3.4.1       What is the purpose of macros?

The macro sheet is the control board to keep track of all broken circularities. First, let’s understand what the purpose of a macro is – afterward, it is easier to understand how to use the macros within this model.

Circular Reference Warning: "There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. Try removing or changing these references, or moving the formulas to different cells."

Ever encountered the below issue and wondered how to fix the problem?

When building financial models, circular references pop up regularly. Especially in a project finance-related context, a deep circularity will most certainly occur at a certain point when trying to model a sculpted debt financing structure in accordance with a target DSCR.

What is a circular reference?

A circular reference is caused by an interdependency of multiple variables when Excel tries to solve different formulas. For example, in the case of a sculpted debt financing structure in accordance with a target DSCR, the principal amount, which is sized on a DSCR target, will influence the interest expense, which could influence the tax amount and therefore change CFADS, which will again affect the principal amount. You will have a circular dependency between different variables.

But how to fix the problem?

Below you can see how Excel will show you that you have a circular reference within your financial model using blue arrows.

First, we will discuss how to fix the issue in a non-best-practice way. Although this way is certainly not recommended when using financial modeling best practices, it is still often used in the project finance market. We want you to know that there is a quick & dirty fix, but we will also show you how to break a circularity properly using a VBA macro.

Go to File ⇒ Options ⇒ Formulas ⇒ Tick the box "Enable iterative calculation"

Once you enable iterative calculations and choose a sufficient number of maximum iterations, Excel should be able to do several sufficient reiterations to calculate the proper outcomes for the debt structure.

So why should you not use the integrated iterative calculation feature of Excel?

Complex financial models will have more than one circularity. Once you have a sufficient amount of different circular dependencies, your Excel will likely crash or even come up with inconsistent calculation outcomes.

Make sure to uncheck the Enable iterative calculation check box before you continue - this will ensure that Excel pops up with the warning box that you integrated a circular reference within your financial model.

We recommend watching the video lessons of the Advanced Renewable Energy Financial Modeling course to gain a deep understanding of how to integrate macros. Nonetheless, below is an explanation of how a circular reference is broken using a macro.

Implementing a macro in VBA to break a circular reference

To understand how to implement a macro that breaks a circularity, look at the short line of code required within the VBA interface.

Step-by-step implementation of macro that breaks a circularity in VBA:

1.      Name all required ranges within the worksheets 

1.      Debt_principal_delta: The first named range is a delta between the actual debt principal line, which flows into the live case of the financial model, and the calculated debt principal, which does not flow into the live model anymore as it would cause a circularity.

2.      Debt_principal_paste: The second named range is the actual debt in the live model - it is a hardcoded value that cannot cause a circularity anymore.

3.      Debt_principal_copy: The last named range is the debt calculated based on a target DSCR - this is the line that Excel will copy and then paste as hardcodes in the Debt_principal_paste range. To avoid the circular reference, this line will not be linked into the integrated project finance model but will only be picked up in the VBA macro.

2.      Once all the required ranges are named within the workbook, you can open the VBA interface - the shortcut to open the interface is ALT + F11. After that, you just have to ensure that the developer is enabled within your ribbon.

3.      Open a Module in the VBA interface to start writing your code.

4.      Write a Sub statement and call it DebtPrincipal() in accordance with the code line above - using an apostrophe ' you can also give the macro a short explanation so that other users can easily understand what you have done.

5.      Implement a Do Until & Loop line around the copy & paste line. Behind "Do Until", you need to tell Excel that you want this macro to keep solving until the Debt_principal_delta range is zero. We want Excel to keep solving the macro until the difference between the calculated & actual debt is zero. We outsourced the iterative process into a macro.

6.      Implement the copy & paste line

1.      Implement Range("Debt_principal_paste").Value - this line tells Excel only to adjust the values of the line; no formatting or formulas are picked up.

2.      Tell Excel to take the hardcoded values from the copy range to paste them into the paste range by setting the first argument equal to the copy values: = Range("Debt_principal_copy").Value

3.      The full line should look like this: Range("Debt_principal_paste").Value = Range("Debt_principal_copy").Value

7.      Don't forget to put a Loop at the end of the Do Until argument. Excel will continue this iterative copy & paste process until both ranges sum up to the same amount.

8.      Assign the macro within your spreadsheet to a button and execute it.

After this process, a circular reference is broken. Now, the macro will have to be executed after each material input change of the model, which affects one of the copy/calculation lines.

3.4.2       How to use the macros within this model?

The macro sheet summarizes how each circular reference has been broken and breaks down all these small processes into smaller bits and pieces. If you would like to understand how this sheet has been integrated into the model, we recommend taking the respective video lessons in the Advanced Renewable Energy Financial Modeling course. 

However, as an end-user, it is essential to be able to use the two master macros of the model, which are best used in the Valuation Dashboard in the Inputs_TI sheet. Therefore, for the sake of completeness of this part of the User Guide, the explanations from an earlier section of how to use the master macros in the Inputs_TI sheet are repeated. 

Scenario selection & Valuation dashboard

Before getting into the details of how to use the following two sections in the Inputs_TI sheet, we recommend reading section 3.12.1, “How to value a wind farm or solar plant?” on page 89 if you do not yet understand how investors view the value of a renewable energy asset.

The scenario selection & valuation dashboard are powerful sections that can help to understand how a potential investor might view the asset’s value and how a project developer can decide whether this project would be worth the development effort considering the project’s Net Present Value (NPV).

Let’s first see how to use the model from a developer’s perspective.

To turn on the developer’s perspective, select “Off” using the switch for the target IRR valuation via the drop-down menu in G13.

The model has two master macros integrated into this valuation dashboard section. Depending on the perspective (investor vs. developer), the macro, which needs to be executed (pressed on) after each input change of the model, will be highlighted in green. 

So if you want to change any input in the model, always press the highlighted green macro button afterward!

In cell G18, the valuation case can be selected via a drop-down menu. So if you would like to value the asset from either perspective, please select the relevant valuation case here. The dropdown menu in cell M3 can be manually adjusted and represents the current live case in the model. However, after executing any of the two master macro buttons, the macro will value the selected case in cell G18. After the macro has been executed successfully, the case selected in G18 will also be the live case in the model selected in M3. 

If you are confused about how the macros work, don’t worry! The sections further below give an in-depth explanation of the macro's functionality. Additionally, our video courses on how to build an advanced renewable energy financial model help understand all these concepts practically.

Next, let’s see how to use the model from an investor’s perspective.

To turn on the investor’s perspective, select “On” using the switch for the target IRR valuation via the drop-down menu in G13.

After selecting the investor’s perspective, the Macro incl. IRR target needs to be executed!

Please note that this macro is doing a highly iterative calculation process which depending on your computing power, can take multiple minutes. 

This macro will set the NPV of the investment to zero given a selected IRR target which is a manual input in cell G14. Please note that the NPV @Target IRR in cell G14 still displays a value of 1. The entire model is set up in thousands of EUR, but the NPV is still at approximately 1,000 EUR. The macro is set up so that a few thousand EUR NPV difference from zero is acceptable. Considering the circumstances that valuing a renewable energy asset incorporates many highly unpredictable variables and future cash flows, this is an acceptable tolerance.

It is also noteworthy that from a developer’s perspective, the Enterprise Value was calculated as 44.360 mEUR plus an NPV of 18.362 mEUR, summing up to 62.722 mEUR. 

However, after turning on the investor’s perspective, the Enterprise Value is displayed as 74,811 mEUR. Don’t worry; this is not an error! This significant difference in Enterprise Values is due to different capital structures in both cases. The investor’s perspective uses significantly more senior debt with lower capital costs than equity. However, from the developer’s perspective, the future free cash flows to equity are discounted at the selected 6.00% target IRR to determine the NPV @target IRR. These effects cause such a discrepancy in both valuation approaches.

Consequently, from a developer's perspective, the NPV @target IRR can only be interpreted as a proxy for whether developing this asset is worth the effort. Only if the NPV is positive the asset should be realized; however, if a developer wants to understand how an investor might value the asset given a certain IRR return expectation, we recommend switching to the investor’s perspective and executing the respective master macro.

For more info, refer to section 3.12.1, “How to value a wind farm or solar plant?”. 

3.5       Timing Sheet – Model Timeline

When building and understanding a project-specific financial model, the Timing sheet should be considered next.

From a bird’s eye perspective, this sheet is the second sheet of the second model structure block Processing.

3.5.1       The header of the Timing sheet

The header of the timing sheet is also displayed in the Calculations, Inputs_TD, and Valuation sheets. However, the calculations for the model's timing-relevant sections only occur within the Timing sheet. The other sheets then refer to this Timing sheet, which ensures the robustness and flexibility of the model.

The inputs for the start and end dates of the construction and operations period come from the Inputs_TI sheet. 

Important topics to consider for the Timing sheet are the EOMNONTH function and the proper use of flags – to gain a complete understanding of these concepts, please read section 3.11.6, “EOMONTH Function – Financial Modeling” and section 3.11.7, “How to use flags in financial models?”.

3.5.2       Model timeline

The model timeline considers the essential values relevant for calculations within the Calculations and Valuation sheet. 

To give a few examples, the percentage fraction of a year in row 15 will be relevant for diverse operational expenditures. In contrast, the operations quarters in row 13 are essential to calculate the release flag within the Major Maintenance Reserve Account, and days in a period in row 11 are relevant for working capital adjustment calculations.

3.5.3       Macroeconomics

This section of the Financial Model User Guide picks up the inputs from the Inputs_TD sheet to calculate different inflation profiles. Please note that the base index can be set in column F in the Inputs_TD sheet, which is then pulled into column H in the Timing sheet. In the above case, all base indices have been set at 1.000.

For the underlying model, there are only two integrated inflation profiles: CPI being flat at 2.0% p.a. and no inflation in row 20 for cost and revenue assumptions that may not be inflated.

However, the model gives the flexibility to integrate five different inflation assumptions in case an operational expenditure item or certain revenues are linked to other indices but the CPI. 

3.6       Model Core – Calculations Sheet

The Calculations sheet should be considered next when building and understanding a project-specific financial model. This sheet is the heart of the Processing part of the model and reflects most of the calculations.

From a bird’s eye perspective, this sheet is the third sheet of the second model structure block Processing.

3.6.1       The header of the Calculations sheet

The values of the header of the Calculations sheet come from the Timing sheet. These values are also displayed in the Inputs_TD and Valuation sheets. The calculations for the timing-relevant sections of the model all take place within the Timing sheet only. The other sheets only refer to the Timing sheet, which ensures the robustness and flexibility of the model.

The inputs for the start and end dates of the construction and operations period come from the Inputs_TI sheet. 

Important topics to consider for the timing relevant calculations are the EOMNONTH function and the proper use of flags – to gain a complete understanding of these concepts, please read section 3.11.6, “EOMONTH Function – Financial Modeling” and section 3.11.7, “How to use flags in financial models?”.

3.6.2       Model timeline

The model timeline is, in general self-explanatory. The timeline reflects only outputs that come from the Timing sheet. More details regarding the model timeline can be found in section 3.5.2, “Model timeline”.

3.6.3       Macroeconomics

This section of the Financial Model User Guide picks up the outputs from the Timing sheet so that the user can refer to these indexations rows within calculations further below in the sheet.

For the underlying model, there are only two integrated inflation profiles: CPI being flat at 2.0% p.a. and no inflation in row 20 for cost and revenue assumptions that may not be inflated.

However, the model gives the flexibility to integrate five different inflation assumptions in case an operational expenditure item or certain revenues are linked to other indices but the CPI.

3.6.4       Cashflow Statement / Waterfall

This section of the Financial Model User Guide displays the cashflow statement, also referred to as cashflow waterfall. Most of the cash flow movements are pulled in from calculations further below.

A cashflow statement in project finance deals is a critical tool for analyzing the financial health of a project. Its purpose is to provide an accurate and complete picture of a project's cash inflows and outflows over a specific period and the entire asset life, which is displayed in the row totals in column I.

A cashflow statement helps project financiers and investors understand the timing and amount of cash that a project generates and requires to meet its financial obligations. It provides insights into the project's operating, investing, and financing activities, allowing financiers to evaluate the project's financial performance and assess its ability to generate sufficient cash flows to cover its expenses, debt service, and ultimately meet the return expectations of equity investors.

Overall, a cash flow statement is a crucial component of a project finance deal, providing critical information for investors, lenders, and other stakeholders to make informed investment decisions.

If you would like to gain an in-depth understanding of how this cashflow statement has been created, we recommend watching the video lessons of the Advanced Renewable Energy Financial Modeling course.

3.6.5       Income Statement

An income statement, also known as a profit and loss statement, is a financial statement that provides a summary of a company's revenues and expenses over a specific period. In project finance deals, the purpose of an income statement is to provide an accurate and complete picture of the project's revenue generation and profitability.

The income statement provides information on the project's revenue, operating costs, operating income, depreciation & amortization, interest expenses, taxes, and net income or loss. 

In project finance deals, the income statement is especially relevant for tax calculation purposes as it provides information on the project's taxable income. The taxable income is calculated by subtracting the project's tax-deductible expenses from its total revenue. Tax-deductible expenses are expenses that can be deducted from the project's revenue to reduce its taxable income.

Income and cashflow statements are essential financial statements used in project finance deals. Still, they differ in their purpose, structure, and the type of information they provide. Here are the key differences between an income statement and a cash flow statement:

1. Purpose: The income statement provides information on the project's revenues, expenses, and tax-relevant profitability over a specific period, while the cash flow statement provides information on the project's cash inflows and outflows over the same period.
2. Timing: The income statement records revenues and expenses when they are incurred, regardless of when the cash is received or paid out, while the cash flow statement records cash inflows and outflows when they occur.
3. Structure: The income statement consists of revenue, operating costs, operating income, depreciation & amortization, interest expenses, taxes, and net income or loss, while the cash flow statement is structured around cash inflows and outflows from operating activities, investing activities, and financing activities.
4. Information provided: The income statement provides information on the project's profitability and financial health, while the cash flow statement provides information on the project's ability to generate cash and meet its financial obligations.

In summary, the income statement focuses on tax-relevant profitability, while the cash flow statement focuses on cash flow. While they both provide important information for project finance deals, they serve different purposes and provide different types of information.

3.6.6       Balance Sheet

A balance sheet is a financial statement that provides a snapshot of a company's financial position at a specific point in time. In project finance deals, the purpose of a balance sheet is to provide an overview of the project's assets, liabilities, and equity, which can be used to assess its financial health, liquidity, and solvency.

The balance sheet provides information on the project's assets, which include current and non-current assets such as cash, reserve accounts, and long-term, short-term, and financing assets. It also lists the project's liabilities, including tax credits, debt, and shareholder loans. The difference between the project's assets and liabilities represents its equity or net assets.

Project financiers and investors use the balance sheet to evaluate the project's financial position and assess its ability to meet its financial obligations. For example, the balance sheet can be used to calculate key financial ratios such as the debt-to-equity ratio, which measures the project's leverage, or the current ratio, which measures the project's liquidity.

Overall, a balance sheet in a project finance deal is to provide stakeholders with an understanding of the project's financial position, enabling them to make informed investment decisions. It provides critical information on the project's assets, liabilities, and equity, which can be used to assess its financial health and solvency.

3.6.7       Uses of funds

In project finance deals, the term "uses of funds" refers to the specific purposes for which the funds raised through financing will be utilized. The uses of funds provide a detailed breakdown of how the project's financing will be allocated to support its development, construction, and operation.

Here are some common uses of funds in a project finance deal:

1. Construction costs/capital expenditures: The primary use of funds in project finance deals is usually the construction or development of the project – also referred to as full wrap EPC or EPCM. For more info regarding EPC vs. EPCM, please read section 3.12.2, “EPCM vs. EPC agreement – what’s the difference?”.
2. Working capital: Project financing may also be used to cover the project's operating expenses, such as land lease, insurance, and maintenance costs until the project becomes cash flow positive – referred to as SPV cash reserve account during construction.
3. Debt repayment: If the project is refinanced, the proceeds may be used to repay existing debt or finance any pre-existing debt on the project.
4. Reserves: Project financiers may require a portion of the financing to be allocated to reserves such as the DSRA and MMRA, which can be used to cover unexpected costs or fund future maintenance and repair.
5. Other uses: The uses of funds can also include various expenses and fees associated with the financing, such as legal and advisory fees, transaction fees, and other costs.

Overall, the uses of funds in a project finance deal provide investors and financiers with a detailed breakdown of how the financing will be allocated to support the project's development, construction, and operation. It helps to ensure that the funds are used efficiently and effectively to achieve the project's objectives and maximize returns for all stakeholders involved.

3.6.8       Sources of funds

Sources of funds in a project finance deal typically include:

1. Equity: Equity financing is investors' ownership stake in a project. Equity investors provide the initial capital to start the project and share in the risks and rewards of the project. The equity investors may be project sponsors, institutional investors, or private equity firms.
2. Shareholder loan: A shareholder loan - also referred to as "SHL", is - strictly speaking - debt from an accounting perspective. However, the company's shareholders financing a project use their company's equity to give out the loan. Therefore, a shareholder loan is a subordinate loan, meaning that the loan only gets repaid after all other liabilities are paid off. Typically, this subordination holds true not only for the principal of the SHL but also for the SHL interest charged. For more info regarding a shareholder loan, please read section 3.12.11, “What’s the purpose of a shareholder loan?”.
3. Debt: Debt financing is a loan secured against the project's assets and cash flows. The loan is repaid with interest over a set period. The lenders may be commercial banks, development banks, or bond investors.

3.6.9       Generation

In this section the Financial Model User Guide, the electricity generation is calculated, considering effects such as availability, degradation, seasonality, and allocations to different offtaking structures such as PPA or merchant production.

For additional info regarding concepts applied in this section, it is recommended to read the following sections:

-        3.12.6 “What is degradation and how to model it?”

-        3.12.5 “How to model seasonality of renewable energy revenues?”

-        3.12.4 “How to model P50, P75, and P90 energy yield?” 

-        3.12.8 “PPA Offtake Agreements” 

-        3.11.7 “How to use flags in financial models?” 

3.6.10    Revenue prices

This section of the Financial Model User Guide considers the time-dependent merchant, PPA, and other income prices after inflation. 

For additional info regarding concepts applied in this section, it is recommended to read the following sections:

-        3.12.8 “PPA Offtake Agreements” 

-        3.11.7 “How to use flags in financial models?” 

-        3.11.4 “INDEX Function – Financial Modeling” 

-        3.11.8 “LOOKUP Function – Financial Modeling” 

3.6.11    Revenue

This section of the Financial Model User Guide summarizes the allocation of revenues from different offtaking sources. It considers a simple multiplication of generation allocations and their underlying periodic prices. 

Additionally, the relative percentage proportion of contracted revenues is calculated over the entire asset lifetime.

3.6.12    Operational expenditures

This section of the Financial Model User Guide considers all relevant calculations necessary to reflect all different operational expenditure assumptions from the Inputs_TI sheet.

For additional info regarding concepts applied in this section, it is recommended to read the following sections:

-        3.11.7 “How to use flags in financial models?” 

-        3.11.4 “INDEX Function – Financial Modeling” 

-        923.12.3 “What’s the difference between kW and kWh?” 

3.6.13    Major Maintenance Reserve Account (MMRA)

The Major Maintenance Reserve Account (MMRA) ensures that during certain operational years with lumpy capital expenditures, a project can be maintained without raising further capital. This requires careful planning of financial needs in the future, best done using a best-practice financial model for renewable energy investments.

What is the purpose of an MMRA?

Similar to a Debt Service Reserve Account (DSRA), the implementation of a Major Maintenance Reserve Account is often required by lenders to ensure an extra layer of security within a project finance deal. The purpose of the DSRA is to ensure that the debt provider receives the debt service in a given period, even though the operational cash flow falls short of sufficiently covering the debt service.

Similarly, the MMRA ensures that major components of a renewable energy plant can be exchanged once they break or are worn out, even if the operational cash flow in a given period may not be sufficient to pay for these extraordinary costs. For example, solar parks typically require an exchange of inverters during the asset's lifetime. Unfortunately, inverters often break halfway through the asset life of the plant. In addition, some of the modules of a solar park or the blades of a wind farm might need to be replaced at a certain point.

How to implement an MMRA within a financial model?

Integrating an MMRA into a financial model is not as complex as implementing a DSRA. Unlike the DSRA, the MMRA does not cause an interdependency of different variables resulting in a circular reference that has to be broken, which would require using a macro similar to the debt sculpting procedure in accordance with a target DSCR.

The above MMRA has the flexibility to model up to three different releases for different purposes, such as the exchange of modules, inverters, or other components.

During the development and construction of a power plant, a technical advisor typically recommends how much cash reserve should be put aside in each period to cover upcoming expenses sufficiently. This figure is given in thousands of EUR per MWp in the above example.

Lastly, implementing additions and release flags ensures the flexibility and robustness of the MMRA within the financial model.

For additional info regarding concepts applied in this section, it is recommended to read the following sections:

-        3.11.7 “How to use flags in financial models?” 

-        923.12.3 “What’s the difference between kW and kWh?” 

3.6.14    Decommissioning Reserve Account (DRA)

A decommissioning reserve account is a financial provision established to cover the costs of decommissioning or dismantling a project at the end of its useful life. In a project finance deal, a decommissioning reserve account ensures that funds are available to pay for the costs of decommissioning when the project reaches the end of its useful life.

The purpose of a decommissioning reserve account is vital for several reasons. First, it provides a mechanism to ensure that the project's sponsors are financially responsible for decommissioning costs. Second, it helps to mitigate the risk that the project's assets will become stranded or abandoned at the end of their useful life, which could have negative environmental and social impacts. Third, it helps to assure lenders and investors that the project's liabilities will be adequately covered.

The amount of money required to fund a decommissioning reserve account will depend on a number of factors, including the nature of the project, the types of assets being used, and the regulatory requirements in place. However, the amount of money required will typically be calculated based on an estimate of the decommissioning costs, which may include site remediation, asset removal, and waste disposal.

For additional info regarding concepts applied in this section, it is recommended to read the following sections:

-        3.11.7 “How to use flags in financial models?” 

3.6.15    SPV cash reserve account during construction

The SPV cash reserve account during construction serves the purpose of funding operational expenditures and financing costs during the development and construction period, when the project does not generate any operational cashflows.

3.6.16    Working capital adjustments

This section of the Financial Model User Guide accounts for changes in working capital. In a project finance-related context, working capital refers to the funds required to finance the day-to-day operations of a project during its operational phase. Working capital is an essential part of a project finance model, as it ensures that there is enough funding available to cover the short-term operating expenses of the project, such as accounts receivable (referred to debtors above) and accounts payable (referred to creditors above).

Working capital is calculated as the difference between current assets and current liabilities. In the above case, the difference between the creditors and debtors row.

The working capital requirements of a project can vary over time, depending on the nature of the project and the cash flow patterns. For example, during the construction phase of a project, the working capital requirements may be low, as there are relatively few operating expenses. However, during the operational phase, the working capital requirements may increase as the project incurs operating expenses such as payroll and supplier payments.

3.6.17    Depreciation categories & Accounting depreciation

This section of the Financial Model User Guide first distinguishes the capital expenditures into three categories within the depreciation categories section. Then it considers these three categories in detailed calculations in the accounting & tax depreciation section.

In the Inputs_TI sheet, the user can select straight-line or reducing balance depreciation for accounting and tax purposes.

Straight-line depreciation and reducing balance depreciation are two methods used to calculate the depreciation of an asset over its useful life. The critical difference between the two methods is how the depreciation expense is calculated each year.

Straight-line depreciation is a method in which the same depreciation expense is allocated to the asset over each year of its useful life. The calculation of depreciation is based on the asset's cost minus its residual value, divided by the number of years of its useful life. For example, if an asset has a cost of €100,000, a residual value of zero, and a useful life of 10 years, the straight-line depreciation would be €10,000 per year (€100,000 - €0 / 10).

On the other hand, reducing balance depreciation is a method in which the depreciation expense is calculated as a percentage of the remaining value of the asset each year. This means that the depreciation expense is higher in the early years of the asset's useful life and gradually reduces over time. The calculation of depreciation is based on the asset's cost multiplied by a predetermined rate, which is usually expressed as a percentage. For example, if an asset has a cost of €100,000 and a reducing balance depreciation rate of 20%, the depreciation expense for the first year would be €20,000 (€100,000 x 20%), and the remaining value of the asset would be €80,000. The next year's depreciation expense would be calculated based on the remaining value of €80,000, multiplied by the same depreciation rate of 20%, resulting in a depreciation expense of €16,000 (€80,000 x 20%).

In summary, the key difference between straight-line depreciation and reducing balance depreciation is that straight-line depreciation allocates the same amount of depreciation expense to the asset over each year of its useful life, while reducing balance depreciation calculates the depreciation expense as a percentage of the remaining value of the asset each year. The choice of depreciation method depends on the nature of the asset and the company's accounting policies.

For additional info regarding concepts applied in this section, it is recommended to read the following sections:

-        3.11.7 “How to use flags in financial models?” 

-        3.11.4 “INDEX Function – Financial Modeling” 

3.6.18    Tax depreciation

This section of the Financial Model User Guide is mechanically set up in the same manner as the accounting depreciation section, and the same principles apply. 

However, some jurisdictions differentiate in the depreciation methods for accounting and tax purposes. Therefore, the end user can choose different methodologies for both purposes, which can be selected in the Inputs_TI sheet.

3.6.19    Depreciation summary

This section of the Financial Model User Guide summarizes all calculations from the previous sections. This summary then ultimately flows into the income statement and balance sheet.

3.6.20    Taxes

The tax section of this Financial Model User Guide is somewhat complex and lengthy, given that the model offers high flexibility to model diverse tax structures. Let’s first see how different interest barriers have been implemented.

Interest barrier

In a tax-related context, an interest barrier is a mechanism used to limit the amount of tax deductions that a company can claim on its interest expenses. An interest barrier provision specifies a maximum amount of interest expense that can be deducted in a given period, such as a quarter.

The primary purpose of an interest barrier is to prevent companies from using excessive debt to reduce their taxable income. By limiting the amount of interest expense that can be deducted, an interest barrier makes it more difficult for companies to use debt and shareholder loans to minimize their tax payments.

Interest barriers can take various forms, but they usually involve a calculation that compares the amount of interest paid by the company to a specific percentage of the company's EBITDA – in the above example, this is calculated as 30% of EBITDA in row 791. If the amount of interest paid exceeds the limit set by the interest barrier, the excess amount is not tax-deductible. This can lead to higher tax liabilities for the company.

In some jurisdictions, the absolute annual amount of interest expense deductible may also apply. The SHL & debt interest barrier switch is on in the above example and set at EUR 1,000,000 per annum. This amount will depend on what barrier may be applicable in the respective project’s jurisdiction. The model also offers flexibility to differentiate between debt interest, SHL interest, and overall interest expense from debt and SHL for the interest barrier calculation—the inputs for which are selected in the Inputs_TI sheet.

The following section reflects the respective calculations for the corporate income tax (CIT) under consideration of the effect of debt and shareholder loan financing (leverage)– i.e., this section considers all tax deductible interest effects after the tax barrier.

Corporate income tax (CIT) in project finance refers to the tax paid on the profits earned by a corporate entity involved in a project. This tax is imposed on the net income earned by the corporation after deducting allowable expenses, such as operational expenditures, depreciation, interest, and other business-related costs.

In project finance, corporate income tax can be an essential consideration when evaluating the financial viability of a project. The amount of tax paid by a project company will depend on the tax laws in the jurisdiction where the project is located and the tax planning strategies employed by the project sponsors.

Additionally, some jurisdictions may offer tax incentives or exemptions for specific projects, such as renewable energy projects, which can provide a competitive advantage for these types of investments.

Similar to the section above, this section of the Financial Model User Guide reflects the respective calculation for the trade tax under consideration of the effect of debt and shareholder loan financing (leverage)– i.e., this section considers all tax-deductible interest effects after the tax barrier.

Trade tax, also known as business tax or municipal tax, is a tax on the income earned by businesses operating within a specific jurisdiction. Municipalities or local authorities levy this tax, and the trade tax rate varies depending on the location of the business.

The trade tax liability is based on the project company's taxable income, calculated separately from the corporate income tax.

The trade tax rate varies significantly between different jurisdictions and is often higher in urban areas than in rural areas. In addition, the amount of trade tax paid by the project company can affect the project's cash flows, as it reduces the amount of profits available for distribution to project stakeholders.

Therefore, project finance transactions typically incorporate tax-efficient structures to minimize the trade tax liability of the project company and maximize the returns for the stakeholders. This may involve structuring the project to take advantage of tax incentives or exemptions offered by the local authorities or locating the project in a jurisdiction with a lower trade tax rate.

Project sponsors and investors need to consider the trade tax implications of a project, as this can significantly impact the project's financial returns.

This section considers all other local taxes which might be relevant in a given jurisdiction. 

The model structure allows for the consideration of four different input units most commonly used – the same units also in place for the operational expenditure inputs. These local taxes can be implemented in thousands of EUR, as thousands of EUR per MW, as EUR per MWh, and as % of total revenue. For instance, the latter is relevant and applicable to the generation tax in Spain (as of 2023). 

After all relevant taxes on a geared basis (under consideration of debt and shareholder loan financing) have been calculated, the above section summarizes these outputs. This summary flows into the three statements, i.e., cash flow statement, income statement, and balance sheet.

This section reflects corporate income and trade tax calculations without the effect of the financing structure. The taxes are calculated on an unlevered basis, meaning that the effect of debt and shareholder loan financing is ignored – i.e., this section considers none of the tax-deductible interest effects after the tax barrier.

This helps investors, financiers, and sponsors gain insight into the effect of the project's capital structure on the tax bill and how it affects different return metrics.

The summary of the unlevered tax calculation only flows into the Valuation sheet and is considered within the unlevered return calculations.

3.6.21    Senior debt

Linear debt repayment

Three different tranches with different underlying tenors and financing conditions can be implemented within the model. However, if the model should use linear tranches, please choose “Linear” in O254 in the Inputs_TI sheet.

The tenor assumptions are implemented within the Inputs_TI sheet, in columns G, and H. Implement assumptions for the total loan tenor (which is the grace period + repayment period), the grace period (which is a granted period by the bank in which only interest payments occur but no debt repayment is required), the tenor for the debt commitment period (which should not be longer than the assumed construction/development period within the model), and lastly the starting date of the tranche in column H.

The total amount for each tranche is implemented in column O in the Inputs_TI sheet (in thousands of EUR). Furthermore, the commitment fee (which is integrated as a percentage of the all-in interest rate) and the all-in interest rate can be set in the Inputs_TI sheet. 

If only one linear tranche is used, leave the amounts for the other two tranches blank.

Sculpted debt financing in accordance with a target Debt Service Coverage Ratio (DSCR) is a common approach used in project finance to align debt repayments with the expected cash flow profile of the project while maintaining a minimum level of debt service coverage. The DSCR measures a project's ability to generate sufficient cash flow to cover its debt service obligations, including principal and interest payments.

In a sculpted debt financing structure with a target DSCR, the debt repayment schedule is designed to achieve a specific DSCR over the project's life. This means that the debt service payments will be structured to match the project's expected cash flows while ensuring that the DSCR stays at or above the target level.

For example, if the target DSCR is 1.30x, the project company would need to generate sufficient cash flow to cover 130% of the debt service obligations. Therefore, the debt repayment schedule would be designed to achieve this target DSCR by varying the amount of principal and interest payments over time.

During the early stages of the project, when cash flows may be lower, the debt service payments would be lower, allowing the project to build up cash reserves and avoid default. As the project generates more cash flow over time, the debt service payments would increase, ensuring that the DSCR stays above the target level.

Sculpted debt financing in accordance with a target DSCR can help reduce the risk of default and improve the overall financial viability of the project. By aligning debt repayments with the expected cash flow profile of the project and maintaining a minimum level of debt service coverage, project sponsors can better manage cash flow and reduce the risk of default, thereby improving the project's attractiveness to lenders and investors.

For more information on how to implement this debt repayment structure, we recommend watching the video lessons of the Advanced Renewable Energy Financial modeling course.

Depending on the choice made for the financing structure within the Inptus_TI sheet, this section applies the selected financing structure, which then ultimately flows into the integrated financial statements of the model.

This is also the section where two circularities within the model have been broken—the circularity caused by debt principal and interest.

For more information regarding how to break a circularity, we recommend watching the video lessons of the Advanced Renewable Energy Financial modeling course and reading section 3.4.1, “What is the purpose of macros?”.

This section calculates the Debt Service Coverage Ratio (DSCR) of the applied capital structure.

The debt service coverage ratio (DSCR) determines how much the cash flow available for debt service (CFADS) exceeds the total scheduled debt service, i.e., debt principal and interest, in any given period. Depending on the project's riskiness, i.e., the uncertainty of energy production or the contractual basis of cash flows, debt financing banks are willing to give specific minimum DSCR targets for sculpted repayment structures.

DSCR (t) = CFADS (t) / Debt Service (t)

The last section of the senior debt part reflects the calculations for the Debt Service Reserve Account (DSRA). 

The Debt Service Reserve Account (DSRA) protects a lender against an unexpected decrease in cash flow available for debt service (CFADS). The DSRA ensures that the debt provider receives the debt service in a given period, even though the operational cash flow falls short of sufficiently covering the debt service. This cash reserve account is usually established during the construction period or filled up with the target amount during the first months of operation.

The DSRA releases cash each time the debt service coverage ratio (DSCR) falls below 1, specifically when there is a gap to be billed between CFADS and debt service (interest and principal payment). During the negotiation and finalization of the facility agreement between the project SPV and the lender, a target profile for each period within the lending period is predetermined using a financial model. As a result, the DSRA is typically dimensioned to cover six months of forward-looking principal and interest payments.

How to implement a Debt Service Reserve Account within a financial model?

Integrating a DSRA into a financial model is rather complex, as different cash releases and funding movements must be considered. In addition, the implementation of the DSRA might cause an interdependency of different variables resulting in a circular reference that has to be broken using a macro similar to the debt sculpting procedure in accordance with a target DSCR.

Cash movement considerations for modeling purposes

The above example shows the different cash movements that need to be considered for adequately modeling the cash account. So let's take it one at a time!

Initial funding from sources: The first line item in the account is the funding from sources which occurs only once at the end of construction. In our example above, the initial funding is approximately 359,000 EUR. However, you might notice that the balance quickly rises to over 1.5 mEUR within the first two years of operation. In addition, due to a grace period of 12 months, the debt service in the first operational year consists of interest payments only, resulting in a low initial funding need, given a target DSRA balance of 6 months of forward-looking debt service.

Funding from cash flow: Once construction is over and the solar park or wind farm is operational, the funding will occur from operational cash flow. At the end of each operational quarter, the six months of forward-looking debt service are considered to determine potential additional funding needs. If additional funding needs are required, this gap will be filled only if sufficient cash flow for the DSRA is available. In the model above, you can see how this funding need is calculated at the bottom of the screenshot, which flows into line item 1080.

Release due to cash flow shortfall: If the project does not generate sufficient operating cash flow in a certain period, there will be a release from the DSRA to cover the shortfall in debt service. Once the project reaches sufficient cash flows again, the DSRA will be filled up again in the periods thereafter.

Release due to excess account funding: Banks typically require the project SPV to hold sufficient cash in the DSRA to cover six months of forward-looking debt service. So if the account balance of the DSRA is too high, the equity owner is allowed to release this extra cash. In row 1082, you can see how excess cash will be treated in the third quarter of 2025. You might wonder why the account is so overfunded in this period? As the two-quarters of debt service ahead are Q4 2025 and Q1 2026, there are comparatively low CFADS available due to the seasonality of the solar project. In the above example, a sculpted debt financing structure is in place, which causes debt service in the summer months to be higher than during the winter months. Consequently, excess cash is available in the DSRA at the end of the year's third quarter.

3.6.22    Equity

The Equity section is the final part of the Calculations sheet. It considers the balance sheet items for equity purposes and all relevant cash flow movements to the equity investor. 

The above section reflects the last calculations of the Calculations sheet. 

What's the benefit of using a shareholder loan when financing a renewable energy project? It's simple - saving taxes!

What is a shareholder loan?

A shareholder loan - also referred to as "SHL", is - strictly speaking - debt from an accounting perspective. However, the company's shareholders financing a project use their company's equity to give out the loan. So why not call it equity if the loan is financed with equity anyway?

A shareholder loan is a subordinate loan, meaning that the loan only gets repaid after all other liabilities are paid off. Typically, this subordination holds true not only for the principal of the SHL but also for the SHL interest charged.

Shareholder loan interest lowers the tax bill.

The major difference between a shareholder loan and pure equity in the form of share capital is the interest payment charged. The interest rate of the shareholder loan is most typically fixed over the entire tenor of the loan. Due to the subordination of an SHL, it is also reasonable to argue for a higher interest rate than the ones charged within the senior debt agreements - if any senior debt is raised. But before deciding how much SHL interest can be charged and how much overall SHL gearing is reasonable for a specific project, we strongly advise consulting a local tax advisor. Abusing an SHL structure will end up in trouble with the tax authorities. Typically a transfer pricing study from an independent third party is put in place before giving out the loan and defining its conditions.

For enhancing equity returns further, a cash sweep percentage for the repayment of the shareholder loan over different periods can be integrated within the model. In the above example, only 50% of the free cash flow available for SHL repayment is used for repayment. The rest is paid out as a dividend. Such an approach will generate more SHL interest over the asset's lifetime, further reducing the tax bill. However, before implementing such a rather aggressive financing structure consult a professional tax advisor within your jurisdiction. If you are unsure whether only a partial repayment of the shareholder loan is allowed in your jurisdiction, set the inputs in the Inputs_TI sheet to a value of 100%. This will ensure that all free cash flow available for equity will actually go towards the repayment of the shareholder loan until the loan is fully repaid.

Dividends

The dividend section in the Inputs_TI sheet allows choosing how much of the free cash flow available for dividend payout is actually paid out in any given period, the rest of which will be kept as cash.

Lastly, conditions breaches such as an underfunded debt service reserve account or a DSCR covenant breach ensure that a dividend is only paid if all relevant conditions are met.

3.7       Checks Sheet

When building and understanding a project-specific financial model, the Checks sheet should be considered next.

From a bird’s eye perspective, this sheet is the fourth sheet of the second model structure block Processing.

3.7.1       Integrity & Signal Checks

The Checks sheet ensures the robustness and correctness of the model at any time. 

All master checks are included in every sheet so that the user can instantly spot whether one of the inputs might have caused a check to fail. Often, running the master macros will solve the issue, as the copy & paste lines will not match after specific input changes.

However, in the above example, the user failed to properly integrate the payment plan in the Inputs_TD sheet, so only a correct integration of these inputs will solve the issue.

3.8       Charts Sheet

The Charts sheet should be considered next when building and understanding a project-specific financial model.

From a bird’s eye perspective, this sheet is the fifth sheet of the second model structure block Processing.

3.8.1       Charts sheet

The charts sheet summarizes all relevant outputs for creating the charts for the model's Dashboard. If you would like to learn how to create and build the underlying charts of the model, we recommend watching the video lessons of the Advanced Renewable Energy Financial modeling course.

3.9       Valuation Sheet

The Valuation sheet should be considered next when building and understanding a project-specific financial model.

From a bird’s eye perspective, this sheet is the first sheet of the third model structure block Output.

3.9.1       Model timeline & Macroeconomics

The model timeline is, in general self-explanatory. The timeline reflects only outputs that come from the Timing sheet. More details regarding the model timeline can be found in section 3.5.2, “Model timeline”.

3.9.2       Levered Return

The Valuation sheet calculates the most crucial return figures for any renewables project. 

The calculations of the return figures take place on a levered and unlevered basis. Therefore, the unlevered figures are beneficial to “compare apples with apples”, i.e., understand whether benchmark projects are better or worse without consideration of specific capital structures.

Internal Rate of Return (IRR)

The Internal Rate of Return (IRR) is a financial metric commonly used in project finance to measure the profitability of an investment. It represents the rate of return that would make the present value of future cash flows from the investment equal to the initial investment amount.

In project finance, the IRR is used to evaluate the financial viability of a project by estimating the expected rate of return on the investment. The IRR calculation considers the timing and magnitude of the expected cash flows, the initial investment, and any financing costs.

The IRR is calculated by solving for the discount rate that makes the net present value (NPV) of the expected cash flows equal to zero. The NPV is calculated using the chosen discount rate by discounting the expected cash flows back to their present value.

A higher IRR indicates that the project is expected to generate a higher rate of return on investment, making it more attractive to investors. However, the IRR alone does not provide a complete picture of the financial viability of the project, as it does not take into account the scale and risk of the investment.

Therefore, project finance professionals often use a range of financial metrics, including the IRR, to evaluate a project's potential profitability and its financial viability.

Net Present Value (NPV)

Net Present Value (NPV) is a financial metric commonly used in project finance to assess the financial viability of an investment by comparing the present value of expected cash inflows to the present value of expected cash outflows. In other words, it measures the difference between the present value of the project's future cash flows and the initial investment.

The NPV calculation considers the timing and magnitude of the expected cash flows and the cost of capital used to discount the cash flows back to their present value. As a result, a positive NPV indicates that the present value of the expected cash inflows is greater than the present value of the expected cash outflows, indicating that the investment is expected to be profitable. On the other hand, a negative NPV indicates that the present value of the expected cash outflows is greater than the present value of the expected cash inflows, indicating that the investment is not expected to be profitable.

In project finance, NPV is often used as a decision-making tool to evaluate the financial viability of a project and to compare the potential profitability of different investment opportunities. Project sponsors and lenders typically use a predetermined threshold for NPV as a benchmark for assessing the attractiveness of a project. If the NPV of the project exceeds the predetermined threshold, the project is considered financially viable and may be approved for financing.

However, it is essential to note that the NPV calculation depends on various assumptions and estimates, including cash flow projections, discount rates, and other factors, which can affect the accuracy and reliability of the calculation. Therefore, project finance professionals typically use a range of financial metrics, including NPV, to evaluate a project's financial viability and its risks and potential rewards.

FCFE to Invested Equity

FCFE to Invested Equity, also called equity cash-on-cash return, is a financial metric used in project finance to measure the cash income earned on the investment compared to the amount of cash invested in the project. It is expressed as a percentage and represents the cash income earned on the project as a proportion of the cash invested in the project.

In project finance, the cash-on-cash return is calculated by dividing the project's annual cash flow by the initial cash investment in the project. The annual cash flow used in the calculation typically includes all cash inflows from the project, including operating cash flows, tax benefits, and any other cash receipts, minus all cash outflows, including operating expenses, capital expenditures, and debt service payments.

A higher cash-on-cash return indicates that the project is generating a higher rate of return on the cash invested in the project. This metric is particularly useful in project finance because it focuses on the actual cash flows generated by the project rather than relying on accounting or other non-cash measures of profitability.

Project sponsors and investors often use the cash-on-cash return as a key performance indicator for measuring the project's financial success. It can also be used to compare the relative attractiveness of different investment opportunities and to make investment decisions based on the expected cash returns from each opportunity.

However, it is important to note that the cash-on-cash return does not consider the time value of money or other factors that may affect the project's profitability. Therefore, project finance professionals typically use a range of financial metrics, including cash-on-cash return, to evaluate a project's financial viability and its risks and potential rewards.

Equity payback period

The equity payback period is a financial metric used in project finance to measure the amount of time it takes for an investor to recoup their initial equity investment in a project. It represents the number of years required for the cumulative cash inflows from the project to equal the initial equity investment.

In project finance, the equity payback period is calculated by dividing the initial equity investment by the annual cash inflows generated by the project. 

Project sponsors and investors often use the equity payback period as a key performance indicator for measuring the project's financial success and assessing the risk of the investment. A shorter payback period indicates that the project generates cash flows more quickly and is, therefore, less risky. In comparison, a more extended payback period indicates that the project generates cash flows more slowly and is, therefore, riskier.

However, it is important to note that the equity payback period does not consider the time value of money or other factors that may affect the project's profitability. Therefore, project finance professionals typically use a range of financial metrics, including the equity payback period, to evaluate a project's financial viability and its risks and potential rewards. 

3.9.3       Unlevered Return

The unlevered section of the Valuation sheet first considers a few adjustments which need to be made to compare “apples to apples”, i.e., taking out any effects of the debt & shareholder loan capital structure.

After that, the same return figures as in the levered section are calculated on an unlevered basis.

All of these return figures then feed into the Dashboard of the model, which gives an instant overview of all key investment metrics of the project on both a levered and unlevered basis. 

3.10   Model Dashboard

The Dashboard sheet should be considered last when building and understanding a project-specific financial model. This is the heart of the model when it comes to decision-making

From a bird’s eye perspective, this sheet is the last of the third model structure block Output.

If you're looking to invest in renewable energy, you need a comprehensive financial model dashboard that summarizes all the key investment metrics relevant to your decision-making process. 

This financial model dashboard for renewable energy investments is designed to help you make informed investment decisions with ease. It includes a detailed breakdown of the project's capital structure, allowing you to understand the financing mix of the project and the risks associated with each layer of the capital stack.

The dashboard also considers critical investment metrics such as IRR and NPV on both a levered and unlevered basis, giving you a complete picture of the project's profitability.

In addition to these essential investment ratios and multiples, the dashboard details the payback periods, CFADS/EV, Revenue/EV, EV/MWp, and EV/MWh. These metrics are crucial in evaluating the financial viability of the project and determining its potential for generating returns over the long term.

The dashboard also features eye-catching charts that provide a visual representation of the project's cash flow generation over its entire asset lifetime. This feature will give you valuable insights into the project's cash flow patterns and help you make informed investment decisions.

With all these critical investment metrics and data points in one place, this financial model dashboard is the ultimate tool for renewable energy investment decision-makers.

3.11   Excel Essentials 

In the following part of the Financial Model User Guide, you can read up on Excel essentials for financial modeling purposes.

3.11.1    How to use the Name Manager in Excel?

The Name Manager in Excel is a powerful tool for creating references to certain cells and ranges. Using the Name Manager to create different macros in VBA, i.e., to break circular references, is best practice.

Where can I find the Name Manager?

To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager.

After opening the Name Manager within a new file, you will likely not see any named ranges. Below is an example of what it will look like after defining multiple named ranges within a financial model for renewable energy investments.

Instead of referring directly to a cell or range, the use of named ranges can be a substitute for directly referring to a cell or range. This practice is recommended for certain technical inputs, such as constant numbers, i.e., hours per day, hours in a year to calculate full load hours, or large numbers like a million.

Using named ranges when creating macros is essential!

When creating macros, it is crucial to only use named ranges! It is surprising how many professional financial modelers still refer to certain cells and ranges directly within the VBA interface. One slight change to the structure of the financial model will likely crash the VBA code or result in incorrect outcomes.

Deleting or inserting a row above a named range will cause a reference within VBA to be incorrect after the structural change of the model has been done. Unlike an automatic reference update within formulas that refer to certain cells or ranges, VBA cannot change such references automatically. The only way to ensure references do not change is by using named ranges. Breaking circular references caused by debt sculpting according to a target DSCR is a situation where the Name Manager comes in handy.

How do I name certain cells and ranges?

Naming cells and ranges is a rather simple exercise once you know how it's done!

Select a cell or range you would like to name.

In the Name box, type in a name for the cell or reference. You can find the name box to the left of the Formula Bar.

Press Enter. Your cell or range has been given a name that will now appear in the Name Manager.

How to refer to a named cell or range within the Excel spreadsheet?

First, we will name a cell with a constant number of hours per year. This is an important number for renewable energy investments and is used to calculate the full load hours of an energy plant.

In the above example, the constant number 8,760 hours per year in cell E7 has been named Hours_year.

After naming the cell, you can start typing a formula with an equal sign (=) and start writing Hours_year - the reference will show up and can be selected by pressing Enter.

3.11.2    XIRR Function – Financial Modeling

XIRR function - determining the Internal Rate of Return (IRR) for a series of periodically uneven cashflows.

What is the XIRR function?

The Excel XIRR function returns the Internal Rate of Return (IRR) for a schedule of cash flows that is not necessarily periodic. While the IRR function works well to determine the IRR for periodically annualized cashflows, the XIRR function assigns specific dates to each cashflow.

Therefore, the main benefit of using XIRR is that it can calculate a precise return for a series of cashflows that occurs unevenly. This is especially beneficial for calculating the return metrics of project financings and is used as best practice in the context of financial modeling for renewable energy investments.

How to use XIRR using Excel?

The XIRR function uses three inputs, of which only two are required.

1.      The first argument, values, requires the range of cashflows (both cash inflows and cash outflows) that the investment/project generates.

2.      The second argument, dates, requires the range of dates which are linked to each specific cashflow of the first range.

3.      The last argument, [guess], can help Excel to calculate the return faster but is not required to be filled in and we recommend omitting this argument when using XIRR.

What's important to consider when using XIRR?

First, the length of both ranges for the first two arguments must match exactly to ensure that Excel can link each cash flow to a specific date.

Second, the value range of cashflows does always need to start with a negative number as an investment typically requires an initial cash outflow - at least, this is how Excel thinks in the manner of this function. If the first number within the value range is not negative, the function will not work - however, there is a workaround: simply use a very small negative number for the first value. Below is an example of how the XIRR function is applied in a typical project finance model for renewable energy investments.

XIRR vs. IRR function - what's the difference?

The IRR function is much less flexible than the XIRR function and does not require any dates as input. The output is the same - the internal rate of return on the investment - however, when using the IRR function, Excel assumes that between each given value for a cashflow lay precisely 12 months.

As it is highly improbable to encounter an investment in the real world with only one cashflow at the end of each year, it is recommended to always prefer the XIRR function over the IRR function.

The XIRR function has the flexibility to consider different cashflows daily, which is why it uses daily compounding, while the IRR function uses simple annual compounding.

What does the IRR tell me as an investor?

Investors often view the IRR as the most crucial investment metric. From a mathematical standpoint, the IRR is the required discount rate for all future cashflows, which sets the investment's Net Present Value (NPV) to zero. This implies that if an investor invests specific payments in accordance with all the negative cash outflows of the input range of the XIRR function - he or she will earn the IRR as a return over the entire lifetime of the asset.

3.11.3    XNPV Function – Financial Modeling

XNPV function - determining the Net Present Value (NPV) of an investment with non-periodic cashflows.

What is the XNPV function?

The Excel XNPV function returns the Net Present Value (NPV) of an investment with non-regular cashflows. The XNPV function is much more flexible and precise than the regular NPV function, so it should always be preferred to use XNPV over NPV.

The main benefit of using XNPV is that it can calculate a precise NPV for an uneven cashflow series. This is especially beneficial for calculating the NPV of project financings and is used as best practice in the context of financial modeling for renewable energy investments.

How to use XNPV using Excel?

The XNPV function uses three inputs, all of which are required.

The first argument, rate, is the project's and/or investor's required rate of return. All cashflows which are generated over the asset's lifetime will be discounted at this rate of return.

The second argument, values, requires the range of cashflows (both positive cash inflows and negative cash outflows) that the investment/project generates.

The last argument, dates, requires the range of dates which are linked to each specific cashflow of the second range.

What's important to consider when using XNPV?

First, the length of both ranges for the last two arguments must match exactly to ensure that Excel can link each cashflow to a specific date.

Second, the value range of cashflows does always need to start with a negative number as an investment typically requires an initial cash outflow - at least, this is how Excel thinks in the manner of this function. If the first number within the value range is not negative, the function will not work - however, there is a workaround: simply use a very small negative number for the first value. Below is an example of how the XNPV function is applied in a typical project finance model for renewable energy investments.

XNPV vs. NPV function - what's the difference?

The NPV function is much less flexible than the XNPV function and does not require any dates as input. The output is the same - the Net Present Value of the investment - however, when using the NPV function, Excel assumes that between each given value for a cashflow lay precisely 12 months.

As it is highly improbable to encounter an investment in the real world with only one cashflow at the end of each year, it is recommended to always prefer the XNPV function over the NPV function.

The XNPV function has the flexibility to consider different cashflows daily, which is why it uses daily compounding, while the NPV function uses simple annual compounding.

What does the NPV tell me as an investor?

Investors often view the Net Present Value as one of the most crucial investment metrics. Generally, the rule for an investment decision is to invest in the project if the NPV is positive and not to invest if the NPV is negative.

Why invest in a project that is NPV-positive? A positive NPV implies that the investment generates a higher return than the required rate of return at which all future cashflows are discounted.

But the critical question a sophisticated investor will ask is how much return the investment generates exactly? This is where the Internal Rate of Return (IRR) comes into play.

From a mathematical standpoint, the IRR is the required discount rate for all future cashflows, which sets the investment's Net Present Value (NPV) to zero. This implies that if an investor invests specific payments in accordance with all the negative cash outflows of the input range of the XIRR function - he or she will earn the IRR as a return over the entire lifetime of the asset.

3.11.4    INDEX Function – Financial Modeling

INDEX Function in Excel: Returns a value or the reference to a value from within a table or range. Learn how to apply the INDEX function for financial modeling purposes.

What is the INDEX function?

INDEX is a powerful tool for making references within a financial model. Since the function returns values from an array depending on a selected row and, optionally, column number, INDEX is especially useful for considering different scenarios in a financial model.

How to apply the INDEX function in Excel?

The INDEX function uses three inputs, of which only two are required.

The first argument, array, requires selecting a range of cells or an array constant. If the selected array only refers to one row or one column, the subsequent row_num or column_num argument is optional.

The second argument, row_num, is required unless the third argument, column_num has an input. Row_num selects the row within the array from which to return a value. If the argument is left blank, the third argument column_num is required.

The third argument, column_num, is optional unless the second argument, row_num, is omitted. Column_num selects the column within the array from which to return a value.

How to use INDEX in a scenario manager of a financial model?

Using the INDEX function for setting up a scenario manager in a financial model for renewable energy investments is a must.

In the above example, an input sheet for time-independent variables uses the INDEX function for setting up a robust and flexible scenario manager.

When considering renewable energy investments, banks often require a different case for financing and debt sizing purposes than equity investors. Debt providers typically consider the P75 or P90 energy production value for their analysis, while equity investors rely on the P50 energy production value, reflecting a less conservative investment view.

In the above financial model, five cases are set up while a live case in column M flows through the dependent calculations and integrated financial statements. The end user can select the live case in cell M3 via a dropdown menu. The selected live case is the second argument for the INDEX function. The first argument is simply the row of the respective input line. 

In the above example, this is the P-value for the energy generation selection in row 104, resulting in an INDEX(O104:S104,$M$3) function, which will return P50 given that the second case is selected. 

3.11.5    NORMINV Function – Financial Modeling

NORMINV Function in Excel: Returns the inverse of the normal cumulative distribution considering two inputs - the specified mean and standard deviation.

What is the NORMINV function?

The NORM.INV function assumes a normal distribution and returns any P-value on this normal distribution using a given mean and standard deviation - also called uncertainty.

How to apply the NORMINV function in Excel?

The NORM.INV function uses three inputs, of which all three are required.

The first argument, probability, is a probability corresponding to the normal distribution. Using this input, Excel is essentially being told what P-value on the normal distribution shall be returned. Attention! Excel will return the inverse of the normal cumulative distribution! So if we would like to determine the P90 value, we would have to use the P10 value as input for the probability since P90 is the inverse of P10.

The second argument, mean, requires the arithmetic mean of the distribution. This is the P50 energy production value of the power plant.

The third argument, standard_dev, requires the standard deviation of the distribution. This is the energy production uncertainty, typically higher for wind farms than for solar parks.

How to use the NORMINV function to determine P75, P90, and any other P-value of energy production?

Using the NORM.INV function correctly in an energy production-related context is not super intuitive - so let's see how it's done in a best practice financial model for renewable energy investments.

In the above example, we consider a wind farm with 2,400 full load hours per year. So our mean energy production on a P50 basis is 2,400 MWh/MW - this is the input "mean" for the second argument of the formula.

We also know that the standard deviation, here referred to as uncertainty, is 12%. The mean and uncertainty are all we need to determine any other P-value.

For the first argument of the formula "probability", Excel needs a percentage input, more precisely, any value between 0 and 1. In the above model, the P-value input cells have been formatted so that a "P" appears in front of the value, but actually, the cells only contain the numbers 50, 75, and 90.

For the P75 value, the first argument essentially reads "(1-(75/100))". Since Excel returns the inverse cumulative distribution, we need to subtract  75% from 100% to arrive at the P75 value as output.

For the second argument, "mean", a simple reference to the cell containing 2,400 MWh/MW is sufficient.

The last argument, "standard_dev", has to be converted to the same unit as the mean. Given that the mean has been provided as an absolute input in MWh/MW, we need to multiply this mean with the 12% uncertainty in energy production.

After carefully setting up the function, Excel returns 2,206 and 2,031 MWh/MW for the P75 and P90 values, respectively.

3.11.6    EOMONTH Function – Financial Modeling

EOMONTH Function - returns the last day of the month, which depends on the number of months before or after a particular start date.

What is the EOMONTH function?

The EOMONTH function returns the serial number for the last day of the month, that is, the indicated number of months before or after a selected start date.

If you use the function without pre-formatting the cell in which you apply the formula, you might be surprised why Excel will return a simple serial number. Excel starts counting the days from the first of January of the year 1900 - so if you format the number 1 to a date format, it will return this date, as you can see below.

So before using the EOMONTH function, make sure you format your selected cell to a date format of your choice. To format a cell, you can either right-click the cell and select "Format Cells..." or speed up your modeling skills and use the shortcut CTRL + 1.

How to apply the EOMONTH function in Excel?

The EOMONTH function uses two inputs, of which both are required.

The first argument, start_date, is a reference start date.

The second argument, months, requires the months before or after the reference start date. Excel can return both a date before and after the reference start date. A positive value for months will return a date in the future compared to the reference date, while a negative value for months will return a past date compared to the reference date.

Example of how to apply the EOMONTH function using best practices.

When creating a timeline for a financial model, a proper application of dates is key to keeping a model clean, flexible, and robust.

Below you can see how the EOMONTH function is applied within a timeline of a financial model for a renewable energy investment. Typically models for renewable energy assets have a quarterly timeline which is why we want the formula to result in the end of a given quarter, referring to a given start date of the financial quarter.

In the above timeline, the start_date for the EOMONTH function refers to the first of January 2023. So our goal for the function is to result in the last day of March.

Within the function's second argument, we need the number which will result in our goal - the end of the year's first quarter. Applying the number "0" would result in the end date of the month of the same month as the reference date. So we will need to apply the number "2" for the second argument to achieve our goal.

You might be surprised why there is a named range "Months_qtr" within the second argument in the formula above. It is best practice to name certain constants, such as the number of months per quarter, as technical inputs within a financial model. Technical inputs help with the robustness of the financial model - the Name Manager is a handy tool to keep an overview of named ranges and technical inputs. For more information regarding the Name Manager read section 3.11.1 “How to use the Name Manager in Excel?” on page 73.

So in our case, we put in the months per quarter, which will return in the number "3" which is why we need to subtract "1" for the formula to result in the end of March 2023.

You might think this is quite some work to follow all these best practices, so why not simply put the numbers directly in the function? Naming certain ranges will give you much more flexibility if you ever want to change certain inputs. Instead of manually adjusting hundreds of different cells - which is also prone to error - you can simply adjust one technical input cell.

3.11.7    How to use flags in financial models?

Using flags in Excel is a powerful tool for improving financial modeling timelines. Best practice financial models for project finance, such as renewable energy financial models, require a financial modeler to master flags.

So what is a flag?

Using Excel, a flag is a cell with a binary output - the cell either contains the value 1 or 0. Consequently, flags come in handy to turn a formula on or off by multiplying the respective formula with the flag.

Modeling clean and robust timelines with flags.

To properly display timelines in a financial model, using flags is a must. Below is an example of a best practice timeline in a project finance model for renewable energy investments. The green flags display the construction period, while the blue ones represent the operational period of the solar park.

The user can easily grasp that the construction of the solar park takes place in the full year of 2023, and thereafter, the park's operations start.

How to model a flag?

There are different types of flags, but we will take you through modeling the most common ones using best practices.

1. Flags for modeling a timeline.

In the above example, all we need to model the construction and operations flags is the start and end dates. To ensure that the dates are always properly reflected, combining quarterly or annual inputs with the EOMONTH function is recommended.

Once the dates are set up in an input sheet, they can be pulled into the timing sheet. To model the flag, we want the output to be 1 if the date falls between the start and end date. Otherwise, we want a 0 to be displayed.

Using an IF function in combination with an AND function will do the trick. If the start date of the model timeline is equal to or larger than the input start date AND if the end date of the timeline is equal to or smaller than the input end date, a 1 should be returned; otherwise, 0.

Modeling timelines in combination with flags is the most common use for a flag. It is a powerful and clean tool to turn on calculations such as revenue streams, operational expenditures, power generation, debt repayment, and much more.

Below is another example of how useful flags can be when modeling sculpted debt repayment structures.

The end user can easily see the underlying timelines for the commitment period, the loan tenor, the grace period, and the debt repayment period.

2. Flags for one-time events.

Flags can be very helpful for one-time events such as the initial funding of the Debt Service Reserve Account (DSRA). In the below screenshot, you can see that the initial funding of the DSRA occurs as a one-time event in the last construction quarter.

3. Flags for checks of the financial model integrity.

Flags are also helpful in discovering errors within a financial model. In a best practice model a balance sheet check is a must. If there is a discrepancy between net assets and equity, a flag can help to easily visualize which period causes the error within the model.

How to use conditional formatting to display a flag properly?

To open Conditional Formatting, on the Home tab, in the Styles group, click Conditional Formatting.

Using conditional formatting, a rule can be implemented stating that if a cell contains the value 1, then certain formatting shall apply.

After the rule has been implemented as above, you can adjust the formatting by pressing Format.

First, formatting the borders will ensure that the flags visually stand out from the other cells.

Second, formatting the Fill with a distinct pattern style and color will ensure that everyone using the model can spot active flags instantly.

3.11.8    LOOKUP Function – Financial Modeling

The LOOKUP Function in Microsoft Excel is a versatile function that can retrieve data from a table or range of cells based on a specified value. There are three types of LOOKUP functions in Excel: the VLOOKUP function, the HLOOKUP function, and the LOOKUP function.

We will guide you through how to use all three LOOKUP functions and which one to prefer when building a best-practice financial model for renewable energy investments.

VLOOKUP Function

The VLOOKUP function (vertical lookup) searches for a value in the first column of a specified table and returns a value in the same row from a specified column. Here's the basic syntax for the VLOOKUP function:

where:

·        lookup_value is the value you want to search for in the first column of the table.

·        table_array is the range of cells that contains the table.

·        col_index_num is the column number in the table from which you want to retrieve the value.

·        [range_lookup] is an optional argument that specifies whether you want an exact match (0 or FALSE) or an approximate match (1 or TRUE). The default value is 1 (TRUE).

HLOOKUP Function

The HLOOKUP function (horizontal lookup) searches for a value in the first row of a specified table and returns a value in the same column from a specified row. Here's the basic syntax for the HLOOKUP function:

where:

·        lookup_value is the value you want to search for in the first row of the table.

·        table_array is the range of cells that contains the table.

·        row_index_num is the row number in the table from which you want to retrieve the value.

·        [range_lookup] is an optional argument that specifies whether you want an exact match (0 or FALSE) or an approximate match (1 or TRUE). The default value is 1 (TRUE).

LOOKUP Function

Unlike the VLOOKUP and HLOOKUP functions, the LOOKUP function is not limited to searching for data in a single column or row and can search data vertically as well as horizontally.

Here's the basic syntax for the LOOKUP function:

where:

·        lookup_value is the value you want to search for in the lookup vector.

·        lookup_vector is the range of cells that contains the values you want to search.

·        result_vector is the range of cells that contains the values you want to return based on the lookup.

To use the LOOKUP function in Excel, simply enter the function into a cell and specify the required arguments. For example, if you have a table of data in cells A1 to B10, and you want to retrieve the value in cell B5 based on the value in cell A5, you could use the following formula: =LOOKUP(A5, A1:A10, B1:B10).

Why is the LOOKUP function the best option for building a robust financial model?

The LOOKUP function is considered to be better to use and less prone to errors than the VLOOKUP and HLOOKUP functions for several reasons:

1.     Simplicity: The LOOKUP function is simpler and easier to use than the VLOOKUP and HLOOKUP functions, as it requires fewer arguments.

2.     Flexibility: The LOOKUP function can search data both vertically and horizontally, while the VLOOKUP and HLOOKUP functions are limited to searching data in a single column or row.

3.     Error-resilience: The LOOKUP function is less prone to errors, as it automatically returns the closest match if an exact match is not found. In contrast, the VLOOKUP and HLOOKUP functions can return an error if an exact match is not found and the [range_lookup] argument is set to 0 or FALSE.

4.     Performance: The LOOKUP function is faster and more efficient than the VLOOKUP and HLOOKUP functions, as it can handle large amounts of data more quickly.

In conclusion, the LOOKUP function in Excel is a powerful and flexible function that can retrieve data from a table or range of cells. It is considered to be better and less prone to errors than the VLOOKUP and HLOOKUP functions, making it a useful tool for anyone who needs to search for and retrieve data in Excel.

3.12   Renewables Essentials

3.12.1    How to value a wind farm or solar plant?

How to value a wind farm or solar plant - is the fair valuation simply the price paid for the asset, or do investors view the value of a renewable energy asset differently?

When approaching 100 different potential investors for a large-scale renewables investment, the seller of the asset will likely receive 100 different pricing offers. The reason for such a diverse valuation of the same asset is that most investors use a discounted cash flow (DCF) approach to determine the fair value of an investment. So how to value a wind farm or solar plant properly?

DCF analysis is a valuation methodology that helps to determine the fair value of an investment today by discounting all future cash flows a project will generate using a required rate of return, also referred to as the internal rate of return (IRR) of the project.

DCF analysis at a glance

• Discounted cash flow analysis supports determining a fair value of an investment based on its future cash flows.
• Future cash flows are discounted at an investor's required rate of return to arrive at the present value.
• From a project developer's perspective, it only makes sense to realize the project if the fair value - according to DCF - exceeds the project's cost.
• Using leverage (debt) can help increase an asset's enterprise value, assuming that the equity IRR requirements increase at a slower rate than the extra cost of additional debt.
• A significant uncertainty using this valuation approach is the unpredictability of precise future cash flows.

Capital structure - a significant value driver for renewable energy investments

The capital structure is one of the most important value drivers.

1. Lower tax payments: The use of debt financing increases interest costs which are deductible within the income statement and ultimately lead to lower earnings before tax (EBT) which lowers the overall tax bill for the asset.
2. Use of shareholder loans: Sophisticated investors use shareholder loans within their capital structure. Essentially, a shareholder loan (SHL) is a loan given by an equity investor to the project's special purpose vehicle (SPV). Since the loan is subordinate to the debt loan from a bank, the shareholder loan interest is often even much higher, ultimately leading to even higher tax savings. The maximum SHL interest rate and gearing are often limited by fiscal authorities depending on different jurisdictions - a tax advisor should always be consulted when implementing such structures.
3. Increase of equity IRR through leverage: Typically, the cost of debt financing is much cheaper than the cost of equity. Therefore, if a project can take on a significant portion of the debt, this increase in leverage - also referred to as gearing - can increase the equity IRR significantly. Institutional investors often require a minimum rate of return which implies that if the overall equity IRR can be increased using debt, the asset's enterprise value can be improved until the equity IRR is back to its minimum rate of return.

Below is the valuation dashboard used in our Advanced Renewable Energy Financial Modeling course. This online video course teaches you how to build the financial model covered in this Financial Model User Guide with advanced capital structures from scratch.

What other fundamental value drivers are of importance?

• Source of revenue: The projected revenues highly depend on future cashflows. Such cashflows can typically come from merchant revenue, bilaterally concluded power purchase agreements ("PPAs"), or state-subsidized feed-in tariffs ("FiTs").
• Energy yield: Renewable energy plants produce electricity - technically referred to as kWh. This electricity is sold to create a source of revenue, so it is essential to know how much electricity the project will generate. Equity investors typically consider a P50 value, while debt investors consider more conservative production assumptions such as the P75 or P90 value.
• Bankability: The bankability of a project depends on many different factors. However, the source of revenue is a significant driver of how much debt the bank is willing to give out. The more revenues are contractually fixed, the more de-risked the project will be, ultimately leading to more willingness by banks to give out debt. If a fixed price PPA is concluded, it is also of importance whether it has a pay-as-produced or baseload structure underlying.
• Operational expenditures: Renewable energy assets have several different operational expenditures such as O&M, land lease, commercial & technical management, insurance, and route-to-market costs, which all reduce the future cashflows in the DCF model.
• Inflation: In the long term, inflation will affect both - revenue streams and operational expenditures.

3.12.2    EPCM vs. EPC agreement – what’s the difference?

EPC stands for Engineering, Procurement, and Construction, and an EPC agreement in a renewable energy context refers to a contract between a developer and a contractor in which the contractor agrees to design, procure, and construct a renewable energy project such as a solar farm, wind farm or a hydropower plant. The EPC contractor is responsible for providing all labor, materials, and equipment required to build the project and to bring it to a fully operational state, often within a fixed budget and timeline. The EPC agreement typically outlines the scope of work, schedule, and payment terms for the project.

What is a full-wrap EPC?

A full-wrap EPC to an agreement between the developer and a contractor in which the contractor provides a comprehensive, turn-key solution for the development and construction of a renewable energy project. In a full-wrap EPC arrangement, the contractor takes full responsibility for all aspects of the project, from design and procurement to construction and commissioning, and provides a single point of contact for the developer. The full-wrap EPC agreement typically includes the provision of all necessary materials, equipment, and labor to build the project, and the contractor is responsible for ensuring that the project is completed on time, within budget, and to the agreed-upon specifications and standards. The full-wrap EPC approach provides a one-stop solution for the developer, allowing them to outsource all project risks and responsibilities to the contractor, which can be useful for complex or large-scale projects.

What is the difference between an EPC and EPCM contract for renewable energy projects?

EPC (Engineering, Procurement, and Construction) and EPCM (Engineering, Procurement, and Construction Management) contracts are both used for the development and construction of renewable energy projects. Still, there is an important distinction between the two in terms of the scope of work and responsibilities of the contractor.

In an EPC contract, the contractor takes full responsibility for designing, procuring and constructing the renewable energy project. The contractor is responsible for delivering the project on time, within budget, and to the agreed-upon specifications and standards. The developer typically provides a defined scope of work and sets the budget for the project, but the contractor is responsible for all aspects of its delivery.

In an EPCM contract, the contractor provides construction management services to the developer rather than taking full responsibility for the project. The developer retains control over the design and procurement of the project, while the contractor provides management and supervision of the construction process. The EPCM contractor provides expertise and oversight to ensure that the project is built to the agreed-upon specifications and standards, but the developer is responsible for the actual construction work and procurement of materials and equipment.

In summary, an EPC contract provides a turn-key solution for the developer, while an EPCM contract provides support and expertise for the developer to manage the construction process themselves.

EPC vs. EPCM – What’s the best solution for my project?

Advantages of EPC (Engineering, Procurement, and Construction) contract:

1.      Turn-key solution: The contractor takes full responsibility for delivering the project, providing a one-stop solution for the developer, and minimizing their involvement in the construction process.

2.      Single point of contact: The contractor acts as a single point of contact for the developer, which can simplify communication and coordination.

3.      Reduced project risk: The contractor assumes all the construction risks, including cost and schedule overruns, allowing the developer to focus on other aspects of the project.

4.      Improved project quality: The contractor is responsible for delivering the project to the agreed-upon specifications and standards, which can lead to improved project quality.

Disadvantages of EPC contract:

1.      Limited control: The developer has limited control over the construction process, which can reduce their ability to make changes or take corrective action if needed.

2.      Dependence on the contractor: The project's success is largely dependent on the competence and performance of the contractor, which can create additional risks for the developer.

3.      Higher cost: EPC contracts are typically more expensive than EPCM contracts, as the contractor takes on more responsibility and risk.

Advantages of EPCM (Engineering, Procurement, and Construction Management) contract:

1.      Increased control: The developer retains control over the design and procurement of the project, allowing them to make changes or take corrective action if needed.

2.      Lower cost: EPCM contracts are typically less expensive than EPC contracts, as the contractor is providing only construction management services rather than taking full responsibility for the project.

3.      Improved project flexibility: The developer has greater flexibility to change the project scope or make other modifications, which can be useful in an evolving market.

Disadvantages of EPCM contract:

1.      Increased project risk: The developer assumes all the construction risks, including cost and schedule overruns, which can be a significant disadvantage.

2.      Multiple points of contact: The developer is responsible for coordinating with multiple suppliers and contractors, which can create additional management overhead.

3.      Reduced project quality: The developer is responsible for delivering the project to the agreed-upon specifications and standards, which can lead to reduced project quality if they do not have the necessary expertise.

In conclusion, the choice between EPC and EPCM contracts depends on the specific needs and requirements of the renewable energy project, including budget, risk tolerance, and the level of control desired over the construction process.

3.12.3    What’s the difference between kW and kWh?

While the units kW and kWh may seem very similar, they are two fundamentally different things, especially when it comes down to modeling a renewable energy investment. Still, many people in the renewables industry seem to get confused about this topic. Talking to clients, colleagues, or supervisors, mixing up kW and kWh may make the difference between sounding like an expert or a rookie.

So what's the difference between kW and kWh?

The simple difference between kW and kWh is that a kilowatt (kW) measures power, and a kilowatt-hour (kWh) measures energy, or more specifically, electricity.

Power (kW) is the rate at which a wind farm, solar plant, or any other renewable energy asset or conventional power plant produces electricity. It is also the rate at which your kitchen appliances, television, or computer use electricity.

While power (kW) represents the electricity usage rate, electricity (kWh) reflects the total amount of energy used over a specific period. Ultimately, it all comes down to time! To make it simple to understand, let's look at an example.

How much electricity does your rooftop solar plant produce in one year?

Let's suppose that you just decided to invest some of your savings in a brand new rooftop solar plant for your house. The plant's total installed capacity (or power) is 10 kWp. By the way, the power of a solar plant is measured in kWp (kilowatt peak), which is essentially also a unit for power only with a further solar-related specification. A kilowatt peak (kWp) is the maximum power produced by a solar plant under standard temperature and sunlight conditions. The kilowatt peak specification is only relevant for solar plants, not for wind farms, here we simply refer to kW.

So how much electricity will my solar plant produce?

Well, this depends on where you live. If you live in sunny California, your solar plant might be able to run at 2,000 full load hours per year, while if you live in Germany, the same solar plant would most likely only run at 1,000 full load hours per year. So if you live in California, the same solar plant would produce 10 kWp x 2,000 hours = 20,000 kWh per year. While in cloudy Germany, you would end up with only 10 kWp x 1,000 hours = 10,000 kWh at the end of the year. Seems logical, right? If there are more sunny hours during the year, your solar plant should produce more electricity.

3.12.4    How to model P50, P75, and P90 energy yield?

How to model P50, P75, and P90 energy yield? When estimating future cash flows for wind or solar investments, forecasting the energy yield is one of the most critical steps in building a reliable business plan, i.e., financial model. As the production values of renewable energy assets vary from year to year, investors look at different probability figures, i.e., P50, P75, and P90 values.

Probability figures P50, P75, and P90

According to the Gaussian distribution function, annual energy yields of renewable energy assets are normally distributed. Therefore, when analyzing the energy yield of an investment, the P50 value is the median of energy production, which essentially means that 50% of the time, the forecasted production value is expected to be exceeded. In comparison, over the remaining 50% of the time, the forecast is expected to be lower than the estimated P50 value. This is why equity investors most often consider the P50 production figure in their base case, as this value is statistically most accurate to reflect the actual energy production over a given asset lifetime.

However, debt investors typically are willing to take fewer risks, which is why they mainly consider the P90 or even more conservative production estimates in their forecasts. Consequently, when considering P90 values, debt investors would expect the P90 production estimate to be exceeded 9 out of 10 times when looking at an operation period of, for instance, 10 years. Especially when debt sculpting according to a DSCR target is used, debt investors typically consider the P90 production figure.

How to calculate the P50 value?

As a project developer, one critical step in developing wind or solar assets is the estimation of the expected energy yield and its variability, i.e., uncertainty. When developing a wind farm, wind measuring campaigns of at least one year usually take place at the expected project's site. Subsequently, technical advisors use such data in conjunction with historical data from surrounding areas as input for numeric forecasting models to determine the expected P50 value and its uncertainty.

From gross yield to net yield

After running the above-mentioned numeric forecast models, technical advisors will develop an estimated gross energy yield. This yield will then be adjusted by different factors such as plant availability, electrical losses, wake losses (only wind), maintenance losses, curtailment losses, degradation, and other factors. Finally, after considering all such losses, we will come to our estimated net energy yield production, representing the actual energy expected to be produced by our renewable energy asset. Therefore, this figure is one of the primary value drivers of any renewable project and ultimately flows into the business plan or, better, the financial model.

What defines the spread between P50 and P90 values?

To reliably forecast a normal distribution of estimated energy yield, all that is needed is the median of the distribution, i.e., the P50 value and the distribution's uncertainty. Knowing the distribution's uncertainty, which essentially is a statistical term for the variability of estimated production figures, allows us to calculate any other P-value, may it be the P75, P90, or even P99.

How to model P50, P75, and P90 production figures using uncertainty?

Microsoft Excel can model any normal distribution with the NORM.INV function using two inputs only. All you need is a median (P50 value) and the uncertainty of the distribution.

3.12.5    How to model seasonality of renewable energy revenues?

In a project finance deal, the seasonality of renewable energy revenues has a significant impact on monthly cashflows. Consequently, seasonality needs to be considered in a best-practice financial model for renewable energy investments.

What is the seasonality of renewable energy?

Everyone is aware of seasonal weather patterns throughout the year, especially the further you live away from the equator, the more severe the seasons will be. This seasonal effect impacts the production of renewable energy power plants.

Luckily, solar and wind production is at their energetic peaks at opposite periods of the year. While solar parks are at peak production during the summer months, wind farms produce most electricity in the winter.

How to model the seasonality of renewable energy?

Modeling seasonality in a monthly cash flow model is rather straightforward, while properly reflecting the seasonal effect in a quarterly model can be rather tricky if the model should keep its full flexibility.

First, irrespective of a monthly or quarterly model, the inputs for the seasonal production need to be set up on a relative basis throughout the year.

Above is an example of how a best-practice model for a wind investment reflects the seasonal inputs on a relative basis for each month. You may notice that in the above case, the strongest energy production occurs during the winter months. 

Typically, the technical advisor of a transaction will provide the required seasonal input for the financial model. A check within the input sheet in the model ensures that the seasonality always sums up to 100%.

First, let's look at the more straightforward modeling of the seasonality in a monthly model.

You will immediately notice that implementing flags is a must to transparently model the seasonality effect of renewables. Afterward, the monthly generation profile percentage will simply be multiplied by the annual energy production.

Setting up the flags for a quarterly model is much tricker and requires careful consideration of different periodic effects.

The formula for the flags for the quarterly model ends up lengthy and complex. Consequently, this implementation may seem not like best practice modeling. However, this implementation is necessary to ensure the robustness and flexibility of the model so that any start period of the model can be considered.

Why even consider the seasonal effect in such detail?

Lenders are keen on knowing the exact monthly cashflows that a project may generate as loan repayments typically occur more than once a year, i.e., on a quarterly or semi-annual basis.

Seasonality has a significant impact on the cash flow available for debt service (CFADS). Therefore, this seasonal impact can already be considered when determining the repayment profile of a loan through the use of debt sculpting.

3.12.6    What is degradation and how to model it?

Degradation reduces the electricity output of solar panels over time. These losses must be considered when building a financial model for a solar plant.

What exactly is degradation?

Nowadays, high-quality solar panels degrade at a rate of approximately 0.25% - 0.50% per year, which reduces the overall electricity output of a solar plant by 8.0% - 15.0% over a 30-year asset lifetime. This reduction in electricity output should underline why considering degradation in a financial model for renewable energy investments is so important.

There are different forms of degradation. Broadly speaking, they can be distinguished between mechanical and chemical causes from the solar module's exposure to light.

1. Light-induced degradation (LID): In the first days of solar panels being exposed to sunlight, the panels' efficiency typically falls by approximately 1.0%. In addition, during the construction period, shipping and mounting effects can also lead to a slight reduction in the power output caused by micro-cracks in the panels.
2. Potential-induced degradation (PID) & Light and elevated temperature-induced degradation (LeTID): Nowadays, PID-free & LeTID-free modules are available. Unlike LID - PID & LeTID can be avoided and do not necessarily affect all solar modules. PID occurs if the voltage of different module components, such as the frame and cells, operate at slightly different voltages, resulting in voltage leaks, ultimately reducing the overall energy output.
3. Age-related degradation is the most important consideration when building a business case for solar parks in a financial model. High temperatures and exposure to sand, snow, ice, and rainfall result in micro-cracks, corrosion, cell contamination, and hardening of the crystalline silicon.

Overall it is reasonable to assume 1.0% of degradation for the first year of operation and long-term losses of 0.25% - 0.50% p.a. - however, we strongly recommend consulting a technical advisor to conduct a site-specific analysis for estimating degradation losses.

How to model degradation losses in a financial model?

First, setting up proper modeling flags in Excel to activate different loss assumptions for different periods is important.

In the above example, flags have been implemented to activate periods of different loss assumptions. Thereafter, a short-term loss of 1.0% p.a. is assumed for the first operational year, while the long-term loss assumption due to degradation is assumed at 0.25% per annum.

3.12.7    What are full load hours and what is a capacity factor?

What are full load hours?

First of all, there are 8,760 hours in one year. This is a very important number within the renewables industry. If we go back to our solar plant in California, you might think there must be more than 2,000 sunny hours per year in California! And you are absolutely right!

While there might be about 3,000 sunny hours per year in a southern Californian city, your solar plant would primarily produce electricity (kWh) at a rate (kW) that is lower than its full capacity. Only during very sunny hours, right around noon, your plant will run at its full capacity (10 kWp). During the morning and afternoon hours, your plant might only run at half its capacity, i.e., 5 kWp. This would imply that during such hours your plant only produces 5 kWp x 1 hour = 5 kWh vs. 10 kWp x 1 hour = 10 kWh during the midday hours.

Full load hours make it much easier to interpret the electricity production potential at any given location. As the name implies, full load hours are the number of hours per year when a renewable energy asset produces electricity at its maximum capacity, i.e., installed capacity.

What is a capacity factor?

The capacity factor is a relative interpretation of full load hours. For example, a Swedish wind farm with an installed capacity of 12 MW (12,000 kW) and 3,000 full load hours would have a capacity factor of 3,000 full load hours / 8,760 hours per year = 34.2%. As a straightforward interpretation, our wind farm runs 34.2% of the year at its full capacity (12 MW), while during the remaining 65.8%, it would not produce any electricity at all.

Thus, the installed capacity is irrelevant to calculate the capacity factor. All we need to know is the number of full load hours.

3.12.8    PPA Offtake Agreements

Pay-as-produced vs baseload PPA - Bilaterally concluded power purchase agreements ("PPAs") are becoming increasingly popular in the renewables industry. Many state-subsidized feed-in tariff programs ("FiTs") are slowly phasing out as governments observe that most renewable energy projects are economically viable without government-backed support regimes. However, this challenges renewables developers to provide long-term cashflow certainty, especially when it comes to providing a solid business plan to banks when seeking attractive project financing terms. This is where PPAs come into play.

So what exactly is a PPA?

Power purchase agreements are contractual agreements between two parties, one party selling electricity ("Generator") and one party buying it ("Offtaker"). Typically, PPAs are concluded over extended periods between 5 and up to 20+ years.

Energy seller = Generator

Energy buyer = Offtaker

What kind of PPAs exist?

PPAs are typically bilaterally concluded, and as of today, there are no specific regulations in place that would require standardized contractual framework agreements. Therefore, each PPA might have unique characteristics when it comes to contractual details. That being said, there are still four general categories into which typical PPA agreements fall:

1. Pay-as-produced PPA
2. Annual baseload PPA
3. Monthly baseload PPA
4. Fixed-shape PPA

So let’s take one at a time.

What is a pay-as-produced PPA?

A pay-as-produced ("PAP") PPA is the most common and plain vanilla structure for an electricity offtake agreement. Under a pay-as-produced structure, the energy buyer or Offtaker is obliged to purchase the entirety of the produced electricity at a pre-determined fixed price. Sometimes only a particular portion of the electricity is sold under the fixed PPA price (often 70% of the produced volume), while some exposure to merchant revenues remains. Nonetheless, the Offtaker is required to buy all electricity. In contrast, the merchant exposure (i.e., the electricity portion without a fixed price) is contractually often linked to the relevant electricity price spot curve, which is revised at regular intervals to determine the geometrically weighted average of the fair merchant price.

The advantage of pay-as-produced structures is that projects provide price certainty during the contractual tenor and are typically favored by banks because of price and volume certainty. The disadvantage is that the security of such structures comes at a discount compared to other offtaking structures. Most PPA projects are nonetheless realized with a pay-as-produced structure as banks are willing to offer a higher gearing ratio which ultimately maximizes the equity IRR for the developer and/or investor.

What is an annual baseload PPA?

The key difference between a pay-as-produced and annual baseload PPA is that under a baseload structure, the generator guarantees and is obligated to deliver a certain amount of energy (i.e., 10 MWh) for each hour of the year. Therefore, profile and volume risks sit with the generator (seller), and the offtaker (buyer) has much more certainty regarding the amount and timing of electricity received under the contract. For this certainty, the offtaker is willing to pay more than under a pay-as-produced structure.

This increased price per MWh comes at a risk of shortfall or overproduction of the generator during a particular hour. In case of a shortfall in production, the generator is obligated to buy electricity in the spot market at potentially much higher prices than received under the fixed price in the baseload PPA. Contrary, in the case of overproduction during a particular hour, the generator might be forced to sell the surplus of electricity at low spot prices.

Such baseload structures are hazardous for solar PV plants. During the night and hours of no sunshine, these assets are not producing any electricity, which comes at a significant risk of potentially being forced to buy the electricity at high prices in the spot market and sell it at a lower price under the baseload PPA. During 2022, considerably higher volatility within all essential energy markets across the globe was observed, and generators increasingly faced distressed renewables assets under certain baseload obligations.

Banks often do not tolerate this increased risk and are unwilling to finance annual baseload structures. If they do, they want to be compensated through higher interest rate margins, higher requirements for minimum DSCRs, and lower allowable overall asset gearing.

What is a monthly baseload PPA?

A monthly baseload PPA has the same characteristics as an annual baseload PPA, the only difference is that the seasonality of energy production is considered each month. In this PPA structure, the offtaker agrees to buy different amounts of energy for each month of the year. Nonetheless, each hourly energy delivery obligation is the same for a particular month, which makes this structure more tolerable for wind farms but is still not ideal for solar assets.

What is a fixed-shape PPA?

A fixed-shape PPA can be viewed as a hybrid between a pay-as-produced PPA and a baseload PPA. While the delivery obligations are still swapped to the generator, the offtake structure is significantly derisked as the fixed-shape PPA considers expected seasonality for each hour of the year separately, making it much more suitable for solar PV assets.

PPAs - pros and cons

While PPAs give price certainty over the contractually concluded period, this certainty in predictable cash flows typically comes at a significant discount against forecasted merchant prices. Offtakers are usually only willing to enter into PPA offtake agreements to save money in the long run. Historically and depending on the jurisdiction, a 20 - 30% discount was observable between observable spot prices at the time of contractual conclusion and the fixed price underlying the PPA. So why even conclude a PPA as an investor? Most debt investors are only willing to enter into project finance agreements if they have some certainty of future cash flows in the form of fixed-price PPAs. Often, there is no project debt financing available based on a full merchant business case unless the financing conditions are substantially more conservative, i.e., higher minimum DSCRs and higher interest rates. Consequently, if equity investors want to use higher gearing, i.e., sculpted debt financing, to maximize their return, they are often forced to enter into such agreements.

3.12.9    How to model sculpted debt financing with DSCR target?

Sculpted project debt financing according to a target debt service coverage ratio (DSCR) is essential to maximizing project financing and thus equity return. While traditional debt repayment structures like an annuity or a linear repayment structure have the same or decreasing repayment profiles over a specific period, debt sculpting can be used to adjust debt repayments considering the unique cashflow profiles of any renewable energy project.

Debt Service Coverage Ratio (DSCR)

The debt service coverage ratio (DSCR) determines how much the cash flow available for debt service (CFADS) exceeds the total scheduled debt service, i.e., debt principal and interest, in any given period. Depending on the project's riskiness, i.e., the uncertainty of energy production or contractual basis of cash flows, debt financing banks are willing to give specific minimum DSCR targets for sculpted repayment structures.

DSCR (t) = CFADS (t) / Debt Service (t)

A solar PV plant in Spain with low uncertainty in energy production and a government-backed long-term remuneration scheme according to a feed-in tariff (FiT) might have a minimum DSCR target of 1.30x on a P50 basis. However, a wind farm in Finland with high uncertainty and volatility in energy production and no contractually backed cash flow offtake-agreement in place (i.e., no feed-in tariff (FiT) nor Power Purchase Agreement (PPA)) might have a minimum DSCR target of 1.70x or higher on a P90 basis.

The lower the DSCR target, the higher the potential gearing ratio (use of relative debt financing) for a given project.

In the figure below, you can see what a cash flow waterfall according to a minimum DSCR target of 1.40x on a P90 basis may look like for a renewable energy investment.

You will encounter a circularity within your financial model when modeling a sculpted debt financing structure if the iterative calculation feature of Excel is turned off. Learn how to implement sculpted debt structures within a financial model and understand how to break such circular references in our step-by-step video course.

3.12.10 How to model a Debt Service Reserve Account (DSRA)?

The Debt Service Reserve Account (DSRA) protects a lender against an unexpected decrease in cash flow available for debt service (CFADS). The DSRA ensures that the debt provider receives the debt service in a given period, even though the operational cash flow falls short of sufficiently covering the debt service. This cash reserve account is usually established during the construction period or filled up with the target amount during the first months of operation.

The DSRA releases cash each time the debt service coverage ratio (DSCR) falls below 1, specifically when there is a gap to be billed between CFADS and debt service (interest and principal payment). During the negotiation and finalization of the facility agreement between the project SPV and the lender, a target profile for each period within the lending period is predetermined using a financial model. As a result, the DSRA is typically dimensioned to cover six months of forward-looking principal and interest payments.

How to implement a Debt Service Reserve Account within a financial model?

Integrating a DSRA into a financial model is rather complex, as different cash releases and funding movements must be considered. In addition, the implementation of the DSRA might cause an interdependency of different variables resulting in a circular reference that has to be broken using a macro similar to the debt sculpting procedure in accordance with a target DSCR.

Below is an example of how a DSRA might be implemented within a financial model for renewable energy investments using market best practices.

Cash movement considerations for modeling purposes

The above example shows the different cash movements that need to be considered for adequately modeling the cash account. So let's take it one at a time!

1. Initial funding from sources: The first line item in the account is the funding from sources which occurs only once at the end of construction. In our example above, the initial funding is approximately 359,000 EUR. However, you might notice that the balance quickly rises to over 1.5 mEUR within the first two years of operation. Due to a grace period of 12 months, the debt service in the first operational year consists of interest payments only, resulting in a low initial funding need, given a target DSRA balance of 6 months of forward-looking debt service.
2. Funding from cash flow: Once construction is over and the solar park or wind farm is operational, the funding will occur from operational cash flow. At the end of each operational quarter, the 6 months of forward-looking debt service are considered to determine potential additional funding needs. If additional funding needs are required, this gap will be filled only if sufficient cash flow for the DSRA is available. In the model above, you can see how this funding need is calculated at the bottom of the screenshot, which flows into line item 1080.
3. Release due to cash flow shortfall: If the project does not generate sufficient operating cash flow in a certain period, there will be a release from the DSRA to cover the shortfall in debt service. Once the project reaches sufficient cash flows again, the DSRA will be filled up again in the periods thereafter.
4. Release due to excess account funding: Banks typically require the project SPV to hold sufficient cash in the DSRA to cover 6 months of forward-looking debt service. So if the account balance of the DSRA is too high, the equity owner is allowed to release this extra cash. In row 1082, you can see how excess cash will be treated in the third quarter of 2025. You might wonder why the account is so overfunded in this period? As the two-quarters of debt service ahead are Q4 2025 and Q1 2026, there are comparatively low CFADS available due to the seasonality of the solar project. In the above example, a sculpted debt financing structure is in place, which causes debt service in the summer months to be higher than during the winter months. Consequently, excess cash is available in the DSRA at the end of the year's third quarter.

3.12.11 What’s the purpose of a shareholder loan?

What's the benefit of using a shareholder loan when financing a renewable energy project? It's simple - saving taxes! Learn how to properly integrate a shareholder loan into a financial model for renewable energy investments!

What is a shareholder loan?

A shareholder loan - also referred to as "SHL", is - strictly speaking - debt from an accounting perspective. However, the company's shareholders financing a project use their company's equity to give out the loan. So why not call it equity if the loan is financed with equity anyway?

A shareholder loan is a subordinate loan, meaning that the loan only gets repaid after all other liabilities are paid off. Typically, this subordination holds true not only for the principal of the SHL but also for the SHL interest charged.

Shareholder loan interest lowers the tax bill.

The major difference between a shareholder loan and pure equity in the form of share capital is the interest payment charged. The interest rate of the shareholder loan is most typically fixed over the entire tenor of the loan. Due to the subordination of an SHL, it is also reasonable to argue for a higher interest rate than the ones charged within the senior debt agreements - if any senior debt is raised. But before deciding how much SHL interest can be charged and how much overall SHL gearing is reasonable for a specific project, we strongly advise consulting a local tax advisor. Abusing a SHL structure will end up in trouble with the tax authorities. Typically a transfer pricing study from an independent third party is put in place before giving out the loan and defining its conditions.

Effect of interest payments within the income statement.

Interest payments from senior debt or shareholder loans reduce the earnings before tax (EBT) - and therefore the corporate taxes paid. Below you can see the effect of debt interest and SHL interest payments within an income statement of a project finance model for a large-scale solar plant.

Total interest payments for senior debt and the shareholder loan sum up to approximately 10 mEUR, of which 3.3 mEUR are charged from the SHL. If we assume a corporate income tax of 25%, the SPV of the project will save 0.25 x 3.3 = 0.83 mEUR. Saving almost one million EUR in taxes should underline the benefits of a shareholder loan.

The second benefit of an SHL: The vacuum effect.

Many jurisdictions restrict dividend payments if a company has negative retained earnings. Especially renewable energy projects often start with many years of negative retained earnings - mostly stemming from high depreciation & amortization charges in the income statement. This might lead to an absurd situation - the project might generate sufficient cashflows but is unprofitable from an accounting perspective. Depending on your jurisdiction, the company might not be allowed to pay dividends to its shareholders.

In this case, the company could end up with a full cash account but is legally not allowed to pay out any of it due to the negative retained earnings. Certainly, this would harm the IRR and cash distributions of the project.

However, a shareholder loan is at least a partial cure for this issue. Shareholders will receive both the principal repayment and the interest charged on the shareholder loan.

In a nutshell, a shareholder loan can improve the IRR of a project through tax savings and earlier receipts of cash in case a jurisdiction has dividend restrictions for negative retained earnings.

Renewables Valuation Analyst Program

In addition to this insightful Financial Model User Guide, we invite you to explore the Renewables Valuation Analyst Program on our website.

This comprehensive program is designed for professionals seeking in-depth knowledge and practical skills in financial modeling and valuation within the renewable energy sector. It offers a blend of theoretical understanding covering topics as the ones reflected in this Financial Model User Guide and real-world application, equipping you with the tools to excel in this dynamic and growing field. Discover how this program can be a catalyst in advancing your career and contributing to the sustainable energy landscape.