Pages Layout

Follow

Cover Page

The first tab in the Excel Worksheet is the Cover Page.  This shows the detailed Borrower information that was uploaded by the user.  LoanBeam also identifies business documents that are missing from the Borrower.  Based on current and prior year tax data, LoanBeam shows what information the Borrower still needs to provide.

Summary Sheet

The qualifying income from all the sheets in the workbook is presented on the “Summary Sheet”. The Income Category column shows the description of the line item whereas the monthly amount column populates the amount from the individual worksheets.

Wages Input Forms

The Wages Borrower and Wages Co-Borrower worksheets are similar. But, on the “Wages Borrower” sheet, input the wages of borrower and input the wages of co-borrower on the “Wages Co-Borrower” sheet.

All the fields on these sheets are manual input fields. Manually enter the $ amounts and # of units for wages depending upon the salary types (Hourly, Weekly, Bi-weekly, Bi-monthly, Monthly or Annually). The lower of the calculated $ per month amounts ($/hr and $YTD) is considered.


This amount is used in the calculation of “Wage Earner Total (Monthly)” field in the Two Year Average Income Calculations section at the bottom of the sheet.

 

“Two-Year Average Income Calculation” can also be obtained from the Wages Input Form.

 

Enter the amounts in the aqua shaded fields for the respective years. The total monthly average and the “Wage Earner Total (Monthly)” are auto-calculated. You can also manually override the calculated monthly wage earner amount.

 

Comments if any should be included in the space provided for writing the comments. The amount from the “Wage Earner Total (Monthly)” field flows to the main worksheet and the annual qualifying income is calculated from this amount on the main worksheet.

Non-SE Income Calculation

The Non-SE Income earned by the borrower such as royalty income, wages, etc. can be included on this worksheet. All the fields on this worksheet are manual input fields.

On this worksheet, depending upon the salary type enter the amounts from the borrower’s pay stubs.

Total qualifying income is calculated from the data input here.

Main Sheet - SAM

The Main Sheet captures cash flow information from the Borrower’s tax returns.

While numbers from Form 1040 Page 1, Schedule A, Form 2106 etc., are populated directly on the Main Sheet, information from multi-instance forms like Schedule C, Rental, Partnership etc., are broken-down into details on their own individual tabs and summary information is passed back into the Main Sheet.

 

Example of a Schedule C (multi-instance form)

Inside the Main Sheet, there are three additional columns for your Cash Flow Analysis – Qualifying Income (Annual), Qualifying Income (Monthly) and 2 Yr. Avg. or CY.

“Qualifying Income (Annual)”compares the Current Year (CY) income to Prior Year (PY) and arrives at the most conservative value based on the trend.  For example, if CY is lower than PY, then LoanBeam will use CY.  If CY is greater than PY, then LoanBeam will use the 24 month average (e.g. CY + PY divided by 2.). “Qualifying Income (Monthly)” is Qualifying Income Annual divided by 12.  The right hand column displays which approach was used for Qualifying Income.

Schedule C

This form populates from Schedule C form of the Tax Returns. If there are multiple entities in the Tax Return for this Form then LoanBeam creates repeatable groups to represent each entity individually on the same Schedule C worksheet.


Verify the amounts populated from the Borrower’s tax returns.


In case the amounts need to be changed, the Manual Override columns provide space to enter the corrections. The total annual and monthly QI figures will change accordingly.

There is space in the Notes section to comment on the reason for manual override or any other changes made in the worksheet. To exclude the income from the income calculation type “X” in the box provided as shown in the image.




If there is a declining trend in the income for a particular entity then the same is indicated as a note for that entity highlighted in “Red”.

If you manually input the profit and loss statement amounts then the amounts from the tax return are not considered in the QI calculations.


If you do not want to input these amounts manually then select “No”. The fields will be grayed out and total annual and monthly qualifying income are calculated from the amounts from the borrower’s tax returns.




The calculated total from all the entities on this form is represented at the top of the sheet and this amount flows to the Main Worksheet.

Schedule E

This sheet populates the amounts from the Schedule E Rental and Royalty Income and Loss of the Tax Return.  Repeatable groups are automatically created if there are multiple entities.

The Rental Income (Loss) amounts from the borrower’s/co-borrower’s tax returns are populated here.

The fields for Depreciation expense or depletion, HOA Dues, Amortization and miscellaneous non-recurring expense are manually input fields. Input the amounts directly in these fields.

The amounts from the monthly mortgage statements, tax bills, insurance statements and HOA statements can be manually entered. If participation percentage is less than 100%, then enter the participation percentage manually.


Royalty Income (Loss) Calculation is also obtained from this sheet.

Repeatable groups are automatically created if there is more than one entity.

Selecting “No” from the dropdown for “Profit-And-Loss Statement Available” will calculate the QI using the amounts populated from the tax returns.

The total calculated from all the entities on this sheet flows to the top of this sheet. This calculated total for Schedule E which flows to the main worksheets is used in the QI

Schedule F

The Schedule F worksheet helps in the analysis of borrower’s Farm Income. All the data from the Tax Return that helps in the QI Analysis is captured and populated in the CY and PY columns.


 

Verify these amounts with the amounts on the Tax Return. If any of the amounts do not match or you want to manually override the amounts, then use the columns provided for manual override.

LoanBeam automatically calculates the Schedule F Total Net Farm Income, Qualifying Income - Annual and Monthly. If there is a declining trend in the income then a note will be displayed.

To exclude the income from calculation type “X” in the cell provided for “X” to exclude income from calculation.

To calculate the QI using the amounts populated from the tax returns, select “No” from the drop down for “Profit-And-Loss Statement Available”.

The calculated QI for all the entities is represented at the top of the sheet and this amount flows to the main worksheet.

Trust

The Trust Income that is used in the QI Analysis flows to this sheet from the Tax Return.

The Trust Name, EIN # and Beneficiary Name are also populated on this sheet. Verify all the figures populated from the Tax Return. To manually override use the columns provided for manual overrides. Overriding the amounts directly in the CY and PY columns will corrupt the formulas in those cells.  Include notes in the notes section for such manual overrides.




If required, make adjustments to the income in the cell for “Other Adjustments” (shaded in aqua blue)


Type “X” to exclude income from the calculation in the cell provided for the same.

The Total Income (Schedule K-1) is analyzed and is displayed at the top of the sheet. This amount flows to the main worksheet.

Partnership - Form 1065

The Partnership 1065 worksheet populates the borrower’s Income from K1s and Adjustments to the income from the Partnership 1065 form of the Tax Return. Partnership name and Partner’s name also populate for each entity appearing on this sheet.



LoanBeam automatically chooses the best suited method of calculation – Traditional Method or Distributed Method. You can change the selection from the dropdown. If you choose to change the original method a message indicating the same will be displayed on the worksheet.



The fields related to the method that is unselected will be grayed out and will not be considered in the calculation.



Verify all the figures populated from the Tax Return.

The ownership percentage is multiplied and the QI calculations are generated. While Capital Ending is the default value, the cell offers a drop down to select other values from the K-1.



To calculate the QI using the amounts populated from the tax returns, select “No” from the dropdown for “Profit-And-Loss Statement Available”.



The total from this sheet is presented at the top of the sheet and the amount for Partnership Total Income flows to the main sheet.

S Corporation - Form 1120s

S Corporation income is automatically calculated on this sheet from Form 1120S.



LoanBeam automatically chooses the best suited method of calculation – Traditional Method or Distributed Method. You can change the selection from the dropdown. If you choose to change the original method a message indicating that the original method has been overridden will be displayed on the worksheet.



The fields related to the method that is unselected will be grayed out and will not be considered in the calculation.



Verify all the figures populated from the Tax Return. If the amounts do not match, then use the Manual Override columns and provide a reason for the changes in the “Notes” section.

Manually input the Wages paid from S Corporation to the Borrower in the cell highlighted in aqua blue.


Make other adjustments to the business cash flow in the cell for “Other Adjustments”.



To calculate the QI using the amounts populated from the tax returns, select “No” from the dropdown for “Profit-And-Loss Statement Available”.



The calculated QI for each entity is presented on the sheet along with the total calculated QI for all the entities flows to the top of the sheet. The S. Corporation total income flows to the main worksheet.


Corporation

The CY and PY amounts (if available) from the Tax Return are used in the Qualifying Income analysis on this worksheet.



Verify all the amounts populated from the Tax Return. If any of the amounts need to be changed then it should be done in the columns provided for manual override.

Enter the CY and PY amounts for Line 26 “Amortization” and “Dividends Paid to Borrower: Form 1040, Schedule 8, Line 5” from the Tax Returns manually in the cells provided. The “Wages” amount and the “Multiplied by Ownership Percentage” fields are also manually input fields. Other adjustments can also be made here.




Once the ownership percentage is entered the QI calculation is done depending upon the ownership percentage.




The amount for Line 17 “Mortgages or Notes Payable in Less than 1 Year” is selected to be recurring if it is available for both years and is considered in the QI calculation. You can select this amount to be non-recurring.  Upon selecting the amount as non-recurring, it will be grayed out and excluded from QI calculations.




To calculate the QI using the amounts populated from the tax returns, select “No” from the dropdown for “Profit-And-Loss Statement Available”.




The total QI analysis is done based on these figures. If there are multiple entities then total from all these fields is represented at the top of the sheet. The amount represented at the top of the sheet flows to the main Worksheet.

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments