Loan Calculator Interest Included - Excel

Document Sample
Loan Calculator Interest Included - Excel Powered By Docstoc
					DSR / NSR Calculator                                                                                                                  18/05/11 5:54 PM
Version 07 March 2011                              v1.0

Underwriter's Name
                                                                                                                                         Cell Index
Insert the relevant details into blank fields in yellow cells                                                                   Yellow shaded cells require input
Show split loan amounts in correct fields                                                                               White shaded cells derived from formulas
There must be at least one borrower                                                                                  Blue shaded cells applies appropriate scales
In the "Entity" field of DSR Calculation for Individual applicants,
Insert "B" for borrowers who are not mortgagors; if applicable for investment purposes, "B" will receive
apportioned deductible interest but no rent income is approtioned                                                           All loan purposes involving Investment properties will require
Insert "BR" for borrowers who are mortgagors; If applicable for investment purposes, "BR" will receive                      weekly rental income in the appropriate Rent p.w. field
apportioned rent income and deductible interest
Insert "G" for Guarantor and no rent income or deductible interest is apportioned                                           Note: if the borrower combination is a company and individual
Insert "GR" for guarantors who are mortgagors to receive apportioned rent income but no deductible                          person/s, please refer the loan directly to the RESIMAC Credit
interest                                                                                                                    Department


1. LOAN DETAILS

Application Name / Loan No.

Number of Adults
Number of Couples
Number of Dependent Children
                                                                                                                                       Security Position
Loan Amount - Owner Occupied                                                                                                     Value - Owner Occupied Property
Loan Amount - Investment                                                                                                           Value - Investment Property/ies
Total Loan Amount Sought                     $             -                                                                                  Total Security Value $                         -
Term of Loan (No. of Years)                                                                         0                             Loan to Valuation Ratio (LVR)                #DIV/0!
Interest Rate - Actual                                                                        0.0000%
Mortgage Insurer
Interest Rate - Benchmark                        #VALUE!                                #VALUE!                                                 Existing Home Loan
Loan Repayment p.m. - Loaded                     #VALUE!                   Actual P&I Repayment p.m.         #NUM!                       Existing Investment Loans


2. DSR CALCULATION
                                                                                                        Taxable Income         Tax Applied                                 Net Income
                                                                   Apportioned Rent Deductible Interest
                                                                                                          Business Net       (Medicare Levy       Social Security    Business Net Profit After
                                     Entity Gross Income p.a.      to Borrowers (to    Business
                                                                                                        Profit Before Tax       inclusive)           Income             Tax (NPAT) plus
                                                                      total 80%)       Addbacks
                                                                                                             (NPBT)           Business Tax                                 Addbacks
Individual Applicant 1                                             $             -  $                - $               -    $              -                         $                     -
Individual Applicant 2                                             $             -  $                - $               -    $              -                         $                     -
Individual Applicant 3                                             $             -  $                - $               -    $              -                         $                     -
Individual Applicant 4                                             $             -  $                - $               -    $              -                         $                     -
Totals for Individuals                       $             -       $             -  $              -    $              -    $              -     $             -     $                     -
Business Income                                                                                                             $              -                         $                     -

            Rent Details                                                                                                           Financial Expenses p.a.
Rent p.w. - New Investment Property                                                                                                                    This Loan              #VALUE!
Rent p.w. - Other Investment Securities                                                                                       Other Mortgages - Owner Occupied
Rent p.w. - Existing Securities                                                                                                     Other Mortgages - Investment
Total Rent p.w.                              $             -                                                                                      Personal Loans
Total Rent p.a.                              $             -                                                                Credit Cards Limit                    $                          -
80% of Total Rent p.a.                       $             -                                                                                               Others
Total Incomes (Individual Gross,                                                                                                        Total Financial Expense               #VALUE!
Allowable Social Security and                $             -
Business NPAT/Addbacks )                                                                                             Less 80% Rental Income                          $                       -
Debt Service Ratio (DSR)                         #VALUE!                                                              Net Financial Expense                                   #VALUE!



3. NSR CALCULATION

Individual Gross Income                      $             -
Rental Income @80%)                          $             -
Deductible Interest                               #NUM!
Taxable Income                               $                 -
Tax Applicable                               $             -
Net Cash after Tax                           $             -
Business NPAT plus Addbacks                  $             -                                                                                                         $                       -
Social Security Allowances                   $             -
Living Expenses (as per scales)              $             -
                                                                                                                            NOTE: This servicing calculator was prepared as a guide for loan
Net Disposable Income                        $             -
                                                                                                                            assessment. If DSR/NSR results are marginal, other mitigating
Surplus Cash after Financial Expense             #VALUE!
                                                                                                                            factors may be considered. Please provide a detailed explanation
                                                                                                                            in the "Comments" section.
Net Service Ratio (NSR)                          #VALUE!           Minimum NSR = 1.00



4. COMMENTS




Underwriter's Signature




                                                                                                                                                                                                 1


                                                                                                  3c6e4003-13ef-4e09-ac5a-d6f143950cbb.xls
Living Expenses (Annual)          Poverty Line Australia:        September Quarter 2010

Each Independent Adult            $      14,976
Each Couple                       $      21,772

No of Dependants          0                   0
                          1       $       5,292
                          2       $      10,584
                          3       $      15,876
                          4       $      21,168
                          5       $      26,460
                          6       $      31,752
                          7       $      37,044
                          8       $      42,336
                          9       $      47,628
                         10       $      52,920
                         11       $      58,212                                  LMI Benchmark Servicing Interest Rate
                         12       $      63,504                               Select Mortgage InsurerRate       Effective Date
                         13       $      68,796                               Genworth              9.29%       01/01/2011
                         14       $      74,088                               QBE LMI               9.30%       20/12/2010
                         15       $      79,380                               Other (loading)       2.00%


Credit card Assessment                    3.00%


                         Tax Rates (with Medicare levy):                                  Tax Rates from 01/07/10
                   Tax Boundary        Tax Due       Excess %                  Tax Boundary          Tax Due        Excess %
                         0                    0           1.5%                        0                     0              0%
                      $6,000               $90           16.5%                     $6,000                 $0            15.0%
                      $37,000           $5,205           31.5%                    $37,000             $4,650            30.0%
                      $80,000          $18,750           38.5%                    $80,000            $17,550            37.0%
                     $180,000          $57,250           46.5%                   $180,000            $54,550            45.0%
        Company Net Profit adjusted where loan applied for will be paid for by company
                                  (i.e. company borrower)

Calculate the interest payable on this loan using the 'actual' interest rate. Enter this as a negative
figure on the Financial Expenses 'Others' field.

Include this interest figure as a new expense to the company by taking the amount away from the
current Net Profit of the company.

Where this loan will pay out a previous debt of the companies, add back the interest that was included
as an expense in the company financials of this debt.

Where this loan is to purchase a new property that will be rented, calculate 80% of the proposed rent
and add this to the Net Profit figure. Where this is done do not include that rent under Rent Details.

Where income of the directors (guarantors) is being used in the serviceability calculation all their
expenses also need to be included.

				
DOCUMENT INFO
Description: Loan Calculator Interest Included document sample