Spreadsheet: 629525d1561393917-help-with-a-string-of-if-statements-sample
Sheet: Annual Cashflow

 | Borrower
 | Initial Loan Requirement
 | Property Value
 | Annuity Loan Profile on |  | @ |  |  | PERFORMING |  |  |  |  |  | STRESSED INTEREST RATE |  |  |  |  |  | STRESSED RENT
 |  |  | @ |  |  | @ current interest rates |  |  |  |  |  | @ interest rate increased by 2% |  |  |  |  |  | @ interest rate increased by 2%
 | Term |  |  |  |  | Leases Performing for full duration of Loan Facility |  |  |  |  |  | Leases Performing for full duration of Loan Facility |  |  |  |  |  | Estimated 20% rent reduction
 | [NAME] - opening
 | Principal paid down
 | Closing Balance
 | LTV @ year end
 |  |  |  |  | Gross Yield | 2019 | 2020 | 2021 | 2022 | 2023
 | Rental Income |  |  |  |  | 12 months | 12 months | 12 months | 12 months | 12 months |  | 12 months | 12 months | 12 months | 12 months | 12 months |  | 12 months | 12 months | 12 months | 12 months | 12 months
 | Gross Rental Income
 | Total
 | Interest p.a. |  |  |  |  |  |  |  |  |  |  | - | - | - | - | - |  | - | - | - | - | -
 | Interest p.a. |  |  |  |  | - | - | - | - | -
 | Service Charges & Costs - lessee
 | Profit before tax
 | Tax Payable (estimated) @ |  |  |  | See Below
 | Total Interest, Expenses & Tax
 | Cashflow available pre Principal Repayments p.a.
 | Net Cashflow Available per above
 | Add back interest
 | Cashflow available for Debt Servicing
 | Cashflow after Debt Servicing
 | Gross Interest Cover
 | DSCR Pre Tax
 | DSCR Post Tax
 | Surplus Cashflow Available p.a. - pre tax
 | Surplus Cashflow Available p.a. - post tax
 | Tax Calculation
 | Rent
 | Expenses
 | Interest |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.01
 | Interest Dissalowed |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.02
 | Capital Allowance |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.03
 | Taxable Surplus |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.04
 | Tax Payable @ |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.05
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.06
 | Rent Reserve |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.08
 | Opening reserve |  |  |  |  | 0 |  |  |  |  |  | 0 |  |  |  |  |  | 0
 | Surplus Cashflow Available p.a. - post tax |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.09
 | Cumulative reserve |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.1
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.23
 | Individual Asset Metrics | Notes |  | General CRE & I/O Loans | Bridging Loans | Construction Loans |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.24
 | Minimum EIR / IRR | 1 |  | 500bps | 600bps | 800bps | 1 | Subject to delivery of Portfolio metrics on overall EIR / IRR |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.25
 | Min Base Case Pre-Tax ICR | 2, 3 |  | 1.25x | n/a | n/a | 2 | Generallly income from the asset, but where security is available, may include cash secured by way of rent or income reserve. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.26
 | Min Stressed Case DSCR, post-tax | 2, 3 |  | 1.00x | n/a | n/a | 3 | Income metrics on Bridging Loans are prospective rather than passing metrics for own to hold strategies. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.27
 | [NAME] LTVs Zones A & B | 4, 5, 9 |  | 0.8 | 0.8 | 0.8 |  | Disposal plans will operate to LTV or disposal milestone metrics, with repayment profile having appropriate release price mechanism from asset sales. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.28
 | [NAME] C | 4, 5, 9 |  | 0.75 | 0.75 | 0.75 | 4 | GCC may approve facilities at LTV 85% where appropriate mitigation applies. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.29
 | Max LTVPV industrial / owner occupied | 6 |  | 1 | 1 | n/a | 5 | LTV means Loan to Gross Development Value for Construction Loans. Working assumption is Development Loans should have [NAME] 85%, [NAME] LTC 80%. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.3
 | [NAME] | 7 |  | 5 years | 2 years | 2 years |  | LTV should be stated gross (i.e. not after deduction of CGT). However, all applications should have a new paragraph in the credit application discussing refinancing risk, specifically commenting on CGT and mitigants where it arises. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.31
 | Maximum amortisation profile |  |  | 30 years | n/a | n/a |  | Generally a net of CGT valuation in excess of 90% gearing would require very significant mitigation. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.32
 | Maximum Loan Size | 8 |  | €15m | €10m | €5m | 6 | Credit enhancement or mitigation required for breach of these metrics, which are reduced to 90% for Zone C assets. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.33
 | Maximum Connection Exposure |  |  | €25m | €25m | €25m | 7 | General CRE Loan & I/O Loan Maturity may be extended to 15 yrs (however maximum IO period is 5 years), subject to Portfolio Metrics. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.34
 |  |  |  |  |  |  |  | Bridging loans may term out to General CRE Loan or Interest Only Loan criteria where they meet those requirements. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.35
 |  |  |  |  |  |  | 8 | Discuss loan proposals >€10m with MD FICM or Portfolio Director FICM before issuing terms or selection of legal or valuation firms.
 |  |  |  |  |  |  |  | Enhanced diligence on cashflows, refinancing risk, building condition and loan documentation will be required. Loans > €10m should be in Zone A & B and not involve industrial property.
 |  |  |  |  |  |  | 9 | Where majority of property is residential, LTV must be <70%. Where LTV is >70%, the borrower should be a company / SPV and the proposal should be progressed by Finance Ireland Property Finance DAC. |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.36
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.45
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.46
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.47
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.48
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.49
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.5
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.51
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.52
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.53
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.54
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.55
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.56
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.57
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.58
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.59
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.6
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.61
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.62
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.63
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.64
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.65
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.66
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.67
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.68
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.69
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.7
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.71
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.72
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.73
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.74
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.75
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.76
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.77
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.78
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.79
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.8
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.81
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.82
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.83
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.84
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.85
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.86
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.87
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.88
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.89
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.9
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.91
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.92
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.93
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.94
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.95
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.96
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.97
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.98
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.99
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 1