GETTING STARTED

Create a Folder on your computer and copy the spreadsheet template files Lender Software Pro v1.7.8.xlsx and Mortgage Sample v1.7.8.xlsx to this folder.

In our example, the folder we have used has the path:  C:\Users\Owner\Documents\Mortgage. You can create a folder anywhere on your computer with whatever name you want.

Tip: You can create multiple folders for different clients, as long as you follow the same steps for setting up each.

Also copy APR costs to include.pdf to this folder.

WARNING! –  ERRORS WITH COPY & PASTE

IMPORTANT: If you copy and paste from another spreadsheet you may encounter errors, as even if a cell looks similar, it might be a different data type. This is especially important for cells containing numbers.

Some merged cells will not allow you to directly copy from cells that are not merged. You will encounter this on the Setup worksheet and will need to manually type in the contents. There aren’t that many like this.

If you follow the steps below, you should not have a problem:

1.       Using your mouse

  1. On your spreadsheet with the cell you want to copy to Lender Software Pro, highlight the cell to copy. For example the Lender’s name.
  2. Right click on your mouse and select ‘Copy’.
  3. Go to your Lender Software Pro template and single click on the Lender’s name which in this example is Cell C8 on the Setup worksheet to select it.
  4. Then, right click on your mouse and select ‘Paste Special…’.How to right click and paste special
  5. Then, click on ‘Values’. This will paste the values only and not any formatting.How to select values and paste special

Or

2.       Using the Excel top menus:

  1. On your spreadsheet with the cell you want to copy to Lender Software Pro, highlight the cell to copy. For example the Lender’s name.
  2. Right click on your mouse and select ‘Copy’.
  3. Go to your Lender Software Pro template and single click on the Lender’s name which in this example is Cell C8 on the Setup worksheet to select it.
  4. Then, click on the down arrow on the Paste option and then click on ‘Paste Values’.

How to paste special using the menu bar

 

LENDER DETAILS

Open the Template Lender Software Pro v1.7.8.xlsx which should be in the folder you created and select the Setup worksheet:

Then enter your Lender details into the colored boxes at the top of the spreadsheet:

Enter your Lender Details into Lender Software Pro

IMPORTANT: Ensure you fill in all the colored boxes as they’re essential for payment coupons and letter templates. This will be your master template you create other files from, so add everything now and you won’t have to individually enter information later.

When you have filled in the lender details on your master template, save a copy in the same folder.

Change the file name using File > Save As > My Mortgage Template.xls (or whatever you want to call it).

When you are ready to create more files, simply open the My Mortgage Template.xlsx template and save as mortgage1.xlsx, mortgage2.xlsx etc.

TIP: You can call your mortgage files whatever you want, but something descriptive is easier. In our example, you could always call the file ‘Smith Investments LLC – $125k Jul 2012’.

BORROWER DETAILS

Open mortgage.xlsx which should be in the folder you created

Click on the Setup worksheet.

Tip: You will find a filled-in example in Mortgage Sample v1.7.8.xlsx

Enter the borrower name, address etc. into the light blue cells. It is very important to enter the cells correctly. Hover your mouse over the cells with a red corner in the top right to show helpful comments.

Here you enter the borrower details, their names, addresses, social security numbers etc.

WARNING: Be careful who has access to your borrowers’ social security numbers as this is confidential information by law.

You can alter the salutation for the letter templates in Cell C24. You may want to be formal and say ‘Dear Mr. Smith’ or perhaps ‘Dear John’.

Enter the company name if there is one.

Enter Borrower Details in Lender Software Pro

PROPERTY DETAILS

Enter the property address.

If you enter the property value in Cell C31, the Loan to Value is shown. You can update this at anytime by over-writing the date and changing the property value.

Enter the property details into Lender Software Pro

LOAN DETAILS

Enter loan details in the light blue cells only. Example below and in Mortgage Sample.xlsx.

Enter your loan details into Lender Software Pro

Enter the Date of loan, Amount of Loan, Annual Interest Rate, Days before you make a Late Charge, Bad Check Charge, Percentage late charge.

Loan amount 125,000.00.

The loan was originated on July 15, so there will be 17 days of pre-paid interest and a first payment date of September 1.

The annual interest rate is 7.00% and there is a 5% late charge on payments received after 10 days.

The loan is amortized over 120 months (10 years) but there is a balloon after 60 months.

This balloon is calculated to be 73,296.37.

IMPORTANT: Enter dates like 9/30/12 and they will appear as Sep-30-12 and enter amounts without “$” or “,” between 1,000s. Otherwise you may get errors.

AMORTIZATION

Enter the amortization period in Cell C36. This would be 360 for 30 yr. amortization, 180 for 15 yr. etc. and

enter 5000 for interest only mortgages.

If the loan is fully amortizing, there is no balloon. Then enter the same number in Cell C38 that you entered in Cell C36 for the amortization period.

BALLOON PAYMENT

Enter the months before any balloon payment (when the balance of the loan is due and payable) in Cell C38.

The balloon payment is automatically calculated for you.

You don’t need to calculate balloon payments, pre-paid interest or last payment dates. It is all done for you.

YOUR LOAN ORIGINATION DATE

The software is designed to automatically calculate pre-paid interest based on you receiving payments

on the 1st of the month, as is USA mortgage standard. Thus if you originate a mortgage on July 15, 2012,

the first payment is due on September 1, 2012 and you collect 17 days pre-paid interest at closing. See PRE-PAID INTEREST below.

Some private lenders originate a mortgage mid-month, and collect their payments on the SAME day the following month. If this is the case, you will need to override the payment start date in the orange background Cell I35.

If you wished to do this in this example, your payments would start on August 15. Usually this orange background cell will be empty.

POINTS AND OTHER FINANCE CHARGES

This sample mortgage has two points. If your mortgage has no points, just replace this with zero.

$1,000 in Other Finance Charges. Likewise replace with zero if there are no extra finance charges. Other finance charges are defined in law. See the APR help guide included. Copyright: The Compliance Group, Inc.

PREPAID INTEREST

The pre-paid interest is calculated for you automatically provided you have correctly entered the loan origination date.

Please be clear about this. If your loan date is Feb 1, then the first payment due is Mar 1 with no pre-paid interest.

But if your loan date is Feb 2, then the first payment is due April 1 and you should collect 27 days pre-paid interest.

Enter the date you receive this pre-paid interest (usually the day the loan closes) and the amount in Row 5 on the Record Payments worksheet. Or you might lend less money rather than collecting a check.

Either way the pre-paid interest should be shown on the HUD-1 closing statement page 2, line 901.

Line 901 says” Interest from         to           at             per day for             days.”

When you receive the pre-paid interest, enter the date received and the amount in the Yellow Cells, D5 and G5 on the Record Payments worksheet.

NO PRE-PAID INTEREST

The above pre-paid interest treatment is standard for USA lenders. However some lenders may have originated mortgages where the payments start exactly one month later and there is NO pre-paid interest.

Example: Mortgage funded July 15, 2012, first payment August 15, 2012.

To correctly handle this, just enter the first payment due date in the orange background Cell I35 on the Setup worksheet. This will now adjust every future payment due date.

DISCOUNTED MORTGAGES

If you bought the mortgage at a discount enter the price you paid in Cell I37 (in the Loan Details Section).

The Amount of Loan (Cell F33) and Number of Payments Left (Cell C38) will be that at the date you purchased the Note.

You will be able to see the monthly Capital Returned to You and Discount Earned in Columns AD and AE on the Record Payments worksheet, also in the year totals in the Year View worksheet.

ESCROWING FOR TAXES AND INSURANCE

You can escrow for taxes and insurance. Enter the escrow deposit and initial monthly escrow charge in lilac background Cells G41 and G42 on the Setup worksheet.

Enter any escrow payments or deposit into Lender Software Pro

If the Monthly Escrow payment changes later, enter the date and new amount in the Lilac Cells in the Setup worksheet.

Cells L42 to L49 and M42 to M49.

Enter any changes to escrow payments in Lender Software Pro

When you disburse money from the Escrow Account, enter the Date, Purpose and Amount in Columns L, M and N with a Lilac background on the Record Payments worksheet.

Enter extra info on type and amount of escrow disbursements