Calculations

DSCR

Sum of Cash Flow (inclusive of borrowers and guarantors) divided by sum of Debt Service (inclusive of borrowers and guarantors debt service and loan debt service)

LTV

Loan Amount divided by the selected Valuation Amount type (selected via the graph - Primary Income / Sales Comps / Alternative)

Cash Return

Interest Only Cash Return

• Monthly interest rate multiplied by the loan term and loan amount, plus the total fees

P&I Cash Return

• Cumulative interest paid on the loan plus the total fees

Interest Only Cash Return = (Interest rate/12* loan Term * loan amount)+ Sum(fees)

P&I Cash Return = CUMIPMT(interest rate/12, amortization term, gross loan, 1, term,1) + Sum(fees)

Debt Yield

NOI (of the selected Primary Income Valuation) divided by the Loan Amount

Cash Flow

Sum of Collateral, Borrowers, and Guarantors Cash Flow

totalCashFlow = totalCollateralCashFlow + borrowerCashFlow + guarantorCashFlow

Debt Service

Sum of Collateral, Borrower, and Guarantor Debt Service

totalDebtService = totalCollateralDebtService + borrowerDebtService + guarantorDebtService

Net Cash Flow

Total Cash Flow minus Total Debt Service

totalNetCashFlow = totalCashFlow - totalDebtService

Assets

Borrower Assets plus Guarantor/Sponsor Assets

totalAssets = borrowerTotalAssets + guarantorTotalAssets

Liabilities

Borrower Liabilities plus Guarantor/Sponsor Liabilities

totalLiabilities = borrowerTotalLiabilities + guarantorTotalLiabilities

Net Worth/Equity

Total Assets minus Total Liabilities

totalEquity = totalAssets -totalLiabilities

Liquidity

Borrower Liquidity plus Guarantor/Sponsor Liquidity

Liquidity referring to:

• Person: Cash + Other Liquid Assets from Personal Financial Statement

• Company: Cash/Liquidity item from Balance Sheet

Liquidity Ratio

Total Liquidity divided by Loan Amount

Net Worth Ratio

Total Net Worth/Equity divided by Loan Amount

Property Sales Comps Valuations

Sum of all component Sales Comp Valuations

• component1SalesCompValuation = (Sum of all included comps sale price divided by the sum of all included sales comps SQFT) multiplied by the subject component SQFT

componenet1SalesCompValuation + componenet2SalesCompValuation + componenet3SalesCompValuation + componenet”n”SalesCompValuation

AVG MO $/SF (COMPS)

• Multifamily

  • [Comp1(#Units * $/SF) + ….. + CompXYZ(#Units * $/SF)] / Total #Units in Comps

• Commercial

  • Average of all included rent comps' Mo. $/SF

TOTAL MO. REVENUE

• Multifamily

  • Number of Units multiplied by Average SF multiplied by AVG $/SF (COMPS)

• Commercial

  • SF multiplied by AVG MO $/SF (COMPS)

ANNUAL AVG. $/SF (COMPS)

AVG MO $/SF (COMPS) multiplied by 12

Commercial ONLY

TOTAL ANNUAL REVENUE

TOTAL MO. REVENUE multiplied by 12

Deal Sizing - DSCR (IO) Constraint

(NOI of the Primary Income Valuation / DSCR value in the slider} / Interest Rate)

Deal Sizing - DSCR (Amortizing) Constraint

Present Value(Interest Rate, Amortization Term/12, NOI of Primary Income Valuation/DSCR value in the slider)

• IO deals have a default Amortization Term of 360 months.

Deal Sizing - Debt Yield Constraint

NOI of the Primary Income Valuation / Debt Yield value in the slider

Deal Sizing - LTV Constraint

Primary Income Valuation * LTV value in the slider

Stress Test - DSCR

(Multivariable)

(Revenue*(1+Rev%change)-Concessions+OtherIncome*(1+Rev%change)-Revenue*(vacancy+vac%change)-Expenses*(1-exp%change))/Annual Debt Service

Stress Test - Debt Yield

(Multivariable)

(Revenue*(1+Rev%change)-Concessions+OtherIncome*(1+Rev%change)-Revenue*(vacancy+vac%change)-Expenses*(1-exp%change))/Loan Amount

Stress Test - Default Income LTV

(Multivariable)

Loan Amount/((Revenue*(1+Rev%change)-Concessions+OtherIncome*(1+Rev%change)-Revenue*(vacancy+vac%change)-Expenses*(1-exp%change))/(capRate + caprate%change))

Stress Test - LTV Single-Variable Tests and Deal Breakpoints

Value: Loan Amount/(proforma income valuation*(1-%change))

Revenue: Loan Amount/((Revenue*(1-%change)-Concessions+Other Income(1-%change)-Revenue*vacancy-Expenses)/capRate)

Vacancy: Loan Amount/((Revenue-Concessions+Other Income - Revenue*(vacancy+ %change)-expenses)/capRate)

Expenses: Loan Amount/((EffectiveIncome-expenses*(1+%change))/capRate)

Cap Rate: Loan Amount/(NOI/(capRate+%change))

Min Value: -(((Loan Amount * Cap Rate)/LTV)+(revenue*vacancy)+expenses+Concessions)/(revenue+other income)-1

Min. Revenue: ((((Loan Amount*Cap Rate)/LTV)+(rev*vac)+expenses)/(revenue+other income))-1

Max Vacancy: (-(((Loan Amount*Cap Rate)/LTV)-revenue-other income+expenses)/revenue)-vacancy

Max Expenses: -((((Loan Amount * Cap Rate)/LTV)-Effective Income)/expenses+1)

Max Cap Rate: ((LTV* NOI)/Loan Amount)-Cap Rate

Stress Test - Debt Yield Single-Variable Tests and Deal Breakpoints

Revenue: (Revenue*(1-%change)-Concessions+Other Income(1-%change)-Revenue*vacancy-Expenses)/Loan Amount

Vacancy: (Revenue-Concessions+Other Income - Revenue*(vacancy+ %change)-expenses)/Loan Amount

Expenses: (EffectiveIncome-expenses*(1+%change))/Annual Debt Service

Min Revenue: (((Debt Yield*Loan Amount)+revenue*vac + expenses)/(revenue+other income))-1

Max Vacancy: (-((Debt Yield*Loan Amount)-revenue-other income+expenses)/Revenue))-vacancy

Max Expenses: -(((DSCR*Annual Debt Service)-Effective Income)/Expenses)+1)

Stress Test: DSCR Single-Variable Tests and Deal Breakpoints

Revenue: (Revenue*(1-%change)-Concessions+Other Income(1-%change)-Revenue*vacancy-Expenses)/Annual Debt Service

Vacancy: (Revenue-Concessions+Other Income - Revenue*(vacancy+ %change)-expenses)/Annual Debt Service

Expenses: (EffectiveIncome-expenses*(1+%change))/Loan Amount

Min Revenue: (((DSCR*Debt Service)+revenue*vac + expenses)/(revenue+other income))-1

Max Vacancy: (-((DSCR*Debt Service)-revenue-other income+expenses)/Revenue)-vacancy

Max Expenses: -(((Debt Yield* Loan Amount)-Effective Income)/Expenses+1)

Max Interest Rate: NOI/(gross loan *((i*(1+i)^n)/((1+i)^n - 1))=min DSCR
goal seek for i and then i - interest rate=change%

People - Net Worth

Net Worth = Sum of Assets - Sum Liabilities

From PFS page

• Using Proforma column values

People - Liquidity

Liquidity = Cash + Other Liquid Assets + Marketable Securities

From PFS page

• Using Proforma column values

People - DSCR

Cash Flow available for debt service / Person debt service

• From Cash Flow page / Overview page

  • Using Proforma column values

• personalDSCR= cashFlowAvailableforDebtService / personDebtService

People - Net Cash Flow

=cashFlowAvailableforDebtService - personDebtService

• From Cash Flow page / Overview page

  • Using Proforma column values

People - Assets

Sum of all Personal Assets

= Cash + Other Liquid Assets + Retirement Accounts + Marketable Securities + Equity in Closely Help Businesses + Notes Receivable + Other Assets + Real Estate

• Using Proforma column values

People - Liabilities

Sum of all Personal Liabilities

= Revolving Debt/Credit Card Debt + Installment Debt + Other Liabilities + Real Estate Debt

• Using Proforma column values

People - Debt Ratio

Liabilities / Assets

• Using Proforma column values

People - Total Value

Sum of all (SRE property Market Value multiplied by the % Ownership)

From SRE page

People - Total Debt

Sum of all (SRE property Current Loan Balance multiplied by the % Ownership)

From SRE page

People - Total Equity

Total Equity = Total Value - Total Debt

From SRE page

People - Cash Flow before Living Expenses

Sum of all the subtotals of all the income categories including the schedule C, schedule E, and K-1s, (there may be one or more subcategories under each category).

• From Cash Flow page

  • Using Proforma column values

• Schedule C are LLC businesses, Schedule E are income generating properties, and K-1s are partial ownership of corporations. So, for instance, a person may have 5 income-generating properties on their tax return. Each will have it’s own list of gross rents, interest expense, deprecation expense, amortization expense, and total expenses that will need to be subtotaled based on credits and debits.

People - Cash Flow available for debt service

cashFlowAvailableForDebtService=cashFlowBeforeLivingExpenses+ livingExpenses

(This is assumed to be a sum because the calculation for living expenses makes the value negative)

• From Cash Flow page

  • Using Proforma column values

• Living expenses is an estimate of the total expenses a borrower will incur in a year and thus will not be available for cash flow to pay off debt. Here is the calculation:

livingExpenses=IF((cashFlowBeforeLivingExpenses*%ofCF)>upperBound,-upperBound,(IF(cashFlowBeforeLivingExpenses*%ofCF<lowerBound,-lowerBound,-cashFlowBeforeLivingExpenses*%ofCF)))

We use the following hardcoded values for this calculation. If a bank wants to adjust, we can update the hard coded numbers for this specific bank

• %ofCF=.2

• lowerBound=24,000

• upperBound=100,000

People - Debt Service

personDebtService = revolvingDebtService + installmentDebtService + otherliabilityDebtService + realEstateDebtService

• From Cash Flow page

  • Using Proforma column values

• Revolving Debt Service = (revolvingMonthlyPaymentsSubtotal) * 12

•  Installment Debt Service = (installmentDebtMonthlyPaymentsSubtotal)*12

•  Other Liability Debt Service = (otherliabilityMonthlyPaymentsSubtotal)*12

•  Real Estate Debt Service = realEstateDebtService=sum of excessCashFlowOwned IF excessCashFlowOwned<0 to calculate the real estate debt, we need to take the debt service from the real estate schedule of excessCashFlowOwned IF the value is less than 0. The assumption is that if it’s positive, it’s an income generating property.

Company - Net Worth

Net Worth = Sum of Assets - Sum Liabilities

From Balance Sheet

• Using Proforma column values

Company - Cash/Liquidity

Same as the Cash amount

• Using Proforma column values

Company - DSCR

Cash Flow / Company Debt Service

The debt service is amounted to the Interest line item in Other Expenses (from the Income Statement)

• Using Proforma column values

Company - Cash Flow

Equal to NOI

(Total Income - Total Expenses) from Income Statement

• Using Proforma column values

Company - Assets

Sum of all Company Assets

= Total Current Assets + Total Non Current Assets

• Using Proforma column value

Company - Total Current Assets

= Cash + Accounts Receivable + Inventory + U.S. Government Obligations + Tax-exempt Securities + Other Current Assets

• Using Proforma column values

Company - Total Non Current Assets

= Loans to Partners/Shareholders + Mortgage and Real Estate Loans + Other Investments + Buildings and other Depreciable Assets + Depletable Assets + Land (net of any amortization) + Intangible Assets (amortization only) + Other Assets

• Using Proforma column values

Company - Liabilities

Sum of all Company Liabilities

= Total Current Liabilities + Total Long Term Liabilities

• Using Proforma column values

Company - Total Current Liabilities

= Accounts Payable + Mortgages, Notes, Bonds Payable in <1year + Other Current Liabilities

• Using Proforma column values

Company - Total Long Term Liabilities

= All Nonrecourse Loans + Loans from Partners/Shareholders + Mortgages, Notes, Bonds Payable in >1year + Other Long Term Liabilities

• Using Proforma column values

Company - Net Worth / Equity

Sum of all Equity line items

= Partners' capital accounts + Capital Stock: Common Stock + Additional paid-in capital + Retained Earnings + Retained earnings: Appropriated + Retained earnings: Unappropriated + Adjustments to shareholders' equity + Less cost of treasury stock

• Using Proforma column values

Company - Debt Service

Equal to “Interest” line item from Income Statement

• Using Proforma column values

Company - Net Cash Flow

Cash Flow - Debt Service

• Using Proforma column values

DCF - Net Present Value

NPV(Discount Rate, Sum of Cash Flows)