Debt Inputs Tab

Overview

The Debt Inputs tab allows the user to enter a total of three loans: Loan 1, Loan 2, and Loan 3 (Refinance). Loan 3 may be treated as a third loan or as a refinance of Loan 1 and/or Loan 2. Each loan has four sections that require user inputs: “General Inputs”, “Loan Funding”, “P&I Payment”, and “Cap-Ex Funding.” The Loan 3 (Refinance) has an additional section, entitled “Refinance Inputs” that is discussed in greater detail below in the Refinance section.

Article Contents

• Data Flow
• General Inputs
• Loan Funding
• Index Rate
• P&I Payment
• Cap Ex Funding
• Loan Summary (Information Fields)
• Amortization Schedule (Information Fields)
• Refinance (Loan 3)
• Debt Examples
  • Example 1: One Loan
  • Example 2: One Loan with Refinance
  • Example 3: Two Loans with Refinance
  • Example 4: One Loan with Cap-Ex Funding

Data Flow

The Debt Inputs tab receives cash flow and valuation data from the Unleveraged Cash Flow tab. The input fields on the Debt Inputs tab are inputs to the Debt Input tab’s Amortization Schedule. The Amortization Schedule feeds into the Monthly CF (Lev) tab. The Monthly CF (Lev) tab feeds the Leveraged Cash Flow tab.

General Inputs

Input Field Category /  Name	Input Options	Description	Impact on Model
Loan Type	Drop-down	None Existing Note – assumes loan existed prior to model begin. User enters all assumptions (e.g. balance, term) as of model begin date. New Funding – New loan that is assumed to fund either on or after the model begin date. Allows for the loan to be funded at a % of Purchase Price or a defined amount.	The selection drives which subsequent fields are available for input on the Debt Inputs tab.
Loan Name	Text	User-defined Loan Name.  Can be left bank	Appears on Loan 1 header and on Loan 3 as a drop-down selection for Refinance
Funding Month (BOP)	Integer	Only available when Loan Type = New Funding. Enter the month of the analysis term in which the loan will be funded. Example: enter “13” for Month 13 of the loan. Specific dates are not permitted.	Establishes the start period for the Amortization Schedule
Remaining Term (Months) / Term (Months)	Integer	If Loan Type = Existing Note, enter # months remaining in the Existing Note’s term If Loan Type = New Funding, enter # months in the loan term. If left blank, the default value is 240 months.	Establishes the end period for the Amortization Schedule box (Funding Month + Term defines Amortization Schedule)
Subordination	Drop Down	Senior – Debt service coverage for Senior debt is broken out on the Leveraged Cash Flow tab. Subordinated – an additional debt service coverage ratio for all debt (senior and subordinated) is also calculated on the Leveraged Cash Flow tab.	Both Senior and Subordinated Debt flow to the “Debt Summary” on the Annual Leveraged Cash Flow.
Rate Type	Drop Down	Fixed Rate – Loan payments based on a fixed interest rate Floating Rate – Loan payments based on a fixed spread over a floating index rate Note: The Spread and Index Rate inputs are discussed in more detail in the “P&I Payment” section below.	Affects fields available in P&I Payment section and impacts the Amortization Schedule
Funding Method	Drop Down	Funding Method can be Amount or % of Purchase Price. Only available when Loan Type = New Funding.	Effects fields available in the Loan Funding section.
Fund Cap-Ex	Drop Down	Determine whether or not to fund capital expenditures as part of the loan draw.	Cap Ex will be funded on a monthly basis. Effects fields available in the Cap Ex Funding section.

Loan Funding

In the Loan Funding box, you enter basic loan information for either a new or an existing loan. The fields may vary in name or be inactive depending upon the Funding Method used in the General Inputs box as noted in blue under the Input Field column.

Input Field Category /  Name	Input Options	Description	Impact on Model
Current Balance / Funding Amount Active when Funding Method = Amount	$ Amount	If Loan Type = Existing Note, field is labeled “Current Balance” and holds the balance as of the model begin date. If Loan Type = New Funding, field is labeled “Funding Amount” and holds the new loan amount.	Defines the beginning balance for the Amortization Schedule
Purchase Price Active when Funding Method = % of Purchase Price	n/a	Equal to the Purchase Price on the Annual Leveraged Summary worksheet. No user input is allowed.
% Financed Active when Funding Method = % of Purchase Price	% Entered as xx	The percentage of the Purchase Price that the loan will fund. Displays no decimals but will accept decimal entry.	Used to calculate Funding Amount
Funding Amount Active when Funding Method = % of Purchase Price	n/a	Equal to the Purchase Price x % Financed. No user input is allowed.
Loan Fee %	% Entered as xx.xx	This fee is calculated as a percent of the initial loan balance and paid in the initial month.	Adds Loan Fee amount to Amortization Schedule
Prepayment Fee %	% Entered as xx.xx	This fee is paid in the month that the loan is either a) refinanced or b) retired due to a sale of the asset and calculated as a percent of the loan balance at that time.	Adds Prepayment amount to Amortization Schedule

Index Rate

To the left of the Amortization Schedule, there is a box labelled “Index Rate.” The Index Rate box is used for entering a base index interest rate. For loans that use a “Floating” rate type, the interest rate applied to the loan will be a function of the rates that are modeled in this box.

An Index Rate may be entered for up to 360 months of the analysis period and must be entered as an annual percentage rate. In the Update Index column, enter an initial index interest rate in the month that New Funding begins. For Existing Notes, enter the initial index rate in Month 1. If future index rates are projected to rise or fall, enter the adjusted interest rate in the month of the adjustments.

P&I Payment

In the P&I Payment section you define the interest rate, interest calculation methodology, and amortization period. The fields vary depending upon the Rate Type used in the General Inputs box.

Input Field Category /  Name	Input Options	Description	Impact on Model
Index Rate at Funding [Information Field]	n/a	Equal to the Index Rate in the month that the loan funds as shown in the Index Rate box. No user input is allowed.
Rate Active when Rate Type = Fixed Rate	% Entered as xx.xx	Enter the loan’s interest rate	Amortization Schedule
Spread Active when Rate Type = Floating Rate	% Entered as xx.xx	Enter the interest rate spread. The total interest rate paid is equal to the sum of the Index Rate and the Spread.	Amortization Schedule
Interest Calc	Drop Down	Five options for the monthly calculation of interest: 1) “30/360” (30 days/mo, 360 days/yr), 2) “Actual/360” (actual #days/mo, 360 days/yr), 3) “Actual/365” (actual #days/mo, 365 days/yr), 4) “Coupon (1 yr)” (compounds once a year), and 5) “Coupon (6 months)” (compounds every 6 months).	Amortization Schedule
IO Periods (Months)	Integer	Enter the number of interest only months in this field. Note: When Fund Cap-Ex = Yes, the IO Period should be used in conjunction with the Draw Period (it is assumed that amortization would not begin until after the Draw period). The IO Period should be greater than or equal to the Draw Period.	Amortization Schedule
Remaining Amort (Months) / Amort (Months)	Integer	If Loan Type = Existing Note, field label = Remaining Amort. If Loan Type = New Funding, field label = Amort (Months). Enter the amortization period for a new loan or the remaining amortization period for an existing loan. The amortization period will begin on the first month after the IO Period ends.	Amortization Schedule
Fixed P&I Payment	$ Amount	Fixed P&I Payment is only available when the “IO Period” and “Amort” fields are not in use. Enter total principal (P) and interest (I) payment that will be automatically allocated in the Amortization Schedule based on the other debt assumptions.	Amortization Schedule

Cap Ex Funding

You may choose to fund capital expenditures as part of the loan draw. Please note that the input fields described below are available only if “Yes” is chosen for the Fund Cap-Ex field in the General Inputs section.

Input Field Category /  Name	Input Options	Description	Impact on Model
TI%	% Entered as xx.xx	Enter % of tenant improvements to fund.	Amortization Schedule
LC%	% Entered as xx.xx	Enter % of leasing commissions to fund.	Amortization Schedule
CapEx%	% Entered as xx.xx	Enter % of capital expenditures to fund.	Amortization Schedule
Draw Period (Months)	Integer	Enter number of months to fund the selected tenant improvements, leasing commissions, and capital expenditures. Note: The Draw Period should be used in conjunction with the IO Period (it is assumed that amortization would not begin until after the Draw period). The IO Period should be greater than or equal to the Draw Period.	Amortization Schedule

Note: If Fund Cap-Ex = “Yes” then the desired percentage of TIs, LCs, and CapEx to be funded will be added to the existing loan balance on a monthly basis throughout the Draw Period.

For floating rate loans, the interest rate used will be the sum of the Index Rate at Funding and the Spread found in the “Rate” box.

Loan Summary [Information-Only Fields]

While none of the fields in this section require user input, they do provide valuable information about the loan.

Input Field Category /  Name	Input Options	Description	Impact on Model
Start Date	n/a	Equal to “Month 1” for pre-existing loans or the Funding Month for new loans.
End Cap-Ex	n/a	If Fund Cap Ex is set to “Yes”, equal to the month Cap-Ex draws end.
Amort Begin	n/a	Equal to the month that amortization begins. Dependent on the Funding Month and IO Period.
Maturity/Prepayment	n/a	Equal to the earlier of the loan maturity date or the month the loan is prepaid. Aligns to hold period / sale date if loan extends beyond hold period.
Initial Balance	n/a	Equal to the Funding Amount for New Funding or the Current Balance for an Existing Note.
Total Funded Cap-Ex	n/a	If Fund Cap-Ex is set to “Yes”, equal to the total Cap-Ex funded, based on the Draw Period in the Cap-Ex Funding section.
Total Funding	n/a	Equal to the Funding Amount for New Funding or the Current Balance for an Existing Note
Ending Balance	n/a	Equal to the loan balance in the Maturity / Prepayment month.
Interest Rate/ Initial Interest Rate	n/a	For Fixed Rate loans, equal to the Rate in the P & I Payment box. For Floating Rate loans, equal to the sum of the Index Rate at Funding and the Spread.
Interest on Total Balance	n/a	For Fixed Rate loans only, equal to the interest payment in the month that amortization begins.
Principal Payment	n/a	For Fixed Rate loans only, equal to the principal payment in the month that amortization begins
Total P&!	n/a	Equal to the total interest and principal payment in the month that amortization begins.

Amortization Schedule [Information-Only Fields]

The Amortization Schedule shows calculated monthly loan data based on the input fields discussed in the prior section for the following categories:

Refinance (Loan 3)

In addition to the Loan Types available in Loans 1 and 2, Loan 3 contains an additional Loan Type called “Refinance.” Loan 3 may be used as either a third loan or to refinance Loan 1 and/or Loan 2. The “Refinance” option is used to signal the refinancing of prior loans, and contains the following inputs:

Note: the Refinance Inputs box is available only when “Refinance” is selected as the Loan Type for Loan 3. Loan 1 and Loan 2 inputs are available within the Refinance Inputs box only if loan terms were previously modeled at the base loan level and the Refinance “Funding Month” is at a date later than the funding month of the initial loan(s). All other boxes / fields for Loan 3 function as described previously.

Refinance Inputs

Input Field Category /  Name	Input Options	Description	Impact on Model
LOAN 1 Balance / LOAN 2 Balance [Information Field]	n/a	Equal to the base loan’s Ending Balance in the month prior to the Refinance loan’s Funding Month. No user input is allowed.
Refinance LOAN 1? / Refinance LOAN 2?	Drop down	Yes: Refinances the base loan. No: Leaves the base loan terms as-is.	“Yes” ends the base loan amortization schedule as of the Loan 3 Refinance funding month
Total Balance Prepaid	n/a	Sum of Loan 1 and Loan 2 balances to be refinanced. No user input is allowed.
Fund This Balance?	Drop down	Yes: Funds the Total Balance Prepaid No: Allows user to enter a different amount into the Funding Amount field in Loan Funding box. See Note below.	Determines the beginning balance/Funding Amount for the loan.

Note: If the user chooses to fund a different loan amount, the Funding Amount field will appear in the Loan Funding box. The new Funding Amount should be entered here. If desired, enter a Loan Fee % and/or a Prepayment Fee % in the appropriate field.

Debt Examples

EXAMPLE 1: One Loan

Loan 1 is a new 20-year senior amortizing loan with a 75% LTV, 30-year amortization period, and 6% interest.

Key Inputs	Loan 1
Loan Type	New
Funding Month (BOP)	Month 1
Term (Months)	240
% Financed	75
Interest Calc	Actual/360
Subordination	Senior
Rate Type	Fixed
Funding Method	% of Purchase Price
Fund Cap-Ex	No
Rate	6
Amort (Months)	360

EXAMPLE 2: One Loan with Refinance

Loan 1 is an existing floating rate loan with a current balance of $6,000,000 and a 2% prepayment fee. Interest is based upon the index rate plus a 1% spread. The existing loan balance is refinanced after 12 months by a new 20-year senior amortizing loan with a 30-year amortization period, a 6% interest rate and a 2% loan fee.

Key Inputs	Loan 1	Refinance
Loan Type	Existing Note (Balance: $6M)	Refinance
Funding Month (BOP)	n/a	Month 13
Term (Months)	240 Months (default)	240 Months (default)
Subordination	Senior	Senior
Rate Type	Floating Rate	Fixed
Fund Cap-Ex	No	No
Current Balance	6,000,000	6,000,000
Loan Fee %	0%	2%
Prepayment Fee %	2%	0%
Index Rate at Funding (informational)	6%	7%
Rate	n/a	6%
Spread	1%	n/a
Interest Calc	30/360	30/360
IO Period (Months)	240	0 [blank]
Remaining Amort	0	360 Months

EXAMPLE 3: Two Loans with Refinance

Loan 1 is an existing floating rate, interest only loan with a current balance of $6,000,000 and a 2% prepayment fee. There are 3 years remaining on the loan term. Interest is based upon the index rate plus a 1% spread. Loan 2 is an existing fixed rate loan with a current balance of $2,000,000 and a 2% Prepayment Fee. There are 15 years remaining on the loan term. Loan 1 is refinanced after 12 months by a new 20-year senior amortizing loan with a 30-year amortization period, a 6% interest rate and a 2% loan fee.

Key Inputs	Loan 1	Loan 2	Refinance
Loan Type	Existing Note	Existing Note	Refinance (Loan 1)
Funding Month (BOP)	n/a	n/a	Month 13
Term (Months)	36 Months	180 Months	240 Months (default)
Subordination	Senior	Senior	Senior
Rate Type	Floating Rate	Fixed Rate	Fixed Rate
Current Balance / Funding Amount	6,000,000	2,000,000	6,000,000
Loan Fee %	0%	0%	2%
Prepayment Fee %	2%	2%	0%
Rate at Funding	Index Rate = 6% in Mo 1; Incr 0.25% every 3 mos.	6%	6%
Spread	1%	n/a	n/a
Interest Calc	30/360	30/360	30/360
IO Period (Months)	36	0 [blank]	0 [blank]
Remaining Amort	0	300 Months	360 Months

EXAMPLE 4: One Loan with Cap-Ex Funding

Loan 1 is a new $6,000,000 twenty-year senior amortizing loan with a 30-year amortization period and a 6 % interest rate. 100% of the TI, LC, and Cap Ex amounts will be funded for the first 3 years of the loan.

Key Inputs	Loan 1
Loan Type	New Funding
Term (Months)	240 Months (default)
Subordination	Senior
Rate Type	Fixed Rate
Current Balance / Funding Amount	6,000,000
Rate	6%
Interest Calc	30/360
IO Period (Months)	36 Months
Amort (Months)	360
Fund Cap-Ex	Yes
TI %	100%
LC %	100%
Other Capital %	100%
Draw Period	36 Months

Debt Inputs Conclusion

There are many ways to structure debt financing. The UW Debt Inputs tab can be used to evaluate different debt scenarios to find the best solution for your transaction. Pay close attention to all inputs to ensure the proper results.