To build a high$-$level model so you can size a PROJECT FINANCE LOAN and see

leverage on a project. The operations have been kept very simple so you can focus on the debt calculations. As an analyst or associate in a lending or equity role this would form a major part of your work. Your role would be to analyse different structures $($such as repayments$)$ and determine which one provides the best solution to the equity investor.

Instructions

Build out the cashflows to CADS by including revenue, expenses, and maintenance capex $($project life is $10$ years$)$

$$ Revenue and expenses should be inflated at $1\mathrm{.}5\%$ p$.$a$.($i$.$e$.$ every year the values increase by $1\mathrm{.}5\%$ from last year$)$

Maintenance does not have inflation, it is $$15m$ in year $5_{{?}_{?}}$

Calculate the unlevered project IRR $($i$.$e$.$ the IRR without debt$)$

Calculate the debt repayments for the $3$ cases provided:

V$1-$ DSCR sculpted repayments similar to the in$-$class

V$2-$ Linear debt repayment $($principal will be equal in every period$)$

V$3-$ Annuity repayment $($total debt service will be constant every period$)$

Calculate the cashflows to equity $($i$.$e$.$ CADS less debt service$)$ and calculate the following outputs for $V1,V2,$ and $V3$ :

Maximum debt size to achieve the DSCR constraint

Required Equity $($total project capital cost less the debt size$)$

Leverage as a $\%($debt $/$ total funding required$)$

Levered "equity" IRR $($i$.$e$.$ IRR to equity with debt in the project$)$

The DSCR each period $($do not simply restate it$,$ calculate it$)$

The year in which the min DSCR is hit. If there are multiple years when the minimum is hit, show only the first date

Explain in a few sentences $($in the Excel file$)$ why the results calculated above are logical

Hints and other notes

Lay out a debt schedule indicating the principal balance at the beginning of the period, the principal amortized during the period, and the principal balance at the end of the period. You will need to insert additional lines into the starting spreadsheet, so you have room for calculations

Make certain the loan is repaid $($i$.$e$.$ the loan balance $=0)$ by the end of the loan tenor. It shouldn't be repaid before or after the end of the loan tenor

Hints and other notes ${?}_{(})$

Lay out a debt schedule indicating the principal balance at the beginning of the period, the principal amortized during the period, and the principal balance at the end of the period. You will need to insert additional lines into the starting spreadsheet, so you have room for calculations

Make certain the loan is repaid $($i$.$e$.$ the loan balance $=0)$ by the end of the loan tenor. It shouldn't be repaid before or after the end of the loan tenor

In setting up your model, try and make it flexible so that the assumptions can be modified dynamically. Whenever possible, rely on formulas and cell references rather than hard$-$coded numbers. If you need more assumptions, add them

Although possible to use Excel's goal seek function, try not to$.$ All of the solutions can be solved without goal seek ${?}_{(}{)}_{?}$

Watch your units!

A placeholder is NOT provided for the outputs, please add these yourself in a clear manner

Conceptual assumptions

The project is a partnership and does not pay any taxes. Recall that Partnerships are flow$-$through entities passing the tax benefits or liabilities though to the individual Partners who own a stake in the project. You can safely ignore taxes in this assignment

The debt is at the Project level. There is no need to model any of the cash flows at the level of any individual Partner or Partners.

There are no cash subsidies $($rebates$,$ RECs, production$-$based incentives, etc.$)$ for the project.

The project achieves its Commercial Operation Date $("$COD$")$ on January $1$ of Year $1$ and all debt is in place from January $1$ of Year $1.$

All years are $365$ days $($ignore leap years$).$

All cash flows $($revenue$,$ operating expense, major maintenance capex, interest expense, principal amortization$)$ occur once per year on December $31.$

Interest expense is compounded once per year on December $31.$

All construction and commissioning costs $($including any interest accrued during construction$)$ are already captured under Total Capital Cost.

Although the annual electrical output of solar panels in the field typically displays a level of degradation over time, assume that the electrical output is the same in all years.