GeniusModelData Book
Narratives
Foundations
Beginner practitioner
Expert practitioner

Solar C&I Tax Equity Partnership model

Overview
Contributors
John Marciano
John Marciano
John Marciano
Tax lead, Black Sabbath
Haydn Palliser
Haydn Palliser
Haydn Palliser
Principal, Pivotal 180
Oliver Beavers
Oliver Beavers
Oliver Beavers
Founder, Phosphor
About the model

A comprehensive, annotated sponsor equity model for solar C&I projects. It includes the sponsor’s view of a Tax Equity Partnership financing, a back-leverage debt facility, and Sponsor returns and economics.

Legend
Input

Inputs are inputs which can be defined for the model.

Formula
Formulas define the model's calculation logic.
Dates

A special formula that establishes the index to which the model is anchored.

Financial Model
Dates
DateRange(Date(2024,1,31),360,M)
Calendar month
Month(Dates)
Calendar year
Year(Dates)
Days in month
Day(Dates)
Start of commercial ops
Dates=EndOfMonth(Commercial ops date,0)
Project end of life
Dates=EndOfMonth(Commercial ops date,Periods per year*Useful life)
Operations Active
And(Dates≥EndOfMonth(Commercial ops date,0),Dates<EndOfMonth(Commercial ops date,Useful life*Periods per year))
Distribution period
Or(Or(Calendar month=3,Calendar month=6),Or(Calendar month=9,Calendar month=12))*Operations Active
Start of Operating Year
Month(Dates)=Month(Commercial ops date)*Operations Active
End of Operating Yr
Month(Dates)=Month(EndOfMonth(Commercial ops date,-1))*Operations Active
Operating Period
Sum(Operations ActiveFrom start)*Operations Active
Operating Year
Sum(Start of Operating YearFrom start)*Operations Active
Start or End of Life
Or(Start of commercial ops,Project end of life)
NTP Period
Dates=EndOfMonth(Notice to proceed,0)*1
Mechanical completion period
Dates=MC milestone date
Substantial completion period
Dates=SC milestone date
Post NTP Flag
Sum(NTP PeriodFrom start)
Periods from NTP
Sum(Post NTP FlagFrom start)
Days in COD month
Days in month-Day(Commercial ops date)*Start of commercial ops
Days in EOL month
Project end of life*Day(EndOfMonth(Commercial ops date,Periods per year*Useful life))-Sum(Days in COD month)
Escalation Year
Max(0,Operating Year-1)
Project availability
Days in month/Day(Dates)*Operations Active
Periods per year
12
Months per year
12
NTP milestone date
EndOfMonth(Commercial ops date, NTP (offset from COD))
MC milestone date
EndOfMonth(Commercial ops date,MC (offset from COD))
Placed in service (PIS) date
EndOfMonth(Commercial ops date,0)
SC milestone date
EndOfMonth(Commercial ops date,SC (offset from COD))
W per MW
1000000
kW per MW
1000
MW DC online
MW DC*Operations Active
KW DC online
MW DC online*kW per MW
Degradation index
1-Degradation rate*Escalation Year*Operations Active
Seasonality
Operations Active*Lookup(Calendar month,Index,Values,0)
Generation (kWh AC)
kWh per kWp*MW DC*kW per MW*Degradation index*Project availability*Seasonality
Total revenue
PPA revenue
PPA term active
And(PPA term≥Operating Year,Operations Active)
Tariff rate ($/kWh)
PPA rate*1+PPA escalator^Escalation Year*PPA term active
PPA revenue
Generation (kWh AC)*Tariff rate ($/kWh)
O&M contract
O&M Y1 per kW*1+O&M escalator^Escalation Year*KW DC online/Periods per year
Asset management
Asset mgmt Y1 per kW*1+Asst mgmt escalator^Escalation Year*KW DC online/Periods per year
Ground lease
Lease Y1 cost*1+Lease escalator^Escalation Year*Operations Active/Periods per year
PILOT/Property taxes
PILOT Y1 cost*1+PILOT escalator^Escalation Year*Operations Active/Periods per year
Insurance - all risk
Allrisk Y1 cost*1+Allrisk escalator^Escalation Year*Operations Active/Periods per year
Total operating expenses
O&M contract+Asset management+Ground lease+PILOT/Property taxes+Insurance - all risk
Net change in working cap
Net change in debtors-Net change in creditors
Opening balance
Closing balancePrevious
Revenue received
If(Project end of life,Opening balance+Total revenue,Opening balance+Total revenue*Days in month-Receivables days/Days in month*Operations Active)
Closing balance
Opening balance+Total revenue-Revenue received
Net change in debtors
Opening balance-Closing balance
Opening balance
Closing balancePrevious
Expenses paid
If(Project end of life,Opening balance+Total operating expenses,Opening balance+Total operating expenses*Days in month-Payable days/Days in month*Operations Active)
Closing balance
Opening balance+Total operating expenses-Expenses paid
Net change in creditors
Opening balance-Closing balance
Inverter date
EndOfMonth(Commercial ops date,Inverter years*12)
Inverter budget timer
And(Dates≥EndOfMonth(Inverter date,-Inverter budget mths),Dates<Inverter date)
Inverter budget
If(Inverter budget timer,Inverter cost per W*MW DC*W per MW/Inverter budget mths,0)
Inverter target reserve balance
Sum(Inverter budgetFrom start)
O&M reserve target balance
If(Operations Active,Sum(O&M contractO&M budget mths),0)
Expected O&M reserve funding
Start of commercial ops*O&M reserve target balance
Total cost ($/w)
EPC contract ($/w)+ Interconnection ($)/MW DC/W per MW+Dev margin ($/w)
Total cost ($)
Total cost ($/w)*MW DC*W per MW
EPC S-Curve
Lookup(Periods from NTP,Index,Values,0)
EPC payments
EPC S-Curve*EPC contract ($/w)*MW DC*W per MW
Interconnection payment
Interconnection ($)*Mechanical completion period
Developer margin payment schedule
NTP Period*Dev margin NTP milestone %+Mechanical completion period*Dev margin MC milestone %+Substantial completion period*Dev margin SC milestone %
Developer margin payments
Developer margin payment schedule*Dev margin ($/w)*MW DC*W per MW
Total CapEx
EPC payments+Interconnection payment+Developer margin payments
Fair market value
Total cost ($)*1+FMV step up (%)
ITC eligible basis
Fair market value*ITC eligible %
ITCs generated
ITC eligible basis*ITC rate
ITC tax basis reduction
ITCs generated* ITC basis reduction %
Tax on FMV step up
Fair market value-Total cost ($)*Sponsor tax rate %
MACRS 5y basis
Fair market value*MACRS 5y eligible %
SL 15y basis
Fair market value-MACRS 5y basis
MACRS 5y depreciation
MACRS 5y basis*Lookup(Operating Year,Index,Values,0)/Periods per year
SL 15y depreciation
SL 15y basis*Operating Year≤15*Operations Active/15/Periods per year
Total depreciation
MACRS 5y depreciation+SL 15y depreciation
EBITDA
Total revenue-Total operating expenses
Taxable income / (loss)
EBITDA-Total depreciation
Tax cost / (benefit)
Taxable income / (loss)*Sponsor tax rate %
Operating cash flow
EBITDA-Net change in working cap
Pre-tax IRR cash flow
Operating cash flow-Inverter budget-Expected O&M reserve funding-Total CapEx
Tax efficient IRR cash flows
Pre-tax IRR cash flow+ITCs generated*Start of commercial ops-Tax cost / (benefit)
Tax efficient IRR
1+Irr(Tax efficient IRR cash flows)^12-1
Opening balance (lockbox)
Closing balance (lockbox)Previous
Cash added to lockbox
Operating cash flow
Cash released from SPV lockbox
If(Distribution period,Opening balance (lockbox)+Cash added to lockbox,0)
Closing balance (lockbox)
Opening balance (lockbox)+Cash added to lockbox-Cash added to lockbox
Tax FYE
Calendar month=Fiscal year end (mth)
Buyout date
EndOfMonth(SC milestone date,Buyout month)
Buyout period
Dates=Buyout date
Partnership active
Sum(Substantial completion periodFrom start)-Sum(Buyout periodFrom start)
Operating cash flow to partnership
Cash released from SPV lockbox
ITCs transferred
ITCs sold via transfer
Tax equity sizing
(TE ITC allocation-ITCs transferred)*TE syndication rate
TE contribution at MC
Tax equity sizing*(1-Contribution % at SC)*Mechanical completion period
TE contribution at SC
Tax equity sizing*Contribution % at SC*Substantial completion period
TE contributions
TE contribution at MC+TE contribution at SC
TE active bal
Sum(TE contributionsFrom start)*Partnership active
Opening balance (pref accrued)
Closing balance (pref accrual)Previous
Preferred return accrued
TE active bal* TE preferred return (%)/Periods per year
Preferred return paid
Max(Min(Opening balance (pref accrued)+Preferred return accrued,Distributable cash flow),0)
Closing balance (pref accrual)
Opening balance (pref accrued)+Preferred return accrued-Preferred return paid
Tax equity buyout modeled
Buyout period*Tax equity sizing*Buyout as % of capital
Distributable cash flow
Sum(Operating cash flow to partnershipLast three)*Distribution period
Cash available for sharing
Distributable cash flow-Preferred return paid
TE cash sharing
TE cash sharing %*Cash available for sharing *Partnership active
Cash distributed to sponsor
Cash available for sharing -TE cash sharing-TE cash sharing
Tax income / (loss) - quarterly
Sum(Taxable income / (loss)Last three)*Distribution period
TE income allocation
TE pre-flip income %*Partnership active*Tax income / (loss) - quarterly
TE ITC allocation
ITCs generated*TE pre-flip income %
TE ITC booked
TE ITC allocation*Substantial completion period
TE ITC basis reduction
ITC tax basis reduction*TE pre-flip income %*Substantial completion period
Sponsor income allocation
Tax income / (loss) - quarterly-TE income allocation
Sponsor ITC allocation
ITCs generated*1-TE pre-flip income %
Sponsor ITC booked
Sponsor ITC allocation*Substantial completion period
Sponsor ITC basis reduction
ITC tax basis reduction*1-TE pre-flip income %*Substantial completion period
Opening bal - TE 704b
Closing balance - TE 704bPrevious
Income allocated
Max(TE income allocation,0)
First interim balance
Opening bal - TE 704b+TE contributions+Income allocated
Loss allocated
Min(TE income allocation,0)
Balance before reallocation
First interim balance+Loss allocated-TE ITC basis reduction
Reallocations to sponsor
-Min(Max(Loss allocated,Balance before reallocation),0)
Reallocations from sponsor
Reallocations to TE
Closing balance - TE 704b
First interim balance+Reallocations to sponsor-Reallocations from sponsor
Loss incl reallocations (TE)
Loss allocated+Reallocations to sponsor-Reallocations from sponsor
Opening bal - s704b
Closing bal - s704bPrevious
Contributions at FMV
Fair market value-Tax equity sizing*TE contributions/Tax equity sizing
Positive income allocated
Max(Sponsor income allocation,0)
First interim bal s704b
Opening bal - s704b+Contributions at FMV+Positive income allocated
Loss allocated s704b
-Min(Sponsor income allocation,0)
Second interim balance
First interim bal s704b+Loss allocated-Sponsor ITC basis reduction
Reallocations to TE
-Min(Max(Loss allocated s704b,Second interim balance),0)
Reallocations from TE
Reallocations to sponsor
Closing bal - s704b
First interim bal s704b+Reallocations to TE-Reallocations from TE
Loss incl reallocations (sponsor)
Loss allocated s704b+Reallocations to TE-Reallocations from TE
Opening outside balance TE
Closing TE outside balPrevious
Interim bal before 731a gain
Opening outside balance TE+TE contributions+Income allocated-TE ITC basis reduction-Preferred return paid-TE cash sharing
731a gain
-Min(Interim bal before 731a gain,0)
Balance before 704d losses
Interim bal before 731a gain+731a gain+Loss incl reallocations (TE)
Suspended loss - opening bal
704d loss - closing balPrevious
704d losses suspended
Max(0,-Balance before 704d losses)
704d losses unsuspended
If(Balance before 704d losses>0,Min(Suspended loss - opening bal+704d losses suspended,Balance before 704d losses),0)
704d loss - closing bal
Suspended loss - opening bal+704d losses suspended-704d losses unsuspended
Net 704d suspended losses
704d losses suspended-704d losses unsuspended
Closing TE outside bal
Balance before 704d losses+Net 704d suspended losses
Opening sponsor outside basis
Closing sponsor outside balPrevious
Interim bal before 731a gain (sponsor)
Opening sponsor outside basis+Contributions at FMV+Positive income allocated-Sponsor ITC basis reduction-Cash distributed to sponsor
731a gain (sponsor)
-Min(Interim bal before 731a gain (sponsor),0)
Bal before 704d losses
Interim bal before 731a gain (sponsor)+731a gain (sponsor)+Loss incl reallocations (sponsor)
704d loss starting bal
704d loss closing balPrevious
704d losses suspended (sponsor)
Max(0,-Bal before 704d losses)
704d losses unsuspended (sponsor)
If(Bal before 704d losses>0,Min(704d loss starting bal+704d losses suspended (sponsor),Bal before 704d losses),0)
704d loss closing bal
704d loss starting bal+704d losses suspended (sponsor)-704d losses unsuspended (sponsor)
Net 704d suspended losses (sponsor)
704d losses suspended (sponsor)-704d losses unsuspended (sponsor)
Closing sponsor outside bal
Bal before 704d losses+Net 704d suspended losses (sponsor)
Total 731a gains
731a gain+731a gain (sponsor)
Sponsor pre-tax cash after TE
Pre-tax IRR cash flow+TE contributions-Preferred return paid-TE cash sharing-Tax equity buyout modeled
Sponsor pretax post TE IRR
1+Irr(Sponsor pre-tax cash after TE)^12-1
Sponsor pre-tax post-TE MOIC
Sum(Operating cash flow)-Sum(Preferred return paid)-Sum(TE cash sharing)-Sum(Tax equity buyout modeled)/Total cost ($)-Tax equity sizing
Taxable income / (loss) booked - sponsor
Positive income allocated+731a gain (sponsor)-Loss incl reallocations (sponsor)-704d losses suspended (sponsor)+704d losses unsuspended (sponsor)
Tax (payments made) / losses monetized
Taxable income / (loss) booked - sponsor*-Sponsor tax rate %
Sponsor post tax, post TE cash flow
Sponsor pre-tax cash after TE+Sponsor ITC booked+Tax (payments made) / losses monetized
Sponsor post tax, post TE IRR
1+Irr(Sponsor post tax, post TE cash flow)^12-1
Loan contribution period
SC milestone date
Debt funding period
Dates=Loan contribution period
Debt sizing period
Dates=EndOfMonth(Loan contribution period,1)
Debt term flag
And(Dates>Loan contribution period,Dates≤EndOfMonth(Loan contribution period,Debt term (months)))
Loan maturity period
Dates=EndOfMonth(Loan contribution period, Maturity term (months))
Agency fees booked
If(Debt term flag,Agency fees ($ pa)/12,0)
Agency fees payable
Sum(Agency fees bookedLast three)*Distribution period
Independent manager fee booked
If(Debt term flag,Independent mgr fee ($ pa),0)
Independent manager fee payable
Sum(Independent manager fee bookedLast three)*Distribution period
Total loan expenses booked
Agency fees booked+Independent manager fee booked
Total loan expenses payable
Agency fees payable+Independent manager fee payable
Cash available for debt service (CFADS)
Cash available for P&I payments-Inverter budget-O&M target deposits*Debt term flag
Present value of CFADS
Npv(Debt discount rate/Periods per year,Cash available for debt service (CFADS)Debt term (months))*Debt term flag
Borrowing base
Present value of CFADS/Target DSCR
Debt size
Sum(Debt sizing period*Borrowing base)
Syndication fee
Debt size*Loan syndication fee (%)
Target debt service
Cash available for debt service (CFADS)/Target DSCR
Opening balance (term debt)
Closing balance (term debt)Previous
Loan contributions
Debt size*Debt funding period
Scheduled amortization (DSCR-sculpted)
Min(Max(Target debt service-Interest due,0),Opening balance (term debt)+Loan contributions)
Closing balance (term debt)
Opening balance (term debt)+Loan contributions-Scheduled amortization (DSCR-sculpted)
Interest factor - Days/360
Days in month/360
Interest accrued
Opening balance (term debt)*Interest factor - Days/360*Interest rate (%)
Interest due
Sum(Interest accruedLast three)*Distribution period
Total debt service (P&I) due
Scheduled amortization (DSCR-sculpted)+Interest due
Shortfalls in debt service
Max(Cash after debt service, before shortfalls,0)
Planned shortfalls at closing
Sum(Shortfalls in debt service*Debt term flag)
Opening balance (shortfalls reserve)
Closing balance (shortfall reserve)Previous
Shortfall reserve funded
Debt funding period*Planned shortfalls at closing
Shortfalls covered by reserve
Min(Shortfalls in debt service,Opening balance (shortfalls reserve))
Closing balance (shortfall reserve)
Opening balance (shortfalls reserve)+Shortfall reserve funded-Shortfalls covered by reserve
Target DSRA balance
Debt term flag*Sum(Total debt service (P&I) dueDSRA months)
Cash available for DSRA adjustments
Max(Cash available for DSRA,0)
Missed DS payments
-Min(Cash available for DSRA,0)
Opening balance (DSRA)
Closing balance (DSRA)Previous
DSRA funded at closing
Debt funding period*Sum(Target DSRA balance*Debt sizing period)
Releases to cover DS shortfalls
Min(Opening balance (DSRA)+DSRA funded at closing,Missed DS payments)
Adjustments to meet DSRA target balance
Min(Cash available for DSRA adjustments,Target DSRA balance+Releases to cover DS shortfalls-Opening balance (DSRA)-DSRA funded at closing)*Debt term flag
Closing balance (DSRA)
Opening balance (DSRA)+DSRA funded at closing-Releases to cover DS shortfalls+Adjustments to meet DSRA target balance
Inverter reserve target balance (debt)
Inverter target reserve balance
Inverter reserve target CFs (for CFADS calc)
Inverter reserve target balance (debt)-Inverter reserve target balance (debt)Previous
Inverter deposits required
Max(Inverter reserve target balance (debt)-Closing balance (inverter rsv)Previous,0)
Opening balance (inverter rsv)
Closing balance (inverter rsv)Previous
Inverter account deposits
Max(Min(Cash available for inverter reserves,Inverter deposits required),0)
Closing balance (inverter rsv)
Opening balance (inverter rsv)+Inverter account deposits
O&M reserve target balance (debt)
O&M reserve target balance
O&M target deposits
O&M reserve target balance (debt)-O&M reserve target balance (debt)Previous
O&M reserve deposits required
Max(O&M reserve target balance (debt)-Closing balance (O&M reserve)Previous,0)
Opening balance (O&M reserve)
Closing balance (O&M reserve)Previous
O&M account deposits
Max(Min(Cash available for O&M reserve,O&M reserve deposits required),0)
Closing balance (O&M reserve)
Opening balance (O&M reserve)+O&M account deposits
Cash assigned to borrower
Cash distributed to sponsor
Cash available for P&I payments
Cash assigned to borrower-Total loan expenses payable
Cash after debt service, before shortfalls
Cash available for P&I payments-Total debt service (P&I) due
Cash available for DSRA
Cash after debt service, before shortfalls+Shortfalls covered by reserve
Cash available for inverter reserves
Cash available for DSRA+Releases to cover DS shortfalls-Adjustments to meet DSRA target balance
Cash available for O&M reserve
Cash available for inverter reserves-Inverter account deposits
Cash distributed from borrower
Cash available for O&M reserve-O&M account deposits
Loan life coverage (LLCR)
If(Closing balance (term debt)>0,Present value of CFADS+Closing balance (shortfall reserve)/Closing balance (term debt),0)
Loan life coverage (LLCR) incl DSRA
If(Closing balance (term debt)>0,Present value of CFADS+Closing balance (shortfall reserve)+Closing balance (DSRA)/Closing balance (term debt),0)
TTM cash available for P&I payments
Debt term flag*Sum(Cash available for P&I paymentsTTM)
TTM P&I payments due
Debt term flag*Sum(Total debt service (P&I) dueTTM)
Debt service coverage (TTM DSCR)
Debt term flag*TTM cash available for P&I payments+Closing balance (shortfall reserve)/TTM P&I payments due
Debt service coverage (TTM DSCR) incl DSRA
Debt term flag*TTM cash available for P&I payments+Closing balance (shortfall reserve)+Closing balance (DSRA)/TTM P&I payments due
Minimum LLCR
MinIfs(If(Closing balance (term debt)>0,Present value of CFADS+Closing balance (shortfall reserve)/Closing balance (term debt),0),Debt term flag,1)
Minimum LLCR incl DSRA
MinIfs(If(Closing balance (term debt)>0,Present value of CFADS+Closing balance (shortfall reserve)+Closing balance (DSRA)/Closing balance (term debt),0),Debt term flag,1)
Minimum TTM DSCR
MinIfs(Debt term flag*TTM cash available for P&I payments+Closing balance (shortfall reserve)/TTM P&I payments due,Debt term flag,1)
Minimum TTM DSCR incl DSRA
MinIfs(Debt term flag*TTM cash available for P&I payments+Closing balance (shortfall reserve)+Closing balance (DSRA)/TTM P&I payments due,Debt term flag,1)
Taxable income / (loss) summary
Taxable income / (loss) booked - sponsor-Interest due-Total loan expenses payable
Opening balance (NOLs)
Closing balance (NOLs)Previous
NOLs added
-Min(Taxable income / (loss) summary,0)
NOLs applied to taxes
If(Taxable income / (loss) summary>0,Min(Taxable income / (loss) summary*Maximum NOL offset %,Opening balance (NOLs)),0)
Closing balance (NOLs)
Opening balance (NOLs)+NOLs added-NOLs applied to taxes
Taxable income (positive only)
Max(Taxable income / (loss) summary-NOLs applied to taxes,0)
Taxes due before tax credits
Taxable income (positive only)*Sponsor tax rate %
Opening balance (tax credits)
Closing balance (tax credits)Previous
ITCs from partnership
Sponsor ITC booked
Tax credits consumed
If(Taxes due before tax credits>0,Min(Taxes due before tax credits,Opening balance (tax credits)+ITCs from partnership),0)
Closing balance (tax credits)
Opening balance (tax credits)+ITCs from partnership-Tax credits consumed
Taxes due after tax credits
Taxes due before tax credits-Tax credits consumed
Sponsor pre-tax cash after B/L debt
Cash distributed from borrower-Total CapEx+TE contributions+Loan contributions-Tax equity buyout modeled
Sponsor pre-tax IRR after B/L debt
1+Irr(Sponsor pre-tax cash after B/L debt)^12-1
Sponsor post-tax cash after B/L debt
Sponsor pre-tax cash after B/L debt-Taxes due after tax credits
Sponsor post-tax IRR after B/L debt
1+Irr(Sponsor post-tax cash after B/L debt)^12-1

Model Saved

Click anywhere to dismiss.